Protect your SharePoint 2013 data tier with SQL Server 2012 Always On Availability Groups

 

3 weeks ago, Isabelle Van Campenhoudt (SQL Server MVP) and me (don’t forget that I’m just a simple SharePoint dev) did setup SQL Server 2012 Always on Availability Groups with SharePoint 2013; we wil publish (later) a video illustrating our setup.

Always On will only protect your farm data tier; there are many other tiers in a SharePoint farm. This is just a technology, so you still have to define your SLA (RPO & RTO and more).

But it is quite easy to setup and to maintain.

Part 1.Background:

SQL Server 2012 Enterprise (yes, Enterprise) provides a new HA (High Availability) and DR (Disaster Recovery) option : Always On Availability Groups.

I know it is confusing, but there  are actually 2 Always on technologies :

  • Always On Failover Cluster Instance (for instance level protection)  : this is NOT NEW (in this option, we can have a clustered instance of SQL Server that requires a shared storage, which is a point of failure). Its is basically the good old clustering technology.
  • Always on Availability Groups (for database level protection) : this is NEW. It is a combination of the good old Clustering technology AND the mirroring technology + much more.

In this post we will quickly illustrate how Always on Availability Groups works with SharePoint 2013.

With the Always on Availability Groups, we can have:

  • multiple standalone SQL Server instances
  • each instance runs in a failover cluster that consists of multiple Windows Server Failover Clustering (WSFC) nodes (and this is very easy to setup).
  • databases (including SharePoint databases) are grouped in “Failover groups” that can be copied (mirrored) on each SQL Server instances (we don’t need shared storage here)
  • (secondary) node can be active (not just passive) : backup can be performed on the secondary nodes, and the mirrored databases can be readable if needed (this last option is not supported with SharePoint 2013)
  • every SharePoint database (including Service application database) can be part of an availability group (doing that with the logging database is not recommended, however; more details here)

 

In contrast to database mirroring, there is no witness role in AlwaysOn Availability Groups;the overall health of a WSFC cluster is determined by the votes of quorum of nodes in the cluster; each node can have a different weight.

Automatic failover is supported with SharePoint 2013; the SharePoint database server must be the Virtual Network Name of the Group listener.

We can have 4 nodes with SQL Server 2012 and up to 8 with SQL Server 2014.

At time of writing, the Virtual Network Name of the Group listener is not yet supported in Azure VMs.

Also, (but not possible yet in SharePoint 2013), Always On Listeners can be used to route read requests between the primary and readable secondary nodes for performance purposes : for instance we can imagine that an Archive Content database can be access in read mode on another node; this is not yet supported in SharePoint 2013. The connection string must specify ApplicationIntent=ReadOnly to be able to access readable replicas, that is probably one of the reason why it is not yet supported in SP2013; but is is achievable in custom applications.

Part 2. Demo

Here is our configuration :

everything works in my laptop, a Dell Precision 7600 with 32 GRam and Windows 8 Pro & HyperV.

4 VMs :

  • ShareQL_Ad (Active Directory)
  • ShareQL_SP (SharePoint)
  • ShareQL_SP1 (SQL Server)
  • ShareQL_SP2 (SQL Server)

 

1

The Failover Cluster feature has been installed and configured on 2 nodes : ShareQL_SQL1 (my secondary replica) and ShareQL_SQL2 (my primary replica).

If I go to ShareQL_SQL2, when I start the Failover Cluster Manager, I get this : 2 nodes and no shared storage (: indeed “Disks” is empty).

2

If we open SQL Server Management Studio, we can open a connection to our 2 nodes (my SQL Server instances are named SQL1\sharepoint on the ShareQL_SQL1 computer, and SQL2\sharepoint2 on the ShareQL_SQL2 machine (I wouldn’t do that anymore because during the database synchronization, SQL Server will require the data files to be in folders with the same relative names). Personal advice : keep the same instance name across the differents nodes.

I can also point to the Availability Group Listener (I’ve named it aglistener)

image

There is an interesting folder named “AlwaysOn High Availability”); right click on it and display the (Availability Group) dashboard:

image

This will show you the state of the situation : primary secondary node, the database states and the synchronization states.

image

The availability group listener (here “aglistener”) must be specified as the dafault database server in SharePoint Central Administration:

image

image

Now let’s start with a basic team site : ShareQL_SQL2 is used as the primary node

image

Let’s go to the AG dashboard and click on Start Failover Wizzard

image

The wizzard shows that we are going to switch from SQL2 as the current primary replica to SQL2

image

…and after 3 or 5 seconds, we get this :

image

If we check our team site again, it works ! so yes we had a very very short & limited loss of service (a few seconds), but SharePoint 2013 is smart enough to trap the error and to reinitialize its database connection(s).

Now here is the final situation :

image

Adding a database to an availability group is very easy as well:

go to the Availibility Groups folder, right click, select Add database:

image

About these ads

One response to “Protect your SharePoint 2013 data tier with SQL Server 2012 Always On Availability Groups

  1. Pingback: Le joli fruit de mes mauvaises fréquentations… ShareQL | The SqlGrrrl·

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s