Transactional Replication Enhancement in SQL Server 2017 CU6 – Distribution database in AlwaysOn AG
At the event of SQLSaturday Dallas 2018, it was a great honor to present this new feature that allows you to put Distribution database in AlwaysOn AG. The slide deck for the session can be downloaded at https://www.sqlsaturday.com/SessionDownload.aspx?suid=20757. As many audience requested, I am writing this blog to share the demo scripts, enjoy! 
Notes: The feature was released with SQL Server 2017 CU6 on 4/20/2018, it will also be back ported to future CUs for SQL Server 2016 SP2 The feature now is also available in SQL Server 2016 SP2+CU3.
In SQL 2017 + CU6, in order to support Distributor database in AG, there are a few highlights as below:
Remote Distributor required
Distribution database AG needs a listener
Replication jobs use the distributor listener
A new job created to monitor distributor AG state
Synchronous mode is recommended and preferred
Transactional replication with immediate or queued updating subscriber is not supported
P2P, Merge, Heterogeneous, Bi-directional TR not supported
Configure by script only
Must be new replication, existing distribution db not supported
For the full list of supported/un-supported scenarios, please refer to - /en-us/sql/relational-databases/replication/configure-distribution-availability-group?view=sql-server-2017
This demo is to show you how to configure the Distribution database in AlwaysOn AG step by step, the demo environment:
SQL Server 2017 RTM + CU6 Build# 14.0.3025.34
Distributor in AG (SQLNODE1 & SQLNODE2)
Publisher in AG (SQLNODE4 & SQLNODE5) - Test database AW2014 (it is a copy of AdventureWorks sample database, the table in the demo person.address_test is a copy of existing table person.address)
Subscriber (SQLTest\SQL2017Sub)
The goal in this demo is to make the transactional replication topology like this visually:
Below is the scripts including comments that can help understand the whole process, BTW, the script is written in SQLCMD mode, so, make sure you set SSMS in SQLCMD mode before you play with the script in your testing/dev environment.
Important: All scripts/procedures provided here is for demo purpose only. Although it is tested in my lab, please read and test it thoroughly before you apply this to your system.
-- SECTION 1 ---- CONFIGURE THE DISTRIBUTOR SERVERS
-- Step1 - Configure the Distribution DB nodes (AG Replicas) to act as a distributor
:Connect SQLNode1
sp_adddistributor @distributor = @@ServerName, @password = 'Pass@word1'
Go
:Connect SQLNode2
sp_adddistributor @distributor = @@ServerName, @password = 'Pass@word1'
Go
-- Step2 - Configure the Distribution Database
:Connect SQLNode1
USE master
EXEC sp_adddistributiondb @database = 'DistributionDB', @security_mode = 1;
GO
Alter Database [DistributionDB] Set Recovery Full
Go
Backup Database [DistributionDB] to Disk = 'Nul'
Go
-- Step 3 - Create AG for the Distribution DB.
:Connect SQLNode1
USE [master]
GO
CREATE ENDPOINT [Hadr_endpoint]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
:Connect SQLNode2
USE [master]
GO
CREATE ENDPOINT [Hadr_endpoint]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
:Connect SQLNode1
-- Create the Availability Group
CREATE AVAILABILITY GROUP [DistributionDB_AG]
FOR DATABASE [DistributionDB]
REPLICA ON 'SQLNode1'
WITH (ENDPOINT_URL = N'TCP://SQLNode1.Features2014DC.local:5022',
FAILOVER_MODE = AUTOMATIC,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL),
SEEDING_MODE = AUTOMATIC),
N'SQLNode2' WITH (ENDPOINT_URL = N'TCP://SQLNode2.Features2014DC.local:5022',
FAILOVER_MODE = AUTOMATIC,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL),
SEEDING_MODE = AUTOMATIC);
GO
:Connect SQLNode2
ALTER AVAILABILITY GROUP [DistributionDB_AG] JOIN
GO
ALTER AVAILABILITY GROUP [DistributionDB_AG] GRANT CREATE ANY DATABASE
Go
--STEP4 - Create the Listener for the Availability Group. This is very important.
:Connect SQLNode1
USE [master]
GO
ALTER AVAILABILITY GROUP [DistributionDB_AG]
ADD LISTENER N'DistributionDBList' (
WITH IP
((N'10.10.3.50', N'255.0.0.0')) , PORT=1433);
GO
-- STEP 5 - Enable SQLNode2 also as a Distributor
:CONNECT SQLNODE2
EXEC sp_adddistributiondb @database = 'DistributionDB', @security_mode = 1;
GO
--STEP 6 - On all Distributor Nodes Configure the Publisher Details
-- ******@Publisher is the current primary replca of the publisher, we will set redirect publisher to Publisher's listener in the end******-------------
:CONNECT SQLNODE1
sp_helpdistpublisher
EXEC sp_addDistPublisher @publisher = 'SQLNode4', @distribution_db = 'DistributionDB',
@working_directory = '\\SQL2014DC\Dist_Work_Directory\'
GO
:CONNECT SQLNODE2
--sp_helpdistpublisher
EXEC sp_addDistPublisher @publisher = 'SQLNode4', @distribution_db = 'DistributionDB',
@working_directory = '\\SQL2014DC\Dist_Work_Directory\'
GO
-- ******@Publisher is the current secondary replca of the publisher, we will set redirect publisher to Publisher's listener in the end******-------------
-- ******because after failover, the secondary replica of the publisher can also be a publisher to use the distributionDB******
:CONNECT SQLNODE1
EXEC sp_addDistPublisher @publisher = 'SQLNode5', @distribution_db = 'DistributionDB',
@working_directory = '\\SQL2014DC\Dist_Work_Directory\'
GO
:CONNECT SQLNODE2
EXEC sp_addDistPublisher @publisher = 'SQLNode5', @distribution_db = 'DistributionDB',
@working_directory = '\\SQL2014DC\Dist_Work_Directory\'
GO
--sp_helpdistpublisher
-- SECTION 2 ---- CONFIGURE THE PUBLISHER SERVER ON ALL PUBLISHER NODES
:CONNECT SQLNODE4
--sp_helpdistributor
EXEC sp_addDistributor @distributor = 'DistributionDBList', -- Listener for the Distribution DB.
@password = 'Pass@word1'
Go
:CONNECT SQLNODE5
EXEC sp_addDistributor @distributor = 'DistributionDBList', -- Listener for the Distribution DB.
@password = 'Pass@word1'
Go
--sp_helpdistributor
-- On the Secondary replicas of the Distribution DB, add the Subscriber as a linked server.
:CONNECT SQLNODE2
EXEC master.dbo.sp_addlinkedserver @server = N'SQLTest\SQL2017Sub', @srvproduct=N'SQL Server'
:CONNECT SQLNODE2
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SQLTest\SQL2017Sub',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
/* For security reasons the linked server remote logins password is changed with ######## */
--EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SQLNODE5',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
-- On the Secondary replicas of the Publisher DB, add the Subscriber as a linked server.
:CONNECT SQLNODE5
EXEC master.dbo.sp_addlinkedserver @server = N'SQLTest\SQL2017Sub', @srvproduct=N'SQL Server'
:CONNECT SQLNODE5
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SQLTest\SQL2017Sub',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
-- SECTION 3 ---- CONFIGURE DISTRIBUTION DATABASE FULL/LOG BACKUP PLAN
-- ***Make sure Distribution database has Full and T-log backup scheduled since it is in FULL recovery mode ***--
-- SECTION 4 ---- CONFIGURE THE PUBLICATION FOR THE PRIMARY REPLICA (JUST TREAT IT AS STANDALONE)
-- On Publisher's primary replca, enable the dataabse AW2014 for transactional replication
:CONNECT SQLNODE4
USE master;
GO
EXEC sys.sp_replicationdboption
@dbname = 'AW2014',
@optname = 'publish', --this is for transactional replication
@value = 'true';
-- On primary Publisher SQLNode4, create the publication using script that you previously saved as below
-- created publication AW2014_Pub
-- You can script out your existing publication, here is just an example script that only contains 4 articles
-- execute the script to create the publication on SQLNode4-- create publication AW2014_Pub on the primary replica SQLNode4
:CONNECT SQLNODE4
--use [AW2014]
--exec sp_replicationdboption @dbname = N'AW2014', @optname = N'publish', @value = N'true'
--GO
-- Adding the logreader job
--@publisher_security_mode = 1: integrate authentication mode, no @publisher_login/password needed
--@publisher_security_mode = 0: SQL authentication mode, requires sql login for @publisher_login/password
use [AW2014]
exec [AW2014].sys.sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1, @publisher_login = null, @publisher_password = null
GO
-- Adding the transactional publication
use [AW2014]
exec sp_addpublication @publication = N'AW2014_Pub', @description = N'Transactional publication of database ''AW2014'' from Publisher ''SQLNODE4''.', @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, @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'false', @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
-- no need snapshot agent job
-- exec sp_addpublication_snapshot @publication = N'AW2014_Pub', @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
use [AW2014]
exec sp_addarticle @publication = N'AW2014_Pub', @article = N'Address', @source_owner = N'Person', @source_object = N'Address', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Address', @destination_owner = N'Person', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_PersonAddress', @del_cmd = N'CALL sp_MSdel_PersonAddress', @upd_cmd = N'SCALL sp_MSupd_PersonAddress'
GO
use [AW2014]
exec sp_addarticle @publication = N'AW2014_Pub', @article = N'Address_Test', @source_owner = N'Person', @source_object = N'Address_Test', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Address_Test', @destination_owner = N'Person', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_PersonAddress_Test', @del_cmd = N'CALL sp_MSdel_PersonAddress_Test', @upd_cmd = N'SCALL sp_MSupd_PersonAddress_Test'
GO
use [AW2014]
exec sp_addarticle @publication = N'AW2014_Pub', @article = N'AddressType', @source_owner = N'Person', @source_object = N'AddressType', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'AddressType', @destination_owner = N'Person', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_PersonAddressType', @del_cmd = N'CALL sp_MSdel_PersonAddressType', @upd_cmd = N'SCALL sp_MSupd_PersonAddressType'
GO
use [AW2014]
exec sp_addarticle @publication = N'AW2014_Pub', @article = N'AWBuildVersion', @source_owner = N'dbo', @source_object = N'AWBuildVersion', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'AWBuildVersion', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboAWBuildVersion', @del_cmd = N'CALL sp_MSdel_dboAWBuildVersion', @upd_cmd = N'SCALL sp_MSupd_dboAWBuildVersion'
GO
-- SECTION 4.2 ---- CONTINUE TO CONFIGURE THE PUBLICATION
-- check the publication
:CONNECT SQLNODE4
use [AW2014]
EXEC sp_helppublication @publication = N'AW2014_Pub'
-- set the publication to allow_initialize_from_backup
:CONNECT SQLNODE4
use [AW2014]
EXEC sp_changepublication
@Publication = N'AW2014_Pub',
@property = N'allow_initialize_from_backup',
@value = True
-- Section 4.3 ---- INIT SUBSCRIBER DATABASE USING BACK/RESTORE
:CONNECT SQLNODE4
BACKUP DATABASE [AW2014] TO DISK = N'C:\Backups\UserDB\AW2014-ForSubscriber.bak' WITH NOFORMAT, NOINIT,
NAME = N'AW2014-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
:CONNECT SQLNODE4
BACKUP LOG [AW2014] TO DISK = N'C:\Backups\UserDB\AW2014-ForSubscriber.trn' WITH NOFORMAT, NOINIT,
NAME = N'AW2014-T Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
:CONNECT SQLTest\SQL2017Sub
/*
Noted: if the SQLTest\SQL2017Sub is higher than the publisher, then backup/restore will work,
otherwise you cannot use backup/restore to init subscriber db.
*/
USE [master]
RESTORE DATABASE [AW2014_Repl] FROM DISK = N'\\sqlnode4\c$\Backups\UserDB\AW2014-ForSubscriber.bak' WITH FILE = 1,
MOVE N'AdventureWorks2014_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017Sub\MSSQL\DATA\AW2014_ReplOLTP_Data.mdf',
MOVE N'AdventureWorks2014_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017Sub\MSSQL\DATA\AW2014_ReplOLTP_Log.ldf',
NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [AW2014_Repl] FROM DISK = N'\\sqlnode4\c$\Backups\UserDB\AW2014-ForSubscriber.trn'
WITH FILE = 1, NOUNLOAD, STATS = 5
GO
-- SECTION 5 ---- CREATE THE SUBSCRIPTION USING THE SYNC_TYPE = 'INITIALIZE WITH BACKUP'
-- execute the script to create subscription on the publisher SQLNode4
-- Sometimes you may use @sync_type = N'replication support only'
:CONNECT SQLNODE4
use [AW2014]
exec sp_addsubscription @publication = N'AW2014_Pub', @subscriber = N'SQLTest\SQL2017Sub', @destination_db = N'AW2014_Repl',
@subscription_type = N'Push',
@sync_type = N'initialize with backup', --INITIALIZE WITH BACKUP
@article = N'all', @update_mode = N'read only', @subscriber_type = 0,
@backupdevicetype ='disk', --REQUIRED
@backupdevicename = N'\\SQLNode4\C$\Backups\UserDB\AW2014-ForSubscriber.bak' --REQUIRED
:CONNECT SQLNODE4
use [AW2014]
exec sp_addpushsubscription_agent @publication = N'AW2014_Pub', @subscriber = N'SQLTest\SQL2017Sub',
@subscriber_db = N'AW2014_Repl', @job_login = null, @job_password = null, @subscriber_security_mode = 1,
@frequency_type = 64, @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 = 20180212, @active_end_date = 99991231,
@enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
----------------------------CONFIGURE DISTRIBUTOR IN AG COMPLETED------------------------------
----------STOP HERE AT THIS POINT, TEST DISTRIBUTOR IN AG AND STANDALONE PUBLISHER-------------
/*
Distributor in AG (SQLNode1 & SQLNode2)
SQLNode4 is the standalone publisher
SQLTest\SQL2017Sub is the subscriber
*/
--update table
:Connect SQLNODE4
update AW2014.person.address_test set addressline1 = '1972 Napa Ct.'
where addressid = 1
--watch result in publisher and subscriber
:Connect SQLNODE4
select top 5 * from AW2014.person.address_test
Go
:Connect SQLTest\SQL2017Sub
select top 5 * from AW2014_Repl.person.address_test
Go
-- check if the transaction and command is replicated to the secondary distributor replica
:connect sqlnode1
use distributiondb
select * from [dbo].[MSrepl_transactions] with(nolock)
select * from [dbo].[MSrepl_commands] with(nolock)
--where xact_seqno = 0x0000005300001A08001C
:connect sqlnode2
use distributiondb
select * from [dbo].[MSrepl_transactions] with(nolock)
select * from [dbo].[MSrepl_commands] with(nolock)
--where xact_seqno = 0x0000005300001A08001C
-- Failover Distributor AG and Test the above table update again
-- 1. Failover Ditributor AG
:Connect SQLNODE1
:Connect SQLNODE2
ALTER AVAILABILITY GROUP [DistributionDB_AG] FAILOVER;
GO
-- 2. Watch jobs on Distributor are disabled and enabled on different replicas automatically as expected (may take a min)
-- 3. Test the above table update again and watch if result is all good
--update table
:Connect SQLNODE4
update AW2014.person.address_test set addressline1 = '1975 Napa Ct.'
where addressid = 1
--watch result in publisher and subscriber
:Connect SQLNODE4
select top 5 * from AW2014.person.address_test
Go
:Connect SQLTest\SQL2017Sub
select top 5 * from AW2014_Repl.person.address_test
Go
-- 4. Failover back to SQLNODE1 as primary Distributor replica
:Connect SQLNODE2
:Connect SQLNODE1
ALTER AVAILABILITY GROUP [DistributionDB_AG] FAILOVER;
GO
-- 5. Wait a min or two, the jobs should be refreshed again as expected
------------------TEST DISTRIBUTOR IN AG AND STANDALONE PUBLISHER COMPLETED-----------------------
---------------IF THE ABOVE TEST SUCCESSFUL, CONTINUE TO CONFIGURE PUBLISHER IN AG----------------
-- SECTION 6 ---- ***CONFIGURE THE PUBLISHER DB AG AND PUBLISHER LISTENER***
-- Backup publisher database AW2014 on SQLNode4 and restore it on Secondary on SQLNode5 (with no recovery)
:CONNECT SQLNODE4
BACKUP DATABASE [AW2014] TO DISK = N'C:\Backups\UserDB\AW2014-ForAG.bak' WITH NOFORMAT, NOINIT,
NAME = N'AW2014-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
:CONNECT SQLNODE4
BACKUP LOG [AW2014] TO DISK = N'C:\Backups\UserDB\AW2014-ForAG.trn' WITH NOFORMAT, NOINIT,
NAME = N'AW2014-T Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
:CONNECT SQLNODE5
USE [master]
RESTORE DATABASE [AW2014] FROM DISK = N'\\SQLNode4\C$\Backups\UserDB\AW2014-ForAG.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [AW2014] FROM DISK = N'\\SQLNode4\C$\Backups\UserDB\AW2014-ForAG.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
GO
-- Create publisher AG "AW2014Pub_AG" Using GUI (Join Only). I did not script out this on-purpose to show that this part can be done with GUI, you can surely use script to create publisher database AG. Keep in mind, most of the Distributor database in AG part must be done with script, GUI doesn’t support it yet.
-- 1. Primary replica: SQLNODE4 (readable)
-- 2. Secondary replica: SQLNODE5 (readable)
-- 3. Database: AW2014
-- Add publisher AG listener - AW2014PubAGList
:CONNECT SQLNODE4
USE [master]
GO
ALTER AVAILABILITY GROUP [AW2014Pub_AG]
ADD LISTENER N'AW2014PubAGList' (
WITH IP
((N'10.10.3.56', N'255.0.0.0')
)
, PORT=1433);
GO
-- Check all secondary replcas have replication feature installed --
:CONNECT SQLNODE5
USE master;
GO
DECLARE @installed int;
EXEC @installed = sys.sp_MS_replication_installed;
SELECT @installed;
-- Return 1 is installed
-- SECTION 7 ---- ***CONFIGURE THE REDIRECT PUBLISHER***
-- on paimary distributor replicas SQLNode1, add publisher redirect by using publsher database ag listener AW2014PubDist
:CONNECT SQLNODE1
USE distributionDB;
GO
EXEC sys.sp_redirect_publisher
@original_publisher = 'SQLNode4',
@publisher_db = 'AW2014',
@redirected_publisher = 'AW2014PubAGList';
:CONNECT SQLNODE1
USE distributionDB;
GO
EXEC sys.sp_redirect_publisher
@original_publisher = 'SQLNode5',
@publisher_db = 'AW2014',
@redirected_publisher = 'AW2014PubAGList';
-- check and validate all publisher replicas (must be on primary distributor replica)
/*
Notes: sp_validate_replica_hosts_as_publishers is an extention of sp_validate_redirected_publisher.
All publisher's replica must be readable/accessible to get them verified,
Otherwise, the validation will fail with error msg 21899.
*/
:CONNECT SQLNODE1
USE distributionDB;
GO
DECLARE @redirected_publisher sysname;
EXEC sys.sp_validate_replica_hosts_as_publishers
@original_publisher = 'SQLNode4',
@publisher_db = 'AW2014',
@redirected_publisher = @redirected_publisher output;
select @redirected_publisher as redirect_publisher
-- alternatively, check and validate with sp_validate_redirected_publisher (must be on primary distributor replica)
:CONNECT SQLNODE1
USE distributionDB;
GO
DECLARE @redirected_publisher sysname;
EXEC sys.sp_validate_redirected_publisher
@original_publisher = 'SQLNode4',
@publisher_db = 'AW2014',
@redirected_publisher = @redirected_publisher output;
select @redirected_publisher as redirect_publisher
-- or maybe simply use query to check redirect directly, result looks good too
-- this check doesn't guarentee all secondaries are accessible, only check its meta data
:CONNECT SQLNODE1
select * from DistributionDB.[dbo].[MSredirected_publishers]
/*
-- result looks good as below, expalin the result below --
original_publisher publisher_db redirected_publisher
----------------------------------------------------------
SQLNode4 AW2014 AW2014PubAGList
SQLNode5 AW2014 AW2014PubAGList
*/
-- check the distributor and publisher relationship in distribution db, the result looks good too
:CONNECT SQLNODE1
sp_helpdistpublisher
-------------------------------PUBLISHER IN AG CONFIGURATION COMPLETED----------------------------------------
---------------------------NOW, BOTH DISTRIBUTOR AND PUBLISHER ARE IN AG -------------------------------------
--------------------------------------NOW TEST THE WHOLE THING------------------------------------------------
-- Test the replication behavior with both Publisher and Distributor in AGs --
-- ***Test the above table update again and watch if result is all good*** --
--use listener to update primary publisher database
:Connect AW2014PubAGList
update AW2014.person.address_test set addressline1 = '1789 Napa Ct.'
where addressid = 1
--check primary publisher database result
:Connect AW2014PubAGList
select * from AW2014.person.address_test where addressid = 1
Go
--check secondary publisher database result
:Connect SQLNODE5
select * from AW2014.person.address_test where addressid = 1
Go
--check the subscriber database result
:Connect SQLTest\SQL2017Sub
select * from AW2014_Repl.person.address_test where addressid = 1
Go
-- ***Failover Distributor AG and Test the above table update again*** --
-- 1. Failover Ditributor AG
:Connect SQLNODE1
:Connect SQLNODE2
ALTER AVAILABILITY GROUP [DistributionDB_AG] FAILOVER;
GO
-- 2. Watch jobs on Distributor are disabled and enabled on different replicas automatically as expected (may take a min)
-- 3. Test the above table update again and watch if result is all good
:Connect AW2014PubAGList
--use listener to update primary publisher database
update AW2014.person.address_test set addressline1 = '1790 Napa Ct.'
where addressid = 1
--check primary publisher database result
:Connect AW2014PubAGList
select * from AW2014.person.address_test where addressid = 1
Go
--check secondary publisher database result
:Connect SQLNODE5
select * from AW2014.person.address_test where addressid = 1
Go
--check the subscriber database result
:Connect SQLTest\SQL2017Sub
select * from AW2014_Repl.person.address_test where addressid = 1
Go
-- ***Failover Publisher AG and Test the above table update again*** --
-- 1. Failover Publisher database AG
:Connect SQLNODE4
:Connect SQLNODE5
ALTER AVAILABILITY GROUP [AW2014Pub_AG] FAILOVER;
GO
-- 2. Test the above table update again and watch if result is all good
:Connect AW2014PubAGList
--use listener to update primary publisher database
update AW2014.person.address_test set addressline1 = '1791 Napa Ct.'
where addressid = 1
--check primary publisher database result
:Connect AW2014PubAGList
select * from AW2014.person.address_test where addressid = 1
Go
--check new secondary publisher database result
:Connect SQLNODE4
select * from AW2014.person.address_test where addressid = 1
Go
--check the subscriber database result
--it may take a while, eventually you will see logreader parse new log and distrib send tx/cmd over to the subscriber
:Connect SQLTest\SQL2017Sub
select * from AW2014_Repl.person.address_test where addressid = 1
Go
-- Default behavior and how TF1448 works – refer to https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-2017
-- Failover back to SQLNODE1 as primary Distributor replica
:Connect SQLNODE2
:Connect SQLNODE1
ALTER AVAILABILITY GROUP [DistributionDB_AG] FAILOVER;
GO
-- Wait a min or two, the jobs should be refreshed again as expected
-- Failover back to SQLNODE4 as primary Publisher replica
:Connect SQLNODE5
:Connect SQLNODE4
ALTER AVAILABILITY GROUP [AW2014Pub_AG] FAILOVER;
GO
-- ***Final test with both Distributor and Publisher database failover together*** --
--update table
:Connect AW2014PubAGList
update AW2014.person.address_test set addressline1 = '1789 Napa Ct.'
where addressid = 1
--check primary publisher database result
:Connect AW2014PubAGList
select * from AW2014.person.address_test where addressid = 1
Go
--check secondary publisher database result
:Connect SQLNODE5
select * from AW2014.person.address_test where addressid = 1
Go
--check the subscriber database result
--it may take a while, refresh and eventually the tx/cmd is transmitted over to the subscriber through logread/distrib
:Connect SQLTest\SQL2017Sub
select * from AW2014_Repl.person.address_test where addressid = 1
Go
------------------------------------END - CONFIGURATION TEST COMPLETED HERE - END-----------------------------------------------
-------------------------------------------------------------CLEAN-UP SCRIPT---------------------------------------------------------------
-- Removing distributor and publisher
-- This workflow is about removing publisher and distributor when distribution database is in AG.
-- Publisher workflow
-- 1) On publisher, drop all the subscriptions and publications for this publisher
-- 2) And then call "sp_dropdistributor".
:CONNECT SQLNODE4
sp_dropdistributor
:CONNECT SQLNODE5
sp_dropdistributor
-- Distributors cleanup workflow
-- 1) Assuming SQLNode1 is the current primary of distribution database AG. SQLNode2 is the secondary replica.
-- 2) On SQLNode2, call "sp_dropdistpublisher '[Publiser]'" with parameter @no_checks = 1
:CONNECT SQLNODE2
exec sp_dropdistpublisher 'SQLNODE4', @no_checks = 1
exec sp_dropdistpublisher 'SQLNODE5', @no_checks = 1
--3) On distributor primary replica, call "sp_dropdistpublisher '[Publiser]'".
:CONNECT SQLNODE1
exec sp_dropdistpublisher 'SQLNODE4'
exec sp_dropdistpublisher 'SQLNODE5'
--4) Delete the Distributor AG.
:CONNECT SQLNODE1
USE [master]
GO
DROP AVAILABILITY GROUP [DistributionDB_AG];
GO
-- 5) On the distributor secondary replica, change the "distribution" database to read_write mode by restoring the database with recovery.
-- Call "sp_dropdistributiondb 'distribution'" with parameter @former_ag_secondary=1 to drop distribution database
-- and to retain the snapshot directory. This will remove all the dangling jobs on this replica.
:CONNECT SQLNODE2
RESTORE DATABASE DistributionDB WITH RECOVERY
EXEC sp_dropdistributiondb 'DistributionDB', @former_ag_secondary=1
-- 6) On the distributor primary replica, call "sp_dropdistributiondb 'distribution'" to drop distribution database.
:CONNECT SQLNODE1
EXEC sp_dropdistributiondb 'DistributionDB'
-- 7) If there are no other distribution databases in AG, sp_dropdistributor can be done on all distributor replicas.
:CONNECT SQLNODE1
EXEC sp_dropdistributor
go
:CONNECT SQLNODE2
EXEC sp_dropdistributor
-- CHECK AND DELETE THE FOLLOWINGS MANUALLY
-- Any Replication SQL Agent jobs left on all servers
-- Any Replication related linked servers on all servers
-- Any Mirroring Endpoints for Replication AlwaysOn AG that is not in used
-- Drop any databases if it is part of setup
-- Drop any database backup files if it is part of setup
-- Drop publisher AG
:CONNECT SQLNODE4
USE [master]
GO
DROP AVAILABILITY GROUP [AW2014Pub_AG];
GO
-- Drop any endpoints for publisher AG on SQLNode4 and SQLNode5
-- Drop the AW2014 database on SQLNode5 only (secondary)
-- Drop the AW2014_Repl database on subscriber SQLTest\SQL2017Sub
-- Delete all backup files for AG and Replication setup in \\sqlnode4\c$\Backups\UserDB\
Special Thanks to our SQL Server Tiger team who made this happen!
Thanks,
~Henry@MS