Messaging SQL high availability configurations

  • Description

    The Sitecore Messaging library can be configured to retry connecting to the SQL database in case a connection error occurs. The Sitecore.Framework.TransientFaultHandling library is used to provide the retry functionality. The following steps are required to configure Messaging to use the connection retrying behavior.

  • Defining a retryer

    The following example defines an Exponential Retryer that will fail after retrying to connect to the database 10 times.

    The DefaultSqlPolicyRetryerFactory creates a retryer that, by default, can handle SQL Always On and SQL Azure transient errors. Additional error codes can be added under the CustomErrorCodes section:

    1. For xConnect instances, change the \App_Data\Config\Sitecore\Messaging\sc.XConnect.TransientFaultHandling.xml and \App_Data\jobs\continuous\AutomationEngine\App_Data\Config\sitecore\Messaging\sc.XConnect.TransientFaultHandling.xml files (if they exist) as follows:
      <Settings>
        <Sitecore>
          <XConnect>
            <Services>
              <TransientFaultHandling>
                <Type>Sitecore.Framework.TransientFaultHandling.RetryerRegistry, Sitecore.Framework.TransientFaultHandling</Type>
                <As>Sitecore.Framework.TransientFaultHandling.IRetryerRegistry, Sitecore.Framework.TransientFaultHandling.Abstractions</As>
                <Options>
                  <Retryers>
                    <Messaging.ExponentialRetryer>
                      <Type>Sitecore.Framework.TransientFaultHandling.Sql.DefaultSqlPolicyRetryerFactory, Sitecore.Framework.TransientFaultHandling.Sql</Type>
                      <Options>
                        <CustomErrorCodes>
                          <ConnectionError>19</ConnectionError>
                        </CustomErrorCodes>
                        <ExponentialRetry>
                          <MaxAttempts>10</MaxAttempts>
                          <MinBackoff>00:00:01</MinBackoff>
                          <MaxBackoff>00:00:30</MaxBackoff>
                          <DeltaBackoff>00:00:10</DeltaBackoff>
                        </ExponentialRetry>
                      </Options>
                    </Messaging.ExponentialRetryer>
                  </Retryers>
                </Options>
              </TransientFaultHandling>
            </Services>
          </XConnect>
        </Sitecore>
      </Settings>
    2. Create a new configuration file in the \App_Config\Include folder of your Sitecore XP instance containing the following:
      <configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
        <sitecore>
          <TransientFaultHandling>
            <Retryers>
              <Messaging.ExponentialRetryer>
                <Type>Sitecore.Framework.TransientFaultHandling.Sql.DefaultSqlPolicyRetryerFactory, Sitecore.Framework.TransientFaultHandling.Sql</Type>
                <Options>
                  <CustomErrorCodes>
                    <ConnectionError>19</ConnectionError>
                  </CustomErrorCodes>
                  <ExponentialRetry>
                    <MaxAttempts>10</MaxAttempts>
                    <MinBackoff>00:00:01</MinBackoff>
                    <MaxBackoff>00:00:30</MaxBackoff>
                    <DeltaBackoff>00:00:10</DeltaBackoff>
      	    </ExponentialRetry>
                </Options>
              </Messaging.ExponentialRetryer>
            </Retryers>
          </TransientFaultHandling>
        </sitecore>
      </configuration>
    3. For the Sitecore Cortex™ Processing Engine Role:
      1. Create a new configuration file in the \App_Data\jobs\continuous\ProcessingEngine\App_Data\Config\Sitecore\Messaging folder (for example, sc.Messaging.Retry.xml) with the following content:
        <Settings>
          <Sitecore>
            <Processing>
              <Services>
                <RetryerRegistry>
                  <Options>
                    <Retryers>
                      <ExponentialRetryer>
                        <Type>Sitecore.Framework.TransientFaultHandling.Sql.DefaultSqlPolicyRetryerFactory, Sitecore.Framework.TransientFaultHandling.Sql</Type>
                        <Options>
                          <CustomErrorCodes>
                            <ConnectionError>19</ConnectionError>
                          </CustomErrorCodes>
                          <ExponentialRetry>
                            <MaxAttempts>10</MaxAttempts>
                            <MinBackoff>00:00:01</MinBackoff>
                            <MaxBackoff>00:00:30</MaxBackoff>
                            <DeltaBackoff>00:00:10</DeltaBackoff>
                          </ExponentialRetry>
                        </Options>
                      </ExponentialRetryer>
                    </Retryers>
                  </Options>
                </RetryerRegistry>
              </Services>
            </Processing>
          </Sitecore>
        </Settings>
      2. For the On-premise setup, restart the Processing Engine Windows service.
  • Configure Messaging Bus to use Retryers

    In order to configure a Messaging Bus to use TransientFaultHandling retryers, the Messaging transport needs to explicitly specify the retryer in its configuration. This can be done via the following configuration:

    1. Define a retryer for Sitecore Cortex Processing Engine role:
      1. In the \xconnect\App_Data\jobs\continuous\ProcessingEngine\App_Data\Config\Sitecore\Messaging\sc.Processing.Engine.TaskProgress.xml file, add a new child node <RetryerName> with the retryer name defined in the sc.Messaging.Retry.xml file into the <SqlServer> section:
        <Sitecore.Processing.Engine.Buses.TaskProgressConsumer>
          <Transport>
            <SqlServer>
              <OneWay>false</OneWay>
              <ConnectionStringOrName>messaging</ConnectionStringOrName>
              <TableName>Sitecore_Transport</TableName>
              <InputQueueName>SitecoreProcessingTaskProgressConsumer</InputQueueName>
              <RetryerName>ExponentialRetryer</RetryerName>
            </SqlServer>
          </Transport>
          <Logging Type="Sitecore.Framework.Messaging.Rebus.DotNetLoggerFactory, Sitecore.Framework.Messaging.Rebus" />
        </Sitecore.Processing.Engine.Buses.TaskProgressConsumer>
      2. In the \xconnect\App_Data\jobs\continuous\ProcessingEngine\App_Data\Config\Sitecore\Messaging\sc.Processing.Engine.TaskRegistration.xml file, add a new child node <RetryerName> with the retryer name defined in the sc.Messaging.Retry.xml file into the <SqlServer> section:
        <Sitecore.Processing.Engine.Buses.TaskRegistrationConsumer>
          <Transport>
            <SqlServer>
              <OneWay>false</OneWay>
              <ConnectionStringOrName>messaging</ConnectionStringOrName>
              <TableName>Sitecore_Transport</TableName>
              <InputQueueName>SitecoreProcessingTaskRegistrationConsumer</InputQueueName>
              <RetryerName>ExponentialRetryer</RetryerName>
            </SqlServer>
          </Transport>
          <Logging Type="Sitecore.Framework.Messaging.Rebus.DotNetLoggerFactory, Sitecore.Framework.Messaging.Rebus" />
        </Sitecore.Processing.Engine.Buses.TaskRegistrationConsumer>
      3. In the \xconnect\App_Data\jobs\continuous\ProcessingEngine\App_Data\Config\Sitecore\Messaging\sc.Processing.Engine.TaskStatusBus.xml file, add a new child node <RetryerName> with retryer name defined in sc.Messaging.Retry.xml file into the <SqlServer> sections of the <Transport> and <Subscriptions> nodes:
        <Sitecore.Processing.Engine.Abstractions.Buses.TaskStatusBus>
          <Transport>
            <SqlServer>
              <OneWay>false</OneWay>
              <!--Set of credentials for proper connection to database-->
              <ConnectionStringOrName>messaging</ConnectionStringOrName>
              <!--The name of the table which holds messages until they are handled-->
              <TableName>Sitecore_Transport</TableName>
              <!--The name of MSMQ queue which publisher uses for publishing message to the bus-->
              <InputQueueName>TaskStatus</InputQueueName>
              <RetryerName>ExponentialRetryer</RetryerName>
            </SqlServer>
          </Transport>
          <Subscriptions>
            <SqlServer>
              <!--Set of credentials for proper connection to database-->
              <ConnectionStringOrName>messaging</ConnectionStringOrName>
              <!--The name of the table which saves the information about the relationships between queues and messages-->
              <TableName>Sitecore_Subscriptions</TableName>
              <!--Means that both subscribers and publishers have access to the same central subscription storage-->
              <IsCentralized>true</IsCentralized>
              <RetryerName>ExponentialRetryer</RetryerName>
            </SqlServer>
          </Subscriptions>
          <Logging Type="Sitecore.Framework.Messaging.Rebus.DotNetLoggerFactory, Sitecore.Framework.Messaging.Rebus" />
        </Sitecore.Processing.Engine.Abstractions.Buses.TaskStatusBus>
      4. Restart the Processing Engine windows service in case of on-premise deployment (Azure will handle configuration file changes automatically).
    2. Define a retryer for the proper working of the Sitecore Cortex Processing Engine on Sitecore XP CM or Standalone roles (depending on your setup). In the \App_Config\Sitecore\Processing.Tasks.Messaging.Xmgmt\Sitecore.Processing.Tasks.Messaging.config file, add a new child node <RetryerName> with the retryer name defined in the sc.Messaging.Retry.xml file into the <SqlServer> section of the following nodes: <Sitecore.Processing.Tasks.Messaging.Buses.TaskRegistrationProducer> and <Sitecore.Processing.Tasks.Messaging.Buses.TaskProgressProducer>. The following is an example for the <Sitecore.Processing.Tasks.Messaging.Buses.TaskRegistrationProducer> node:
      <Sitecore.Processing.Tasks.Messaging.Buses.TaskRegistrationProducer>
        <Transport>
          <SqlServer>
            <OneWay>false</OneWay>
            <ConnectionStringOrName>messaging</ConnectionStringOrName>
            <TableName>Sitecore_Transport</TableName>
            <!-- InputQueueName should be unique for CM/CD instance to handle response messages on the correct instance. -->
            <InputQueueName>SitecoreProcessingTaskRegistrationProducer_${MachineName}_${ProcessId}</InputQueueName>
            <RetryerName>ExponentialRetryer</RetryerName>
          </SqlServer>
        </Transport>
        <Routing>
          <TypeBasedMappings>
            <TypeMappings>
              <RegisterDistributedTaskMap>
                <Type>Sitecore.Processing.Engine.Abstractions.Messages.RegisterDistributedTask, Sitecore.Processing.Engine.Abstractions</Type>
                <DestinationQueue>SitecoreProcessingTaskRegistrationConsumer</DestinationQueue>
              </RegisterDistributedTaskMap>
              <RegisterDeferredTaskMap>
                <Type>Sitecore.Processing.Engine.Abstractions.Messages.RegisterDeferredTask, Sitecore.Processing.Engine.Abstractions</Type>
                <DestinationQueue>SitecoreProcessingTaskRegistrationConsumer</DestinationQueue>
              </RegisterDeferredTaskMap>
            </TypeMappings>
          </TypeBasedMappings>
        </Routing>
        <Logging Type="Sitecore.Messaging.SitecoreLoggerFactory,Sitecore.Messaging"/>
      </Sitecore.Processing.Tasks.Messaging.Buses.TaskRegistrationProducer>
  • Configure EXM to use Retryers

    In order to configure EXM to use retryers, Sitecore.EmailExperience.Messaging.config files on corresponding servers should be updated.

    1. Define retryers on all CM instances. Navigate to \App_Config\Sitecore\Messaging and create a new configuration file as shown in the Defining a retryer section.
    2. On all CM instances, update the \App_Config\Sitecore\EmailExperience \Sitecore.EmailExperience.Messaging.config file by adding a new child node <RetryerName> with a retryer name after <InputQueueName> under the /configuration/Sitecore/Messaging/Rebus/[name of messaging bus]/Transport/SqlServer node for all messages buses:
      • Sitecore.EmailCampaign.Model.Messaging.Buses.AutomatedMessagesBus
      • Sitecore.EmailCampaign.Model.Messaging.Buses.UpdateListSubscriptionMessagesBus
      • Sitecore.EmailCampaign.Model.Messaging.Buses.ConfirmSubscriptionMessagesBus
      • Sitecore.EmailCampaign.Model.Messaging.Buses.EmailOpenMessagesBus
      • Sitecore.EmailCampaign.Model.Messaging.Buses.ClearSuppressionListMessagesBus
      • Sitecore.EmailCampaign.Model.Messaging.Buses.EmailAddressHistoryBus
      • Sitecore.EmailCampaign.Model.Messaging.Buses.SentMessagesBus

      For example:

      <Sitecore.EmailCampaign.Model.Messaging.Buses.UpdateListSubscriptionMessagesBus role:require="!DedicatedDispatch">
      <Transport>
        <SqlServer>
          <OneWay role:require="(Standalone or ContentManagement) and !ContentDelivery">false</OneWay>
          <OneWay role:require="ContentDelivery">true</OneWay>
          <ConnectionStringOrName>messaging</ConnectionStringOrName>
          <TableName>Sitecore_Transport</TableName>
          <InputQueueName>UpdateListSubscriptionMessagesQueue</InputQueueName>
          <RetryerName>ExponentialRetryer</RetryerName> 
        </SqlServer>
      </Transport>
      </Sitecore.EmailCampaign.Model.Messaging.Buses.UpdateListSubscriptionMessagesBus>
    3. Define retryers on all DDS instances. Navigate to \App_Config\Sitecore\Messaging and create a new configuration file as shown in the Defining a retryer section.
    4. Repeat the second step for the \App_Config\Sitecore\EmailExperience\Sitecore.EmailExperience.Messaging.config files on all DDS instances.
    5. Define retryers on all CD instances. Navigate to \App_Config\Sitecore\Messaging and create a new configuration file as shown in the Defining a retryer section.
    6. Repeat the second step for the \App_Config\Sitecore\EmailExperience\Sitecore.EmailExperience.Messaging.config files on all CD instances.
    7. Define retryers on the XConnect instance. Navigate to \yourInstance_collection\App_Data\Config\Messaging and create a new configuration file as shown in the Defining a retryer section.
    8. On the XConnnect instance update the \yourInstance_collection\App_Data\Config\Sitecore\Messaging\ sc.EXM.Messaging.xml file by adding a new child node <RetryerName> with a retryer name after the <InputQueueName> under the /Settings/Sitecore/XConnect/Services/Messaging/Options/Rebus/name of messaging bus/Transport/SqlServer node for all messages buses:
      • Sitecore.EmailCampaign.Model.Messaging.Buses.AutomatedMessagesBus
      • Sitecore.EmailCampaign.Model.Messaging.Buses.ClearSuppressionListMessagesBus

      For example:

      <Sitecore.EmailCampaign.Model.Messaging.Buses.AutomatedMessagesBus>
      <Transport>
        <SqlServer>
          <OneWay>true</OneWay>
            <ConnectionStringOrName>messaging</ConnectionStringOrName>
            <TableName>Sitecore_Transport</TableName>
            <InputQueueName>AutomatedMessagesQueue</InputQueueName>
            <RetryerName>ExponentialRetryer</RetryerName>
        </SqlServer>
      </Transport>
      <Sitecore.EmailCampaign.Model.Messaging.Buses.AutomatedMessagesBus>
    9. Repeat the previous step for the \App_Data\jobs\continuous\AutomationEngine\App_Data\Config\sitecore\Messaging\sc.EXM.Messaging.xml file.

Applies to:

CMS 9.1 Initial Release+

December 06, 2018
December 06, 2018