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