Migrate to MS SQL database

[Anonymous]

Hello - Following the steps in: https://help.talend.com/reader/iI9xIGH2NZKUfs0D0thQpA/MEOdB_xT868zSMBXx34mlA (Migrating database X to Y) for migrating from the H2 database to a MSSQL database gives me the results: '{"error":"unknown url type","returnCode":1}' I am using the command: ./MetaServletCaller.sh --tac-url https://kp1.full.dns:8090/org.talend.administrator --json-params='{"actionName":"migrateDatabase","dbConfigPassword":"PASS","mode":"synchronous","sourcePasswd":"PASSWORD","sourceUrl":"jdbc:h2:/pss/tomcat-analytics/webapps/org.talend.administrator/WEB-INF/database/talend_administrator","sourceUser":"USER","targetPasswd":"OTHERPASS","targetUrl":"jdbc:sqlserver://sql.full.dns:1433;databaseName=tac_security","targetUser":"OTHERUSER"}'

I am using Talend 6.4.1, and Tomcat 8.0.47

I double checked that the MSSQL database drivers were located in the correct locations, restarted everything, rebuilt everything, and put the MSSQL drivers in every place I could think of throughout my tomcat installation. I also tried the targetUri: 'jdbc:sqlserver://sql.full.dns:1433/tac_security' for good measure and no luck.

So I switched to the jTDS driver and initially there was an error about it not being installed so I downloaded the driver and placed it in the endorsed path per the Talend docs, restarted tomcat and I can preform the database migration with that driver. 

My question becomes, why doesn't the MSSQL driver work even though it is listed in the compatibility docs as working for this, or what is wrong with my 'url'? Management would prefer to use the official Microsoft java drivers so I am kind of stuck here since it is listed in the docs as working. Any help is greatly appreciated, thanks!

[Anonymous]

Hi kpeasepss,

I have faced this issue as well. You will have to place the drivers at two locations and then restart TAC 

1) <root path>/tac/apache-tomcat/endrosed and

2)<root path>\tac\apache-tomcat\webapps\org.talend.administrator\WEB-INF\lib 

or as an alternative run the command as follows

<tac url>?<encoded command>

example:

http://localhost:8080/org.talend.administrator/metaServlet?eyJhY3Rpb25OYW1lIjoibWlncmF0ZURhdGFiYXNlIiwiZGJDb25maWdQYXNzd29yZCI6ImFkbWluIiwibW9kZSI6InN5bmNocm9ub3VzIiwic291cmNlVXJsIjogImpkYmM6aDI6QzovVGFsZW5kLzYuNS4xL3RhYy9hcGFjaGUtdG9tY2F0L3dlYmFwcHMvb3JnLnRhbGVuZC5hZG1pbmlzdHJhdG9yL1dFQi1JTkYvZGF0YWJhc2UvdGFsZW5kX2FkbWluaXN0cmF0b3IiLCJzb3VyY2VVc2VyIjoidGlzYWRtaW4iLCJzb3VyY2VQYXNzd2QiOiJ0aXNhZG1pbiIsInRhcmdldFBhc3N3ZCI6ImFkbWluIiwidGFyZ2V0VXJsIjoiamRiYzpteXNxbDovL2xvY2FsaG9zdDozMzA2L3RhbGVuZF90YWMiLCJ0YXJnZXRVc2VyIjoicm9vdCJ9 

This worked for me. please try it out and let me know.

Thanks,

Rekha

[Anonymous · Accepted solution]

Rekha - Thanks for your advice. However, that metaServlet is for MySQL not MSSQL, after searching through the forms for a few days I found this article: 

https://community.talend.com/t5/Migration-Configuration-and/What-is-the-supported-Microsoft-SQL-Serv.... Which states that you HAVE to use the http://jtds.sourceforge.net driver for the Talend Administration Center database. I tried a few things like replacing the driver path throughout my install and at best I was only able to use the Microsoft distributed driver with MANY errors during connections. So I am going to mark this one as solved as in you can't use the Microsoft driver and you have to use the one provided by http://jtds.sourceforge.net. Hopefully this helps someone else in my shoes.