Adding Distribution Database to Always ON

Hi Friends,

Today I thought of checking how to add Distribution Database to Availability Group. Although we have Microsoft Document where we can get all the steps I tried this just to get my hands Dirty

By the way to configure this we need to have SQL Server 2017 CU6 or SQL Server 2016 SP2-CU3.

In my case we have 4 servers 2 of them are Distributor Servers one publisher and one Subscriber.

As I am majorly interested in testing I took the advantage of having same SQL Server Service Account and provided sysadmin permissions (which is not all required).

I have configured replication with minimal permissions and below is the link for the same.

https://www.chiranjeevivamsydba.com/2019/01/configuring-transactional-replication.html

Some Important points to keep in Mind:

1) Publisher and distributor need to be on separate SQL Server instances

2)If the listener for the availability group hosting the distribution database is configured to use a non-default port, then its required to setup an alias for the listener and the non-default port.

3)SSMS does not show Distribution Database as synchronizing/synchronized, when distribution database is added to an availability group.

4)Configuring distributor on the publisher needs to be done with scripts

5)Configuring the AG for distribution databases can only be done through scripts.

6)Switching an existing distribution database to an AG is not supported

There are many more but I just highlighted few of them. Okay enough of Theory let's chip in to Action.

Here are the Steps: (you need to run by enabling SQLCMD MODE in SSMS)

:Connect NODE1

sp_adddistributor @distributor = @@ServerName, @password = 'XXXXXXXX'

Go 

:Connect NODE2

sp_adddistributor @distributor = @@ServerName, @password = 'XXXXXXX'

Go


-- Step2 - Configure the Distribution Database

:Connect NODE1

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 NODE1

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 NODE2

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 NODE1

-- Create the Availability Group

CREATE AVAILABILITY GROUP [DistributionDB_AG]

FOR DATABASE [DistributionDB]

REPLICA ON 'INODE1'

WITH (ENDPOINT_URL = N'TCP://NODE1.contoso.com:5022', 

FAILOVER_MODE = AUTOMATIC, 

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 

BACKUP_PRIORITY = 50, 

SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL), 

SEEDING_MODE = AUTOMATIC),

N'NODE2' WITH (ENDPOINT_URL = N'TCP://NODE2.contoso.com:5022', 

FAILOVER_MODE = AUTOMATIC, 

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 

BACKUP_PRIORITY = 50, 

SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL), 

SEEDING_MODE = AUTOMATIC);

 GO



:Connect NODE2

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 NODE1


USE [master]

GO

ALTER AVAILABILITY GROUP [DistributionDB_AG]

ADD LISTENER N'LSNRTTU-DBA' (

WITH IP

((N'192.168.58.16', N'255.255.255.0')) , PORT=1433);

GO


-- STEP 5 - Enable SQLNode2 also as a Distributor

:CONNECT NODE2

EXEC sp_adddistributiondb @database = 'DistributionDB', @security_mode = 1;

GO


--STEP 6 - On all Distributor Nodes Configure the Publisher Details 

:CONNECT NODE1

EXEC sp_addDistPublisher @publisher = 'NODE3', @distribution_db = 'DistributionDB', 

@working_directory = '\\NODE1\repldata\'

GO

:CONNECT NODE2

EXEC sp_addDistPublisher @publisher = 'NODE3', @distribution_db = 'DistributionDB', 

@working_directory ='\\NODE1\repldata\'

GO


-- SECTION 2 ---- CONFIGURE THE PUBLISHER SERVER

:CONNECT NODE3

EXEC sp_addDistributor @distributor = 'LSNRTTU-DBA', -- Listener for the Distribution DB.

@password = 'XXXXX'

Go


-- SECTION 3 ---- CONFIGURE THE SUBSCRIBERS 

-- On Publisher, create the publication as one would normally do.

-- On the Secondary replicas of the Distribution DB, add the Subscriber as a linked server.

:CONNECT NODE2

EXEC master.dbo.sp_addlinkedserver @server = N'NODE4', @srvproduct=N'SQL Server'

 /* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'NODE4',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

As you can see from the above steps we configured Always ON for Distribution database using scripts.

We added publisher server using Listener Name instead of Server Name.

once you are done you can go ahead with traditional way of creating publication and subscription there is no change in this process.

After doing this I added rows to my article in publication and the rows got transmitted to subscriber .

Here are few things what I noted before fail over below are the jobs that got created






As per Microsoft Article the jobs should get created with Listener Name but in my case I could see them with server Names (I am not sure if I went Wrong some where)

After I initiated the fail-over the jobs got created on the secondary replica of the distribution Database






If you see the above image all the replication jobs got created at 13:16 instead of 12:25 on Primary replica. There is a new job by name "Monitor and sync replication agent jobs"  that helps us in failing over the jobs accordingly.

when we fail-over I got the below error however it ran on it's own after few seconds












In my next post I will try to come up with configuring HA for Distribution, Publisher and Subscriber Databases. You can reach me on +91-9739093905 or +353894316824 for any help.

Comments