SQL Mirroring with SQL 2008, part 1

So you’ve made the decision that you want to use SQL Mirroring for your DR solution. You have several options for DR for SQL databases: you can use Windows clustering, log shipping, or mirroring. My preference in any scenario is to just use Windows Clustering, but there are reasons why you could go with another solution. Clustering is nice because it’s outside of SQL and just sits at the OS-level, but by definition you are going to have some hardware sitting there idle even if you go Active/Active (assuming you planned for 1 server going down and allocated your CPU/RAM appropriately. Plus doing it at the OS level doesn’t require you to be an expert or have a fair bit of knowledge of SQL. Using mirroring also allows you to be fully HA as you can have both servers sitting in different sites on different subnets and not have to worry about having your network guys stretch a subnet geographically, which they never want to do.

Note that SQL 2008 (and all versions, for that matter) cannot be used on a Windows 2008 geo-cluster that is on different subnets.

And in case you weren’t aware: HA = Highly Available, DR = Disaster Recovery. Separate, but not necessarily mutually exclusive technologies.

All that being said, let me give you a quick rundown on SQL Mirroring. I can’t say it better than Microsoft, with their gaggle of technical writers, so here’s an article from them on the benefits of Database Mirroring:


A few items of note that I always like to tell my clients:

  • You need a minimum of 3 instances of SQL for full redundancy on Mirroring (Principal, Mirror, Witness). You can technically do it in 2, but you lose the capability for automatic failover without the witness. Think of it as the 3rd vote if this were a cluster.
  • All SQL servers need to be a member of an AD domain and running under the same service account
  • There is a copy of the database on both the Principal and the Mirror.
  • The Witness allows for automatic failover in the event of a server failure
  • Mirroring is on a per-database level, not per-server level. A SQL server can be a mixture of single databases plus mirror instances.
  • The database can be set in synchronous or asynchronous mode
    • Under asynchronous operation, the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance
    • Under synchronous operation, a transaction is committed on both partners, but at the cost of increased transaction latency

There are also a few selling points for Mirroring, if you’re having trouble selling having all these servers to your management or to your clients.

  • Since mirroring requires 3 servers be active at all times, you can run databases on all 3 servers, some mirrored and some not. You can also have any combination of Principal/Mirror/Witness running across the 3 servers (i.e. Active/Active/Active)
  • Mirroring can be done across multiple sites and subnets, thus allowing for full HA/DR

In my next article I’ll get into the nitty-gritty of how to actually set this up and show you all the SQL commands for this.