Walkthrough setting up SQL Replication Distributor using TSQL commands

Walkthrough setting up SQL Replication Distributor using TSQL commands

–Chris Skorlinski
–Microsoft SQL Server Escalation Services

The scripts below and the video capture walkthrough setting up a Microsoft SQL Server Replication Distributor using SQL commands. As discussed in previous posting, the Distributor is a key role in Replication.  It serves as a “cache” holding transactions for Transactional Replication and history tracking for all types of Replication.  I’ve highlighted some of the key options at each phase.


As you can see from SQL Server Management Studio, Databases, there is no Distribution database.  This confirms Replication Distributor has not yet been configured on this server.


Also if I expand the Replication folder the “Configure Distribution…” option is available.


Now Let’s set setup a Distributor.


/****** Creating Distributor.


Chris Skorlinski

Microsoft SQL Server Escalation Services


              “REPL TALK”

Troubleshooting Transactional Replication – Distributor




Purpose: These script will create a Replication Distributor


These script work best if you are both

       a SQL Server and a Windows Administrator.


Database: distribution




USE master



/****** 1) Specify Distributor Server name


       – KEY Parameters


@heartbeat_interval= 10           Agent job, default = 10 minutes

@password =                       Linked Server for distributor_admin account



exec sp_adddistributor @distributor = @@SERVERNAME,

              @heartbeat_interval= 10, @password = N’Password1!’



/***** 2) Adding default agent profiles


exec sp_MSupdate_agenttype_default @profile_id = 1

exec sp_MSupdate_agenttype_default @profile_id = 2

exec sp_MSupdate_agenttype_default @profile_id = 4

exec sp_MSupdate_agenttype_default @profile_id = 6

exec sp_MSupdate_agenttype_default @profile_id = 11




/****** 3) Adding the distribution database


       – KEY Parameters


@data_folder                Recommend Installing Distribution

                               database on own drive?

@max_distretention = 72,    Transaction cleanup = 3 days

                              balance size with “down time”

@min_distretention = 3      Minimum retention period

                            >0 usefule in recovery scenarios

@data_file_size             Initial database size.

                            Indirectly controlled by Distribution Cleanup Job

                            watch distribution db for unexpected growth

@history_retention = 48     Distribution History Tables

                            Keep more then 2 days when troubleshooting

@security_mode              1 = Windows Authentication

                            2 – SQL Authentication


use master


exec sp_adddistributiondb @database = N’distribution’,

@data_folder = null, @data_file = N’distribution.MDF’, @data_file_size = 7,

@log_folder = null, @log_file = N’distribution.LDF’, @log_file_size = 2,

@min_distretention = 0, @max_distretention = 72, @history_retention = 48,

@security_mode = 1





At this point the server has been defined as a Distributor.  You can verify the setting from Replication folder of SSMS as shown below.






To see settings click the build “. . . “ button.








On the Distributor Properties page, note the Distributor Password settings.  This “Replication Administrator”password is used by the Replication Agent to obtain various publication and subscription settings during Replication. 




Now let’s continue with the script and setup a Publisher and Subscriber that can use this Distributor.




/***** 4) Specifying Publishers to this Distributor


       – KEY Parameters


@working_directory   Defaults to the ReplData folder


       For example ‘C:\Program Files\Microsoft SQL Server\MSSQL\MSSQ.1\ReplData’


       Should be UNC for Snapshot Share for Snapshot files

       Example: \\CHRISSKACER\REPLDATA  


       Publisher will need Read/Write files access

       Subscrbier will need Read access rights


@publisher_type      MSSQLSERVER, ORACLE, or ORACLE Gateway.



exec sp_adddistpublisher @publisher = @@SERVERNAME,

@distribution_db = N’distribution’, @security_mode = 1,

@working_directory = N’\\CHRISSKACER\repldata’, @trusted = N’false’,

@thirdparty_flag = 0, @publisher_type = N’MSSQLSERVER’




The publisher name now also appears on the Distributor Properties screen.  Like the Distributor Properties, you can select eh “. . .” button to see the specific Publisher Properties.









/***** 5) Specifying Subscribers to this Distributor


       – Key Parameters


@type                0 = SQL Subscriber

                     1 = ODBC data source server

                     2 = Microsoft Jet database

                     3 = Other OLE DB providers


@security_mode       1 = Windows Authentication

                     2 – SQL Authentication


exec sp_addsubscriber @subscriber = @@SERVERNAME, @type = 0,

@description = N’Test Subscriber’,




–Display Distributor settings






–Disable Replication and DROP the distribution database

WARNING: This also drops ALL Publication and Subscriptions using the Distributor

sp_dropdistributor 1,0


Comments (0)