SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

How to set the default replication agent profile

Over the last couple of years I've come to the conclusion that the default replication agent profiles just don't cut it.  For example, usually I want my replication agent jobs (Snapshot, Log Reader, Distribution, or Merge) to have a custom replication agent profile which differs from the default agent profile.  I'm usually interested in increasing agent timeout parameters and/or tuning parameters such as the Merge Agent -UploadGenerationsPerBatch/-DownloadGenerationsPerBatch to suit my needs.  I thought I would take a moment to share with you an approach that is readily available but generally unknown to the average database administrator to achieve this goal without having to manually set every individual agent profile.

Before creating publications and subscriptions, which in turn create replication agent jobs (minus SQL Server Express Edition), custom Snapshot, Log Reader, Distribution, and Merge Agent profiles can be configured via SSMS or using T-SQL.  Then, using the Distributor Properties dialog, or T-SQL, we can set the default profile for the replication type we're wanting to set so that all newly created agents will use said profile.

To do this using SSMS, on the Distributor right-click on the Replication node in Object Explorer -> Distributor Properties..., click on Profile Defaults... — Add a new agent profile and check the profile as Default for New.

Default for New

From here all newly created agents will use that profile.

If you must use T-SQL, the same thing be accomplished using the undocumented stored procedure sp_MSupdate_agenttype_default.  The stored proc takes 1 parameter profile_id, and when executed will set the default agent profile for the agent_type of the specified agent profile.  This technique can be useful when creating several publications and/or subscriptions and all of them need to use a custom replication agent profile without having to set each one individually after the fact.


A blog about SQL Server Replication.


Leave a comment on the original post [www.sqlrepl.com, opens in a new window]

Loading comments...