SQL Server Mirroring and automatic failover - why don't all DBs failover to the mirror?

by SandraV   Last Updated June 16, 2015 10:02 AM

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?

Answers 1

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?

Robert L Davis
Robert L Davis
January 07, 2015 23:53 PM

Related Questions

Having DB Mirroring and Replication together

Updated May 13, 2015 12:40 PM

Mirroring the Distributor

Updated May 13, 2015 15:02 PM

SQL Server 2008 R2 Mirroring issues

Updated July 24, 2015 13:02 PM