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:
To timely react or prevent a problem, you should be aware of following issues that you may face while setting up the Transactional Replication:
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.
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.
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.
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" />
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.
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.
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:
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.
Right click on the newly created publication and choose "New Subscriptions" to configure its Subscriptions.
Follow the Wizard steps and instructions to configure Subscriptions:
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.
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.