Configuring Peer-to-Peer replication using T-SQL

This article brings you the easiest way to configure P2P replication using TSQL. Also I assume that the database has been initialized in all nodes participating in the replication.

1. Configure Distributor:

This script needs to be executed against all servers that participates in the replication.

/* Check if distributor database already exists*/
DECLARE @svrname AS sysname
SELECT @svrname = @@SERVERNAME
DECLARE @cnt INT
SELECT @cnt=COUNT(*) FROM sys.databases WHERE name = 'distribution'
IF @cnt>0
BEGIN
PRINT 'distributor already exists'
END
ELSE
BEGIN
EXEC sp_adddistributor @distributor=@svrname
EXEC sp_adddistributiondb @database='distribution',@security_mode=1
END

2. Create Publication and Subscriber:

This script needs to be executed against all the publisher databases involved in the P2P replication instances. There are few parameters that you might need to change in this script which will be noted so edit the parameters before executing.

/*Add the publisher*/
DECLARE @svrname AS sysname
SELECT @svrname = @@SERVERNAME
/* Add the publisher server now*/
EXEC sp_adddistpublisher @publisher =@svrname,
@distribution_db = N'distribution',
@security_mode = 1,
@publisher_type = N'MSSQLSERVER'
/* Enable the database for publication*/
DECLARE @dbname VARCHAR(30)
DECLARE @dbcheck SQL_VARIANT
/* Check if the database is set as publication*/
/*Set you database name*/
SELECT @dbname = '------' /***** Set Your Database Name Here**************/
SELECT @dbcheck = DATABASEPROPERTYEX(@dbname,'ispublished')
SELECT @dbcheck
IF @dbcheck = 0
BEGIN
PRINT 'Database not set as Publisher. Setting as Publisher'
EXEC sp_replicationdboption @dbname=@dbname,
@optname='publish',
@value='true'
END
ELSE
BEGIN
PRINT 'Database already set as Publisher'
END
/* Add the Publication now*/
DECLARE @pubname VARCHAR(300)
SELECT @pubname = '---------' /********Add Publication Name Here***********/
/*Execute this step against the database which you need to set as publication*/
EXEC sp_addpublication @publication=@pubname,
@restricted='false',
@sync_method='native',
@repl_freq='continuous',
@allow_push='true',
@allow_pull='true',
@immediate_sync='true',
@allow_sync_tran='false',
@autogen_sync_procs='false',
@retention=60,
@independent_agent='true',
@enabled_for_p2p='true',
@status='active',
@allow_initialize_from_backup='true'
GO
/* Add articles for this Publication*/
/**********This step needs to be executed for each article in the publication***************/
DECLARE @artname VARCHAR(300)
DECLARE @artins VARCHAR(300)
DECLARE @artdel VARCHAR(300)
DECLARE @artupd VARCHAR(300)
SELECT @artname = '-------'/**********Add Article Name Here***************/
SELECT @artins = 'CALL [sp_MSins_'+@artname+']'
SELECT @artdel = 'CALL [sp_MSdel_'+@artname+']'
SELECT @artupd = 'CALL [sp_MSupd_'+@artname+']'
/*Execute this step against the database which you need to set as publication*/
DECLARE @pubname VARCHAR(300)
SELECT @pubname = '----------'/***********Add your publication name here*************/
EXEC sp_addarticle @publication = @pubname,
@article = @artname,
@source_owner = N'dbo',
@source_object = @artname,
@type = N'logbased',
@description = N'',
@creation_script = N'',
@pre_creation_cmd = N'none',
@schema_option = 0x00000000000044F7,
@identityrangemanagementoption = N'manual',
@destination_table = @artname,
@destination_owner = N'dbo',
@status = 16,
@vertical_partition = N'false',
@ins_cmd = @artins,
@del_cmd = @artdel,
@upd_cmd = @artupd
GO
/*Add the Subscriber*/
DECLARE @pubname VARCHAR(300)
SELECT @pubname = '----------'/***********Add your publication name here*************/
DECLARE @dbname VARCHAR(30)
SELECT @dbname = '----------'/***********Add your Subscriber database name here*************/
DECLARE @subname VARCHAR(30)
SELECT @subname = '----------'/***********Add your Subscriber Server name here*************/
EXEC sp_addsubscription @publication = @pubname,
@subscriber = @subname,
@destination_db = @dbname,
@sync_type = 'replication support only'
GO
/*Add the Subscriber Agents*/
DECLARE @pubname VARCHAR(300)
SELECT @pubname = '----------'/***********Add your publication name here*************/
DECLARE @dbname VARCHAR(30)
SELECT @dbname = '----------'/***********Add your Subscriber database name here*************/
DECLARE @subname VARCHAR(30)
SELECT @subname = '----------'/***********Add your Subscriber Server name here*************/
EXEC sys.sp_addpushsubscription_agent
@publication = @pubname,
@subscriber = @subname,
@subscriber_db = @dbname,
@subscriber_security_mode = 1,
@frequency_type = 64,
@frequency_interval = 1,
@frequency_relative_interval = 1,
@frequency_recurrence_factor = 0,
@frequency_subday = 4,
@frequency_subday_interval = 5,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_start_date = 0,
@active_end_date = 0,
@dts_package_location = N'Distributor'