SQL Server 2019 : 可用性グループの設定2020/01/21 |
|
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] | |
| [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 ~]# sqlcmd -S localhost -U SA Password: 1> create master key encryption by password = 'password'; 2> create certificate node01_cert with subject = 'AG Node01 Certificate'; 3> backup certificate node01_cert 4> to file = '/var/opt/mssql/data/node01_cert.cer' 5> go 1> create endpoint AGEndpoint state = started 2> as tcp (listener_ip = all, listener_port = 5022) 3> for data_mirroring ( 4> role = all, 5> authentication = certificate node01_cert); 6> go 1> exit[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 ~]# sqlcmd -S localhost -U SA Password: 1> create certificate node02_cert authorization dbm_user 2> from file = '/var/opt/mssql/data/node02_cert.cer' 3> go 1> create certificate node03_cert authorization dbm_user 2> from file = '/var/opt/mssql/data/node03_cert.cer' 3> go 1> grant connect on endpoint::AGEndpoint to dbm_login; 2> go 1> exit |
| [5] | プライマリーノードで可用性グループを作成します。 |
|
[root@node01 ~]# sqlcmd -S localhost -U SA Password: 1> create availability group [AG01] 2> with (cluster_type = none) 3> for replica on 4> N'node01' with ( 5> endpoint_url = N'tcp://10.0.0.51:5022', 6> availability_mode = asynchronous_commit, 7> failover_mode = manual, 8> seeding_mode = automatic, 9> primary_role (read_only_routing_list=(('node02','node03'), 'node01')), 10> secondary_role ( 11> allow_connections = read_only, 12> read_only_routing_url = N'tcp://10.0.0.51:1433')), 13> N'node02' with ( 14> endpoint_url = N'tcp://10.0.0.52:5022', 15> availability_mode = asynchronous_commit, 16> failover_mode = manual, 17> seeding_mode = automatic, 18> primary_role (read_only_routing_list=(('node01','node03'), 'node02')), 19> secondary_role ( 20> allow_connections = read_only, 21> read_only_routing_url = N'tcp://10.0.0.52:1433')), 22> N'node03' with ( 23> endpoint_url = N'tcp://10.0.0.53:5022', 24> availability_mode = asynchronous_commit, 25> failover_mode = manual, 26> seeding_mode = automatic, 27> primary_role (read_only_routing_list=(('node01','node02'), 'node03')), 28> secondary_role ( 29> allow_connections = read_only, 30> read_only_routing_url = N'tcp://10.0.0.53:1433')); 31> alter availability group [AG01] grant create any database; 32> go 1> exit |
| [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 ~]# sqlcmd -S localhost -U SA Password: 1> alter availability group [AG01] add listener 'AG01_listener' 2> ( with ip ( 5> ('10.0.0.51', '255.255.255.0') ), 6> port = 1433); 7> go 1> create database [AG01_DB]; 2> alter database [AG01_DB] set recovery full; 3> backup database [AG01_DB] to disk = N'/var/opt/mssql/data/AG01_DB.bak'; 4> alter availability group [AG01] add database [AG01_DB]; 5> go 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.441 seconds (5.837 MB/sec). 1> exit # セカンダリーノードで追加したデータベースが見えるか確認 [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 2020-01-20 20:53:53.870 (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 ------------------------------------ --------------------- ------------- 94D463BE-D124-48FD-AC27-21EC919A0D01 node01 PRIMARY 850D06C6-6F76-4409-9055-53DFF5B2359E node02 SECONDARY 734F7B6D-0370-4124-8B01-350EC14A98B9 node03 SECONDARY (3 rows affected) 1> exit # リードオンリーアクセスはセカンダリーレプリカが応答 [root@node01 ~]# sqlcmd -S node01 -U SA -K readonly -d AG01_DB -Q 'select @@servername' Password: ------------- node03 (1 rows affected) # リードオンリーアクセスはセカンダリーレプリカが応答 (ラウンドロビンで負荷分散) [root@node01 ~]# sqlcmd -S node01 -U SA -K readonly -d AG01_DB -Q 'select @@servername' Password: ------------- node02 (1 rows affected) # 非リードオンリーアクセスはプライマリーレプリカが応答 [root@node01 ~]# sqlcmd -S node01 -U SA -d AG01_DB -Q 'select @@servername' Password: -------------- node01 (1 rows affected) |
| [9] | Windows 上の SSMS からも、[Always On 高可用性] セクション配下で、状態の確認や一部の設定の変更が可能です。 |
|
関連コンテンツ