cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Create a hybrid Distributed Availability Group Between SQL Windows and SQL Linux
By
Liwei
Published Feb 14 2020 03:32 PM 4,288 Views
Liwei
Microsoft
‎Feb 14 2020 03:32 PM

Create a hybrid Distributed Availability Group Between SQL Windows and SQL Linux

‎Feb 14 2020 03:32 PM

This article describes how to create a Hybrid SQL Server Distributed Availability Group between SQL Linux and SQL Windows

 

Here is the topology of the DAG environment

Data Center 1

Data Center 2
  • Red1:192.168.3.102 (Primary)
  • Red2:192.168.3.103
  • Availability group :AGLinux
  • Listener:AGLinux-Lis
  • IP:192.168.3.111
  • SQL Server: 2019 CU2
  • RHEL 8
  • Node1:192.168.2.101(Forwarder)
  • Node2:192.168.2.102
  • Availability group :AGWindows
  • Listener:AGWindows-Lis:
  • IP:192.168.2.111
  • SQL Server: 2019 CU2
  • Windows 2016

Distributed Availability group:DAGHybrid

Please make use following ports are opened on all the servers in both datacenters.

TCP:5022,2224,3121,21064

UDP:5405

 

For RHEL, you need to have the 'high availability subscription' enabled. Please refer https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-cluster-rhel?view=sql... for detail.

Section 1:Create Availability group in datacenter1

1. Updates /etc/hosts on all the nodes Datacenter1, add IP , replica name and AG Listeners.

Here is a screenshot. All servers in the datacenter1 should have same setting.(Although the IP resources of Listeners will be created in later steps, it’s ok to add at the very beginning)

 

hostx.png

2. Run following bash commands on all servers in datacenter1 to enable Alwayson Feature.

sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
sudo systemctl restart mssql-server

3.Run following T-SQL to Enable Always On Health check event session on all the servers in datacenter1.

 

ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);

 

4.Create a certificate, run the query on primary replica in datacenter1(red1 in this case)

use master
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
go
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';
go
BACKUP CERTIFICATE dbm_certificate TO FILE = '/var/opt/mssql/data/dbm_certificate.cer'
WITH PRIVATE KEY (
FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
ENCRYPTION BY PASSWORD = '**<Private_Key_Password>**'
);

5. Copy the certificate to rest of the servers in datacenter1(red2 in this case)

cd /var/opt/mssql/data
scp dbm_certificate.* root@**<nodeName>**:/var/opt/mssql/data/
 

 

6. Give permission to the mssql user to access the certificate files in rest of the servers(red2 in this case).Run following bash command in red2.

cd /var/opt/mssql/data
chown mssql:mssql dbm_certificate.*

7. Create the certificate on rest of the servers by restoring the certificate backup(red2 in this case)

use master
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**'
go
CREATE CERTIFICATE dbm_certificate
   FROM FILE = '/var/opt/mssql/data/dbm_certificate.cer'  
   WITH PRIVATE KEY (
   FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
   DECRYPTION BY PASSWORD = '**<Private_Key_Password>**'
)

8.Create endpoint on all servers in datacenter1. The port 5022 is used.

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (
    ROLE = ALL,
    AUTHENTICATION = CERTIFICATE dbm_certificate,
    ENCRYPTION = REQUIRED ALGORITHM AES
);
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;

9.Create AG with 2 synchronous replicas in datacenter1. Run following query on primary replica (red1 in this case)

CREATE AVAILABILITY GROUP [AGLinux]
    WITH (DB_FAILOVER = ON, CLUSTER_TYPE = EXTERNAL)
    FOR REPLICA ON
    N'red1'
        WITH (
        ENDPOINT_URL = N'tcp://red1:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = EXTERNAL,
        SEEDING_MODE = AUTOMATIC),
    N'red2'
        WITH (
        ENDPOINT_URL = N'tcp://red2:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = EXTERNAL,
        SEEDING_MODE = AUTOMATIC) 
go
ALTER AVAILABILITY GROUP [AGLinux] GRANT CREATE ANY DATABASE;--grant create any database permission

10.Join the AG group, run the following T-SQL queries on all the secondary servers in datacenter1(red4 in this case)

ALTER AVAILABILITY GROUP [AGLinux] JOIN WITH (CLUSTER_TYPE = EXTERNAL);
ALTER AVAILABILITY GROUP [AGLinux] GRANT CREATE ANY DATABASE

 

