2

  • I've got a database that is mirrored using SQL 2008 Mirroring.

  • I have a java application, running on Linux, using the Microsoft SQL type 4 JDBC drivers.

  • I have this setup duplicated as a QA environment.

  • On my QA environment, when I manually fail over the database, providing a successful connection had already been made, the failover was completely transparent. I did not have to implement anything in order to get the application to talk to the new Principle (Old mirror).

  • In the live environment however, the connections stop working once I have manually failed over.

    There are quite a few things different between the live and QA environments, but not anything I'd consider fundamental to this process, communication between databases is all very localised in both situations and there are no firewalls (Except the ones built into Windows Server 2008) between my java app and the Windows boxes running SQL 2008.
    Does anyone have any ideas about how I can go about diagnosing this issue? Or can anyone tell me how this failover transparency occurs so I can work out how to diagnose this myself?

Matt Fellows
  • 6,512
  • 4
  • 35
  • 57
  • Hi. I've never had this, it must be that your setup is not correct. When properly set up, the failover is supposed to be transparent, your application should not even know what happened. Are you sure the mirror is running properly in the live environment? Remember, settings don't take effect until the services are restarted for many of the mirror options. – Ewald Oct 24 '11 at 10:33
  • Unfortunately yes it is set up correctly on live - It's been running successfully for a couple of years, JBoss copes with the failover as I have multiple URLs in the datasource files, and the automatic failover has been happening a lot recently due to a switch issue (Now resolved) it's just this stand alone java app, making use of the same database which doesn't cope when the failovers happen. – Matt Fellows Oct 24 '11 at 10:37

1 Answers1

1

This behaviour is handled by the SQL Server provider as part of the Client Redirect behaviour. This was part of the SQL Server 2005 JDBC (Java Database Connectivity) 1.1 Driver, I'm unsure if that provider was directly used for the new type 4 drivers though?

In your live environment, is your application successfully connecting to the primary server before it fails over, so it is able to cache the failover partner and make use of that when the failover occurs?

To be sure, you can explicitly state the failover partner in the connection string, which is the recommended practice:

jdbc:sqlserver://serverA:1433; databaseName=AdventureWorks; integratedSecurity=true; failoverPartner=serverB

The full documentation of the redirect behaviour can be found here.

The addition of the failover documentation here suggests that it could be a manual consideration.

Kasaku
  • 2,192
  • 16
  • 26
  • In the live environment it does indeed connect to the principle successfully, usually for a lengthy time before a failover occurs. After a failover the logs indicate that it can no longer communicate with the principle database, I'll dig out the logs and find out exactly what the error message is... I'll also try explicitly adding the failoverPartner argument to teh connection string, though I'm still uncertain how it works in QA without this, if this fixes it... – Matt Fellows Nov 08 '11 at 10:24