Sitecore 8.2 – SQL Server Always On Availability Groups

A few months ago I wrote a post talking about the changes in Sitecore 8.2 from a requirements perspective. At that time, I didn’t know about the support for SQL Server Always On Availability Groups!

Microsoft SQL Server Always On is only supported in Sitecore 8.2 and later

For many years High Availability and Disaster Recovery in SQL Server relied on Mirroring, Clustering, Log Shipping and a couple of others. Since SQL Server 2012 it is possible to enjoy all the benefits listed as follows:

• Data loss protection
• Automatic or manual failover
• Automatic page repair
• Active use of secondaries
• Local or shared storage

Although it has been available since SQL Server 2012, Sitecore first introduced Always On support on the initial release of its 8.2 version.

And how can I tell Sitecore to use my SQL Server Always On?

First of all, make sure you are running Sitecore 8.2 or later!

Second thing, you will need to change the way the ConnectionStrings.config connects to the SQL Server as you can see below:

<add name="master" connectionString="user id=sa;password=YourPasswordHere;Data Source=MySQL;Database=Sitecore_Master;ConnectRetryCount=10;ConnectRetryInterval=5" />

The new parameters ConnectRetryCount and ConnectRetryInterval are a reflection of the time it takes in seconds for a failover to complete.

BONUS!
In Sitecore.config there’s a parameter named Retryer which can be increased, and also used for debug purposes as you can see below:

<retryer disabled="false" type="Sitecore.Data.DataProviders.Retryer, Sitecore.Kernel">
<param desc="Number of tries">30</param>
<param desc="Interval between tries">00:00:01.000</param>
<param desc="Log each exception (should be used for debug only)">false</param>
</retryer>

BONUS! BONUS!

As soon as I finished this post, it came to my mind “How Sitecore controls the communication with SQL Server Always On?” and I thought “LET’S ASK”, then I went to http://sitecore.stackexchange.com/

And thanks to Richard Seal, now I know that the SqlConnection object handles that and it is part of .NET!

If you are more interested in my question and on the answer, check out here

I hope you enjoyed it—thanks for reading! I’ll see you on my next post!