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

ホーム＞技術情報＞ SQL Server 2014 自習書 No.5 Microsoft Azure SQL Database 入門

SQL Server 2014 自習書シリーズ（HTML 版）
「No.5 Microsoft Azure SQL Database 入門」

松本美穂と松本崇博が執筆した SQL Server 2014 自習書シリーズの「No.5 Microsoft Azure SQL Database 入門」の HTML 版です。日本マイクロソフトさんの Web サイトで Word または PDF 形式でダウンロードできますが、今回、HTML 版として公開する許可をいただきましたので、ここに掲載いたします。[2015年12月29日]

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

5.2　Elastic Scale Client Library の利用

（2015年1月時点での情報）

ここでは、NuGet で提供されている「Elastic Scale Client Library」を利用して、どのように Azure SQL Database のシャーディング（データベース分割）を実装するのかを説明します。このライブラリを利用すれば、シャーディングに対して、次のようにデータを INSERT することができます（C# の場合の例）。

    string cnstr = "Server=SQLサーバー名.database.windows.net;"
             + "Database=データベース名;"
             + "User ID=管理者アカウント名;Password=パスワード;Encrypt=true;";
    ShardMapManager smm = ShardMapManagerFactory.GetSqlShardMapManager(
                                cnstr,
                                ShardMapManagerLoadPolicy.Lazy);
    RangeShardMap<long> sm = smm.GetRangeShardMap<long>(mapName);
    int data_colA = 133;         // colA 列に格納するデータ
    string data_colB = "AAAA";   // colB 列に格納するデータ
    // SqlConnection を OpenConnectionForKey で作成
    string cred = "User ID=管理者アカウント名;Password=パスワード;Encrypt=true;";
    using (SqlConnection conn = sm.OpenConnectionForKey(
                                     data_colA,
                                     cred, ConnectionOptions.Validate))
    {
        // SqlCommand で INSERT を実行
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = @"INSERT INTO t1 VALUES(@a, @b)";
        SqlParameter p1 = cmd.Parameters.Add("@a", SqlDbType.Int);
        SqlParameter p2 = cmd.Parameters.Add("@b", SqlDbType.VarChar, 100);
        p1.Value = data_colA;
        p2.Value = data_colB;
        cmd.ExecuteNonQuery();
    }

ADO.NET でのプログラミングと同じように、SqlConnection と SqlCommand オブジェクトを利用して、データを INSERT することができます。通常との違いは、SqlConnection オブジェクトを作成するときに、RangeShardMap オブジェクトの OpenConnectionForKey メソッドを利用しているところです。これは、次の図のような構成になります。

00409

この例では、OpenConnectionForKey メソッドの第１引数に data_colA（colA 列に格納するデータ 133 を保持している変数）を与えていますが、この値（Key 値）によって、どのシャードに格納すべきかを認識できるようになっている、というライブラリになっています。

SqlCommand オブジェクトの利用方法は、通常の ADO.NET と同じです。このように、Elastic Scale Client Library を利用すれば、アプリケーションからは、どのデータベース（シャード）を利用しているかは意識する必要がなくなります。

◆ Let's Try

それでは、Elastic Scale Client Library を試してみましょう。ここでは、次のような C# の Windows フォームアプリケーションを作成する場合を例に説明します。

00410

Button1 をクリックすることでシャーディングを構成して、Button2 でデータの追加（INSERT）、Button3 でデータの参照（SELECT）を行うようにします。

シャーディングは、次の図のように構成します。

00411

データベースを３つ作成して、シャードの管理情報を格納するための「estest」、1～100 のデータを格納するためのシャード「estest_1」、101～200 のデータを格納するためのシャード「estest_2」を作成します。テーブル名は「t1」として、「colA」と「colB」の 2つの列で構成するようにします。

1．まずは、Azure SQL Database の SQL サーバーに接続して、データベースを３つ作成します（master データベースに接続して、CREATE DATABASE ステートメントを実行します）。

CREATE DATABASE estest COLLATE Japanese_CI_AS
( EDITION = 'BASIC' )
CREATE DATABASE estest_1 COLLATE Japanese_CI_AS
( EDITION = 'BASIC' )
CREATE DATABASE estest_2 COLLATE Japanese_CI_AS
( EDITION = 'BASIC' )

00412

このように作成したデータベースは、課金対象になるので、テストが完了したら、削除し忘れないように注意してください。