11.Create database and add to AG group on primary replica.(red1 in this case).

CREATE DATABASE [db2];
ALTER DATABASE [db2] SET RECOVERY FULL;
BACKUP DATABASE [db2] TO DISK = N'db2.bak';
BACKUP log [db2] TO DISK = N'db2.trn';
GO
ALTER AVAILABILITY GROUP [AGLinux] ADD DATABASE [db2];
 

 

12.Create SQL login pacemaker on all servers in datacenter1

CREATE LOGIN [pacemakerLogin] with PASSWORD= N'ComplexP@$$w0rd!'
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [pacemakerLogin]
GO

13.Install pacemaker.Run the following bash command on all servers in datacenter1.

sudo yum install pacemaker pcs fence-agents-all resource-agents

14.Install SQL Server resource agent for SQL Server. Run the following bash command on all servers in datacenter1.

sudo yum install mssql-server-ha

15.Set the password for the default user that is created when installing Pacemaker and Corosync packages. Run following command on all servers in datacenter1(This command asks for password. The password should be same for the servers in same datacenter)

sudo passwd hacluster

16.To allow nodes to rejoin the cluster after the reboot, enable and start pcsd service and Pacemaker. Run the following commands on all servers in datacenter1.

sudo systemctl enable pcsd
sudo systemctl start pcsd
sudo systemctl enable pacemaker

17.Create a cluster in datacenter1. Run following commands on any one of the nodes in datacenter1(just on one server, and just one time). You will be asked to provide the username and password you  specified in step 15.

sudo pcs host auth red1 red2
sudo pcs cluster setup  cluster1 red1 red2
sudo pcs cluster start --all
sudo pcs cluster enable --all

pcshost.png

18.Because the node level fencing configuration depends heavily on your environment, disable it for this tutorial (it can be configured later. In my test, if this value is not set to false, you can’t failover, and IP resource may fail). The following script disables node level fencing(run it on any nodes in datacenter1)

sudo pcs property set stonith-enabled=false

20.On all SQL Server Linux servers in datacenter1 , save the credentials for the SQL Server login.(Use the same user name and password created in step12)

echo 'pacemakerLogin' >> ~/pacemaker-passwd 
echo 'ComplexP@$$w0rd!' >> ~/pacemaker-passwd 
sudo mv ~/pacemaker-passwd /var/opt/mssql/secrets/passwd 
sudo chown root:root /var/opt/mssql/secrets/passwd 
sudo chmod 400 /var/opt/mssql/secrets/passwd # Only readable by root

21.Create availability group resource at OS level in datacenter1, run following command on any one of the nodes in datacenter1(just on one node, and just one time)Here are the resource and constraint before creating the resource(no resource/constraint)

sudo pcs resource create ag_cluster ocf:mssql:ag ag_name=AGLinux meta failure-timeout=60s promotable notify=true

pcsresource.png

22.Create virtual IP resource. The resource name is ‘virtualip1’, and IP Address is 192.168.3.111 in this demonstration. This is the IP Address for the AG Listener.

sudo pcs resource create virtualip1 ocf:heartbeat:IPaddr2 ip=192.168.3.111

 

23.Create Availability group listener for Availability group ag1. Run following T-SQL query in primary replica(red1 in this case). The ip address should be as same as the IP in step 22.

ALTER AVAILABILITY GROUP [AGLinux] 
ADD LISTENER 'AGLinux-Lis' (WITH IP(('192.168.3.111','255.255.255.0')),PORT = 1433);

a)It’s fine if you run step 23 before the step 22, however, you can’t use the IP Address to connect SQL Server until the resource in Pacemaker is created.

b)There is a bug in DMV sys.availability_group_listener_ip_addresses showing the state is offline

24.Add colocation constraint. Please note, without the constraint, the ip resource does not failover when the AG resource failover. Hence we need to create a constraint to guarantee that the IP will failover when cluster resource failover. Run following command on any servers in datacenter1(just on one server, and just one time)

sudo pcs constraint colocation add virtualip1 with master ag_cluster-clone INFINITY with-rsc-role=Master

25.Add ordering constraint

sudo pcs constraint order promote ag_cluster-clone then start virtualip1

constraint.png

