DBInsight’s Blogs

Migrating Database Mirroring to an Availability Group

Posted by Rob Risetto on January 20, 2015

Recently I had to develop a migration plan to move a two server SQL Server 2008 R2 database mirroring environment to a two server SQL Server 2012 Availability Group (AG). The challenges were to minimise down time where possible, avoid transferring 40+ GB of database backups to the second server in the disaster recovery site and to perform the upgrade in line (i.e. no new equipment or VMs).

An important design element of the AG implementation was the Quorum model and voting rights that was selected for this customer’s environment.

Firstly, there was no third data centre, so the tie breaker for the Windows Cluster Quorum had to be either on the primary site or secondary; secondly the customer wanted a manual fail over; and last by not least, a failure (or connectivity loss) of the disaster recovery site should not cause the primary site to stop. Note also that the local site high availability (HA) was implemented via VMWare HA.

Based on these objectives, here’s the rundown of the Quorum configuration chosen :-

  1. A file share on the primary site was created as the tie breaker i.e. majority node and file share was chosen as the Quorum model.
  2. A voting right of 1 was granted to each server and the file share. This ensured that if the DR site failed then more than 50{2ec0bbd3bfdf207d2f0779c26660c3798ccadae611e41b6dbc787103c4a85cdd} of Quorum voting components (file share + primary site server) were running and the primary site stayed up.
  3. If the primary site shutdown, then the DR site SQL Server would stop, and a manual force quorum would be required to bring up the DR site SQL Server.

If the client had gone with a local Availability Group secondary server then I could have granted 1 Quorum voting right to each local server and the file share, but granted zero voting rights to the DR SQL Server to ensure that the DR SQL Server had no impact on the operation of the Availability Group. However, again in this scenario a manual force quorum would be required to bring up the DR SQL Server in the case of DR fail over.

Here’s some useful links if you are contemplating a database mirroring to AG migration:-

Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery (Here)

How To: Migrate to AlwaysOn AlwaysOn from Prior Deployments Combining Database Mirroring and Log Shipping – Part 1

How To: Migrate to AlwaysOn from Prior Deployments Combining Database Mirroring and Log Shipping – Part 2

Spread the love

Leave a Reply

avatar
  Subscribe  
Notify of