This is a continuation of “Repl Talk” series on troubleshooting Transactional Replication. This posting walk through initial setup of Replication then PUSH of Snapshot to the Subscriber. The walkthrough can be done on a single Microsoft SQL Server 2008.
Soon I’ll record a video walkthrough and include the link here.
If not already done, download and install the AdventureWorksLT.msi sample database. Again, use SQL Books Online to fill in any details.
Execute each step of the script to below. Explore SQL Server using Management Studio to see the results. I’ve included comments throughout the script to highlight key “take a ways”.
/****** Scripting replication configuration.
Chris Skorlinski
Microsoft SQL Server Escalation Services
Purpose: These script will create a subscriber database
and configuration Replication. If you already have Replication running don't execute sp_dropdistributor command below.
To simplify Walk-through, the AdventureWorks_TranSub2 is not used.
Once you're comfortable with Replication, add TranSub2 into the mix.
These script work best if you are both
a SQL Server and a Windows Administrator.
*****/
--Create Subscriber databases
Create Database AdventureWorks_TranSub1
Go
-- Create Database AdventureWorks_TranSub2
--Drop All Replication Settings
--This can be used to "cleanup" before re-running script.
use master
sp_removedbreplication 'AdventureWorks_TranSub1'
-- sp_removedbreplication 'AdventureWorks_TranSub2'
sp_removedbreplication 'AdventureWorksLT'
GO
--WARNING: Don't execute this step
-- if you already have Replication configured and running.
-- sp_dropdistributor 1,0
/****** Installing Distributor - KEY Parameters
@heartbeat_interval= 2 Agent job, default = 10 minutes
@data_folder On own drive or server?
@max_distretention = 72, Transaction cleanup = 3 days
balance size with "down time"
@min_distretention = 0 Minimum retention period
>0 usefule in recovery scenarios
@data_file_size Controlled by Distribution Cleanup Job
watch for unexpected growth
@working_directory UNC for Snapshot Share
Pub, Dist, Pub need file access rights
@history_retention = 48 Distribution History Tables
@password = Linked Server for distributor_admin account
******/
exec sp_adddistributor @distributor = @@SERVERNAME,
@heartbeat_interval= 2, @password = N'Password1!'
-- Adding the agent profiles
exec sp_MSupdate_agenttype_default @profile_id = 1
exec sp_MSupdate_agenttype_default @profile_id = 2
exec sp_MSupdate_agenttype_default @profile_id = 4
exec sp_MSupdate_agenttype_default @profile_id = 6
exec sp_MSupdate_agenttype_default @profile_id = 11
-- Adding the distribution databases
exec sp_adddistributiondb @database = N'distribution',
@data_folder = null, @data_file = N'distribution.MDF',
@data_file_size = 7, @log_folder = null, @log_file = N'distribution.LDF', @log_file_size = 2,
@min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
-- Adding the distribution publishers
exec sp_adddistpublisher @publisher = @@SERVERNAME, @distribution_db = N'distribution', @security_mode = 1,
@working_directory = null, @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
--Add same server also as a Subscriber
exec sp_addsubscriber @subscriber = @@SERVERNAME, @type = 0, @description = N''
/****** Installing Publisher - KEY Parameters
@sync_method = N'concurrent' Locks for Schema, but not Data
@retention Override default retention
@immediate_sync Sub last Snapshot + cached Trans
Wizard: "generate now, keep snapshot"
@allow_sync_tran Immediate-updating Sub. Use P2P instead
@allow_queued_tran Allow_queued_updating. Use P2P instead
@compress_snapshot Saved space
@allow_initialize_from_backup Faster method for large databases
@allow_subscription_copy New Sub, Copy existing Sub
@enabled_for_p2p
@p2p_conflictdetection Hidden column stores an ID and version
@p2p_continue_onconflict
-- Enabling the replication database
exec sp_replicationdboption @dbname = N'AdventureWorksLT', @optname = N'publish', @value = N'true'
exec [AdventureWorksLT].sys.sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1
--exec [AdventureWorksLT].sys.sp_addqreader_agent @job_login = null, @job_password = null, @frompublisher = 1
-- Adding the transactional publication
use [AdventureWorksLT]
exec sp_addpublication @publication = N'TranProducts', @description = N'Transactional publication of database AdventureWorksLT.',
@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, @ftp_login = N'anonymous',
@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'true',
@allow_sync_tran = N'false', @autogen_sync_procs = N'false',
@allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1,
@allow_initialize_from_backup = N'true',
@enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
--This section you may need to edit
exec sp_addpublication_snapshot @publication = N'TranProducts', @frequency_type = 1, @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 = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1
--Corp Domain User (optional)
exec sp_grant_publication_access @publication = N'TranProducts',
@login = N'<your domain>\<your domain login>'
--example: MicrosoftCorp\Chris.Skorlinski >>> no, that's not my real login :)
--Local Admin User (optional)
@login = N'<your computer name>\<your logon name'
--example "CHRISLATPTOP\CHRIS"
--Not sure what User to use, then try running:
select suser_NAME()
@login = N'<name from last command>'
exec sp_grant_publication_access @publication = N'TranProducts', @login = N'sa'
exec sp_grant_publication_access @publication = N'TranProducts', @login = N'NT AUTHORITY\SYSTEM'
exec sp_grant_publication_access @publication = N'TranProducts', @login = N'NT SERVICE\SQLSERVERAGENT'
exec sp_grant_publication_access @publication = N'TranProducts', @login = N'NT SERVICE\MSSQLSERVER'
exec sp_grant_publication_access @publication = N'TranProducts', @login = N'distributor_admin'
/****** Installing Publication KEY Parameters
@type Tabled = 'logbased'
SP ='proc exec', helpful for batch maintenance
@pre_creation_cmd None, Drop, Truncate, Delete(w/Horizontal Filter)
@schema_option Do you need those non-clustered indexes?
@identityrangemanagementoption Carefully planned for MERGE
@ins_cmd, @del_cmd, @upd_cmd Optimized to move data using SP not TSQL statements
Example: sp_MSupd_SalesLTProduct
@status = 16 or 24 If ANY 0 or 8, we'll not batch SP calls. Perf Hit
-- Adding the transactional articles
exec sp_addarticle @publication = N'TranProducts', @article = N'Product', @source_owner = N'SalesLT', @source_object = N'Product',
@type = N'logbased', @description = N'', @creation_script = N'',
@pre_creation_cmd = N'drop',
@schema_option = 0x000000000803509F,
@identityrangemanagementoption = N'manual',
@destination_table = N'Product', @destination_owner = N'SalesLT',
@status = 24,
@vertical_partition = N'false',
@ins_cmd = N'CALL [dbo].[sp_MSins_SalesLTProduct]', @del_cmd = N'CALL [dbo].[sp_MSdel_SalesLTProduct]', @upd_cmd = N'SCALL [dbo].[sp_MSupd_SalesLTProduct]'
exec sp_addarticle @publication = N'TranProducts', @article = N'ProductCategory', @source_owner = N'SalesLT', @source_object = N'ProductCategory', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'ProductCategory', @destination_owner = N'SalesLT', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [dbo].[sp_MSins_SalesLTProductCategory]', @del_cmd = N'CALL [dbo].[sp_MSdel_SalesLTProductCategory]', @upd_cmd = N'SCALL [dbo].[sp_MSupd_SalesLTProductCategory]'
exec sp_addarticle @publication = N'TranProducts', @article = N'ProductDescription', @source_owner = N'SalesLT', @source_object = N'ProductDescription', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'ProductDescription', @destination_owner = N'SalesLT', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [dbo].[sp_MSins_SalesLTProductDescription]', @del_cmd = N'CALL [dbo].[sp_MSdel_SalesLTProductDescription]', @upd_cmd = N'SCALL [dbo].[sp_MSupd_SalesLTProductDescription]'
exec sp_addarticle @publication = N'TranProducts', @article = N'ProductModel', @source_owner = N'SalesLT', @source_object = N'ProductModel', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'ProductModel', @destination_owner = N'SalesLT', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [dbo].[sp_MSins_SalesLTProductModel]', @del_cmd = N'CALL [dbo].[sp_MSdel_SalesLTProductModel]', @upd_cmd = N'SCALL [dbo].[sp_MSupd_SalesLTProductModel]'
exec sp_addarticle @publication = N'TranProducts', @article = N'ProductModelProductDescription', @source_owner = N'SalesLT', @source_object = N'ProductModelProductDescription', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'ProductModelProductDescription', @destination_owner = N'SalesLT', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [dbo].[sp_MSins_SalesLTProductModelProductDescription]', @del_cmd = N'CALL [dbo].[sp_MSdel_SalesLTProductModelProductDescription]', @upd_cmd = N'SCALL [dbo].[sp_MSupd_SalesLTProductModelProductDescription]'
/****** Installing Subscriptions KEY Parameters
@subscription_type Push or Pull
@sync_type automatic
replication support only
initialize with backup
@alt_snapshot_folder Can be used for PULL to specify local directory
-- Adding PUSH transactional subscriptions for AdventureWorks_TranSub1
exec sp_addsubscription @publication = N'TranProducts', @subscriber = @@SERVERNAME, @destination_db = N'AdventureWorks_TranSub1',
@subscription_type = N'Push',
@sync_type = N'automatic',
@article = N'all',
@update_mode = N'read only', @subscriber_type = 0
-- SQL Agent Job to run PUSH Distribution Agent distrib.exe
exec sp_addpushsubscription_agent @publication = N'TranProducts', @subscriber = @@SERVERNAME, @subscriber_db = N'AdventureWorks_TranSub1',
@job_login = null, @job_password = null, @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'
/**** OPTIONAL: Adding the Transactional PULL subscription
-- Script to be run at Subscriber
use [AdventureWorks_TranSub2]
exec sp_addpullsubscription @publisher = @@SERVERNAME, @publication = N'TranProducts', @publisher_db = N'AdventureWorksLT', @independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'read only', @immediate_sync = 1
-- SQL Agent Job to run PULL Distribution Agent distrib.exe
exec sp_addpullsubscription_agent @publisher = @@SERVERNAME, @publisher_db = N'AdventureWorksLT', @publication = N'TranProducts', @distributor = @@SERVERNAME, @distributor_security_mode = 1, @distributor_login = N'', @distributor_password = N'Password1!', @enabled_for_syncmgr = N'False', @frequency_type = 1, @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 = 0, @active_end_date = 0,
@alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'false', @job_login = null, @job_password = null, @publication_type = 0
-- PULL Sub script to be run at Publisher
exec sp_addsubscription @publication = N'TranProducts', @subscriber = @@SERVERNAME, @destination_db = N'AdventureWorks_TranSub2', @sync_type = N'Automatic', @subscription_type = N'pull', @update_mode = N'read only'
****/
/**** OPTIONAL: For demonstration, I usually disable cleanup jobs
Update msdb.dbo.sysjobs
set enabled = 0
where name like '%clean up%'
/****** Start the Snapshot Agent from any of these 4 methods.
1) SQL Agent JOB CHRISSKACER-AdventureWorksLT-TranProducts-1
2) Replication Folder, Local Publications, Publication
3) CMD Line: snapshot.exe -Publisher [CHRISLATPTOP] -PublisherDB [AdventureWorksLT] -Distributor [CHRISLATPTOP] -Publication [TranProducts] -DistributorSecurityMode 1
4) Replication Monitor
To see what is created, use SSMS, located Publication Properties,
then find and browse contents of Snapshot Folder.
/***** Key TAKEWAYS
SSMS GUI runs these SP underneath the covers
Right-click "Local Publications" to
Script out your Replication before it breaks
to document settings
For Security Passwords in Script are NULL
See BOL for details on options
Better performance if Distribution DB on its own Drive or Server