0

I have a windows service application that is instaled on two servers, SQL01 and SQL02. On both server there is a SQL server (X1 and X2), which have the same content. A mirroring partnership was set up for one database, called Interface.

Both service connect to their local database and have therefore no knowledge of the other one. The service is pretty simple, it reads from the database and perform some manipulation before sending it to a distant database(the same for both instance). A simple fonction is used to determine if the database is available or not using a dummy SELECT. If the database answer, then it's available, otherwise it's not.

When we started the whole thing the first time, only one database is working obviously (because of the partnership). However, when we do a manual failover, it seems that the database is still available! should I restart the service, it stop doing so and restart to work the way it should.

As far as I can tell, I don't keep any connection open to the database. I open and close them as soon as I am done with the query.

Does anyone have any idea of what could be happening ?

Thanks,

5
  • "it seems that the database is still available!" -- How are you determining this? Jul 6, 2011 at 19:14
  • I determine it because my program produces a log of the transactions it picked up, so it should stop producing results as soon as it can no longer read from the database. At most a few seconds after would be acceptable because it might already have pick up records, but more than that tells me it still is reading from the database even though it's not supposed to be able to Jul 6, 2011 at 19:22
  • Does the connection string for your program happen to have a definition for "Failover Partner"? Have you tried querying the Principal-transitioned-to-Mirror database in SSMS? Have you verified the failover was successful by verifying the Principal and Mirror state? Jul 6, 2011 at 20:38
  • I do have a failover, however, it is set to the same database because we though about using it but abandon the idea. I'm not sure I get the second point, but I know the failover was successfull. Like I mentioned, should I restart the program, everything act as normal. Jul 6, 2011 at 20:50
  • For SQL Server 2008 database mirroring to properly work, the mirror database must be in a recoverying state. If it is not, then you have a split-brained mirror and need to address that. Before you reboot, I would check the connections on the new principal and see if you have connections from the mirror database or its web-server; your infrastructure description indicates this should not happen. If any are found, I would verify they should be active; especially from the mirror's web-server. Also, the "Failover Partner" identifies a SQL Server, not the catalog (database) name. Jul 7, 2011 at 8:04

1 Answer 1

0

What happens is that when you successfully connect to the primary, then the SQL Server will send the failover partner to the client which will then cache this failover partner on the client side. And this is what will be used when a failover occurs. However, when the primary is down when the initial connection is made, then the client will try the failover partner in the connection string. If this then is successful, the SQL Server will send the ‘new’ failover partner to the client which will then cache this failover partner on the client side. This means that what was the original primary server is now stored on the client as the failover partner.

http://blogs.msdn.com/b/spike/archive/2010/12/08/clarification-on-the-failover-partner-in-the-connectionstring-in-database-mirror-setup.aspx

In other words, the SQL client handles the failover automatically (you have to retry any transactions that are in progress during a failover), but if you don't specify a failover partner and the principal is unavailable, then that's when the "Failover Partner" part of the connection string is relevant.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .