SQL Server transactional replication performance tuning and optimization

Forrards
March 31, 2023

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:

transactional replication settings

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.

replication subscription expiration

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.

Forrards
March 31, 2023
Share
Recent articles:
Database consultancy: SaaS database considerations
March 31, 2023

Utilizing given approach each client get his own database. In such case every client`s data stays isolated from others. It simplifies backing up the data, installing new clients but causes higher cost of maintenance.

More
Database consultancy: tree-like structure
March 31, 2023

Subject recursion is well covered in the literature, but, nevertheless, the problem of output “tree” does not mean the client and SQL Server many baffled. So, put the problem: there is a table with the name and record id field indicating the parent identifier. Immediately fill in this table, some sort of test data:

More
Database consultancy: Hierarchy in MS SQL Server
March 31, 2023

In our time of greatest prevalence of databases were relational databases, which are the main targets of the tables and the relationships between them. Tables can solve most problems for data storage and manipulation. But in the real world entity requiring storage is not always presented in a tabular form. One of these very common types of data structures other than the table is a tree structure, where each data element is the parent and the offspring. An example of such a structure may be the structure of state enterprises, which is headed by the director (the root of the tree), his deputies, heads of departments from which are subject to certain deputies, employees of departments, which are subject to the rulers.

More

Subscribe to our blog

    

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Database consultancy: SaaS database considerations

Utilizing given approach each client get his own database. In such case every client`s data stays isolated from others. It simplifies backing up the data, installing new clients but causes higher cost of maintenance.

March 31, 2023
Database consultancy: tree-like structure

Subject recursion is well covered in the literature, but, nevertheless, the problem of output “tree” does not mean the client and SQL Server many baffled. So, put the problem: there is a table with the name and record id field indicating the parent identifier. Immediately fill in this table, some sort of test data:

March 31, 2023

Discuss your project with us

    

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.