Should You Choose Database Mirroring?

High availability in SQL Server can be something of a quagmire to navigate. Choosing the right solution to suit your needs can be difficult if you’re not aware of the intricacies involved in different solutions, their implementation, and the level of DBA support they require. Database mirroring is a choice that can provide the continuous updates of transactional replication, the fault tolerance of clustering, and the ease of management of log shipping. A full discussion of the differences between these difference technologies is beyond the scope of this piece, but I will try to touch on some of the most important considerations.

Related: Database Mirroring for Disaster Recovery

Understanding when to use mirroring is key to a successful implementation, and at first glance, it’s hard to know what mirroring provides you over other HA technologies. Functionally, it’s best to think of mirroring as shipping of individual log records. Third-party vendors in the intelligent standby market have validated mirroring, so I tend to put mirroring in the intelligent standby category as well. Third-party solutions such as XOsoft’s WANSynchHA, SonaSoft’s SonaSafe, and Double-Take Software’s high availability tools have paved the way for using intelligent standby solutions that enable automatic failover across great distances. Such solutions provide a way to keep two servers in synch by keeping track of when changes are made to the database files and copying the changed bits to the standby server. These solutions can’t redirect clients on failover, but they can change DNS entries, and perform other tasks to ensure the clients connect to the appropriate server should the primary go down. These kinds of solutions can be complicated, so you should do thorough research to understand their capabilities. Then, when you’re trying to decide whether to use mirroring, ask yourself if you’d typically turn to one of these third-party vendors for a solution; if so, you’ll know database mirroring might be right for you.

However, playing in the same space as the third-party vendors doesn’t mean that database mirroring is equal to those solutions. For starters, the third-party applications let you configure how often the secondary server gets updated, whereas mirroring is in a constant state of streaming the log. Third-party applications also let you automatically change the DNS, IP, and other settings of the secondary server before a failover, whereas mirroring simply fails over and redirects the clients to the new server. Because they are based on bit-level comparisons of the database files, third-party applications can’t offer zero data loss, whereas mirroring is based on the SQL Server transaction logs, so it does offer zero data loss. Third-party applications render the secondary SQL Server completely incapacitated, whereas with mirroring, only the mirrored database is unavailable; the rest of the databases on the server are fully online.

You’ll also need to understand the differences between mirroring and replication. Replication lets the secondary database be online at all times, whereas with mirroring, the secondary database is effectively offline. Replication lets you configure the intervals at which the secondary database gets updated; mirroring doesn’t. However, like the third-party applications, replication doesn’t give you the option of zero data loss. And mirroring gives you automatic failover, which replication doesn’t provide.

You can see that mirroring is a specialized tool for high availability and disaster recovery. As such, the secondary database in a mirroring environment is good for absolutely nothing except being kept up to date with the primary server. You can’t use it to offload reporting (unless you snapshot it) or backups as you can with replication.

Mirroring Modes

Although database mirroring provides the first true zero-data-loss solution for SQL Server, don’t be fooled into jumping into a mirroring solution too quickly. As with any safety net, mirroring comes with a price. Mirroring has two modes: synchronous and asynchronous. Running in full synchronous mode (safety = on), mirroring will guarantee that you don’t lose any data. But to do this, the system has to complete all transactions on both servers before it reports the success back to the client application. Depending on your network and a host of other factors, this requirement has the potential to slow down your processing drastically if you’re not careful.

Running in asynchronous mode (safety = off) means that the transaction is committed on the primary server but doesn’t have to be hardened (committed) on the standby server to return the commit to the client. In such a case, the standby server might get behind, and if you need to failover, you could lose data.

Choose Your Hardware

Once you know which mode you want to run in, be sure you carefully consider your hardware. Unlike a clustering environment, in which the hardware of both servers has to be identical, mirroring lets you use very different hardware for your primary and secondary servers. Of course, you might face performance problems if you choose very dissimilar machines for your setup (e.g., mirroring a server-class machine with a laptop), so it’s best if both servers have similar capabilities. Also, although creating a centralized mirroring repository server might be temping, I urge you not to do so. A centralized mirroring repository is a server that holds the secondary mirroring instances for several production servers. This kind of setup will drag down your performance if you’re running in synchronous mode. But the biggest problem with a centralized repository is the possibility of downtime. If your mirroring repository should ever fail because of an unforeseen circumstance such as a botched OS patch installation, driver conflict, hardware failure, or botched SQL Server patch installation, every production database that is a primary to this server will go offline. Although this scenario might seem far-fetched, I have seen it happen. When the repository at the company in question failed, all the organization’s servers were unprotected.

So, the moral here is that before you implement mirroring, you need to make sure it’s the right solution for your needs—and you need to be prepared for the costs involved. However, every solution has costs, so don’t let the cost alone scare you off of mirroring as a high-availability solution. To make an informed decision, carefully consider all these important questions:

If you answered yes to any of these questions, mirroring could be for you. If you answered yes to most of these questions, mirroring is definitely for you.