One Star

MS SQL Server DB Mirror

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)
5 REPLIES
Community Manager

Re: MS SQL Server DB Mirror

Hi
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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: MS SQL Server DB Mirror

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
Community Manager

Re: MS SQL Server DB Mirror

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

Re: MS SQL Server DB Mirror

Ticket submitted via OTRS. Thx
Community Manager

Re: MS SQL Server DB Mirror

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