SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

Availability Group でリスナーを経由した透過的なセカンダリレプリカへの接続

2 comments

先日、Denali の自習書で AlwaysOn 編 (SQL Server "Denali" 自習書シリーズ No.2 SQL Server AlwaysOn による可用性の向上) がリリースされました。
自習書シリーズ

自習書の P.53 で Read Only Routing List について触れられていますので、今回はこの内容について少しまとめてみたいと思います。

READ_ONLY_ROUTING_LIST の設定方法が分からなくて、先日の The Microsoft Conference 2011 でスピーカーの方に質問をさせていただいて、ようやく設定方法が分かったのですが自習書には盛り込み済みでした。
毎回凄いクオリティの内容ですね~。

これらの設定の情報に関しては、以下のドキュメントに少し記載されています。
Microsoft SQL Server コードネーム "Denali" CTP3 リリース ノート (ja-JP)
SQL Server Native Client Support for High Availability, Disaster Recover

■セカンダリレプリカへの接続方法


セカンダリレプリカへの接続方法ですが二種類あります。

一つが今までのブログ投稿で書いていた直接セカンダリレプリカに接続する方法です。
image

この方法の場合は、接続するセカンダリを明示的に指定する必要があります。
そのため、SQL Server を利用するアプリケーションがどのサーバーがセカンダリかを意識する必要がありました。

Availability Group ではセカンダリに接続するための方法として、もう一種類用意されています。
それが、Read-Only Routing を使用したリスナーを経由した自動的なセカンダリへの接続になります。
image

リスナーに接続をすると、リスナーがアクティブなセカンダリを判断し、自動的にダイレクトをして接続をするという方法をとることができます。

この設定をするためには、可用性グループに [READ_ONLY_ROUTING_URL] と [REA_ONLY_ROUTING_LIST] の二種類の設定をする必要があります。

それでは実際の設定を見ていきたいと思います。

設定は、

  1. [READ_ONLY_ROUTING_URL]
  2. [REAT_ONLY_ROUTING_LIST]

の順で実施する必要があります。
# [READ_ONLY_ROUTING_URL] を設定していないと、[READ_ONLY_URL_LIST] が設定できません。

また、各設定で使用するクエリは [master] データベース上で実行する必要があります。

どちらの設定も ALTER AVAILABILITY GROUP を使用して設定しますが、現状の Books Online にはこれら二つのオプションは記載されていません。
ALTER AVAILABILITY GROUP (Transact-SQL)

Denali CTP3 のリリースノートに存在することは少し書かれていたりします。
Microsoft SQL Server コードネーム "Denali" CTP3 リリース ノート (ja-JP)

 

今回は以下の環境を例にして設定を見ていきたいと思います。
image

 

■READ_ONLY_ROUTING_URL


READ_ONLY_ROUTING_URL は以下のように設定を行います。
image

READ_ONLY_ROUTING_URL ですが、リスナーからセカンダリレプリカとしての接続要求があった場合に、どこに対して接続するかを設定するものになります。
# 通常は自分自身の情報を設定することになるかと思います。

設定は各サーバーについて、それぞれ行う必要があります。

