2

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?

Improve this question
3
  • Can you have a look at errorlog and see what caused CPU usage to spike. May be some extensive operation like CHECKDB was running. Please also check errorlog of partner server.
    – Shanky
    Commented Nov 24, 2014 at 19:55
  • I rarely find anything of use in the event log or SQL log associated with these failovers. This was no exception. No smoking gun so far. I was more curious as to why just 3 of the 20 were affected.
    – SandraV
    Commented Nov 24, 2014 at 21:38
  • If you plan to use high-safety mode with automatic failover, the normal load on each failover partner should be less than 50 percent of the CPU. If your work load overloads the CPU, a failover partner might be unable to ping the other server instances in the mirroring session. This causes a unnecessary failover. If you cannot keep the CPU usage under 50 percent, we recommend that you use either high-safety mode without automatic failover or high-performance mode. Read this msdn.microsoft.com/en-gb/library/ms366349.aspx
    – Shanky
    Commented Nov 24, 2014 at 22:41

1 Answer 1

Reset to default
3

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?

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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