Walkthrough setting up SQL Replication Distributor using TSQL commands
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
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.
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’
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.
/***** 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
–Disable Replication and DROP the distribution database
—WARNING: This also drops ALL Publication and Subscriptions using the Distributor
sp_dropdistributor 1,0
