Like this article? We recommend
Replication Script
Let's run through a replication script to see how it works; Enterprise
Manager adds numerous comments, but I added a few more to be specific. Because
this article is very lengthy, I don't have room to discuss the details of
each stored procedure. Be sure to check Books Online for each option used with
replication stored procedures.
/****** Please note: Any password parameter was scripted with
NULL or empty string for security reasons. When we execute these procedures
on other servers be sure to specify appropriate server names and passwords. ******/
/****** Script to be run at Distributor ******/
/****** Installing the server D10ZF411 as a Distributor. *****/
use master
GO
/* run sp_adddistributor to configure server as the distributor */
exec sp_adddistributor @distributor = N'D10ZF411', @password = N''
GO
/* Run sp_msupdate_agenttype to update the agent profile defaults.
Note that agent profiles will be discussed in the next article */
sp_MSupdate_agenttype_default @profile_id = 1
GO
sp_MSupdate_agenttype_default @profile_id = 2
GO
sp_MSupdate_agenttype_default @profile_id = 4
GO
sp_MSupdate_agenttype_default @profile_id = 6
GO
sp_MSupdate_agenttype_default @profile_id = 11
GO
/* Adding the distribution database */
exec sp_adddistributiondb
@database = N'distribution',
@data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data',
@data_file = N'distribution.MDF',
@data_file_size = 2,
@log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data',
@log_file = N'distribution.LDF',
@log_file_size = 0,
@min_distretention = 0,
@max_distretention = 72,
@history_retention = 48,
@security_mode = 1
GO
-- Adding the distribution publisher(s)
exec sp_adddistpublisher
@publisher = N'D10ZF411',
@distribution_db = N'distribution',
@security_mode = 1,
@working_directory = N'\\D10ZF411\C$\Program Files\Microsoft SQL Server\MSSQL\ReplData',
@trusted = N'false',
@thirdparty_flag = 0
GO
/****** Script to be run at Publisher ******/
-- Adding a subscriber server
exec sp_addsubscriber
@subscriber = N'D10ZF411\SUBSCRIPTION_SRV',
@type = 0,
@login = N'sa',
@password = N'',
@security_mode = 0,
@frequency_type = 64,
@frequency_interval = 1,
@frequency_relative_interval = 2,
@frequency_recurrence_factor = 0,
@frequency_subday = 8,
@frequency_subday_interval = 1,
@active_start_date = 0,
@active_end_date = 0,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235900,
@description = N''
/* set subscriber's synchronization schedule */
exec sp_changesubscriber_schedule
@subscriber = N'D10ZF411\SUBSCRIPTION_SRV',
@agent_type = 1,
@active_end_date = 0
GO
/* Enable the replication database */
use master
GO
exec sp_replicationdboption
@dbname = N'pubs',
@optname = N'publish',
@value = N'true'
GO
/* Add the transactional publication */
use [pubs]
GO
exec sp_addpublication
@publication = N'pubs_authors_table',
@restricted = N'false',
@sync_method = N'native',
@repl_freq = N'continuous',
@description = N'Transactional publication of pubs database from Publisher D10ZF411.',
@status = N'active',
@allow_push = N'true',
@allow_pull = N'true',
@allow_anonymous = N'false',
@enabled_for_internet = N'false',
@independent_agent = N'false',
@immediate_sync = N'false',
@allow_sync_tran = N'false',
@autogen_sync_procs = N'false',
@retention = 336,
@allow_queued_tran = N'false',
@snapshot_in_defaultfolder = N'true',
@compress_snapshot = N'false',
@ftp_port = 21,
@ftp_login = N'anonymous',
@allow_dts = N'false',
@allow_subscription_copy = N'false',
@add_to_active_directory = N'false',
@logreader_job_name = N'D10ZF411-pubs-1'
/* set snapshot agent's schedule */
exec sp_addpublication_snapshot
@publication = N'pubs_authors_table',@frequency_type = 4, @frequency_interval = 1,
@frequency_relative_interval = 1,
@frequency_recurrence_factor = 0,
@frequency_subday = 8,
@frequency_subday_interval = 1,
@active_start_date = 0,
@active_end_date = 0,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@snapshot_job_name = N'D10ZF411-pubs-pubs_authors_table-1'
GO
/* enable appropriate accounts to have access to the publication */
exec sp_grant_publication_access
@publication = N'pubs_authors_table',
@login = N'distributor_admin'
GO
exec sp_grant_publication_access
@publication = N'pubs_authors_table',
@login = N'sa'
GO
/* Add the transactional article – authors */
exec sp_addarticle
@publication = N'pubs_authors_table',
@article = N'authors',
@source_owner = N'dbo',
@source_object = N'authors',
@destination_table = N'authors',
@type = N'logbased',
@creation_script = null,
@description = null,
@pre_creation_cmd = N'drop',
@schema_option = 0x00000000000000F3,
@status = 16,
@vertical_partition = N'false',
@ins_cmd = N'CALL sp_MSins_authors',
@del_cmd = N'CALL sp_MSdel_authors',
@upd_cmd = N'MCALL sp_MSupd_authors',
@filter = null,
@sync_object = null,
@auto_identity_range = N'false'
GO
/* Add the transactional (push) subscription */
exec sp_addsubscription
@publication = N'pubs_authors_table',
@article = N'all',
@subscriber = N'D10ZF411\SUBSCRIPTION_SRV',
@destination_db = N'pubs',
@sync_type = N'automatic',
@update_mode = N'read only',
@offloadagent = 0,
@dts_package_location = N'distributor'
GO
If we want to set up a pull subscription, we can run a slightly different set
of procedures, as shown next. Keep in mind that we'll still have to define
the distributor and create a publication prior to defining a pull subscription.
/****** Script to be run at Subscriber ******/
use [pubs]
GO
exec sp_addpullsubscription
@publisher = N'D10ZF411',
@publisher_db = N'pubs',
@publication = N'pubs_authors_table',
@independent_agent = N'false',
@subscription_type = N'pull',
@description = N'Transactional publication of pubs database from Publisher D10ZF411.',
@update_mode = N'read only',
@immediate_sync = 0
exec sp_addpullsubscription_agent
@publisher = N'D10ZF411',
@publisher_db = N'pubs',
@publication = N'pubs_authors_table',
@distributor = N'D10ZF411',
@subscriber_security_mode = 1,
@distributor_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_date = 0,
@active_end_date = 0,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@enabled_for_syncmgr = N'false',
@use_ftp = N'false',
@publication_type = 0,
@dts_package_location = N'subscriber',
@offloadagent = N'false'
GO
/****** Script to be run at Publisher ******/
use [pubs]
GO
exec sp_addsubscription
@publication = N'pubs_authors_table',
@subscriber = N'D10ZF411\SUBSCRIPTION_SRV',
@destination_db = N'pubs',
@sync_type = N'automatic',
@subscription_type = N'pull',
@update_mode = N'read only'
GO