Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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,

share|improve this question

migrated from stackoverflow.com Jul 5 '11 at 18:11

This question came from our site for professional and enthusiast programmers.

    
"it seems that the database is still available!" -- How are you determining this? –  Robert Miller Jul 6 '11 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 –  David Brunelle Jul 6 '11 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? –  Robert Miller Jul 6 '11 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. –  David Brunelle Jul 6 '11 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. –  Robert Miller Jul 7 '11 at 8:04
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.