SQL Server AlwaysOn – SharePoint 2013 High Availability and Disaster Recovery

In my opinion, days of SQL mirroring and log shipping were long gone when AlwaysOn made its debut. I find AlwaysOn to be much more easier to deploy and maintain as compared to those other high availability solutions. What makes it so attractive is that AlwaysOn provides automated high availability at a database level without the need of creating SQL cluster. However, it does require Windows Failover Clustering Service (WFCS) to provide the engine for the automated or manual failover and failback functionality.

To provide both high availability and disaster recovery, You will at least need three nodes, two at a local site to provide high availability, and the third node at a remote site to provide disaster recovery. For local replication, you will need to use the sync mode and for remote DR site you will need to use the async mode. Two simplify the concept:

Sync = local high availability

Async = remote, disaster recovery

AlwaysOn consists of an “Availability Group” which is basically a container to which you add your SQL instance nodes and the desired databases to replicate to the other nodes in the cluster. Each node in the cluster is known as the “Replica”. You have a “primary replica” from which you replicate database to your “secondary replicas”. Primary replica is “Read-Write” whereas the secondary replica is “Read-Only”. You can setup your replication for Automatic or Manual failover. For sync mode, Automatic and Manual failover is supported and for the async mode only manual failover is supported.

Availability group uses the concept of “Listener” to direct client traffic to the available replica. Listener is basically a virtual name that consists of the IP addresses from each node in the cluster. You create an A record in DNS and map each IP address of your nodes to it.

It was recently announced that most of the service applications database are now supported for async mode, that is you can now replicate your service application databases such as managed metadata, and user profile service databases, over the WAN. Before, you had to use log shipping to replicate these databases which resulted in a more complex replication process. Configuration and Central Administration databases however are still not supported for remote site replication as they are farm specific. Following diagram provides a high level view of the AlwaysOn Availability Group:

You can use the following sample table to plan for your AlwaysOn availability group:

At a high level, following steps are required to setup AlwaysOn. For more detailed steps, click here.

• 1. Setup Windows Failover Clustering and make each SQL instance from local and remote disaster recovery site member of that cluster.
  2. Enable AlwaysOn on each node.
  3. Create the Availability Group.
  4. Add Replicas to each Availability Group.
  5. Verify Endpoints, open inbound port 5022 (default) on each SQL node
  6. Verify backup preferences.
  7. Create Availability Group Listener, provide port 1433(default), and an IP address from each of the local and remote DR subnets. These IPs should map to an A record for the Listener.

Note: Make sure that the same farm service accounts to each of the Prod and DR SQL Instances login and server roles have been added, otherwise the AlwaysOn synchronization process will fail.

• 8. Create a shared folder for data synchronization for the Availability Group.
  9. After availability group(s) has been created, enable the MultiSubnetFailover databases property to true on each database. To learn more, click here.
  10. Install SharePoint .
  11. After SharePoint has been installed and configured, change Recovery Model for each of its databases to Full. This is a requirement before a database can be added to the Availability Group.
  12. Backup all of the databases. This is a requirement before a database can be added to the Availability Group.
  13. Add databases to their Availability Group(s)
  14. Test failover and failback on each Availability Group.

To learn more on how to setup AlwaysOn Availability Groups, click here.