Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Ctrl-alt-geek

Matt Bowler is a DBA at Trade Me, and teaches database design and administration courses at the local institute of technology. A recent but avid discoverer of SQL Server, Matt is a regular contributor to forums at MSDN, Experts Exchange and SSC and he blogs at mattsql.wordpress.com.

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.


Comments

Leave a comment on the original post [mattsql.wordpress.com, opens in a new window]

Loading comments...