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 http://www.sqlsaturday.com/SessionDownload.aspx?suid=20757. As many audience requested, I am writing this blog to share the demo scripts, enjoy! Smile

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.

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 - https://docs.microsoft.com/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:

image


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

Comments (0)

Skip to main content