Configure SQL Server Always On for the Core, Master, Web, and Reporting databases

Version: 10.0

You can use Microsoft SQL Server Always On to scale any Sitecore SQL Server database, such as the core, master, web, reporting, or session databases.

For up-to-date information about the SQL Server scaling features that Sitecore supports, visit the Sitecore Knowledge base.

Note

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

The benefits of using Microsoft SQL Server Always On include:

High-availability disaster recovery (HADR) – SQL Server keeps live copies of your databases on secondary servers which can be used for backup.
Data loss protection – potentially zero data loss protection if you are using synchronous data replication.
Failover of multiple databases – databases failover at the same time rather than individually.
Automatic or manual failover – set manual or automatic failover. If you use automatic failover you also need to use synchronous data flow.
Automatic page repair – automatic recovery from corrupted pages.

To configure SQL Server Always On in your Sitecore solution, you edit your SQL Server database connection strings, and then configure the Retryer settings in Sitecore.

This topic describes how to:

Set the SQL Server connection string parameters

To configure a SQL Server database connection string to use SQL Server Always On, set the following additional parameters:

In your Sitecore solution, open the ConnectionStrings.config file in the Website\App_Config folder.
Set the ConnectRetryCount and ConnectRetryInterval values to reflect the time it takes in seconds for a failover to complete.

For example, the Master database connection string with SQL Server Always On parameters:
RequestResponse
```
<add name="master" connectionString="Data Source=*****;Initial Catalog=*****;Integrated Security=False;User ID=sa;Password=12345;ConnectRetryCount=10;ConnectRetryInterval=5" />
```
Save your changes.

Set the Retryer parameters in Sitecore

When you use SQL Server Always on, you must enable the retryer.

You can increase the default Retryer parameter values to reflect the time it takes in seconds for a failover to complete.

To configure the Retryer parameters:

In your Sitecore solution, open the Sitecore.config file in the \Website folder.
In the Sitecore.config file, enable the Retryer by changing the retryer disabled="true" setting to retryer disabled="false".

Set the Retryer parameters:

RequestResponse

  <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>

You can set the following three configuration parameters:

Parameter	Description	Default values
Number of tries	The maximum number of attempts to make.	`30`
Interval between tries	The time interval between attempts in seconds.	`00:00:01.000`
Log each exception	Only use this parameter for debug purposes.	`false`

Do you have some feedback for us?

If you have suggestions for improving this article,