Is SQL Server Mirroring Reliable (high availability)?

Hi,

I'm evaluating SQL Server 2008 Mirroring (async) in order to provide better availability. I'd like to know, based on your experience, if SQL Server Mirroring is a reliable and mature technology. Automatic failover is not mandatory but is a nice-to-have. Therefore, I'm evaluating the mirroring mechanism and the automatic failover mechanism separately.

• Is the mirroring mechanism reliable? Does it require continuous configuration and tuning?

• Is the automatic failover option reliable? Does it require continuous configuration and tuning?

Regards,

From serverfault Sly

• Yes, database mirroring is a reliable technology and is in use by many Enterprise customers today. I've helped people set up mirroring both while inside and outside Microsoft - I used to own database mirroring when I responsible for the SQL Server Storage Engine.

  As with any HA technology, you need to understand your requirements and limitations before choosing technologies to help you implement an HA strategy. If you choose technologies before doing this and try to retrofit them to your requirements, you won't get a successful strategy.

  I've just finished writing a 35-page whitepaper on HA with SQL Server for the SQL team - it'll be published before SQL PASS. In the meantime, to get an idea of the kind of questions to ask yourself, see this blog post of mine: HA: Where do you start when choosing a high-availability solution?. For database mirroring, you need to consider:

  • How many databases are in your application ecosystem? I.e. what resources need to all be failed over at once. More than 2-3 and you might look at failover clustering with SAN replication instead.
  • Outside a single application ecosystem, how many databases do you want to mirror? Going more than 10 and you'll start to see issues with worker threads, memory, and waits on the shared mirroring send buffer.
  • What is the transaction log generation rate?
  • What is the network bandwidth and latency? These two things determine whether the mirror will be in synch with the principal - and how much data loss you may be looking at in asynch mode (from a large send queue) or workload throughput degeneration from having to wait for transactions to be hardened on the mirror (for synch mode)

  And so on.

  Once you set up mirroring, you'll need to monitor it to make sure its working within your desired parameters - most important things to monitor are the SEND and REDO queues - which give you a measure of how far behind the mirror is, and how much time a failover will take, respectively. It doesn't need continuous tuning in common scenarios, but YMMV as with any technology.

  Automatic failover - it depends. There are a variety of scenarios to consider, in terms of which network links go down, which mirroring partners go down, and what the actual failure is. Checkout this blog post for a list of failures: Search Engine Q&A #3: Database mirroring failover types and partner timeouts.

  This has all been a bit of a brain dump, but to summarize, yes, mirroring is reliable and shouldn't need excessive tuning and monitoring. SQL Server 2008 added some cool new stuff to help - log stream compression and automatic page repair - both of which I've blogged about in my Database Mirroring blog post category.

  Finally - there are a bunch of good whitepapers you should look at (see the database mirroring links on our whitepapers uber-links page - I wrote the one on combining database mirroring with transactional replication in SS2008) and a new book Pro SQL Server Mirroring.

  Hope this helps!

  mrdenny : Paul, SAN Replication is only needed for Clustering when dealing with Geo-Distributed clustering. Local HA clustering uses shared storage.

  Paul Randal : Indeed - and mirroring is usually used for geographic redundancy, which is why I mentioned moving to clustering+SAN replication when trying to mirror too many databases - so the whole instance fails over instead of just one out of many databases.

  Sly : Paul, thank you for the detailed answer. I now have a lot of reading to do. But at least I now know that I should consider mirroring as a real option. Regards,
  From Paul Randal