SQL Server Transactional Replication

Replication is one of the High Availability features available in SQL Server. Transactional Replication is used when DML or DDL schema changes performed on an object of a database on one server needs to be reflected on the database residing on another server. This change happens almost in real time (i.e. within seconds). In this article, I will demonstrate the step by step approach to configuring transactional replication in SQL Server 2012.

Before we start with the configuration, we need to understand various Transactional Replication Components , let’s discuss each of these in detail.

Publisher: The Publisher can be referred to as a database on which the DML or DDL schema changes are going to be performed.

Subscriber: The Subscriber is the  database which is going to receive the DML as well as DDL schema changes which are performed on the publisher. The subscriber database normally resides on a different server in another location.

Distribution Database: A database which contains all the Replication commands. Whenever any DML or DDL schema changes are performed on the publisher, the corresponding commands generated by  SQL Server are stored in the Distribution database. This database can reside on the same server as the publisher, but it is always recommended to keep it on a separate server for better performance.

Snapshot Agent: The subscriber needs to have matching schema and data as the published tables before incremental changes can be maintained by replication. This is usually achieved by applying a snapshot from the Snapshot Agent which is then distributed to they subscriber by the Distribution Agent, so snapshot agent prepares snapshot files containing schema and data of publication articles and stores the files in the snapshot folder. There are alternatives to this, including initialization through backup or migrating the data via SSIS.

Log Reader Agent: Runs at the Distributor, in most scenarios runs continuously, can run according to a schedule also. Analyzes the transaction log of the publication database and looks for transactions marked for replication using internal sp_replcmds procedure, for any INSERT, UPDATE, DELETE operations Schema modifications. Copies the transactions marked for replication in batches into the distribution database. Only committed transactions are sent. Calls sp_repldone to mark where replication last completed and mark log entries to allow log truncation/clearing.

Distribution Agent: Runs at distributor for “push” subscriptions and at subscriber for “pull” subscriptions. Copies the initial snapshot files from the snapshot folder and sync these files to subscriber. Copies transactions from the distribution database to one or more subscribers. Checks if Publisher and Subscriber data match if the subscription is marked for validation.

Let us now begin with the Configuring of the Transactional Replication. There are following steps involved for Configuring the Transactional Replication:
1. Configuring the Distribution Database.
2. Creating the publisher db and LOG READER Agent(one for single database).
3. Creating the publication and SNAPSHOT agent(one for each publication).
4. Add articles in the publication.
5. Creating the subscription and DISTRIBUTION agent(one for each subscription)(so for a publication with 3 subscriptions there will be 1 => SNAPSHOT, 3 => DISTRIBUTION AGENTS).

Configuring the Distribution database

sp_adddistributor => Configures the server as a distributor
sp_adddistributiondb => Creates a distribution database and associated schema
sp_adddistpublisher => Maps a publisher to a specified distribution database

Creating the publisher db and LOG READER Agent

sp_replicationdboption => Enable publication for a database
sp_addlogreader_agent => Adds a Log Reader Agent for a given database

-- Enabling the replication database
USE master

EXEC sp_replicationdboption @dbname = N'PUB_DB'
  -- Can be "subscribe", "publish", "merge publish"
  -- and "sync with backup"
  ,@optname = N'publish'
  ,@value = N'true'
GO

EXEC [PUB_DB].sys.sp_addlogreader_agent 
  @job_login = NULL /* Is the login for the Microsoft Windows account under which the agent runs. job_login is nvarchar(257), with a default value of NULL. This Windows account is always used for agent connections to the Distributor. */
  ,@job_password = NULL
  ,@publisher_security_mode = 0 /* Is the security mode used by the agent when connecting to the Publisher. publisher_security_mode is smallint, with a default of 1. 0 specifies SQL Server Authentication, and 1 specifies Windows Authentication. A value of 0 must be specified for non- SQL Server Publishers. */
  ,@publisher_login = N'dbadmin' /* Is the login used when connecting to the Publisher. publisher_login is sysname, with a default of NULL. publisher_login must be specified when publisher_security_mode is 0. If publisher_login is NULL and publisher_security_mode is 1, then the Windows account specified in job_login will be used when connecting to the Publisher. */
  ,@publisher_password = N'password'
GO

-- log reader agent sample using windows authentication
EXEC [PUB_DB].sys.sp_addlogreader_agent @job_login = N'DOMAIN\user',
    @job_password = 'password', @publisher_security_mode = 1

-- ** Validate the new Log Reader SQL Server Agent Job **

sp_changelogreader_agent is used to change the Windows account under which a Log Reader agent runs. You can change the password of an existing Windows login or supply a new Windows login and password. After changing an agent login or password, you must stop and restart the agent before the change takes effect.

Creating the publication and SNAPSHOT agent

sp_addpublication => Creates a snapshot or transactional publication
sp_addpublication_snapshot => Creates a Snapshot Agent

-- Adding the transactional publication
USE [PUB_DB]

