다음을 통해 공유

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:

image

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

AlwaysOn Availability Groups

Availability Group Listeners

Availability Databases

Availability Replicas

Replica Role

Replica Availability Mode

Replica Failover Mode

Readable Secondary

AG for SharePoint Databases

SP-AG1

SharePoint Databases

PROD SQL 1

Primary

Sync commit

Automatic

No
   

PROD SQL 2

Secondary

Sync commit

Automatic

No
   

DR SQL 1

Secondary

Async commit

Manual

No

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.

 

    1. Create a shared folder for data synchronization for the Availability Group.
    2. After availability group(s) has been created, enable the MultiSubnetFailover databases property to true on each database. To learn more, click here.
    3. Install SharePoint .
    4. 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.
    5. Backup all of the databases. This is a requirement before a database can be added to the Availability Group.
    6. Add databases to their Availability Group(s)
    7. Test failover and failback on each Availability Group.

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

Comments

  • Anonymous
    January 01, 2003
    Thank you Sal.
  • Anonymous
    January 01, 2003
    Sajid, yes the third node is in a separate DR farm.
  • Anonymous
    May 04, 2015
    Is this applicable for 2 farms. I see that you have SQL node. But is the 3rd node a separate SharePoint DR farm?
  • Anonymous
    June 14, 2015
    The comment has been removed
  • Anonymous
    November 02, 2015
    Hi Sal,

    I want to build the same setup you are doing but here I have once concern since as you know, config DB is farm specific and as per MS it is not supported to be replicated in async-mode and according to your setup your are replicating these databases to the DR site and content databases have to be only replicated to the DR site.

    So is there any work around this setup to avoid replicating the config DB to the DR?