locked
JDBC Connection String error in SQL 2008 Mirioring failover RRS feed

  • Question

  • Hello All,

    Info:

    I am using SQL server 2008 R2 on both serves are on same version and JDBC version we are using is 3.0 and both the servers are on default instance

    I am having an Issue here regarding the JDBC connection string in SQL Server 2008 R2 mirrioring. below is the URL I am using

    jdbc:sqlserver://Server-A:1433;databaseName=Db_Name;failoverPartner=Server-B

    Here are the cases:

    Case-1: If the Server-A is working fine and server-B is in mirror stage, then below URL is working

    jdbc:sqlserver://Server-A:1433;databaseName=Db_Name;failoverPartner=Server-B

    case-2: If the Server-A is not working and server-B is in active stage, then below URL is NOT working

    jdbc:sqlserver://Server-A:1433;databaseName=Db_Name;failoverPartner=Server-B

    if I modify the above URL to

    jdbc:sqlserver://Server-B:1433;databaseName=Db_Name;failoverPartner=Server-A then it's working fine.

    The problem is to modify the URL I need to take the application down and it will take 10-15 minutes to restart the whole application back to normal which I cant afford the amount of time.

    Below is the error message I am getting after I do the database failover:

    com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host Server-A, port 1433 has failed. Error: "null. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.".
          at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:171)
          at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1033)
          at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:817)
          at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:700)
          at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:842)
          at java.sql.DriverManager.getConnection(Unknown Source)
          at java.sql.DriverManager.getConnection(Unknown Source)
          at TestSQLServer.<init>(TestSQLServer.java:20)
          at TestSQLServer.main(TestSQLServer.java:55)

    I tried the option of adding the default instance name after the server name,and I also checked if the TCP/IP ports are open in both servers and are enabled.

    Thanks u all for the valuable advices

    Thursday, July 7, 2011 2:35 PM

All replies

  • Nothing wrong with the connection string...from web server can you be able telnet on 1433 on both Principal and mirror server?

    Are they SQL instances (principal and mirrror) are installed on  individual servers? This problem normal apprears when you install both instance on same server....

    Can you please post if any errors - from event logs after failover happens and failling web server to find new principal server?


    http://uk.linkedin.com/in/ramjaddu
    Thursday, July 7, 2011 3:26 PM
  • We are connecting thru JDBC connection and our application dosent use Telnet

    Both instances are on physically different servers and below is the error message that I got when I do the failover

    com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host Server-A, port 1433 has failed. Error: "null. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.".
          at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:171)
          at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1033)
          at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:817)
          at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:700)
          at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:842)
          at java.sql.DriverManager.getConnection(Unknown Source)
          at java.sql.DriverManager.getConnection(Unknown Source)
          at TestSQLServer.<init>(TestSQLServer.java:20)
          at TestSQLServer.main(TestSQLServer.java:55)

    Thanks

    Thursday, July 7, 2011 3:58 PM
  • What are status of principal / mirroring is it synchronized or synchronizing? are they servers got same hardware and same SQL server configuration? Some thing collation ect..
    http://uk.linkedin.com/in/ramjaddu
    Thursday, July 7, 2011 4:45 PM
  • The principal server status is:  SYNCHRONIZED

    The Mirror server statsu is: Mirror, synchronized/restoring

    They both are in sql server version 10.50.1600.

    Thanks

    Thursday, July 7, 2011 4:48 PM
  • It could be problem with JDBC Drives 3.0 can you please try with older versions like 2.0 ?

     


    http://uk.linkedin.com/in/ramjaddu
    Thursday, July 7, 2011 8:10 PM
  • I updated the JDBC driver from 2.0 to 3.0, thinking that Issue might get resolved but my bad luck.... it's didnt get resolved .....
    Thursday, July 7, 2011 10:54 PM
  • One more thing before we go any furthur - can you please check for login SIDs are they same on both principal and mirror?

    I can't see user name and password in the connection string? any antivirus software installed on boxes?

    here are some guide lines to fix this problem http://kb.swivelsecure.com/wiki/index.php/MS_SQL_Database_How_To_Guide

    hope this helps you


    http://uk.linkedin.com/in/ramjaddu
    Friday, July 8, 2011 8:30 AM
  • Have a look these MSDN links on setting up the correct connection string:

    http://msdn.microsoft.com/en-us/library/ms378988(v=sql.90).aspx

    http://msdn.microsoft.com/en-us/library/ms378672(v=sql.90).aspx list of various options.


    Satya SKJ, SQL Server MVP [Knowledge Sharing Network - www.sqlserver-qa.net]
    Author of SQL Server 2008 R2 Administration cookbook.
    Follow me @sqlmaster.
    Friday, July 8, 2011 12:11 PM
  • The login name and passoword are same and have the dbo permissions in both servers. But the SIDs dosent match between 2 databases, but I can login into each database in each server using the same login and same passoword

    Thanks

    Friday, July 8, 2011 2:53 PM
  • That was the problem ..... Application fail-over fails when Login SIDs are not match between principal and mirror server.

    Please check here how transfer logins between servers http://support.microsoft.com/kb/246133.

     


    http://uk.linkedin.com/in/ramjaddu
    Friday, July 8, 2011 7:26 PM
  • Sorry for late reply.... I did the whole migration again with SIDs matching the steps including backup and restore of DB, transfer the login using the script sp_help_revlogin script and setup the Mirrioring btw 2 sever ... but NO LUCK......still having the same error msg...

    Please let me know where went wrong.....

    Thanks

    Monday, July 11, 2011 9:57 PM
  • I can't think any thing other than any DNS aliases configured with Database Mirroring that need to make sure that the client get's cache flushed locally though otherwise it will still point at the old server after failover... That might be one moer reason application unable to faillover sucessfully....

     


    http://uk.linkedin.com/in/ramjaddu
    Tuesday, July 12, 2011 9:18 AM
  • Did you ever find a solution to this issue? I have run into the same problem.
    Tuesday, October 25, 2011 2:45 PM
  • Is there any message in the SQL Errorlog from the connection attempts?
    Thursday, October 27, 2011 5:21 AM
  • I'm planning to implement a mirrored SQL Server environment and we are also using JDBC to connect to the database server. Had a look at this post and then at the documentation (found here: http://msdn.microsoft.com/en-us/library/aa342332.aspx).

    Two things are clear and please feel free to correct me if I missed something. First, all connection strings must have the "failoverpartner" defined. With the connection first attempt it will try and connect to the first server (standard connection setting), if it fails ... it will try the failover partner. Yes, login SIDs must match (it might connect if you test it, but as soon as you add a default database, either in the connection or with the SQL login, the connection will fail as the login does not match the database user ... off course the error will be different).

    Now if you have a connection already open to the principle (active database) and the server fail with the mirror taking over the role of principle ... additional logic needs to be included to catch the command execution, determine that the failure is related to a original connection not working anymore and then execute a reconnect and re-execute of the SQL statement.

    Last note, and this is not clear in the documentation ... seems like the mirror must run on the same port as the principal as the failoverpartner does not seem to allo a port setting. Just remember that it will take a few seconds (to minutes) to execute the roll forward and rollback of un-commited transactions before the database comes online on the mirror. If your connection time-out setting is too low, you might not be able to connect to the "mirror ... now in the process of becoming the principle" as it is still recovering.

    Regards


    Tuesday, January 24, 2012 11:24 AM
  • I might be stating the obvious but we had the same problem and couldn't work it out for a while but it was due to a syntax issue - we specified the failoverPartner property server with forward slashes '//Server-B' instead of just 'Server-B'
    Sunday, June 24, 2012 10:19 AM