Description

This article describes the configuration, benefits, known issues, limitations and possible risks of using SQL Server Transactional Replication for CD environments.

Primary scenario for choosing transactional replication is when running distributed Sitecore environments with remote publishing targets.

In such high latency environments, publishing can become slow when using the standard publishing approach. Transactional replication may notably increase the performance of transferring content changes between multiple Sitecore instances.

Important! Please carefully evaluate the Limitations and Risks section of the article before using Transactional Replication.

Architecture diagram of the solution described in this article looks like this:

Benefits

• Transactional replication may significantly increase the performance of transferring content changes from CM to geographically remote CD environments.
• The data changes are applied to the Subscribers databases in the same order as they occur at the Publisher database. Transactional consistency is guaranteed within a publication (e.g. order of events in EventQueue table). This reduces the risks of Sitecore functionality being negatively affected by enabling this type of replication.
• Transactional replication is recommended by Microsoft when comparing to replication techniques such as Merge replication.
• Similar approach has been validated to work with SQL Azure Geo-Replication replication.

Limitations And Risks

• This setup is experimental and has not been validated by the Sitecore Quality Assurance team. Some issues are possible. In case of related issues, Sitecore Support will help with troubleshooting, while there is no guarantee that a fix may be available.
• The official way how to configure Geo-replication in Sitecore XP 9.0 and higher is defined in the following documents:
  • Walkthrough: Replicating the Web database using Azure active geo-replication
  • Configure Content Delivery roles for separate EventQueue and Properties tables.
• This article covers the configuration of transactional replication only for Web databases and for CD environments. Core and Master (CM) databases are unlikely to work with Transactional Replication.
• Web Forms For Marketers or other Sitecore modules were not enabled as a part of this experimental setup and may not work.
• By default, Subscribers to transactional publications should be treated as read-only. Changes at Subscribers are not propagated back to the Publisher. This does not allow CD servers to update any content.
• Transactional Replication requires primary keys on all replicated tables. Such keys are not present in a standard Sitecore databases schema, which may add complexities during the upgrade process.
• Sitecore plans to introduce full out-of-the-box support for this type of replication in the future, while there is no ETA available at the moment.
• Sitecore 8.2 and later provides Sitecore Publishing Service feature designed to improve publishing speed in high latency distributed environments. The feature can be used as an alternative to SQL replication and is officially supported. Read more here: https://dev.sitecore.net/Downloads/Sitecore_Publishing_Service.aspx

Known Issues

To timely react or prevent a problem, you should be aware of following issues that you may face while setting up the Transactional Replication:

1. Default value of server configuration option "max text repl size" (65536 bytes) is not enough. As a solution, consider configuring it to be unlimited. Follow this article for more information.

2. When running the Distribution Agent, "The distribution agent failed to create temporary files" errors may occur. To work around this issue, grant necessary rights to the account that is running SQL Server Agent.

3. Replication errors related to data inconsistencies between Publisher and Subscriber may appear if Subscriber is not treated like read-only. Example of the error is "the row does not exist on subscriber". In such situation, replication may get stuck and content changes will not be delivered to Subscriber.
   
   Therefore, Sitecore highly recommends using SQL logins with read-only access to the Subscriber web database. With this configuration, if any write operation from CD server is initiated by Sitecore user, job or API, only an error message will appear in Sitecore log files. The operation will however not be executed and Transactional Replication will continue working normally.
   
   To reduce the amount of errors in the log file, it is also recommended to disable Sitecore cleanup agents and any custom agents you have, which can write to Subscriber web database. Any write operations should be executed on the Publisher side. The changes will be delivered to Subscribers automatically by Transactional Replication.

4. Sitecore writes last processed event stamp value into Properties table with some interval (10 seconds by default). It is recommended to change the value of "EventQueue.PersistStampInterval" setting to a really big one to prevent attempts to store it in the database. Otherwise, it will continuously generate a lot of errors in log file.

   <setting name="EventQueue.PersistStampInterval" value="999:00:00" />

   
   

   Sitecore will continue working correctly in this configuration because it also maintains related value in the in-memory cache.
   
   Also, there is a specific setting in Sitecore to control the maximum age of remote events to be replayed on startup. Consider to keep this value something bigger than Sitecore initialize time to cover it.

   <setting name="EventQueue.PersistStampMaxAge" value="00:10:00" />

Configuration

With the help of "SQL Server Configuration Manager", make sure that "SQL Server" and "SQL Server Agent" services have their "Start Mode" in "Automatic" and necessary rights are granted to their log on accounts, so they are able to start automatically.

It is suggested to perform the initial testing and configuration in QA environment. For every configuration step below you are able to check the option to "generate a script file with steps to configure" it. With the minor changes, like the database names, you are able to use these scripts to configure the replication on production environment by executing T-SQL queries.

Configuring Distribution

In MS SQL Server Management Studio Object Explorer, right click on "Replication" node and click Configure Distribution.

Follow the Wizard steps and instructions to configure Distribution server, snapshot folder, Distribution Database, Database file and Database log file location, allowed publishers.

Verify the choices and click Finish. Configuration should be executed without any errors.

Configuring Publication

Consider to execute ReplColumnsAndKeys.sql script at the publisher database to create primary keys for all tables without them, as it is required for Transactional Replication.

Right click on "/Replication/Local Publications" node and choose "New Publication".

Follow the Wizard steps and instructions to configure Publication Database and Publication Type (Transactional publication).

Select all tables and view to publish as articles and apply the following articles properties:

• Copy nonclustered indexes (true) to all table articles;
• Copy user-defined statistics (true) to all table articles;
• Copy default value specification (true) to all table articles;
• Convert timestamp to binary (true) to all table articles to let the replication troubleshooting be more effective and intuitive; (Optional)

Follow the Wizard steps and instructions to configure the snapshot agent schedule, security settings and publication name.

Verify the choices and click Finish. Configuration should be executed without any errors. The new publication will appear under "/Replication/Local Publications" node.

Configuring Subscription

Right click on the newly created publication and choose "New Subscriptions" to configure its Subscriptions.

Follow the Wizard steps and instructions to configure Subscriptions:

• Consider to run the Distribution Agent at Subscriber side (pull subscriptions) to reduce the processing overhead at the Distributor;
• Choose to run the Distribution Agent continuously;

Verify the choices and click Finish. Configuration should be executed without any errors. The new subscription will appear under the newly created publication.

If you choose to initialize immediately, The Distribution agent will apply schema changes and bulk copy the data to Subscribers. Since this time changes at Publisher will be populated to Subscribers.

Notes

To view the Snapshot Agent Status, Log Reader Agent Status or Replication Monitor, right click on desired publication and choose appropriate context menu option.

To view the Subscription History, Properties, Status, configure the Log Reader Agent Job Properties or Distribution Agent Profile double click on desired Subscription in Replication Monitor.

You can find the jobs and error logs related to the replication under the appropriate SQL Server Agent Jobs and Error Logs nodes.

It is possible to configure SQL Server Agent Alerts to send you notifications on Replication Agents shutdowns, errors, validations etc.

Follow the official Microsoft documentation to find out more about the replication features.