SQL Server パフォーマンスチューニング、コンサルティング、アドバイス、相談、定期診断、トレーニング

ホーム＞技術情報＞ SQL Server 2012 自習書 DWH 関連の新機能

SQL Server 2012 自習書シリーズ（HTML 版）
新機能編 No.2「AlwaysOn による可用性の向上」

松本美穂と松本崇博が執筆した SQL Server 2012 自習書シリーズの「新機能編 No.2 AlwaysOn による可用性の向上」の HTML 版です。日本マイクロソフトさんの Web サイトで Word または PDF 形式でダウンロードできますが、今回、HTML 版として公開する許可をいただきましたので、ここに掲載いたします。[2014年12月26日]

目次へ | 前のページへ | 次のページへ

2.6　アプリケーション（ADO.NET）からの接続確認

◆ アプリケーション（ADO.NET）からの接続確認

次に、VB や C# などのアプリケーションから ADO.NET を利用して、可用性グループへ接続する方法を試してみましょう。とはいっても、通常の SQL Server へ接続する場合と同様、接続文字列を次のように記述するだけで接続できます。

"Data Source=リスナー名;" _
& "Initial Catalog=データベース名;" _
& "Integrated Security=SSPI" ' Windows 認証で接続する場合

Data Source 句でリスナー名（仮想サーバー名）を指定するだけで、可用性グループへ接続することができます。また、自動フェールオーバーや手動フェールオーバーが発生しても、どのサーバーが処理しているかを意識することもなく、透過的に接続することが可能です。

◆ Let's Try

それでは、これを試してみましょう。ここでは、Visual Studio 2010 の Visual Basic 10.0 から ADO.NET 4.0 を利用して、「AGTestDB」データベースへ接続してみましょう（Visual Studio 2005 や 2008、2012 でも同様の手順で試すことができます）。

次のようにボタン（Button1）をクリックすると、SQL Server へ接続して、「t1」テーブルのデータを取得し、リストボックス（ListBox1）へ結果を表示するようにします。

00080

1．まずは、［スタート］メニューから Visual Studio 2010 を起動します。

2．起動後、［スタート］ページの［新しいプロジェクト］をクリックして、新しいプロジェクトを作成します。

00081

［新しいプロジェクト］ダイアログが表示されたら、［インストールされたテンプレート］から［Visual Basic］を選択して、「Windows フォームアプリケーション」を選択します。［名前］には、任意の名前（WindowsApplication１ など）を入力して、［OK］ボタンをクリックします。

3．次に、ツールボックスから［Button］と［ListBox］をドラッグ＆ドロップして、フォーム上へ配置します。

00082

配置後、ボタン（Button1）をダブルクリックして、コードエディターを開きます。

4．コードエディターでは、コードの先頭へ以下を記述します。

Imports System.Data.SqlClient

00083

5．次に、ボタン（Button1）の Click イベントハンドラーへ次のように記述して、「AGTestDB」データベースへ接続し、「t1」テーブルのデータを取得します（コード入力が面倒な方は、サンプルスクリプト内の完成版のファイル（WindowsApplication1 フォルダー内の ADONET_code.txt ファイル）を開いてください。

  Private Sub Button1_Click(..)
        ListBox1.Items.Clear()
        Dim cnstr As String
        cnstr = "Data Source=AG1_Listener;" _
                & "Initial Catalog=AGTestDB;" _
                & "Integrated Security=SSPI"
        Using cn As New SqlConnection(cnstr)
            Using cmd As New SqlCommand()
                cmd.Connection = cn
                Try
                  cn.Open()
                  cmd.CommandText = "SELECT @@SERVERNAME"
                  MessageBox.Show(cmd.ExecuteScalar())
                  cmd.CommandText = "SELECT * FROM t1"
                    Using dr As SqlDataReader = cmd.ExecuteReader()
                        While dr.Read
                            ListBox1.Items.Add( dr("a") )
                        End While
                    End Using
                Catch ex As Exception
                    Debug.Print(ex.Message)
                Finally
                    cn.Close()
                End Try
            End Using
        End Using
  End Sub

Data Source 句へはリスナー名（AG1_Listener）、Initial Catalog 句へはデータベース名（AGTestDB）を指定します。このコードでは、まず cmd.CommandText で指定した「SELECT @@SERVERNAME」を実行して、どのサーバーが処理したかをメッセージボックス（MessageBox.Show）で返し、次の cmd.CommandText で指定した「SELECT * FROM t1」ステートメントで取得した t1 テーブルのデータをリストボックス（ListBox1）へ表示します。

6．コードを記述後、［デバッグ］メニューから［デバッグ開始］をクリックして、実行します。

