Checking Failover after setting up a Database Mirroring
If you use database mirroring, the essence is that you use an additional connection property which is called “Failover partner” to make the client aware that it can try to connect to another server. If the principal went down or you lost connection to the server you will get the error message
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
So there is no real “transparent” redirection to the mirror (which becomes the principal if the former principal will be down), you will have to reconnect to your failover partner. Make sure that you will have to implement this in your client application.
As part of the SQL Server Master class, I did a small application (which you can download in this blog entry) doing a check and display for the information if and when a failover happened on the server and how long the failover took. It might make it easy for you to check you environment for automatic failovers for database mirroring testing.
If you have any feedback or things to change, feel free to ping me.
-Jens
Comments
Anonymous
November 06, 2009
Wow... at last I was able to test SQL mirroring after several days of tribulations, using your very neat CheckFailover test program... Many Thanks. I am still a beginner in this area, if your app is in c# (or VB) would it be possible to email me the source please?Anonymous
November 09, 2009
Sure, I can´t see your mail adress and don´t know where to sent the source code. Can you post me a feedback through the blog functionality ? -Jens