Home  |  Articles  |  Scripts  |  Consultancy  |  FAQs  |  Links  |  SSIS

SQL Server 2005: Peer to Peer Transactional Replication Setup

These scripts have been created to set up a peer to peer system.

The settings I've used:

You'll need to create an identically named database on 2 servers (called peertopeerPublisher for me) or on a default and a named instance (this is because the publication names must be identical, so we can't do this on the same instance). After that you'll need to enable distribution on each server and publishing of the database. This is much the same as before, albeit in a different part of the GUI. There are 2 scripts below - (1) and (2) and all you need to do is use find and replace to modify them according to your settings. 

(1) On First Server
use peertopeerPublisher
go

sp_addlogreader_agent 
@job_login = 'PAULSDOMAIN\Administrator'
, @job_password = 'password'
, @publisher_security_mode = 1 
, @publisher_login = 'PAULSDOMAIN\Administrator' 
, @publisher_password = 'password' 
, @publisher = null 
go

exec sp_addpublication 
@publication = 'PeerToPeer1', 
@description = 'test peer to peer', 
@sync_method = 'concurrent', 
@retention = 0, 
@allow_push = 'true', 
@allow_pull = 'true', 
@allow_anonymous = 'false', 
@enabled_for_internet = 'false', 
@snapshot_in_defaultfolder = 'true', 
@compress_snapshot = 'false', 
@ftp_port = 21, 
@ftp_login = 'anonymous', 
@allow_subscription_copy = 'false', 
@add_to_active_directory = 'false', 
@repl_freq = 'continuous', 
@status = 'active', 
@independent_agent = 'true', 
@immediate_sync = 'true', 
@allow_sync_tran = 'false', 
@autogen_sync_procs = 'false', 
@allow_queued_tran = 'false', 
@allow_dts = 'false', 
@replicate_ddl = 1, 
@allow_initialize_from_backup = 'false', 
@enabled_for_p2p = 'true'
go

exec sp_addarticle 
@publication = N'PeerToPeer1', 
@article = N'PITest', 
@source_owner = N'dbo', 
@source_object = N'PITest', 
@type = N'logbased', 
@description = N'', 
@creation_script = N'', 
@pre_creation_cmd = N'none', 
@schema_option = 0x00000000000044F7, 
@identityrangemanagementoption = N'manual', 
@destination_table = N'PITest', 
@destination_owner = N'dbo', 
@status = 16, 
@vertical_partition = N'false', 
@ins_cmd = N'CALL [sp_MSins_PITest]', 
@del_cmd = N'CALL [sp_MSdel_PITest]', 
@upd_cmd = N'MCALL [sp_MSupd_PITest]'
go

exec sp_addsubscription 
@publication = 'PeerToPeer1', 
@subscriber = 'HOME-SERVER\FIRSTINSTANCE', 
@destination_db = 'peertoPeerPublisher', 
@sync_type = 'replication support only' 
go

exec sys.sp_addpushsubscription_agent 
@publication = N'PeerToPeer1', 
@subscriber = N'HOME-SERVER\FIRSTINSTANCE', 
@subscriber_db = N'PeerToPeerSubscriber', 
@job_login = N'PAULSDOMAIN\Administrator',
@job_password = 'password', 
@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'

(2) On Second Server
use peertopeerPublisher
go

sp_addlogreader_agent 
@job_login = 'PAULSDOMAIN\Administrator'
, @job_password = 'password'
, @publisher_security_mode = 1 
, @publisher_login = 'PAULSDOMAIN\Administrator' 
, @publisher_password = 'password' 
, @publisher = null 
go

exec sp_addpublication 
@publication = 'PeerToPeer1', 
@description = 'test peer to peer', 
@sync_method = 'concurrent', 
@retention = 0, 
@allow_push = 'true', 
@allow_pull = 'true', 
@allow_anonymous = 'false', 
@enabled_for_internet = 'false', 
@snapshot_in_defaultfolder = 'true', 
@compress_snapshot = 'false', 
@ftp_port = 21, 
@ftp_login = 'anonymous', 
@allow_subscription_copy = 'false', 
@add_to_active_directory = 'false', 
@repl_freq = 'continuous', 
@status = 'active', 
@independent_agent = 'true', 
@immediate_sync = 'true', 
@allow_sync_tran = 'false', 
@autogen_sync_procs = 'false', 
@allow_queued_tran = 'false', 
@allow_dts = 'false', 
@replicate_ddl = 1, 
@allow_initialize_from_backup = 'false', 
@enabled_for_p2p = 'true'
go

exec sp_addarticle 
@publication = N'PeerToPeer1', 
@article = N'PITest', 
@source_owner = N'dbo', 
@source_object = N'PITest', 
@type = N'logbased', 
@description = N'', 
@creation_script = N'', 
@pre_creation_cmd = N'none', 
@schema_option = 0x00000000000044F7, 
@identityrangemanagementoption = N'manual', 
@destination_table = N'PITest', 
@destination_owner = N'dbo', 
@status = 16, 
@vertical_partition = N'false', 
@ins_cmd = N'CALL [sp_MSins_PITest]', 
@del_cmd = N'CALL [sp_MSdel_PITest]', 
@upd_cmd = N'MCALL [sp_MSupd_PITest]'
go

exec sp_addsubscription 
@publication = 'PeerToPeer1', 
@subscriber = 'HOME-SERVER', 
@destination_db = 'peertoPeerPublisher', 
@sync_type = 'replication support only' 
go

exec sys.sp_addpushsubscription_agent 
@publication = N'PeerToPeer1', 
@subscriber = N'HOME-SERVER', 
@subscriber_db = N'PeerToPeerSubscriber', 
@job_login = N'PAULSDOMAIN\Administrator',
@job_password = 'password', 
@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'