2．次に、２つのシャード（estest_1 と estest_2 データベース）の中に、まったく同じ構成の「t1」テーブルを次のように作成します。

CREATE TABLE t1
( colA int NOT NULL PRIMARY KEY,
colB varchar(100) NULL )

00413

テーブル名は「t1」として、「colA」と「colB」の 2つの列で構成するようにします。colA は int、colB は varchar(100) データ型で定義して、colA に格納される値に応じて、シャードが分かれるという形にしていきます。

◆ シャーディングの構成～Button1～

次に、Button1 をクリックしたときに、シャーディングを構成するように Elastic Scale Client Library を利用して、コードを記述します。シャーディングの構成では、シャードをいくつ作成するのか、各シャードが保持する値の範囲などを設定します。これには、ShardMapManager や RangeShardMap、RangeMapping、Shard オブジェクトなどを利用しますが、おおまかな関係は、次のとおりです。

00414

CreateSqlShardMapManage メソッドで、シャーディングを管理するための ShardMapManager オブジェクトを作成して、CreateRangeShardMap メソッドで RangeShardMap オブジェクトを作成します。これは値の範囲（1～100 や 101～200 のような範囲）でシャードマップを作成するためのオブジェクトで、特定の値（1 や 2、3 などの値）に応じてシャードを分割することもでき、この場合は ListShardMap オブジェクトを利用します。

それでは、具体的なコードを記述してみましょう。

1．まずは、Visual Studio 2013 を起動して、新しいプロジェクトを作成します。

00415

［テンプレート］で［Visual C#］の［Windows フォームアプリケーション］を選択して、新しいプロジェクトを作成します。

2．次に、Windows フォーム上に［Button］を 3つ配置します。

00416

3．次に、NuGet から Elastic Scale Client Library を取得するために、［ツール］メニューの［Nuget パッケージマネージャー］の［ソリューションの NuGet パッケージの管理］をクリックします。

00417

［NuGet パッケージの管理］ダイアログでは、［オンライン］を選択して、［検索］ボックスに「Elastic Scale」と入力して、Enter キーを押下します。これにより、検索結果に「Azure SQL Database Elastic Scale Client Library」が表示されるので、［インストール］ボタンをクリックして、インストールします。

4．インストールが完了したら、Button1 をダブルクリックして、Click イベントハンドラーを作成し、ここにコードを記述していきます。まずは、ShardMapManager オブジェクトなどを操作するために、次のように using を利用して Elastic Scale Client ライブラリの名前空間を宣言します。

using Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement;

00418

5．次に、Azure SQL Database 上のデータベース「estest」に接続するための接続文字列を private 変数として定義します。また、これから作成するシャードマップの名前も「maptest1」という値で変数として定義しておきます。

private string cnstr = "Server=SQLサーバー名.database.windows.net;"
+ "Database=estest;"
+ "User ID=管理者アカウント名;Password=パスワード;Encrypt=true;";
private string mapName = "maptest1";

00419

接続文字列の記述方法は、通常の ADO.NET と同様です。

6．次に、Button1 の Click イベントハンドラーの中に、ShardMapManager オブジェクトを作成するためのコードを、次のように記述します。

  // 既存の ShardMapManager の存在チェック。TryGetSqlShardMapManager
  ShardMapManager smm;
  bool shardMapManagerExists = ShardMapManagerFactory.TryGetSqlShardMapManager(
                            cnstr,
                            ShardMapManagerLoadPolicy.Lazy,
                            out smm);
    if (shardMapManagerExists)
    {
        // ShardMapManager が既に存在している場合
        Console.WriteLine("Shard Map Manager already exists");
    }
    else
    {
        // ShardMapManager の作成。CreateSqlShardMapManager
        ShardMapManagerFactory.CreateSqlShardMapManager(cnstr);
        Console.WriteLine("Created SqlShardMapManager");
        smm = ShardMapManagerFactory.GetSqlShardMapManager(
            cnstr,
            ShardMapManagerLoadPolicy.Lazy);
    }

ShardMapManagerFactory クラスの TryGetSqlShardMapManager メソッドで、cnstr（接続文字列）を与えて、データベース内に ShardMapManager が存在しているかどうかをチェックして、存在していない場合は、 CreateSqlShardMapManage メソッドで、ShardMapManager を作成するようにしています。作成した ShardMapManager は、GetSqlShardMapManage メソッドを利用して、smm というオブジェクト変数に格納しています。

