SQL Server Replication Enhancement – Dynamic Reloading of Agent Profile Parameters

  • Article

With SQL Server 2017 Cumulative Update 3, we introduced an improvement to SQL Server Replication wherein, changes to the Replication agent parameters can be reloaded dynamically, without having to restart the agent. This improvement will be available in upcoming updates for SQL Server 2016 and SQL Server 2014.

Dynamic Reloading of Agent Parameters

Replication uses several standalone programs, called agents, to carry out the tasks associated with tracking changes and distributing data.  For example, Log Reader Agent used with transactional replication, moves transactions marked for replication from the transaction log on the Publisher to the distribution database. Each database published using transactional replication has its own Log Reader Agent that runs on the Distributor and connects to the Publisher.

When replication is used on high traffic systems with high amount of DML operations, customers often need to tweak the default agent profiles to increase the throughput of the replications agents to keep up with the incoming load. The following sequence of steps are performed for these changes to take effect:

- Find all the relevant agents that needs a change and their corresponding jobs. This is very complex and time consuming when we have multiple publications and subscriptions.

- Disable the jobs corresponding to the agents.

- Stop the agent (stop the jobs if they are still running).

- Change the profile settings.

- Re-enable the jobs related to the affected agents.

- Start the agents

Sometimes this exercise needs to be repeated multiple times to right combination for the agent parameters, which can be tiresome and time consuming. The new improvement in Replication allows users to dynamically change the agent profile parameters without having to restart the agents. Currently the following parameters can be dynamically reloaded.

Log Reader Agent – Output, OutputVerboseLevel, PollingInterval, ReadBatchSize, ReadBatchThreshold

Distribution Agent – CommitBatchSize, CommitBatchThreshold, MaxDeliveredTransactions, PollingInterval, Output, OutputVerboseLevel, SubscriptionStreams.

How it works

When a new agent user profile is created and assigned to an agent, relevant entries are added to the [dbo].[MSagent_profiles] and the [dbo].[MSagent_parameters] tables in the MSDB database on the Distributor server. The values in the tables are updated whenever a parameter value is updated for the Profile. These values are reloaded from the tables, whenever a replication agent is restarted. As part of the improvement a new profile parameter "ProfileReloadInterval" with a default value of 300 seconds, was added to the profiles. This parameter dictates the interval at which the agent reloads the values from the tables. If using the default value, the agent will try and reload the values from the MSDB tables every 5 minutes. If it encounters that any of the above-mentioned parameter values have changed, the new values will be used by the agents.

Changing the Profile Parameter Values

The values can be changed using the SQL Server Management Studio or using the stored procedure sp_change_agent_parameters. Starting with SQL Server Management Studio 18.0, the agent profile UI has an extra column named "Reloadable" indicating if the said parameter can be dynamically reloaded.

When a parameter is changed and is dynamically reloaded, relevant information is added to the Replication Agent logs, if they are being captured. For example, in the snippet below we can see that the "ReadBatchSize" value was changed from 5000 to 500, without having to restart the agent.

2018-02-24 07:45:30.213 Publisher: {call sp_replcmds (5000, 0, 0, , 5047, 500000)}
2018-02-24 07:45:35.264 Publisher: {call sp_replcmds (5000, 0, 0, , 5047, 500000)}
2018-02-24 07:45:39.939 OLE DB DistLog 'CAPTAINAMERICA': exec sp_MShelp_logreader_agentid 2, N'WideWorldImporters'
2018-02-24 07:45:40.020 OLE DB DistLog 'CAPTAINAMERICA': exec sp_MShelp_profile 2, 2, N'testProfile'
2018-02-24 07:45:40.324 Status: 4, code: 20085, text: 'The agent parameter '-ReadBatchSize' with value 500 is taking effect.'.
2018-02-24 07:45:40.507 Publisher: {call sp_replcmds (500, 0, 0, , 5250, 500000)}
2018-02-24 07:45:45.566 Publisher: {call sp_replcmds (500, 0, 0, , 5234, 500000)}

 

Sourabh Agarwal
SQL Server Tiger Team Twitter | LinkedIn
Follow us on Twitter: @mssqltiger | Team Blog: Aka.ms/sqlserverteam