CentOS Stream 8
Sponsored Link

SQL Server 2019 : 可用性グループの設定2021/05/30
 
Always On 可用性グループを作成して、読み取り専用レプリカを追加し、SQL Server を冗長構成に設定します。
当例では、以下のような環境を前提に進めます。
3台の SQL Server でクラスターを構成し、1台は読み書き可能なプライマリーレプリカ、他の 2台を読み取り専用のセカンダリーレプリカとなるよう構成します。 
-----------+-----------------------------+-----------------------------+------------
           |10.0.0.51                    |10.0.0.52                    |10.0.0.53
+----------+-----------+      +----------+-----------+      +----------+-----------+
| [ node01.srv.world ] |      | [ node02.srv.world ] |      | [ node03.srv.world ] |
|      (Primary)       |      |      (Secondary)     |      |      (Secondary)     |
|      SQL Server      |      |       SQL Server     |      |       SQL Server     |
+----------------------+      +----------------------+      +----------------------+
[1]
こちらを参考に全ノードに SQL Server をインストールしておきます。
[2] 全ノードで Always On 可用性を有効にし、データベース同期用のユーザーを作成しておきます。
[password] の箇所は任意のパスワードに置き換えてください。
[root@node01 ~]#
/opt/mssql/bin/mssql-conf set hadr.hadrenabled 1

SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.
[root@node01 ~]#
systemctl restart mssql-server

[root@node01 ~]#
sqlcmd -S localhost -U SA

Password:
1> alter event session AlwaysOn_health on server with (startup_state=on);
2> go
1> create login dbm_login with password = 'password';
2> create user dbm_user for login dbm_login;
3> go
1> exit
[3] 全ノードで、証明書を作成して各ノードへコピーし、エンドポイントを作成します。
[password] の箇所は任意のパスワードに置き換えてください。
また、下例の [node01_cert], [node01_cert.cer] の箇所は、各ノードで重複しない一意の名称に置き換えます。
[root@node01 ~]#
NODENAME=$(hostname -s) 

[root@node01 ~]# cat > create-cert.sql <<EOF
create master key encryption by password = 'password';
create certificate ${NODENAME}_cert with subject = 'AG ${NODENAME} Certificate';
backup certificate ${NODENAME}_cert
to file = '/var/opt/mssql/data/${NODENAME}_cert.cer'
create endpoint AGEndpoint state = started
as tcp (listener_ip = all, listener_port = 5022)
for data_mirroring ( role = all, authentication = certificate ${NODENAME}_cert);
EOF
[root@node01 ~]#
sqlcmd -S localhost -U SA -i create-cert.sql
[root@node01 ~]#
cd /var/opt/mssql/data

[root@node01 data]#
scp node01_cert.cer node02.srv.world:/var/opt/mssql/data/ 

dbm_certificate.cer                           100%  667   614.7KB/s   00:00
dbm_certificate.pvk                           100% 1212     1.0MB/s   00:00
[root@node01 data]#
scp node01_cert.cer node03.srv.world:/var/opt/mssql/data/ 

dbm_certificate.cer                           100%  667   581.6KB/s   00:00
dbm_certificate.pvk                           100% 1212   935.1KB/s   00:00
[root@node01 data]#
ssh node02.srv.world 'chown mssql. /var/opt/mssql/data/node01_cert.cer'

[root@node01 data]#
ssh node03.srv.world 'chown mssql. /var/opt/mssql/data/node01_cert.cer'
# Firewalld 稼働中の場合はエンドポイントに設定したポートを許可

[root@node01 data]#
firewall-cmd --add-port=5022/tcp --permanent

[root@node01 data]#
firewall-cmd --reload

[4] 全ノードで、他ノードからコピーした証明書をリストアしておきます。 
[root@node01 ~]# cat > restore-cert.sql <<EOF
create certificate node02_cert authorization dbm_user
from file = '/var/opt/mssql/data/node02_cert.cer'
create certificate node03_cert authorization dbm_user
from file = '/var/opt/mssql/data/node03_cert.cer'
grant connect on endpoint::AGEndpoint to dbm_login;
EOF
[root@node01 ~]#
sqlcmd -S localhost -U SA -i restore-cert.sql

