On SQL Server 2008 R2, I'm mirroring 20 databases on my SharePoint SQL Server with a witness, high-safety. All mirrored DBs have the default mirroring_connection_timeout = 10. And, occasionally, a handful of DBs will failover to the mirror (not more than once a month). Today, it was 3 DBs and the only abnormality I noticed was some highish CPU at the time of failover.
I assume that the 10 second timeout for receiving a PING has been exceeded and failover occurs. What I don't understand is why, if all DBs have the same timeout they don't all failover when this happens?
Best Answer
The issue is that there is a separate ping for each database, not one for all. So some are successful and some fail. If you need all of the databases to fail over, I outlined a process (using service broker and event notifications) to detect a failover and make sure all mirrored databases fail over in my book Pro SQL Server 2008 Mirroring.
A lot of this information is also located on my blog here Database Mirroring Automation and here: How do I automatically respond to mirrored database failovers?