topic Re: MS SQL Server DB Mirror in Talend Studio

MS SQL Server DB Mirror

Anonymous — Mon, 29 Jul 2013 19:12:54 GMT

I'm attempting to write jobs against the new high available database using database mirroring. I have the additional JDBC parameters set to failoverPartner={failoverPartner}
The job works when the principal is server_0, but when a failover is initiated and server_1 becomes the principal, there is a driver exception. The failover has been tested via SQL Management Studio and client applications using the SQLCLI and .NET Framework Data Provider.
How can this be resolved using the jtds driver? If not, what is the work around?
MS Server 2008 R2 (10.50.4000)
TPEI 5.2.1
Exception in component tMSSqlConnection_1
java.sql.SQLException: Cannot open database "{db_name}" requested by the login. The login failed.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:603)
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:345)
at net.sourceforge.jtds.jdbc.ConnectionJDBC3.<init>(ConnectionJDBC3.java:50)
at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)
at java.sql.DriverManager.getConnection(DriverManager.java:582)
at java.sql.DriverManager.getConnection(DriverManager.java:185)

Re: MS SQL Server DB Mirror

Anonymous — Wed, 31 Jul 2013 13:54:18 GMT

java.sql.SQLException: Cannot open database "{db_name}" requested by the login. The login failed.

Take a look at a similar topic, and try the solutions mentioned in that article.
Shong

Re: MS SQL Server DB Mirror

Anonymous — Thu, 01 Aug 2013 17:34:15 GMT

Database mirroring has been available since SQL Server 2005 (9.0.x). We are using SQL Server 2008 R2 with database mirroring to provide high availability for our databases.
The issue is with the jtds provider. I'm able to validate via ASP (OLEDB), .NET console (.NET Framework Data Provider), and SQL Management Studio.
Principal: SERVER_0
Mirror: SERVER_1
Witness: SERVER_2
Failover between SERVER_0 and SERVER_1 works using the Microsoft providers. The jtds provider works when SERVER_0 is the principal, but fails when SERVER_1 is the principal (failover state) as it doesn't understand the failoverPartner parameter.
DB Type: Microsoft SQL Server
Connection String: jdbc:jtds:sqlserver://SERVER_0:1433/{db_name};failoverPartner=SERVER_1
Server: SERVER_0
Port: 1433
Database: {db_name}
Additional parameters: failoverPartner=SERVER_1
I am able to use a General JDBC connection using the Microsoft JDBC (sqljdbc4.jar). Failover between SERVER_0 and SERVER_1 works using the MS provider. The issue is the previous jobs using the MS SQL Server will have to be re-written and we lose the features provided in the defined provider (jtds).
DB Type: General JDBC
JDBC URL: jdbc:sqlserver://SERVER_0:1433;databaseName={db_name};failoverPartner=SERVER_1
Class name: com.microsoft.sqlserver.jdbc.SQLServerDriver
Mapping file: id_MSSQL

Re: MS SQL Server DB Mirror

Anonymous — Thu, 01 Aug 2013 17:52:41 GMT

Could you report this issue via the Tech Support portal: https://support.talend.com
I believe we need to investigate further your case.

Re: MS SQL Server DB Mirror

Anonymous — Thu, 01 Aug 2013 19:36:45 GMT

Ticket submitted via OTRS. Thx

Re: MS SQL Server DB Mirror

Anonymous — Fri, 02 Aug 2013 08:17:53 GMT

Actually in the mean time I got a feedback from the dev team. And unfortunately they confirmed that for performance and stability reason all our MS SQL components rely on the jtDS lib and that this lib doesn't support mirroring, therefore I'm afraid you may need to do again your job to use JDBC instead if you want to get the mirroring in function.
Sorry about this
Elisa