[5] プライマリーノードで可用性グループを作成します。 
[root@node01 ~]# cat > create-ag.sql <<'EOF'
create availability group [AG01]
with (cluster_type = none)
for replica on
N'node01' with (
endpoint_url = N'tcp://10.0.0.51:5022',
availability_mode = asynchronous_commit,
failover_mode = manual,
seeding_mode = automatic,
primary_role (read_only_routing_list=(('node02','node03'), 'node01')),
secondary_role (
allow_connections = read_only,
read_only_routing_url = N'tcp://10.0.0.51:1433')),
N'node02' with (
endpoint_url = N'tcp://10.0.0.52:5022',
availability_mode = asynchronous_commit,
failover_mode = manual,
seeding_mode = automatic,
primary_role (read_only_routing_list=(('node01','node03'), 'node02')),
secondary_role (
allow_connections = read_only,
read_only_routing_url = N'tcp://10.0.0.52:1433')),
N'node03' with (
endpoint_url = N'tcp://10.0.0.53:5022',
availability_mode = asynchronous_commit,
failover_mode = manual,
seeding_mode = automatic,
primary_role (read_only_routing_list=(('node01','node02'), 'node03')),
secondary_role (
allow_connections = read_only,
read_only_routing_url = N'tcp://10.0.0.53:1433'));
alter availability group [AG01] grant create any database;
EOF
[root@node01 ~]#
sqlcmd -S localhost -U SA -i create-ag.sql
[6] 全セカンダリーノードで可用性グループに参加します。
[root@node02 ~]#
sqlcmd -S localhost -U SA

Password:
1> alter availability group [AG01] join with (cluster_type = none);
2> alter availability group [AG01] grant create any database;
3> go
1> exit
[7] プライマリーノードで、作成した可用性グループへ、リスナーとデータベースを追加します。 
[root@node01 ~]# cat > create-db.sql <<'EOF'
alter availability group [AG01] add listener 'AG01_listener'
( with ip (
('10.0.0.51', '255.255.255.0') ), port = 1433);
create database [AG01_DB];
alter database [AG01_DB] set recovery full;
backup database [AG01_DB] to disk = N'/var/opt/mssql/data/AG01_DB.bak';
alter availability group [AG01] add database [AG01_DB];
EOF
[root@node01 ~]#
sqlcmd -S localhost -U SA -i create-db.sql

Password:
Processed 328 pages for database 'AG01_DB', file 'AG01_DB' on file 1.
Processed 2 pages for database 'AG01_DB', file 'AG01_DB_log' on file 1.
BACKUP DATABASE successfully processed 330 pages in 0.401 seconds (6.419 MB/sec).
# セカンダリーノードで追加したデータベースが見えるか確認

[root@node01 ~]#
sqlcmd -S node02 -U SA -Q 'select name, create_date from sys.databases where name = "AG01_DB"'

Password:
name                        create_date
--------------------------- -----------------------
AG01_DB                     2021-05-30 13:39:31.057

(1 rows affected)
[8] 以上で設定完了です。動作確認として、可用性グループへ追加したリスナー宛てにアクセスを実行し、 読み取りアクセスは設定通りにセカンダリーノードへ転送されること、 および 設定通り 2台のセカンダリーノードへ負荷分散されることを確認しておきます。
[root@node01 ~]#
sqlcmd -S localhost -U SA

Password:

# 現在の状態
1> select L.replica_id,L.replica_server_name,R.role_desc from sys.availability_replicas as L
2> left join sys.dm_hadr_availability_replica_states as R on L.replica_id = R.replica_id
3> go
replica_id                           replica_server_name           role_desc
------------------------------------ ----------------------------- ----------------
C4534651-C88B-4595-B972-7BE7E691F7D8 node01                        PRIMARY
3F68C7C4-43BE-4E10-AFC5-1B4C72D9FDE7 node02                        SECONDARY
940DECA8-C3D4-4BD4-8F2D-D69425EAAFE5 node03                        SECONDARY

(3 rows affected)

1> exit
# リードオンリーアクセスはセカンダリーレプリカが応答

[root@node01 ~]#
sqlcmd -S 10.0.0.51 -U SA -K readonly -d AG01_DB -Q 'select @@servername'

Password:
-------------
node03

(1 rows affected)
# リードオンリーアクセスはセカンダリーレプリカが応答 (ラウンドロビンで負荷分散)

[root@node01 ~]#
sqlcmd -S 10.0.0.51 -U SA -K readonly -d AG01_DB -Q 'select @@servername'

Password:
-------------
node02

(1 rows affected)
# 非リードオンリーアクセスはプライマリーレプリカが応答

[root@node01 ~]#
sqlcmd -S 10.0.0.51 -U SA -d AG01_DB -Q 'select @@servername'

Password:
--------------
node01

(1 rows affected)
[9] Windows 上の SSMS からも、[Always On 高可用性] セクション配下で、状態の確認や一部の設定の変更が可能です。
関連コンテンツ