7．次に、値の範囲でシャードマップを作成するために、続けて、次のようにコードを記述します。

    // RangeShardMap（範囲タイプのシャードマップ）
    RangeShardMap<long> sm = null;
    if (!smm.TryGetRangeShardMap(mapName, out sm))
    {
        // シャードマップの作成。CreateRangeShardMap
        sm = smm.CreateRangeShardMap<long>(mapName);
    }
    // シャードの作成。CreateShard。SQL サーバー名とデータベース名を指定する
    string srvName = "SQLサーバー名.database.windows.net";
    Shard shard1 = null, shard2 = null;
    if (!sm.TryGetShard(new ShardLocation(srvName, "estest_1"), out shard1))
    {
        shard1 = sm.CreateShard(new ShardLocation(srvName, "estest_1"));
    }
    if (!sm.TryGetShard(new ShardLocation(srvName, "estest_2"), out shard2))
    {
        shard2 = sm.CreateShard(new ShardLocation(srvName, "estest_2"));
    }
    // 範囲マッピングの作成。CreateRangeMapping。範囲の設定（1～100 と 101～200）
    RangeMapping<long> rmap = null;
    // shard1 は 1～100 の範囲
    if (!sm.TryGetMappingForKey(1, out rmap))
    {
        sm.CreateRangeMapping(new RangeMappingCreationInfo<long>
            (new Range<long>(1, 100), shard1, MappingStatus.Online));
    }
    // shard2 は 101～200 の範囲
    if (!sm.TryGetMappingForKey(101, out rmap))
    {
        sm.CreateRangeMapping(new RangeMappingCreationInfo<long>
            (new Range<long>(101, 200), shard2, MappingStatus.Online));
    }

00420

CreateRangeShardMap メソッドで RangeShardMap オブジェクトを作成します。これは値の範囲（1～100 や 101～200 のような範囲）でシャードマップを作成するためのオブジェクトで、特定の値（1 や 2、3 などの値）に応じてシャードを分割することもでき、この場合は ListShardMap オブジェクトを利用します。

RangeShardMap オブジェクトでは、CreateRangeMapping メソッドで RangeMapping オブジェクトを作成して、値の範囲を定義し、CreateShard メソッドで Shard オブジェクト（シャード）を作成して、どの範囲のどのデータベースを割り当てるのかに利用します。このコードでは、shard1 に estest_1 データベースを割り当てて、1～100 の範囲マッピング、shard2 に estest_2 データベースを割り当てて、101～200 の範囲マッピングを設定しています。

8．コードの記述が完了したら、ツールバーの［開始］ボタンをクリックしてデバッグを開始し、［Button1］をクリックします。

00421

Button1 をクリックしてから１分ぐらい経過すると、実行が完了するので、完了後、［閉じる］ボタンをクリックして、Windows フォームを終了し、デバッグ実行を終了します。

9．実行が完了した後に、Azure SQL Database 上のデータベース（estest や estest_1 など）を参照すると、次のように ShardMapManager が自動作成されていることを確認できます。

00422

◆ データの INSERT ～Button2～

次に、Button2 がクリックされたときに、t1 テーブルにデータを追加（INSERT）するようにコードを記述していきましょう。

1．データの追加には、ADO.NET の SqlConnection と SqlCommand オブジェクトを利用するので、まずは、次のように System.Data.SqlClient 名前空間を宣言しておきます。

using System.Data.SqlClient;

次に、Windows フォーム上の Button2 をダブルクリックして、Click イベントハンドラーを作成し、次のようにコードを記述します。

    ShardMapManager smm = ShardMapManagerFactory.GetSqlShardMapManager(
                                cnstr,
                                ShardMapManagerLoadPolicy.Lazy);
    RangeShardMap<long> sm = smm.GetRangeShardMap<long>(mapName);
    // 追加するデータ
    int data_colA = 133;
    string data_colB = "AAAA";
    // SqlConnection を OpenConnectionForKey で作成
    string cred = "User ID=管理者アカウント名;Password=パスワード;Encrypt=true;";
    using (SqlConnection conn = sm.OpenConnectionForKey(
                                     data_colA,
                                     cred, ConnectionOptions.Validate))
    {
        // SqlCommand で INSERT を実行
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = @"INSERT INTO t1 VALUES(@a, @b)";
        SqlParameter p1 = cmd.Parameters.Add("@a", SqlDbType.Int);
        SqlParameter p2 = cmd.Parameters.Add("@b", SqlDbType.VarChar, 100);
        p1.Value = data_colA;
        p2.Value = data_colB;
        cmd.ExecuteNonQuery();
    }

