Introduction
Transactional Replication is a powerful tool in Microsoft SQL Server that allows to replicate data from one database to another. When database is part of AlwaysOn Availability group(AG), the setup process requires careful consideration to ensure a seamless and reliable environment.
The following are the steps involved in setting the SQL Server replication when the publisher in Always on Availability group.
Step 1: Setting up the remote distribution
Configure the distribution database on a remote server and the remote server must be accessible from all the nodes of the Always on availability group.
use master
exec sp_adddistributor @distributor = N’distributor_vm_name’, @password = N’password’
GO
exec sp_adddistributiondb @database = N’distribution’,
@data_folder = N’data_folder_path’,
@log_folder = N’log_folder_path’,
@log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48,
@deletebatchsize_xact = 5000, @deletebatchsize_cmd = 2000, @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’snapshot_folder_path’,
‘user’, dbo, ‘table’, ‘UIProperties’
else
EXEC sp_addextendedproperty N’SnapshotFolder’,
N’snapshot_folder_path’,
‘user’, dbo, ‘table’, ‘UIProperties’
GO
exec sp_adddistpublisher @publisher = N’publisher_vm_name’,
@distribution_db = N’distribution’, @security_mode = 0, @login = N’sa’, @password = N’password’,
@working_directory = N’snapshot_folder_path’,
@trusted = N’false’, @thirdparty_flag = 0, @publisher_type = N’MSSQLSERVER’
GO
Add the possible failover nodes to the distributor as the publisher.
Run the below command in the distributor (Replacing the publisher’s name with all the possible primary node of the AG)
exec sp_adddistpublisher @publisher = N’publisher_vm_name’,
@distribution_db = N’distribution’, @security_mode = 0, @login = N’sa’, @password = N’password’,
@working_directory = N’snapshot_folder_path’,
@trusted = N’false’, @thirdparty_flag = 0, @publisher_type = N’MSSQLSERVER’
GO
Step 2: Configure publisher to use the remote distribution.
Run the below command in the Publisher
use master
exec sp_adddistributor @distributor = N’new distributor name’, @password = N’Password‘
GO
Step 3: Setting up the SQL Server replication
3.1 Enabling the database for the Replication.
use master
exec sp_replicationdboption @dbname = N’Publishing_DB_name’, @optname = N’publish’, @value = N’true’
GO
3.2 Adding Transactional Replication
use [Publishing_DB_name]
exec sp_addpublication @publication = N’Publication_name’, @description = N’Transactional publication of database ”Publishing_DB_name” from Publisher ”Publisher_name”.’, @sync_method = N’concurrent’, @retention = 0, @allow_push = N’true’, @allow_pull = N’true’, @allow_anonymous = N’true’, @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’true’, @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’Publication_name’, @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
3.3 Adding the Tables to the publisher
EXEC sp_addarticle @publication = ‘Publication_name’,
@article = ‘sch.TBL_NAME’,
@source_owner = ‘Sch_name’,
@source_object = ‘Tbl_name’,
@description = Null,
@schema_option = 0x000000000903409D,
@identityrangemanagementoption = N’manual’,
@destination_table = ‘Tbl_name’,
@destination_owner = ‘Schema_name’;
3.4 Execute the stored procedure sp_addsubscription at the Publisher on the publication database to add the subscriber
DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
SET @publication = N’Publication_name’;
SET @subscriber = N’subscriber_name’;
SET @subscriptionDB = N’Subscription_dbname’;
–Add a push subscription to a transactional publication.
EXEC sp_addsubscription
@publication = @publication,
@subscriber = @subscriber,
@destination_db = @subscriptionDB,
@subscription_type = N’push’,
Step 4: Create a linked server to the subscribers on all the secondary servers
EXEC sp_addlinkedserver
@server = ‘subscriber name’
Step 5: Redirect the original publisher to Always on Availability listener
USE distribution;
GO
EXEC sp_redirect_publisher
@original_publisher = ‘Publisher name’,
@publisher_db = ‘Publisher DB’,
@redirected_publisher = ‘Listener name’;
At the distribution database, execute the following script to validate the redirection
USE distribution;
GO
DECLARE @redirected_publisher sysname;
EXEC sys.sp_validate_replica_hosts_as_publishers
@original_publisher = ‘publisher name’,
@publisher_db = ‘publisher db’,
@redirected_publisher = @redirected_publisher output;
Once the validation is completed, manually failover the primary node and test the replication status.
Conclusion
Setting up transactional replication in an Always on Availability group environment requires careful planning and execution. Regularly monitor the replication status and agents to identify and address any issues promptly.
Pramodh P