AlwaysOn Availability Group における読み取り可能セカンダリレプリカに対するロードバランシングについて

  • Article

Microsoft Japan Data Platform Tech Sales Team

中川

SQL Server では高可用性、DR対策を実現する機能として AlwaysOn Availability Group(以降、AlwaysOn AGと称す) が実装されています。 こちらのブログでも紹介されているように SQL Server 2016 では AlwaysOn AG についていくつかの機能拡張が行われましたが、今回はその中の “読み取り可能セカンダリレプリカに対するロードバランシング” についてご紹介します。

SQL Server 2014 以前の AlwaysOn AG でも読み取り可能セカンダリレプリカについては

  1. READ_ONLY_ROUTING_LIST に記述する順番で優先度付けされる
  2. その時点で最も優先度の高いレプリカにアクセスする

という仕様にて読み取りワークロードをセカンダリレプリカにオフロードさせるなどが行えていましたが、今回のロードバランシングという機能拡張にて

  1. READ_ONLY_ROUTING_LIST に入れ子のかっこでくくった部分は優先度が同じになる
  2. 優先度の同じレプリカに対してはラウンドロビンでアクセスする

という仕様が追加されました。これにより、例えば読み取りワークロードをプライマリレプリカからセカンダリレプリカにオフロードし、さらにその読み取りワークロードをスケールアウトさせるといったことが SQL Server 2016 から可能になりました。

なお、設定方法についてはマニュアル”可用性グループの読み取り専用ルーティングの構成”にて読み取り専用レプリカを構成する手順が記載されていますが、その中でロードバランシングについても以下のように記載されています。

 CREATE AVAILABILITY GROUP または ALTER AVAILABILITY GROUP コマンドで、READ_ONLY_ROUTING_LIST サーバー インスタンスを囲む、1 レベルの入れ子になったかっこを使用します。

 

といっても文章だけでは動作がイメージしにくいため、上記仕様をいくつかのケースの設定にて動作させながら以下で確認してみました。

今回は、プライマリレプリカ 1台 ”NDB01”、セカンダリレプリカ 3台 “NDB02”,”NDB03”,”NDB04” という AlwaysOn 構成を用意しました。

image

[ケース1]

 ALTER AVAILABILITY　GROUP AG01   MODIFY REPLICA ON   N'NDB01' WITH     (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (’NDB02’,'NDB03',’NDB04’)));

この状態でどのようなルーティング優先順位となっているか確認してみます。

 SELECT   ag.name,ar1.replica_server_name as 'Owner Replica',  rl.routing_priority,  ar2.replica_server_name as 'Routing Replica'FROM   sys.availability_read_only_routing_lists rl    inner join sys.availability_replicas ar1 on rl.replica_id = ar1.replica_id    inner join sys.availability_replicas ar2 on rl.read_only_replica_id = ar2.replica_id    inner join sys.availability_groups ag on ag.group_id = ar1.group_idORDER BY   ag.name, ar1.replica_server_name, rl.routing_priority

READ_ONLY_ROUTING_LIST に書いたリスト順に優先度がつけられています。

image

ReadOnlyを指定してリスナー経由で接続してみると、優先度”1”の”NDB02”にしかアクセスしていません。

 >sqlcmd -S AG01Lsnr -d AGTEST01 -E -K ReadOnly -Q "select @@servername" -w 20 -Y 20 -h -1 NDB02 (1 行処理されました) >sqlcmd -S AG01Lsnr -d AGTEST01 -E -K ReadOnly -Q "select @@servername" -w 20 -Y 20 -h -1 NDB02 (1 行処理されました) >sqlcmd -S AG01Lsnr -d AGTEST01 -E -K ReadOnly -Q "select @@servername" -w 20 -Y 20 -h -1 NDB02 (1 行処理されました)

 

