A SQL Server DBA myth a day: (10/30) database mirroring detects failures immediately

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.)

Off to SQL Connections today in Las Vegas – hope to see some of you there! Stop by and say hi if you're enjoying the myth-a-day series.

Myth #10: Database mirroring detects failures immediately.

FALSE

The marketing hype around database mirroring is that it provides instant detection of failures and instant failover.

No it doesn't. The speed with which a failure is detected depends on what the failure is, among other things.

The fastest detection of failure occurs when the principal SQL Server instance dies/crashes. When the once-per-second ping comes in from the mirror server, the OS on the principal server will know that there's no process listening on the TCP port the mirror server is pinging, and will let the mirror server know. This takes at most one second.

The next fastest detection of failure is when the OS on the principal server has died. In that case, there's no OS to respond to the ping from the mirror server. The mirror server will continue to ping once-per-second until the mirroring partner timeout expires. By default this is 10 seconds, but you may have increased it (for instance to ensure that a local cluster failover can occur before a mirroring failover to a remote server occurs). In this case, detection takes as long as the mirroring partner timeout is.

The next fastest example is a log drive becoming unavailable. SQL Server will continue to issue write requests to the I/O subsystem, will complain in the errorlog after 20 seconds without an I/O completion, and finally declare the log drive inaccessible after 40 seconds. The database is taken offline and a mirroring failure is declared. SQL Server is very patient, you see – with locks, for example, it will happily wait forever unless it detects a deadlock.

A page corruption might not even trigger a failure at all. If a regular query gets an 823 or 824, mirroring doesn't care (although it will attempt to fix them in 2008 (with a few caveats) – see SQL Server 2008: Automatic Page Repair with Database Mirroring). If the rollback of a query hits the 823 or 824 though, the database goes suspect immediately as it becomes transactionally inconsistent -> mirroring failure.

The moral of the story is not to believe everything you read in the brochure :-)

2 thoughts on “A SQL Server DBA myth a day: (10/30) database mirroring detects failures immediately

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.