Converting a database mirroring configuration to Availability Group

  • Article

One of the common questions we hear all the time is what are the requirements for setting up an Availability Group and how does that compare to database mirroring. I would like to answer this by going over a migration scenario.

Let's take a scenario where you
have a primary datacenter and a secondary datacenter. Your mission critical
application depends on 3 databases and all the databases are mirrored to the
secondary datacenter. In this configuration AlwaysOn-Srv2 is the principal SQL
Server instance and AlwaysOn-Srv7 is the mirror SQL Server instance. Both the
instances are standalone instances. The three databases are AlwaysonDb1, AlwaysonDb2 and AlwaysonDb3.
The mirror configuration currently is setup for high safety or synchronous mode.

The following video walks through the steps involved in migrating the database mirroring configuration to an availability group. In this video I'm using the CTP1 release, which only supports creating an availability group with a single asynchronous secondary. The walk through will go over the details of the pre-requisites of AlwaysOn Availability group and the steps involved in creating a new group.

[View:https://www.youtube.com/watch?v=ms-PjlX7S04]

There are a few things i want to recap here around the setup and clustering requirement

  • The machines hosting the SQL Server instances that needs to be part of an availability group should be part of the same Windows Server Failover Cluster (WSFC).  
  • The instances can be in the same datacenter or be in different datacenters but the host machines need to be part of the same windows cluster. Windows Server 2008 and above support multi-site clustering even if the sites are in different subnets.
  • It is required for the machines to be in the same domain to be part of the same windows cluster.
  • You do not require shared storage to setup windows clustering across a number of machines. You can create nodes from standalone machines as long as the machines pass the cluster validation. In the video above, the SQL Server instances were standalone instances running on separate machines with no shared storage.
  • If there are only even number of machines in the cluster the windows cluster quorum model should be changed to pick the appropriate quorum model as described here