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.

http://blogs.msdn.com/chrissk/archive/2010/01/28/troubleshooting-transactional-replication-part-1.aspx

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.

image

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

image

Now Let’s set setup a Distributor.

 

/****** Creating Distributor.

 

Chris Skorlinski

Microsoft SQL Server Escalation Services

 

              "REPL TALK"

Troubleshooting Transactional Replication - Distributor

       http://blogs.msdn.com/chrissk/

       http://blogs.msdn.com/repltalk/

  

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

GO

 

/****** 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!'

GO

 

/***** 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

GO

 

 

/****** 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

Go

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

GO

 

 

 

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

 

 

image

 

 

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

 

image

 

 

image

 

 

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. 

 

image

 

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'

GO

 

 

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.

 

 

image

 

image

 

 

 

/***** 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',

@security_mode=1

GO

 

--Display Distributor settings

sp_helpdistributor

 

image

 

 

--Disable Replication and DROP the distribution database

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

sp_dropdistributor 1,0