If you`re using transactional replication on SQL Server platform on rather large and data intensive environment you may have faced long latency and slow performance. Below are a few tips on how to boost your transactional replication.
1. If possible use replication distributor.
2. For each publisher setup separate distribution database.
3. Set the settings shown below for each publication:
3.1 Create a publication with needed subscriptions using SQL Server Management Studio GUI.
3.2 Script publication with subscriptions as drop and as create to the new windows.
3.3 Run the drop script.
3.4 Modify your publication creation script in the following way.
You`ll have something like this in the first part of the script in the window:
-- Enabling the replication database
use master
exec sp_replicationdboption @dbname = N'YourDBname', @optname = N'publish', @value = N'true'
GO
exec [YourDBname].sys.sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1
GO
-- Adding the transactional publication
use [YourDBname]
exec sp_addpublication @publication = N'YourPublicationName', @description = N'Transactional publication of database ''YourDBname'' from Publisher ''YourServer''.' , @sync_method = N'concurrent', @retention = 48, @allow_push = N'true', @allow_pull = N'false', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO
You`ll need to change a few parameters in that script like shown below:
-- Enabling the replication database
use master
exec sp_replicationdboption @dbname = N'YourDBname', @optname = N'publish', @value = N'true'
GO
exec [YourDBname].sys.sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1
GO
-- Adding the transactional publication
use [YourDBname]
exec sp_addpublication @publication = N'YourPublicationName', @description = N'Transactional publication of database ''YourDBname'' from Publisher ''YourServerName''.' , @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'false', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'false', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO
After doing this you`ll get the settings in publication like on the picture below:
Don`t forget to set independent distribution agent option to true manually.
After then set subscription expiration to 48 hours. By default this data is stored for too long time in distribution database which causes it`s growth and slowness.
And the final step is too setup your subscriptions. For that purpose run the rest of the script which you generated into the window.
Now you should be all set to run transactional replication way much faster than before. Keep an eye on it in replication monitor and feel free to ask any questions in the comments to this post.
