Home > Articles > Data > SQL Server

  • Print
  • + Share This
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
  • + Share This
  • 🔖 Save To Your Account