SQL11-01 を例にすると、リスナーを経由してSQL11-01 にセカンダリの接続要求があった場合、[TCP://SQL11-01.domain.local:1433] に接続をするという設定になります。
つまり自分自身の既定のインスタンスに対して接続をするという設定ですね。

接続先に関しては必ず自分自身に設定をしないといけないということはありませんので、以下のような設定をして、すべて特定のインスタンスにリダイレクトさせるということも可能です。
image

この設定の場合、各サーバーにセカンダリの要求があった場合、[SQL11-03] の既定のインスタンスに対して接続をリダイレクトするという設定になります。

READ_ONLY_ROUTING_URL の設定のためのクエリは以下の用になります。
# クエリは master データベースで実行する必要があります。

以下のクエリを可用性グループ内の可用性サーバーに対して実行をします。

USE [master]
GO

ALTER AVAILABILITY GROUP <可用性グループ名>
MODIFY REPLICA ON N'<ノード名>’
WITH (
SECONDARY_ROLE(READ_ONLY_ROUTING_URL=N’TCP://<接続先インスタンス情報>’)
)

実際の実行例はこちらになります。
# サーバー名はFQDN で設定せずにサーバー名だけでも接続はできるようです。

USE [master]
GO

ALTER AVAILABILITY GROUP AlwaysOn_Group
MODIFY REPLICA ON N’SQL11-01′
WITH (
SECONDARY_ROLE(READ_ONLY_ROUTING_URL=N’TCP://SQL11-01.domain.local:1433′)
)

ALTER AVAILABILITY GROUP AlwaysOn_Group
MODIFY REPLICA ON N’SQL11-02′
WITH (
SECONDARY_ROLE(READ_ONLY_ROUTING_URL=N’TCP://SQL11-02.domain.local:1433′)
)

ALTER AVAILABILITY GROUP AlwaysOn_Group
MODIFY REPLICA ON N’SQL11-03′
WITH (
SECONDARY_ROLE(READ_ONLY_ROUTING_URL=N’TCP://SQL11-03.domain.local:1433′)
)

 

READ_ONLY_ROUTING_URL の設定状況は、[sys.availability_replicas] で確認をすることができます。
image

 

■READ_ONLY_ROUTING_LIST


READ_ONLY_ROUTING_URL の設定が完了したら、READ_ONLY_ROUTING_LIST を設定します。

READ_ONLY_ROUTING_LIST は以下のように設定をします。
image

この設定ですが、対象のサーバーがプライマリになっているときに、リスナーを経由してどのセカンダリに接続をするかを優先順位を意識しながら設定するものになります。
# 必ず全ノードを設定しなければいけないということはありませんので、特定のノードにはリダイレクトしないという設定も可能です。

SQL11-01 がプライマリとして稼働している場合、リスナーを経由したセカンダリへの接続は

  1. SQL11-03
  2. SQL11-02
  3. SQL11-01

の優先順位で行われます。
SQL11-03 が起動していない場合は、SQL11-02 に接続がされるという形ですね。

この設定は各サーバーがプライマリの時にリスナーを経由してどのセカンダリに接続をするという設定になりますので、各サーバーに対して設定を行う必要があります。

設定には以下のクエリを使用します。
# リストはカンマ区切りで指定ができ、優先順の順に指定をします。

USE [master]
GO

ALTER AVAILABILITY GROUP [<可用性グループ名>]
MODIFY REPLICA ONN'<ノード名>’
WITH (
PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(N'<ノード名>’ [,…])
))

実際の設定例はこちらになります。

USE [master]
GO

ALTER AVAILABILITY GROUP [AlwaysOn_Group]
MODIFY REPLICA ON N’SQL11-01′
WITH (
PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(N’SQL11-03′, N’SQL11-02′, N’SQL11-01′)
))

ALTER AVAILABILITY GROUP [AlwaysOn_Group]
MODIFY REPLICA ON N’SQL11-02′
WITH (
PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(N’SQL11-01′, N’SQL11-03′, N’SQL11-02′)
))

ALTER AVAILABILITY GROUP [AlwaysOn_Group]
MODIFY REPLICA ON N’SQL11-03′
WITH (
PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(N’SQL11-02′, N’SQL11-01′, N’SQL11-03′)
))

 

READ_ONLY_ROUTING_LIST の設定状況は、[sys.availability_read_only_routing_lists] で確認をすることができます。
image

■リスナーを経由したセカンダリーへの接続


ここまでで設定は終了しました。
それでは実際にリスナーを経由してセカンダリへ接続してみたいと思います。

READ_ONLY_ROUTING_URL / LIST を設定し、可用性グループの設定は以下の状態になっています。
image

リスナーを経由してセカンダリに接続をするためには、

  1. リスナーのコンピューター名または IP アドレスで接続
  2. 接続側は [ApplicationIntent=ReadOnly] を指定
  3. 接続先のデータベースを明示的に指定する

の条件を満たしておく必要がありそうです。
# 接続するデータベースとして可用性データベースに設定しているデータベースを明示的に指定しないと、Read Only Routing が動作しませんでした。これを思い出すのに一時間ぐらいかかり、かなりはまってしまいました…。

Denali CTP3 の SQLCMD では、[-K] を指定することで、[ApplicationIntent=ReadOnly] を指定することができますので、実際の動作を試してみたいと思います。

[-KReadOnly] (ApplicationIntent=ReadOnly) を指定しない状態では以下のようになります。
image

現在は、[2008R2-SQL11-01] がプライマリとなっていますので、リスナーからはプライマリに接続されます。
image

それではこの状態で、[-KReadOnly] を指定してみます。
image

先ほどと同様リスナーに接続をしていますが、ReadOnly を指定することで透過的にセカンダリーに接続がされています。
image

現在は SQL11-01 の READ_ONLY_ROUTING_LIST の優先順位を [SQL11-03] [SQL11-02] [SQL11-01] の順にしていますので、[SQL11-03] が停止している状態だと、[SQL11-02] に自動的に接続がされます。
image
image

 

■情報の格納場所


READ_ONLY_ROUTING_LIST / URL の設定情報ですが SQL Server のテーブルではなく、クラスターの構成情報として格納がされているようですね。

ALTER AVAILABILITY GROUP で設定をした際にどこに情報が格納されるのか気になって、トランザクションログを確認したのですが、ログレコードが出力されていませんでした。

そこで、クラスターの構成情報をレジストリから覗いていたところ、可用性グループのリソースの構成として、情報が格納されていました。
image

リスナーの実体はクラスターのクライアントアクセスポイント (CAP) になりますので、ルーティングの制御はクラスター側で実施しているのかもしれないですね。

 

■まとめ


いろいろ書いてみましたが軽くまとめてみたいと思います。

リスナーを経由した透過的なセカンダリへの接続 (Read Only Routing) を行うためには設定として、

  • 可用性グループを構成する各ノードに [READ_ONLY_ROUTING_URL] [READ_ONLY_ROUTING_LIST] を設定する必要がある。

各設定の意味ですが、

  • READ_ONLY_ROUTING_URL はセカンダリとして接続の要求があった場合に接続するインスタンスの情報
  • READ_ONLY_ROUTING_LIST はプライマリとして起動している際に、リスナーを経由して透過的なセカンダリへの接続があった場合に、接続するセカンダリの優先順位とノードの情報

リスナーを経由した透過的なセカンダリへの接続を行うためにはアプリケーションで

  • 接続はリスナーに対して行う
  • 接続をする可用性グループに含まれるデータベースを明示的に指定する
  • ApplicationIntent=ReadOnly を指定する

 

これらをすることで透過的なセカンダリへの接続が実施できそうです。

読み取りのワークロードを更新系以外のサーバーに接続先を意識しないでリダイレクトさせるときには有効そうですね。

Written by masayuki.ozawa

10月 2nd, 2011 at 5:06 pm

Posted in SQL Server

Tagged with ,

2 Responses to 'Availability Group でリスナーを経由した透過的なセカンダリレプリカへの接続'

Subscribe to comments with RSS or TrackBack to 'Availability Group でリスナーを経由した透過的なセカンダリレプリカへの接続'.

  1. こんにちは、おつも参考にさせて頂いております。

    1つご存知であれば、教えて頂きたい事がありましたコメント投稿させて頂きます。
    現在、SQL Server2012を4台使いAlwaysOn環境下を構築しており、Read Only Routingを使用しています。
    構成は可能の通りとなりますが、この環境下でプライマリノードが突然ダウンした場合にReadOnlyで接続する
    サーバーが10分間程度、接続する度に変わる現象が発生します。

    つまり、下記構成の場合、プライマリがSV1からSV2に変わり、SV2のRoutingListの指定に従いSV4に接続する動きだと思って
    いたのですが、プライマリがSV1からSV2に変わった直後から、ReadOnly先はSV4だったり、SV2に繋がったり、SV3に繋がったり
    します。プライマリがSV1からSV2に変わって10分程度経過したあたりから、ReadOnly先は接続する度に変わる事はなくなりますが、
    SV2のRoutingListの指定に従いSV4と言う訳ではなく、SV4だったり、SV2だったり、SV3だったりします。
    (プライマリに対してはレコード更新がそれなりに行われ続けています。)

    この現状に関して、経験などありますでしょうか。
    また、RoutingListで指定している各ノードが優先度の高い順に生きている場合には、この優先度を必ず守るまたは可能な限り守らせる
    設定方法などありましたら教えて頂けないでしょうか。

    構成
        ロール   可用性  フェールオーバー  プライマリロール  読み取り可能なセカンダリ  セッションタイムアウト
     SV1: プライマリ 同期   自動        すべて       読み取り目的のみ      10秒
     SV2: セカンダリ 同期   自動        すべて       読み取り目的のみ      10秒
     SV3: セカンダリ 非同期  手動        すべて       読み取り目的のみ      10秒
     SV4: セカンダリ 同期   手動        すべて       読み取り目的のみ      10秒

    Routing List
     SV1: SV4 , SV2 , SV3 , SV1
     SV2: SV4 , SV1 , SV3 , SV2
     SV3: SV4 , SV1 , SV2 , SV3
     SV4: SV1 , SV2 , SV3 , SV4

    以上、宜しくお願い致します。

    miniHORI

    19 8月 15 at 18:22

  2. あう、想定外のところで改行されてしまった・・・
    見辛い状態になってすみません。

    miniHORI

    19 8月 15 at 18:23

Leave a Reply

*