25.To update the property value to 2 minutes run. Run following bash command  on any node(just one time)

sudo pcs property set cluster-recheck-interval=2min
sudo pcs property set start-failure-is-fatal=true
pcs resource update ag_cluster meta failure-timeout=60s

Section 2:Create Availability group in datacenter2(Actually, you can run this section first as long as the certificate is as same as the one used in Section1).

Steps in Section 2 is exactly same to regular SQL Server AG in Windows, the only thing I’d like to highlight is that you have to create the endPoint authenticated by certificate, which is the same certificate you used in SQL Linux.

1.Copy certificate backup in SQL Linux to the two Windows servers.

2.Run following queries to create master key ,certificate and endpoint

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**'
go
CREATE CERTIFICATE dbm_certificate
   FROM FILE = 'C:\Backup\dbm_certificate.cer'  
   WITH PRIVATE KEY (
   FILE = 'C:\Backup\dbm_certificate.pvk',
   DECRYPTION BY PASSWORD = '**<Private_Key_Password>**'
)
go
create ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (
    ROLE = ALL,
    AUTHENTICATION = CERTIFICATE dbm_certificate,
    ENCRYPTION = REQUIRED ALGORITHM AES
);
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;

3.Create login and grant permission to access the endpoint.

CREATE LOGIN InstanceA_Login WITH PASSWORD = 'Strong Passworsdfg1#d';
CREATE USER InstanceA_User FOR LOGIN InstanceA_Login;
go
alter authorization on CERTIFICATE::dbm_certificate to InstanceA_User
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO InstanceA_Login;

4.Create Availability group

 

CREATE AVAILABILITY GROUP [AGWindows]
FOR 
REPLICA ON N'node1' 
WITH
(
    ENDPOINT_URL = N'TCP://node1:5022', 
    FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SEEDING_MODE = AUTOMATIC
), 
N'node2' 
WITH 
(
    ENDPOINT_URL = N'TCP://node2:5022', 
    FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SEEDING_MODE = AUTOMATIC
)

 

5.Create Listener

ALTER AVAILABILITY GROUP [AGWindows] 
ADD LISTENER 'AGWindows-Lis' (WITH IP(('192.168.2.111','255.255.255.0')),PORT = 1433);

6.Run following T-SQL to connect the primary replica on all replicas(node2 in this case)

ALTER AVAILABILITY GROUP [AGWindows] JOIN 
GO
ALTER AVAILABILITY GROUP [AGWindows] GRANT CREATE ANY DATABASE;
 

7.Edit the hosts file on two windows servers, make sure they are able to access the listener AGLinux-Lis.

 

 

Section 3:Create Hybrid Distributed Availability group 

1.Create distributed availability group in datacenter1. Run following query in primary replica in datacenter1( red1 in this case)

CREATE AVAILABILITY
GROUP [DAGHybrid]
WITH (DISTRIBUTED) AVAILABILITY
GROUP ON 'AGLinux'
WITH (
        LISTENER_URL = 'tcp://AGLinux-lis:5022'
        ,AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT
        ,FAILOVER_MODE = MANUAL
        ,SEEDING_MODE = AUTOMATIC
        )
    ,'AGWindows'
WITH (
        LISTENER_URL = 'tcp://AGWindows-Lis:5022'
        ,AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT
        ,FAILOVER_MODE = MANUAL
        ,SEEDING_MODE = AUTOMATIC
        );

2.Run following query on the forward to join the Distributed AG

ALTER AVAILABILITY
GROUP [DAGHybrid]
JOIN AVAILABILITY
GROUP ON 'AGLinux'
WITH (
        LISTENER_URL = 'tcp://AGLinux-lis:5022'
        ,AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT
        ,FAILOVER_MODE = MANUAL
        ,SEEDING_MODE = AUTOMATIC
        ),
'AGWindows'
WITH (
        LISTENER_URL = 'tcp://AGWindows-Lis:5022'
        ,AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT
        ,FAILOVER_MODE = MANUAL
        ,SEEDING_MODE = AUTOMATIC
        );
 

pcsresource.png

 

The article for RHEL 7.6 is here:https://sqlserver.code.blog/2020/02/14/create-a-hybrid-distributed-availability-group-between-sql-wi...

 

Version history
Last update:
‎Aug 05 2020 10:30 PM
Updated by:
Labels