SharePoint High Availability and Disaster Recovery based on SQL Server Options

Microsoft provides many solutions to enable High Availability (HA) or Disaster Recovery (DR) for SharePoint based on SQL Server solutions.

At the high level, these options are:

  • Backup and Restore
  • Log Shipping
  • Replication
  • Mirroring
  • Failover Cluster
  • AlwaysOn Availability Group

There are solutions out of SQL Server product like SAN replication, Hyper-V replication and other solutions but these types of replications are not supported by Microsoft because they may cause consistency issues especially for search index and timer jobs.

The only exception for Virtual machine replication is Azure Site Recovery, which does support replication of virtual machines into Azure for the purposes of Disaster Recovery, you can find more information in these links:
https://docs.microsoft.com/en-us/azure/site-recovery/site-recovery-sharepoint

https://docs.microsoft.com/en-us/office365/enterprise/sharepoint-server-2013-disaster-recovery-in-microsoft-azure

Backup and Restore

The purpose of creating SQL Server backups is to enable you to recover a damaged database. We can summarize the Pros and Cons in the following points:

  • There is a possibility to lose Data
  • Inexpensive solution for DR
  • It doesn’t provide HA
  • Protection at database level

Log Shipping

SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. We can summarize the Pros and Cons in the following points:

  • Can be HA or DR
  • No automatic failover
  • Protection at database level
  • Inexpensive solution
  • There is a possibility to lose Data

Replication

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. We can summarize the Pros and Cons in the following points:

  • Protection at database level
  • No automatic failover
  • Inexpensive solution
  • Can be used as load balancing
  • Can be HA or DR

Mirroring

Database mirroring is a primarily software solution for increasing database availability by replicate the data between primary and secondary servers. We can summarize the Pros and Cons in the following points:

  • Can be HA or DR
  • Limited to two servers
  • It supports automatic failover but it needs witness server for this purpose
  • Replaced with AlwaysOn in SQL Server 2012 and higher releases

Note:

Not all databases can be configured with Failover database server from central administration but you can add this property from PowerShell for these database like configuration database using the below commands:
$database = Get-SPDatabase | where { $_.Name -eq “SomeSharePointDB” }

$database.AddFailoverServiceInstance(“SQLSecond”)

$database.Update();

Failover Cluster

A Windows Server Failover Clustering (WSFC) cluster is a group of independent servers that work together to increase the availability of applications and services. SQL Server takes advantages of WSFC services to provide local high availability through redundancy at the server-instance level. We can summarize the Pros and Cons in the following points:

  • HA
  • Expensive Solution
  • Protection at instance level
  • It supports automatic failover

AlwaysOn Availability Group

This is the latest solution provided by Microsoft from SQL Server 2012 and higher releases which has the features of Database Mirroring and Failover Cluster with many enchantments and new features. We can summarize the Pros and Cons in the following points:

  • Can have up to 4 replicas or more based on SQL Server versions (In Mirroring , only one primary and one secondary servers)
  • Can be HA and DR (HA = Sync mode , DR = Async mode)
  • No need for SAN storage (In Failover cluster , SAN storage or other network disks are required)
  • Can be deployed in geographical (like in Failover Cluster but with more enchantments)
  • No need for witness Server
  • You can use Availability Group Listener (Virtual IP and Name like in Failover cluster)
  • Replica Servers can be accessed for backup or reporting service operations … etc.
  • Supports automatic failover (In case of Sync mode)
  • Needs SQL Server enterprise edition

So in this case you can use one solution to provide HA and DR instead of using multiple solutions like what it was in previous versions for example, using Database Mirroring for remote DR (or log shipping) and Failover cluster for HA.

 Terms

AlwaysOn Failover Cluster instance (FCI) = SQL Server Failover Cluster Instance

AlwaysOn Availability Group = like Database mirroring in old version but with many enhancements

This table from Microsoft whitepaper to show the differences between these solutions (Based on SQL Server 2012 version):

1

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s