00423

データの追加は、ADO.NET でのプログラミングと同じように、SqlConnection と SqlCommand オブジェクトを利用します。SqlConnection オブジェクトを作成するときに、RangeShardMap（シャードマップ）の OpenConnectionForKey メソッドを利用して、colA に格納するデータ（data_colA＝133）を与えるようにします。これは、次の図のような構成になります（前出の図の再掲）。

00424

OpenConnectionForKey メソッドで data_colA（133）を与えることで、この値をどのシャードに格納すべきかを、ライブラリが認識できるようになっています（ShardMapManager がシャードを管理しています）。

SqlCommand オブジェクトの利用方法は、通常の ADO.NET と同じです。

2．コードの記述が完了したら、ツールバーの［開始］ボタンをクリックしてデバッグを開始し、［Button2］をクリックします。

00425

実行が完了したら、［閉じる］ボタンをクリックして、Windows フォームを終了し、デバッグ実行を終了します。

3．次に、追加されたデータを確認するために、Azure SQL Database 上のデータベース（estest_1 と estest_2）に接続して、「t1」テーブルを参照します。

SELECT * FROM t1

00426

シャード２（estest_2 データベース）の t1 テーブルにだけデータ（133）が追加されていることを確認できます。

4．次に、コード内の data_colA 変数に与える値を 133 から 77 に変更して、デバッグを開始し、［Button2］をクリックします。

int data_colA = 77;

00427

5．実行後、追加されたデータを確認するために、「t1」テーブルを参照します。

SELECT * FROM t1

00428

今度は、シャード1（estest_1 データベース）の t1 テーブルにだけデータ（77）が追加されていることを確認できます。

このように、Elastic Scale Client ライブラリを利用すれば、データベースの分割（複数のデータベースを１つのデータベースのように見せかけること）ができるようになります。

◆ データの SELECT ～Button3～

次に、「t1」テーブルのデータを参照（SELECT）するコードを記述してみましょう。

1．データの参照では、複数のシャードからデータを取得するために、MultiShardConnection、MultiShardCommand、MultiShardDataReader というオブジェクトを利用しますが、基本的な利用方法は、ADO.NET の SqlConnection、SqlCommand、SqlDataReader と同じです。まずは、MultiShard～ を利用するために、次のように名前空間を宣言します。

using Microsoft.Azure.SqlDatabase.ElasticScale.Query;

2．次に、Windows フォーム上の Button3 をダブルクリックして、Click イベントハンドラーを作成し、次のようにコードを記述します。

   // ShardMapManager の取得
   ShardMapManager smm = ShardMapManagerFactory.GetSqlShardMapManager(
                                cnstr,
                                ShardMapManagerLoadPolicy.Lazy);
   // RangeShardMap の取得
    RangeShardMap<long> sm = smm.GetRangeShardMap<long>(mapName);
    // MultiShardConnection を利用。GetShards でシャードの一覧を取得
    string cred = "User ID=管理者アカウント名;Password=パスワード;Encrypt=true;";
    using (MultiShardConnection conn = new MultiShardConnection(
                                            sm.GetShards(),
                                            cred))
    {
       // MultiShardCommand で SELECT を実行
       using (MultiShardCommand cmd = conn.CreateCommand())
        {
        cmd.CommandText = "SELECT * FROM t1";
        cmd.CommandType = CommandType.Text;
        cmd.ExecutionOptions = MultiShardExecutionOptions.IncludeShardNameColumn;
        cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults;
            // MultiShardDataReader で結果を Read する
            using (MultiShardDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                    var data_colA = sdr.GetFieldValueint>(0);
                    var data_colB = sdr.GetString(1);
                    Console.WriteLine(data_colA);
                    Console.WriteLine(data_colB);
                }
            }
        }
    }

MultiShardConnection では、GetShards メソッドを利用することで、シャードの一覧を取得することができます。MultiShardCommand で実行したい SELECT ステートメントを定義して、MultiShardDataReader で実行結果を Read するという形になります。

3．コードの記述が完了したら、ツールバーの［開始］ボタンをクリックしてデバッグを開始し、［Button3］をクリックします。

00429