EXEC sp_addpublication @publication = N'Pub_Employee',
    @sync_method = N'concurrent', @allow_push = N'true', @allow_pull = N'true',
    @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false',
    @repl_freq = N'continuous', @status = N'active',
    @independent_agent = N'true', 
	-- We'll talk more about immediate sync in below sections
	-- Big overhead considerations!
    @immediate_sync = N'false', @replicate_ddl = 1,
    @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false',
    @enabled_for_het_sub = N'false';
GO

-- Create the snapshot agent
EXEC sp_addpublication_snapshot @publication = N'Pub_Employee' 
  ,@frequency_type = 1 -- Once (4 for Daily(default option))
  ,@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 = 0
  ,@publisher_login = N'repladmin'
  ,@publisher_password = N''

Add articles in the publication.

sp_addarticle=> Adds an article to a publication

-- Adding the transactional articles
USE [PUB_DB]

EXEC sp_addarticle @publication = N'Pub_Employee'
  ,@article = N'Employee', @source_owner = N'dbo', @source_object = N'Employee'  
  ,@type = N'logbased'
  
  -- What to do if object exists at subscriber
  -- Other options are "none", "delete", "truncate"
  ,@pre_creation_cmd = N'drop'
  
  -- Bitmask for the assorted schema gen option
  ,@schema_option = 0x000000000803109F
  
  -- Manual = NOT FOR REPLICATION 
  ,@identityrangemanagementoption = N'manual'
  
  ,@destination_table = N'Employee',@destination_owner = N'dbo'
  ,@status = 24
  
  -- Column filtering (if sp_articlecolumn used then true)
  ,@vertical_partition = N'false'

  ,@ins_cmd = N'CALL [dbo].[sp_MSins_dboEmployee]'
  ,@del_cmd = N'CALL [dbo].[sp_MSdel_dboEmployee]'
  ,@upd_cmd = N'SCALL [dbo].[sp_MSupd_dboEmployee]'
GO

Creating the subscription and DISTRIBUTION agent

Creating a Push Subscription
sp_addsubscription(at Publisher) => Adds a subscription
sp_addpushsubscription_agent(at Publisher) => Creates new scheduled Distribution Agent job
Creating a Pull Subscription
sp_addsubscription(at Publisher) => Adds a subscription
sp_addpullsubscription(at Subscriber) => Adds a pull subscription
sp_addpullsubscription_agent(at Subscriber) => Creates new scheduled Distribution Agent job

-- Add Subscription
DECLARE @sa_password varchar(100)
DECLARE @subscriber_name varchar(100)
DECLARE @subscriber_db_name varchar(100)
DECLARE @publication_name varchar(100)

SET @sa_password = 'password'
SET @subscriber_name = 'sub1'
SET @subscriber_db_name = 'sub_db'
SET @publication_name = 'pub1'

-- Creating a PUSH subscription
EXEC sp_addsubscription 
  @publication = @publication_name, @subscriber = @subscriber_name,
  @destination_db = @subscriber_db_name, @subscription_type = N'Push',
  
  -- Regarding @sync_type:
  -- "none" (deprecated) for sub that already has data and schema.
  -- "automatic" - means schema/data pushed to subscriber first using snapshot.
  -- "replication support only" - assumes data/schema already exist but generates article procedures/triggers.
  -- "initialize with backup" - schema/data from backup of publication db.
  -- "initialize from lsn' for adding node to P2P topoplogy and assumes subscriber already has schema/data
  @sync_type = N'Automatic', 

  @article = N'all', @update_mode = N'read only', @subscriber_type = 0

-- Create distribution agent
EXEC sp_addpushsubscription_agent 
  @publication = @publication_name
  ,@subscriber = @subscriber_name,@subscriber_db = @subscriber_db_name 
  ,@job_login = null, @job_password = null, @subscriber_security_mode = 0
  ,@subscriber_login = N'repladmin', @subscriber_password = @sa_password 
  -- Autostart
  ,@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'

Kick start the transactional replication

Since @immediate_sync = N’false’ for our publication we need to generate snapshot for this new subscription
For understanding in detail about @immediate_sync
https://www.mssqltips.com/sqlservertip/2668/role-of-the-immediate-sync-option-for-sql-server-replication/
https://www.mssqltips.com/sqlservertip/2502/limit-snapshot-size-when-adding-new-article-to-sql-server-replication/
https://blogs.msdn.microsoft.com/chrissk/2009/07/27/how-replication-setting-immediate_sync-may-cause-transactional-replication-distribution-database-growth/

-- Check snapshot agent job
SELECT name,*
FROM distribution.dbo.MSsnapshot_agents;
GO
-- ** Let's kick off the snapshot agent job now **
EXEC msdb.dbo.sp_start_job 'name from above ' -- CHANGE this name
GO

Test the transactional replication

Test the replication by adding a row in PUB and checking it on SUB

Some more links for learning:
http://www.sql-server-performance.com/2010/transactional-replication-2008-r2/
http://www.dbafire.com/2012/12/05/troubleshooting-sql-server-replication-delays/
https://dba.stackexchange.com/questions/12725/add-article-to-transactional-publication-without-generating-new-snapshot
http://port1433.com/2017/04/11/transactional-replication-and-stored-procedure-execution-silver-bullet-or-poison-pill