Troubleshooting Transactional Replication - PART 2

“Repl Talk”

--Chris Skorlinski
--Microsoft SQL Server Escalation Services

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.

image

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

Go

 

--Drop All Replication Settings

 

--This can be used to "cleanup" before re-running script.

use master

Go

sp_removedbreplication 'AdventureWorks_TranSub1'

Go

-- sp_removedbreplication 'AdventureWorks_TranSub2'

Go

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

 

******/

use master

Go

exec sp_adddistributor @distributor = @@SERVERNAME,

            @heartbeat_interval= 2, @password = N'Password1!'

GO

 

-- 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

GO

 

 

-- Adding the distribution databases

use master

Go

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

GO

 

-- 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'

GO

 

--Add same server also as a Subscriber

exec sp_addsubscriber @subscriber = @@SERVERNAME, @type = 0, @description = N''

GO

 

/****** 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

use master

exec sp_replicationdboption @dbname = N'AdventureWorksLT', @optname = N'publish', @value = N'true'

GO

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

GO

 

-- Adding the transactional publication

use [AdventureWorksLT]

Go

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'

GO

 

--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)

exec sp_grant_publication_access @publication = N'TranProducts',

      @login = N'<your computer name>\<your logon name'

      --example "CHRISLATPTOP\CHRIS"

     

--Not sure what User to use, then try running:

select suser_NAME()

Go

exec sp_grant_publication_access @publication = N'TranProducts',

      @login = N'<name from last command>'

Go   

 

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'

GO

 

/****** 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

use [AdventureWorksLT]

Go

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]'

Go

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]'

Go

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]'

Go

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]'

Go

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]'

GO

 

 

/****** 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

use [AdventureWorksLT]

Go

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

Go

 

-- 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'

GO

 

 

/****  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

GO

 

-- PULL Sub script to be run at Publisher

use [AdventureWorksLT]

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

*****/