Database Mirroring over a WAN? Watch out...

sql server

How far apart can Database Mirroring partners be? This was a question that came up during a recent class. Of course, the answer is "it depends". Actually, the real answer is "how long is a piece of string?". Let's take a look... Database Mirroring was introduced in SQL Server 2005 SP1 and gave us a method of synchronizing a database across two servers with automatic failover and transparent client redirection, all without the expense of a Windows Cluster. Very attractive. The default mode of Database Mirroring is "High Safety with Automatic Failover" which requires three partners: the principal, the mirror and the witness. It also implies synchronous processing using transactions that commit over the network between the Principal and the Mirror. This mode was designed for a fast, reliable network with a constant connection that minimizes possible network failures. So instead of defining a minimum distance, we need to define "fast and reliable". Sorry, "it depends". Microsoft will not be drawn on this question. It's official stance is: "We make no recommendations about whether a wide-area network (WAN) is reliable enough for database mirroring in high-safety mode. If you decide to use high-safety mode over a WAN, be cautious about how you add a witness to the session, because unwanted automatic failovers can occur". One issue is that the Witness needs to constantly communicate with both the Principal and the Mirror so that it knows when to initiate a failover. A poor connection might fool the witness into requesting a false-positive failover. Another issue is that processing is synchronous. This means the Principal has to wait for the "two phase commit" handshake from the Mirror before committing a transaction. This means that during a failover there is no loss of data but it obviously affects performance adversely. The official advice is to try "High Performance" mode first. This performs asynchronous processing such that the principal does not wait on the mirror before committing. It queues up the update. So during failover, there may be some loss of recent updates. Also, there is no witness so all failovers have to be manual. The next mode to try is "High Safety without Automatic Failover". This uses synchronous processing but without a witness. No loss of data at failover but only manual failovers. If you are completely satisfied with previous results and are confident about network speed and reliability, you may attempt to implement the full capabilities of Database Mirroring: "High Safety with Automatic Failover". A better answer would be, if you want to synchronize over the WAN, take a look at Log Shipping. For minimal overhead and administration, Log Shipping may be what you want. You define how often the Transaction Log is backed up, copied and restored to the standby server. Many users use Log Shipping to copy database updates from a Clustered Server to a remote disaster recovery site. Log Shipping was slated for the chopping block a long time ago. Not so now. The oldest features seem to be the best. cheers Brian Prerequisites, Restrictions, and Recommendations for Database Mirroring: http://technet.microsoft.com/en-us/library/ms366349.aspx

Join the Network World communities on Facebook and LinkedIn to comment on topics that are top of mind.
Related:
Now read: Getting grounded in IoT