[ケース2]

 ALTER AVAILABILITY　GROUP AG01   MODIFY REPLICA ON   N'NDB01' WITH     (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ((‘NDB02’,'NDB03','NDB04’))    ));

入れ子のかっこで指定した”NDB02”、”NDB03”、”NDB04”は同じ優先度”1”となっています。

image

ReadOnlyを指定してリスナー経由で接続してみると、セカンダリレプリカすべてにラウンドロビンでアクセスしています。

 >sqlcmd -S AG01Lsnr -d AGTEST01 -E -K ReadOnly -Q "select @@servername" -w 20 -Y 20 -h -1 NDB02 (1 行処理されました) >sqlcmd -S AG01Lsnr -d AGTEST01 -E -K ReadOnly -Q "select @@servername" -w 20 -Y 20 -h -1 NDB03(1 行処理されました) >sqlcmd -S AG01Lsnr -d AGTEST01 -E -K ReadOnly -Q "select @@servername" -w 20 -Y 20 -h -1 NDB04 (1 行処理されました)>sqlcmd -S AG01Lsnr -d AGTEST01 -E -K ReadOnly -Q "select @@servername" -w 20 -Y 20 -h -1 NDB02(1 行処理されました)

 

[ケース3]

 ALTER AVAILABILITY　GROUP AG01   MODIFY REPLICA ON   N'NDB01' WITH     (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ((‘NDB02’,'NDB03'),'NDB04'))    ));

入れ子のかっこで指定した”NDB02”、”NDB03”は同じ優先度”1”、入れ子のかっこ外の”NDB04”は優先度”2”となっています。

image

ReadOnlyを指定してリスナー経由で接続してみると、優先度”1”である”NDB02”と”NDB03”にラウンドロビンでアクセスしています。

 >sqlcmd -S AG01Lsnr -d AGTEST01 -E -K ReadOnly -Q "select @@servername" -w 20 -Y 20 -h -1 NDB02 (1 行処理されました) >sqlcmd -S AG01Lsnr -d AGTEST01 -E -K ReadOnly -Q "select @@servername" -w 20 -Y 20 -h -1 NDB03(1 行処理されました) >sqlcmd -S AG01Lsnr -d AGTEST01 -E -K ReadOnly -Q "select @@servername" -w 20 -Y 20 -h -1 NDB02(1 行処理されました)>sqlcmd -S AG01Lsnr -d AGTEST01 -E -K ReadOnly -Q "select @@servername" -w 20 -Y 20 -h -1 NDB03(1 行処理されました)

ここで”NDB02”を停止すると、優先度”1”のレプリカは”NDB03”のみとなるため、”NDB03”のみにアクセスすることになります。

 >sqlcmd -S AG01Lsnr -d AGTEST01 -E -K ReadOnly -Q "select @@servername" -w 20 -Y 20 -h -1 NDB03 (1 行処理されました) >sqlcmd -S AG01Lsnr -d AGTEST01 -E -K ReadOnly -Q "select @@servername" -w 20 -Y 20 -h -1 NDB03(1 行処理されました) >sqlcmd -S AG01Lsnr -d AGTEST01 -E -K ReadOnly -Q "select @@servername" -w 20 -Y 20 -h -1 NDB03(1 行処理されました)

更に”NDB03”を停止すると、優先度”1”のレプリカが停止しているために、優先度”2”の”NDB04”にアクセスすることになります。

 >sqlcmd -S AG01Lsnr -d AGTEST01 -E -K ReadOnly -Q "select @@servername" -w 20 -Y 20 -h -1 NDB04 (1 行処理されました) >sqlcmd -S AG01Lsnr -d AGTEST01 -E -K ReadOnly -Q "select @@servername" -w 20 -Y 20 -h -1 NDB04(1 行処理されました) >sqlcmd -S AG01Lsnr -d AGTEST01 -E -K ReadOnly -Q "select @@servername" -w 20 -Y 20 -h -1 NDB04(1 行処理されました)

 

[ケース4]

 ALTER AVAILABILITY　GROUP AG01   MODIFY REPLICA ON   N'ndb01' WITH     (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (‘NDB02’,('NDB03','NDB04'))    ));

入れ子のかっこ外の”NDB02”は優先度”1”、入れ子のかっこで指定した”NDB03”、”NDB04”は同じ優先度”2”となっています。

image

ReadOnlyを指定してリスナー経由で接続してみると、優先度”1”である”NDB02”のみにアクセスしています。

 >sqlcmd -S AG01Lsnr -d AGTEST01 -E -K ReadOnly -Q "select @@servername" -w 20 -Y 20 -h -1 NDB02 (1 行処理されました) >sqlcmd -S AG01Lsnr -d AGTEST01 -E -K ReadOnly -Q "select @@servername" -w 20 -Y 20 -h -1 NDB02(1 行処理されました) >sqlcmd -S AG01Lsnr -d AGTEST01 -E -K ReadOnly -Q "select @@servername" -w 20 -Y 20 -h -1 NDB02(1 行処理されました)>sqlcmd -S AG01Lsnr -d AGTEST01 -E -K ReadOnly -Q "select @@servername" -w 20 -Y 20 -h -1 NDB02(1 行処理されました)

ここで”NDB02”を停止すると、優先度”1”のレプリカがいなくなるため、優先度”2”のレプリカにアクセスすることになりますが、優先度”2”は”NDB03”,”NDB04”の二台であるためラウンドロビンでアクセスしています。

 >sqlcmd -S AG01Lsnr -d AGTEST01 -E -K ReadOnly -Q "select @@servername" -w 20 -Y 20 -h -1 NDB03 (1 行処理されました) >sqlcmd -S AG01Lsnr -d AGTEST01 -E -K ReadOnly -Q "select @@servername" -w 20 -Y 20 -h -1 NDB04(1 行処理されました) >sqlcmd -S AG01Lsnr -d AGTEST01 -E -K ReadOnly -Q "select @@servername" -w 20 -Y 20 -h -1 NDB03(1 行処理されました)>sqlcmd -S AG01Lsnr -d AGTEST01 -E -K ReadOnly -Q "select @@servername" -w 20 -Y 20 -h -1 NDB04(1 行処理されました)

 

以上にて冒頭で述べました以下仕様通りに動作することが確認できました。

  1. READ_ONLY_ROUTING_LISTに記述する順番で優先度付けされる
  2. その時点で最も優先度の高いレプリカにアクセスする
  3. READ_ONLY_ROUTING_LISTに入れ子のかっこでくくった部分は優先度は同じになる
  4. 優先度の同じレプリカに対してはラウンドロビンでアクセスする

 

読み取り専用セカンダリレプリカ、更にロードバランスを設計・設定する際のご参考にしていただければと思います。