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.

1. Setup the remote distribution
2. Configure publisher to use the remote distribution
3. Setup the SQL Server replication
4. Creating linked servers to subscribers on the secondary servers
5. Redirect the original publisher to listener of the AG

 

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