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