00084

ボタンをクリックすると、処理したサーバーの名前（SERVER1）がメッセージボックスで表示されて、「t1」テーブルのデータ 3件（1、2、3）がリストボックスへ表示されることを確認できます。

7．正しくデータを取得できたことを確認したら、アプリケーションを終了して、デバッグを停止します。

このように可用性グループでは、リスナー名を利用して接続することで、どのサーバーが処理したのかを意識することなく、アプリケーションを作成することができます。このアプリケーションは、フェールオーバーが実行された後（セカンダリがプライマリへ切り替わった後）でも正しく動作します。次の Step では、手動フェールオーバーを試すので、この動作を確認することができます。

Note：読み取り可能セカンダリへの自動ルーティング（ApplicationIntent=ReadOnly）
SQL Server Native Client 11.0（SQL Server 2012 で提供される SQL Server Native Client）からは、リスナー名を指定したときに、読み取り可能セカンダリへ自動的に転送（ルーティング）してくれる機能が提供されています（読み取り操作に関しても、どのセカンダリが処理するかを意識することなく、透過的にアクセスすることが可能です）。これは、接続文字列を次のように記述します。

"Data Source=リスナー名;" _
   & "Initial Catalog=データベース名;" _
   & "Integrated Security=SSPI;" _
   & "ApplicationIntent=ReadOnly;"

このように Data Source 句にはリスナー名を指定し（セカンダリのサーバー名を指定するのではなく、リスナー名を指定できます）、ApplicationIntent=ReadOnly（読み取り専用指定）を付けることで、可用性グループ内の読み取り可能セカンダリへ自動的に接続しにいってくれます（自動ルーティングしてくれます）。
実際の実行結果は、次のとおりです。
00085

現在のプライマリは SERVER1 ですが、ApplicationIntent=ReadOnly を付けたことで、セカンダリ（SERVER2）へアクセスしにいってくれることを確認できます。

■ ルーティングの設定（READ_ONLY_ROUTING_URL、READ_ONLY_ROUTING_LIST）
ApplicationIntent=ReadOnly を利用してセカンダリへ自動ルーティングするには、可用性グループ側の設定も必要になります。これは、ALTER AVAILABILITY GROUP ステートメントを利用して、次のように READ_ONLY_ROUTING_URL と READ_ONLY_ROUTING_LIST を設定します。

USE master
-- セカンダリに対して  READ_ONLY_ROUTING_URL を設定
ALTER AVAILABILITY GROUP AG1
MODIFY REPLICA ON 'SERVER2'
  WITH
  (    SECONDARY_ROLE
    (    READ_ONLY_ROUTING_URL = 'TCP://SERVER2:1433' ) )
go
ALTER AVAILABILITY GROUP AG1
MODIFY REPLICA ON 'SERVER3'
  WITH
  (    SECONDARY_ROLE
    (    READ_ONLY_ROUTING_URL = 'TCP://SERVER3:1433' ) )
go
-- プライマリに対して  READ_ONLY_ROUTING_LIST を設定
ALTER AVAILABILITY GROUP AG1
MODIFY REPLICA ON 'SERVER1'
  WITH
  (    PRIMARY_ROLE
    (    READ_ONLY_ROUTING_LIST = ('SERVER2', 'SERVER3') ) )
go

セカンダリ（SERVER2 と SERVER3）に対しては、READ_ONLY_ROUTING_URL を自分自身への URL パスへ設定し（SERVER2 なら TCP://SERVER2:1433）、プライマリ（SERVER1）へは READ_ONLY_ROUTING_LIST（読み取り専用ルーティングリスト）へ自動ルーティングさせたいセカンダリ名をカンマ区切りで指定（ここでは SERVER2 と SERVER3 を指定）します。このように設定することで、ApplicationIntent=ReadOnly（読み取り専用）が設定されたアプリケーションが、ルーティングリストに登録されているセカンダリへアクセスしにいくようになります。
このように、可用性グループでは、読み取り操作に関しても、アプリケーションから透過的に（どのサーバーがセカンダリであるかを意識することなく）アクセスすることが可能です。

Note：読み取り可能セカンダリでの遅延
読み取り可能セカンダリでは、データベース複製時のロック待ちを防ぐために（複製によるデータ更新時のロックによって、読み取り操作がブロックされないようにするために）、内部的にスナップショット機能を利用して、セカンダリへの読み取り操作を実現しています。このため、読み取りを行ったときのデータは、遅延が発生する（完全な最新データではない）可能性もあります。また、スナップショットによるオーバーヘッドも数%発生します。

目次へ | 前のページへ | 次のページへ