Blog Post

Setting up transactional replication using T-SQL

,

Replication is one of the more complex of the SQL native “HA/DR” technologies. There are a lot of moving parts. Here are the steps to configure a basic transactional replication topology, with a publisher and distributor on the same instance and using push subscriptions. Most of the stored procedures have many more parameters than I am supplying – check the details in BOL. One example is that by not specifying logins and passwords all the replication agents will run using the SQL Server agent credentials.

Step 1: Set up a shared folder for snapshots.

Step 2: Configure the distributor and publisher:

use master
exec sp_adddistributor @distributor = N'SSLMATTB2'
, @password = N''
GO
exec sp_adddistributiondb @database = N'distribution'
, @data_folder = N'C:\MSSQL\SQLData'
, @log_folder = N'C:\MSSQL\SQLLogs'
, @log_file_size = 2
, @min_distretention = 0
, @max_distretention = 72
, @history_retention = 48
, @security_mode = 1
GO
use [distribution] 
if (not exists (
select * 
from sysobjects 
where name = 'UIProperties' and type = 'U ')) 
create table UIProperties(id int)
if (exists (
select * 
from ::fn_listextendedproperty('SnapshotFolder'
, 'user'
, 'dbo'
, 'table'
, 'UIProperties'
, null, null))) 
EXEC sp_updateextendedproperty N'SnapshotFolder'
, N'C:\MSSQL\SQL_Share'
, 'user'
, dbo
, 'table'
, 'UIProperties' 
else 
EXEC sp_addextendedproperty N'SnapshotFolder'
, N'C:\MSSQL\SQL_Share'
, 'user'
, dbo
, 'table'
, 'UIProperties'
GO
exec sp_adddistpublisher @publisher = N'sslmattb2'
, @distribution_db = N'distribution'
, @security_mode = 1
, @working_directory = N'C:\MSSQL\SQL_Share'
, @trusted = N'false'
, @thirdparty_flag = 0
, @publisher_type = N'MSSQLSERVER'
GO

Step 3: Configure a database for replication, create a publication,  and add an article:

use [AdventureWorks2008]
exec sp_replicationdboption @dbname = N'AdventureWorks2008'
, @optname = N'publish'
, @value = N'true'
GO
use [AdventureWorks2008]
exec sp_addpublication @publication = N'AW_products'
, @sync_method = N'concurrent'
, @retention = 0
, @allow_push = N'true'
, @allow_pull = N'true'
, @allow_anonymous = N'false'
, @enabled_for_internet = N'false'
, @snapshot_in_defaultfolder = N'true'
, @compress_snapshot = N'false'
, @ftp_port = 21
, @allow_subscription_copy = N'false'
, @add_to_active_directory = N'false'
, @repl_freq = N'continuous'
, @status = N'active'
, @independent_agent = N'true'
, @immediate_sync = N'false'
, @allow_sync_tran = N'false'
, @allow_queued_tran = N'false'
, @allow_dts = N'false'
, @replicate_ddl = 1
, @allow_initialize_from_backup = N'false'
, @enabled_for_p2p = N'false'
, @enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @publication = N'AW_products'
, @frequency_type = 1
, @frequency_interval = 1
, @frequency_relative_interval = 1
, @frequency_recurrence_factor = 0
, @frequency_subday = 8
, @frequency_subday_interval = 1
, @active_start_time_of_day = 0
, @active_end_time_of_day = 235959
, @active_start_date = 0
, @active_end_date = 0
, @job_login = null
, @job_password = null
, @publisher_security_mode = 1
use [AdventureWorks2008]
exec sp_addarticle @publication = N'AW_products'
, @article = N'Product'
, @source_owner = N'Production'
, @source_object = N'Product'
, @type = N'logbased'
, @description = null
, @creation_script = null
, @pre_creation_cmd = N'drop'
, @schema_option = 0x000000000803509F
, @identityrangemanagementoption = N'manual'
, @destination_table = N'Product'
, @destination_owner = N'Production'
, @vertical_partition = N'false'
, @ins_cmd = N'CALL sp_MSins_ProductionProduct'
, @del_cmd = N'CALL sp_MSdel_ProductionProduct'
, @upd_cmd = N'SCALL sp_MSupd_ProductionProduct'
GO

Step 4: Backup the database on the publisher and restore to the subscription instance.

Step 5: Configure a subscription (because I am creating a push subscription this script should be run on the publisher).

use [AdventureWorks2008]
exec sp_addsubscription @publication = N'AW_pub'
, @subscriber = N'sslmattb2\INST2'
, @destination_db = N'AW_products'
, @subscription_type = N'Push'
, @sync_type = N'automatic'
, @article = N'all'
, @update_mode = N'read only'
, @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'AW_pub'
, @subscriber = N'sslmattb2\INST2'
, @subscriber_db = N'AW_products'
, @job_login = N'NT AUTHORITY\SYSTEM'
, @job_password = null
, @subscriber_security_mode = 1
, @frequency_type = 64
, @frequency_interval = 0
, @frequency_relative_interval = 0
, @frequency_recurrence_factor = 0
, @frequency_subday = 0
, @frequency_subday_interval = 0
, @active_start_time_of_day = 0
, @active_end_time_of_day = 235959
, @active_start_date = 20120514
, @active_end_date = 99991231
, @enabled_for_syncmgr = N'False'
, @dts_package_location = N'Distributor'
GO

Basic transactional replication is now running. In a future post I’ll look at monitoring and administering the replication environment.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating