Blog Post

SQL Server Support Blog
13 MIN READ

Create Distributed Availability Group in Sql Server Linux(Redhat) --- step by step

Liwei's avatar
Liwei
Icon for Microsoft rankMicrosoft
Nov 14, 2019
This article describes how to create a SQL Server Distributed Availability Group (AG) for high availability on RHEL 7.6. Here is the topology of the DAG environment Data Center 1 D...
Updated Aug 17, 2020
Version 29.0
佳易 楊's avatar
佳易 楊
Copper Contributor
Jan 24, 2020

hi,

This is my memo..

Share to here!

--Azure構築前の注意点
/****************************************
AGのリスナーを構築するため、BackendのVMは必ず最初から可能性セットとして構成しないといけない。
仮想マシンのパブリックIPのSKUはStandardをおすすめ
LBのSKUもStandardをおすすめ
二つ仮想ネットワークをペアリングを行う
*****************************************/

--DAG構成
/****************************************
DC1以下AG1という
SERVER:sql001.azurevm.net
IP:192.168.1.190
SERVER:sql002.azurevm.net
IP:192.168.1.191
SERVER:sql003.azurevm.net
IP:192.168.1.192
SERVER:sql004.azurevm.net
IP:192.168.1.193

LB IP:192.168.1.50

DC2以下AG2という
SERVER:sql003.azurevm.net
IP:192.168.1.194
SERVER:sql004.azurevm.net
IP:192.168.1.195
SERVER:sql005.azurevm.net
IP:192.168.1.196
SERVER:sql006.azurevm.net
IP:192.168.1.197

LB IP:192.168.1.62
*****************************************/

--★(Azureの場合、Rootのパスワードを変更しておく)
--後続の構成プロセスの中に、scpコマンドでレプリカへコピーするため、あらかじめに、Rootのパスワードを変更しておく
--そして、RHELを最新化しておく
/****************************************
sudo su
passwd
sudo yum update -y
*****************************************/

--★
--AG1、AG2のすべてのRHELサーバーにSQL Server2019+GDR1、SQL Server HAパッケージををインストールする
--そして、SQL Server Agentを有効にしておく
/****************************************
sudo curl -o /etc/yum.repos.d/mssql-server.repo <a href="https://packages.microsoft.com/config/rhel/7/mssql-server-2019.repo" target="_blank">https://packages.microsoft.com/config/rhel/7/mssql-server-2019.repo</a>
sudo yum install -y mssql-server
sudo /opt/mssql/bin/mssql-conf setup

sudo yum install mssql-server-ha -y

sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
sudo /opt/mssql/bin/mssql-conf set network.tcpport 2433
sudo systemctl restart mssql-server

sudo firewall-cmd --zone=public --add-port=2433/tcp --permanent
sudo firewall-cmd --zone=public --add-port=5022/tcp --permanent
sudo firewall-cmd --zone=public --add-port=2224/tcp --permanent
sudo firewall-cmd --zone=public --add-port=3123/tcp --permanent
sudo firewall-cmd --zone=public --add-port=21064/tcp --permanent
sudo firewall-cmd --zone=public --add-port=5405/udp --permanent
sudo firewall-cmd --permanent --add-service=high-availability
sudo firewall-cmd --reload

systemctl status mssql-server
*****************************************/

--★
--AG1、AG2のRHELサーバーのHostsを編集する。Hostsには、それぞれのAGのサーバー名とリスナー名のIPを登録しておく
/****************************************
vi /etc/hosts
192.168.1.190 SQL001.azurevm.net
192.168.1.191 SQL002.azurevm.net
192.168.1.192 SQL003.azurevm.net
192.168.1.193 SQL004.azurevm.net
192.168.1.194 SQL005.azurevm.net
192.168.1.195 SQL006.azurevm.net
192.168.1.196 SQL007.azurevm.net
192.168.1.197 SQL008.azurevm.net
192.168.1.190 SQL001
192.168.1.191 SQL002
192.168.1.192 SQL003
192.168.1.193 SQL004
192.168.1.194 SQL005
192.168.1.195 SQL006
192.168.1.196 SQL007
192.168.1.197 SQL008
192.168.1.50 ag1.azurevm.net
192.168.1.62 ag2.azurevm.net
:wq

*****************************************/

--★
--AG1、AG2のそれぞれのサーバーにpacemakerインストールする
--AG1、AG2のそれぞれのサーバーのhaclusterのパスワードを変更しておく
--AG1、AG2のそれぞれのサーバーのpcsdサービスとpacemakerを有効にする
/****************************************
sudo yum install pacemaker pcs fence-agents-all resource-agents -y
sudo passwd hacluster

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

*****************************************/

--★
--pacemakerを設定する前に、AG1、AG2のSQLサーバーに、pacemakerLoginを作成しておく。そして、AlwaysOnのXEventのジョブを開始する。
USE [master]
GO
CREATE LOGIN [pacemakerLogin] with PASSWORD= N'<yourpassword>'
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [pacemakerLogin]
GO

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

--★
--Pacemakerでリソースを作成する際に、SQL Serverのシステムオブジェクトにアクセスがあるため、Pacemaker用のSQLユーザーの資格情報を保存する
--chmod 400はReadonly、Chmod 777は編集可能
/****************************************
echo 'pacemakerLogin' >> ~/pacemaker-passwd
echo '<yourpassword>' >> ~/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
cat /var/opt/mssql/secrets/passwd
*****************************************/

--★
--クラスタを構成するために、AG1のサーバーで、実行する、そして、クラスタ状況を確認する
/****************************************
pcs cluster auth sql001.azurevm.net sql002.azurevm.net sql003.azurevm.net sql004.azurevm.net -u hacluster -p '<yourpassword>'
sudo pcs cluster setup --name cluster1 sql001.azurevm.net sql002.azurevm.net sql003.azurevm.net sql004.azurevm.net --force
sudo pcs cluster start --all
sudo pcs cluster enable --all
pcs cluster status

--Ref:<a href="https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-cluster-rhel?view=sql-server-ver15" target="_blank">https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-cluster-rhel?view=sql-server-ver15</a>
--AG1のすべてのサーバーで実行する
--Shoot The Other Node In The Head
--スプリットブレインを防ぐための機構で、ノード間の通信に異常が発生した場合に、強制的に対向のノードを再起動(フェンシング) することで、両ノードがマスターになる(リソースを同時にアクセスする) ことを防ぐ
sudo pcs property set stonith-enabled=false
*****************************************/

--★
--クラスタを構成するために、AG2のサーバーで、実行する、そして、クラスタ状況を確認する
/****************************************
pcs cluster auth sql005.azurevm.net  sql006.azurevm.net sql007.azurevm.net  sql008.azurevm.net -u hacluster -p '<yourpassword>'
sudo pcs cluster setup --name cluster2 sql005.azurevm.net  sql006.azurevm.net sql007.azurevm.net  sql008.azurevm.net --force
sudo pcs cluster start --all
sudo pcs cluster enable --all
pcs cluster status

--Ref:<a href="https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-cluster-rhel?view=sql-server-ver15" target="_blank">https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-cluster-rhel?view=sql-server-ver15</a>
--AG2のすべてのサーバーで実行する
sudo pcs property set stonith-enabled=false
--Shoot The Other Node In The Head
--スプリットブレインを防ぐための機構で、ノード間の通信に異常が発生した場合に、強制的に対向のノードを再起動(フェンシング) することで、両ノードがマスターになる(リソースを同時にアクセスする) ことを防ぐ
sudo pcs property set stonith-enabled=false
*****************************************/

--★
--構成済みのクラスタに、リソースを追加する。プロパティのパラメータを変更する。
--AG1、Clusterのアクティブ側のサーバーで実行する、要注意
/****************************************
sudo pcs resource create ag_cluster1 ocf:mssql:ag ag_name=ag1 port=2433 meta failure-timeout=60s master notify=true
sudo pcs resource create virtualip1 ocf:heartbeat:IPaddr2 ip=192.168.1.50 cidr_netmask=24
sudo pcs constraint colocation add virtualip1 ag_cluster1-master INFINITY with-rsc-role=Master
sudo pcs constraint order promote ag_cluster1-master then start virtualip1
sudo pcs property set cluster-recheck-interval=2min
sudo pcs property set start-failure-is-fatal=true
sudo pcs resource update ag_cluster1 meta failure-timeout=60s
sudo pcs resource update ag_cluster1 required_synchronized_secondaries_to_commit=2
*****************************************/

--★
--構成済みのクラスタに、リソースを追加する。
--AG2、Clusterのアクティブ側のサーバーで実行する、要注意
/****************************************
sudo pcs resource create ag_cluster2 ocf:mssql:ag ag_name=ag2 port=2433 meta failure-timeout=60s master notify=true
sudo pcs resource create virtualip2 ocf:heartbeat:IPaddr2 ip=192.168.1.62 cidr_netmask=24
sudo pcs constraint colocation add virtualip2 ag_cluster2-master INFINITY with-rsc-role=Master
sudo pcs constraint order promote ag_cluster2-master then start virtualip2
sudo pcs property set cluster-recheck-interval=2min
sudo pcs property set start-failure-is-fatal=true
sudo pcs resource update ag_cluster2 meta failure-timeout=60s
sudo pcs resource update ag_cluster2 required_synchronized_secondaries_to_commit=2
*****************************************/

--★
--AG1のプライマリーレプリカ(SQL001)で、証明書を作成する
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<yourpassword>';
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 = '<yourpassword>')
GO

--★
--AG1のプライマリーレプリカ(SQL001)で作った証明書をSQL002にコピーする、そして、AG2のそれぞれのサーバーにもコピーする
--この操作はAG1プライマリーレプリカ(SQL001)で実行する
/****************************************
cd /var/opt/mssql/data
scp dbm_certificate.* root@sql002.azurevm.net:/var/opt/mssql/data/
scp dbm_certificate.* root@sql003.azurevm.net:/var/opt/mssql/data/
scp dbm_certificate.* root@sql004.azurevm.net:/var/opt/mssql/data/
scp dbm_certificate.* root@sql005.azurevm.net:/var/opt/mssql/data/
scp dbm_certificate.* root@sql006.azurevm.net:/var/opt/mssql/data/
scp dbm_certificate.* root@sql007.azurevm.net:/var/opt/mssql/data/
scp dbm_certificate.* root@sql008.azurevm.net:/var/opt/mssql/data/
*****************************************/

--★
--AG1のセカンダリレプリカ(SQL002)、そして、AG2のそれぞれのサーバーにログインし、コピーされた証明書をmssqlユーザーにアクセス権限を与える
--この操作はAG1プライマリーレプリカ(SQL001)以外のサーバーで実行する
/****************************************
cd /var/opt/mssql/data
chown mssql:mssql dbm_certificate.*
*****************************************/

--★
--AG1のセカンダリレプリカ(SQL002)、そして、AG2のそれぞれのSQLサーバーに証明書をリストアする
--この操作はAG1プライマリーレプリカ(SQL001)以外のサーバーで実行する
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<yourpassword>'
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 = '<yourpassword>')
GO

--★
--AG1、AG2それぞれのサーバーのEndPointを作成する
USE master
GO
CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (
    ROLE = WITNESS,
    AUTHENTICATION = CERTIFICATE dbm_certificate,
    ENCRYPTION = REQUIRED ALGORITHM AES
)
GO
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
GO

--★
--SQL001で可用性グループを作する
USE [master]
GO
CREATE AVAILABILITY GROUP [ag1]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
DB_FAILOVER = ON,
DTC_SUPPORT = NONE,
CLUSTER_TYPE = EXTERNAL,
REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0)
FOR 
REPLICA ON 
N'sql001' WITH (ENDPOINT_URL = N'TCP://sql001.azurevm.net:5022', 
FAILOVER_MODE = EXTERNAL, 
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
SESSION_TIMEOUT = 10, BACKUP_PRIORITY = 50, SEEDING_MODE = AUTOMATIC, 
PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
N'sql002' WITH (ENDPOINT_URL = N'TCP://sql002.azurevm.net:5022', 
FAILOVER_MODE = EXTERNAL, 
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
SESSION_TIMEOUT = 10, BACKUP_PRIORITY = 50, SEEDING_MODE = AUTOMATIC,
PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
N'sql003' WITH (ENDPOINT_URL = N'TCP://sql003.azurevm.net:5022', 
FAILOVER_MODE = EXTERNAL, 
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
SESSION_TIMEOUT = 10, BACKUP_PRIORITY = 50, SEEDING_MODE = AUTOMATIC,
PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
N'sql004' WITH (ENDPOINT_URL = N'TCP://sql004.azurevm.net:5022', 
FAILOVER_MODE = EXTERNAL, 
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
SESSION_TIMEOUT = 10, BACKUP_PRIORITY = 50, SEEDING_MODE = AUTOMATIC,
PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL))
GO

ALTER AVAILABILITY GROUP [ag1]
ADD LISTENER N'ag1-listnener' (
WITH IP
((N'192.168.1.50', N'255.255.255.0')
)
, PORT=2433)
GO

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE
GO

--★
--SQL002、SQL003、SQL004で可用性グループに参加する
USE [master]
GO
ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = EXTERNAL)
GO
ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE
GO

--★
--pacemakerのリソース状況を確認できたら、次へ(クラスタの切り替えで、プライマレプリカやセカンダリレプリカが変わったりするので)

--★
--SQL005で可用性グループを作する
USE [master]
GO
CREATE AVAILABILITY GROUP [ag2]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
DB_FAILOVER = ON,
DTC_SUPPORT = NONE,
CLUSTER_TYPE = EXTERNAL,
REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0)
FOR 
REPLICA ON 
N'sql005' WITH (ENDPOINT_URL = N'TCP://sql005.azurevm.net:5022', 
FAILOVER_MODE = EXTERNAL, 
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
SESSION_TIMEOUT = 10, BACKUP_PRIORITY = 50, SEEDING_MODE = AUTOMATIC, 
PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
N'sql006' WITH (ENDPOINT_URL = N'TCP://sql006.azurevm.net:5022', 
FAILOVER_MODE = EXTERNAL, 
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
SESSION_TIMEOUT = 10, BACKUP_PRIORITY = 50, SEEDING_MODE = AUTOMATIC,
PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
N'sql007' WITH (ENDPOINT_URL = N'TCP://sql007.azurevm.net:5022', 
FAILOVER_MODE = EXTERNAL, 
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
SESSION_TIMEOUT = 10, BACKUP_PRIORITY = 50, SEEDING_MODE = AUTOMATIC,
PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
N'sql008' WITH (ENDPOINT_URL = N'TCP://sql008.azurevm.net:5022', 
FAILOVER_MODE = EXTERNAL, 
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
SESSION_TIMEOUT = 10, BACKUP_PRIORITY = 50, SEEDING_MODE = AUTOMATIC,
PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL))
GO

ALTER AVAILABILITY GROUP [ag2]
ADD LISTENER N'ag2-listnener' (
WITH IP
((N'192.168.1.62', N'255.255.255.0')
)
, PORT=2433)
GO

ALTER AVAILABILITY GROUP [ag2] GRANT CREATE ANY DATABASE
GO

--★
--SQL006、SQL007、SQL008で可用性グループに参加する
USE [master]
GO
ALTER AVAILABILITY GROUP [ag2] JOIN WITH (CLUSTER_TYPE = EXTERNAL)
GO
ALTER AVAILABILITY GROUP [ag2] GRANT CREATE ANY DATABASE
GO

--★
--pacemakerのリソース状況を確認できたら、次へ(クラスタの切り替えで、プライマレプリカやセカンダリレプリカが変わったりするので)

--★
--ag1-listenerとag2-listenerの動作を確認する。DAG構成は、リスナーが動作していることは前提条件。

--★
--AG1のプライマリーレプリカで実行する
USE [master]
GO
CREATE AVAILABILITY
GROUP [distributedag]
WITH (DISTRIBUTED) AVAILABILITY
GROUP ON
'ag1' WITH (
        LISTENER_URL = 'tcp://ag1.azurevm.net:5022'
        ,AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT
        ,FAILOVER_MODE = MANUAL
        ,SEEDING_MODE = AUTOMATIC)
,'ag2' WITH (
        LISTENER_URL = 'tcp://ag2.azurevm.net:5022'
        ,AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT
        ,FAILOVER_MODE = MANUAL
        ,SEEDING_MODE = AUTOMATIC)
GO

--AG2のプライマリーレプリカ(フォーワーダーレプリカ)で実行する
USE [master]
GO
ALTER AVAILABILITY
GROUP [distributedag]
JOIN AVAILABILITY
GROUP ON
'ag1' WITH (
        LISTENER_URL = 'tcp://ag1.azurevm.net:5022'
        ,AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT
        ,FAILOVER_MODE = MANUAL
        ,SEEDING_MODE = AUTOMATIC)
,'ag2' WITH (
        LISTENER_URL = 'tcp://ag2.azurevm.net:5022'
        ,AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT
        ,FAILOVER_MODE = MANUAL
        ,SEEDING_MODE = AUTOMATIC)
GO

--★
--AG1のプライマリーレプリカで実行する
USE [master]
GO

CREATE DATABASE [db_JSSUG]
GO
ALTER DATABASE [db_JSSUG] SET RECOVERY FULL
GO

USE [db_JSSUG]
GO

CREATE TABLE [tbl_JSSUG] (
    col1 INT PRIMARY KEY
)
GO

INSERT INTO [tbl_JSSUG]
VALUES (1)
GO

BACKUP DATABASE [db_JSSUG] TO DISK = N'/var/opt/mssql/data/db_JSSUG.bak'
GO
BACKUP log [db_JSSUG] TO DISK = N'/var/opt/mssql/data/db_JSSUG.trn'
GO

--★
--[db_JSSUG]をAG1に追加する
USE [master]
GO
ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [db_JSSUG]
GO

--★
--分散型可用性グループの[db_JSSUG]を確認する
--終わり