Azure SQL Database Elastic Scale をやっと触ってみる

サンプルはこの手順通りで動きました。
Azure SQL Database Elastic Scale の概要

10/30(木) 三木会で SQL Database のお話しをさせていただきました - おだのスペース
で上がってた質問に回答するためコードを少しいじってみました。

サンプルの 4:Execute sample Multi-Shard Query のクエリを変えてうごかします。

internal static class MultiShardQuerySample
{
  public static void ExecuteMultiShardQuery(RangeShardMap<int> shardMap, string credentialsConnectionString)
  {
    // Get the shards to connect to
    IEnumerable<Shard> shards = shardMap.GetShards();
    // Create the multi-shard connection
    using (MultiShardConnection conn = new MultiShardConnection(shards, credentialsConnectionString))
    {
      // Create a simple command
      using (MultiShardCommand cmd = conn.CreateCommand())
      {
        // Because this query is grouped by CustomerID, which is sharded,
        // we will not get duplicate rows.
/*
        cmd.CommandText = @"
          SELECT 
            c.CustomerId, 
            c.Name AS CustomerName, 
            COUNT(o.OrderID) AS OrderCount
          FROM 
            dbo.Customers AS c INNER JOIN 
            dbo.Orders AS o
            ON c.CustomerID = o.CustomerID
          GROUP BY 
            c.CustomerId, 
            c.Name
          ORDER BY 
            OrderCount";
*/
        cmd.CommandText = "select count(*) from Customers";
...

実行結果はこんな感じ

Enter an option [1-6] and press ENTER: 4
     $ShardName
     ----------
 12  ElasticScaleStarterKit_Shard1
 6   ElasticScaleStarterKit_Shard0
 14  ElasticScaleStarterKit_Shard2

(3 rows returned)

基本 Multi Shared Query は基本それぞれの DB に対してクエリ投げてそれを一元的に受け取れるだけのようです。

公式のドキュメントがあったので貼っときます。
Azure SQL Database Elastic Scale *1
Multi-Shared Queries(MSQ) に、「各シャードに同じクエリ投げて UNION ALL 風味で返します」ってな感じの事が書かれています。

クエリの制約は特に無さそう(SQL Database が対応していれば)ですが、Multi Shared Query の場合は、集計関数/ソートを使う時にプログラム側でも考慮が必要ですね。

で、ここからが本題。
シャードの管理とかデータの分割とかややこしい事は置いといて、SQL Database 以外でも使えるのかというのを試してみました。
今回の構成は

Shared Map Manager(ElasticScaleStarterKit_ShardMapManagerDb)
localhost\MSSQL2014
Shared0(ElasticScaleStarterKit_Shard0)
localhost\MSSQL2012
Shared1(ElasticScaleStarterKit_Shard1)
localhost\MSSQL2014Express
Shared2(ElasticScaleStarterKit_Shard2)
localhost\MSSQL2012Express

という4台構成にしました。

このサンプル良く出来ていて殆ど変更を加えることなく実現出来ます。
変更する箇所は、 App.config の IntegratedSecurity を true にします。

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <connectionStrings>
  </connectionStrings>
  <appSettings>
    <!-- 
      The server name of the database to connect to. Do not include the https:// prefix of the Azure SQL DB server URL.
      Typically, the server name looks like this: abcdefghij.database.windows.net
      
      Edit this field before running the application.
    -->
    <add key="ServerName" value="localhost\MSSQL2014" />
    <!-- 
      Credentials for connecting to your Azure SQL DB server.
      
      Edit these fields before running the application.
    -->
    <add key="UserName" value="" />
    <add key="Password" value="" />
    
    <!--
      The database edition to use when creating databases for this sample in Azure SQL DB.
    -->
    <add key="DatabaseEdition" value="Basic" />
    <!--
      Set to true to enable Windows Authentication instead of SQL Authentication.
      This is useful only if connecting to a local SQL Server database instead of Azure SQL DB.
    -->
    <add key="IntegratedSecurity" value="true" />
  </appSettings>
  <system.diagnostics>
    <trace>
      <listeners>
        <add type="System.Diagnostics.TextWriterTraceListener" name="TextWriter" initializeData="trace.log" />
      </listeners>
    </trace>
  </system.diagnostics>
</configuration>

CreateShardSample.cs に GetServerName を追加、CreateOrGetEmptyShard を変更します。

private static readonly string[] SERVER_NAMES = new String[] { 
  @"localhost\MSSQL2012"
  , @"localhost\MSSQL2014Express"
  , @"localhost\MSSQL2012Express" };
private static string GetServerName(RangeShardMap<int> shardMap) {
  var cnt = shardMap.GetShards().Count();
  return SERVER_NAMES[cnt % 3];
}
/// <summary>
/// Creates a new shard, or gets an existing empty shard (i.e. a shard that has no mappings).
/// The reason why an empty shard might exist is that it was created and initialized but we 
/// failed to create a mapping to it.
/// </summary>
private static Shard CreateOrGetEmptyShard(RangeShardMap<int> shardMap)
{
  // Get an empty shard if one already exists, otherwise create a new one
  Shard shard = FindEmptyShard(shardMap);
  if (shard == null)
  {
    // No empty shard exists, so create one
    // Choose the shard name
    string databaseName = string.Format(ShardNameFormat, shardMap.GetShards().Count());
    string serverName = GetServerName(shardMap);
    // Only create the database if it doesn't already exist. It might already exist if
    // we tried to create it previously but hit a transient fault.
    if (!SqlDatabaseUtils.DatabaseExists(serverName, databaseName))
    {
      SqlDatabaseUtils.CreateDatabase(serverName, databaseName);
    }
    // Create schema and populate reference data on that database
    // The initialize script must be idempotent, in case it was already run on this database
    // and we failed to add it to the shard map previously
    SqlDatabaseUtils.ExecuteSqlScript(
      serverName, databaseName, InitializeShardScriptFile);
    // Add it to the shard map
    ShardLocation shardLocation = new ShardLocation(serverName, databaseName);
    shard = ShardManagementUtils.CreateOrGetShard(shardMap, shardLocation);
  }
  return shard;
}

ここでシャード毎の接続サーバーを切り替えます。
これは、シャードを作成する際に走るとこで、Shard Map Manager の [__ShardManagement].[ShardsGlobal] テーブルにサーバー情報が入ります。

最後にサーバー名とDB名を確認するために MultiSharedQuerySample.cs の ExtractDatabaseName を変更します。

private static string ExtractDatabaseName(string shardLocationString)
{
  return shardLocationString;
}

でビルド実行し以下の手順で動かします。

Create shard map manager, and add a couple shareds
Add another shard
Insert sample rows using Data-Dependent Routing
何回か 3 を繰り返してデータ作成
Execute sample Multi-Shard Query

これの結果が、

Enter an option [1-6] and press ENTER: 4
 CustomerId  CustomerName           OrderCount  $ShardName
 ----------  ------------           ----------  ----------
 101         Northwind Traders      1           [DataSource=localhost\MSSQL2014E
xpress Database=ElasticScaleStarterKit_Shard1]
 114         Lucerne Publishing     1           [DataSource=localhost\MSSQL2014E
xpress Database=ElasticScaleStarterKit_Shard1]
 127         Contoso Ltd.           1           [DataSource=localhost\MSSQL2014E
xpress Database=ElasticScaleStarterKit_Shard1]
 130         Humongous Insurance    1           [DataSource=localhost\MSSQL2014E
xpress Database=ElasticScaleStarterKit_Shard1]
 152         Lucerne Publishing     1           [DataSource=localhost\MSSQL2014E
xpress Database=ElasticScaleStarterKit_Shard1]
 159         Coho Winery            1           [DataSource=localhost\MSSQL2014E
xpress Database=ElasticScaleStarterKit_Shard1]
 167         Coho Winery            1           [DataSource=localhost\MSSQL2014E
xpress Database=ElasticScaleStarterKit_Shard1]
 187         Humongous Insurance    1           [DataSource=localhost\MSSQL2014E
xpress Database=ElasticScaleStarterKit_Shard1]
 197         ProseWare, Inc.        1           [DataSource=localhost\MSSQL2014E
xpress Database=ElasticScaleStarterKit_Shard1]
 165         ProseWare, Inc.        2           [DataSource=localhost\MSSQL2014E
xpress Database=ElasticScaleStarterKit_Shard1]
 147         Contoso Ltd.           2           [DataSource=localhost\MSSQL2014E
xpress Database=ElasticScaleStarterKit_Shard1]
 129         ProseWare, Inc.        2           [DataSource=localhost\MSSQL2014E
xpress Database=ElasticScaleStarterKit_Shard1]
 47          ProseWare, Inc.        1           [DataSource=localhost\MSSQL2012
Database=ElasticScaleStarterKit_Shard0]
 48          Alpine Ski House       1           [DataSource=localhost\MSSQL2012
Database=ElasticScaleStarterKit_Shard0]
 61          Microsoft Corp.        1           [DataSource=localhost\MSSQL2012
Database=ElasticScaleStarterKit_Shard0]
 79          AdventureWorks Cycles  1           [DataSource=localhost\MSSQL2012
Database=ElasticScaleStarterKit_Shard0]
 15          AdventureWorks Cycles  1           [DataSource=localhost\MSSQL2012
Database=ElasticScaleStarterKit_Shard0]
 40          Alpine Ski House       2           [DataSource=localhost\MSSQL2012
Database=ElasticScaleStarterKit_Shard0]
 200         Lucerne Publishing     1           [DataSource=localhost\MSSQL2012E
xpress Database=ElasticScaleStarterKit_Shard2]
 214         AdventureWorks Cycles  1           [DataSource=localhost\MSSQL2012E
xpress Database=ElasticScaleStarterKit_Shard2]
 224         Lucerne Publishing     1           [DataSource=localhost\MSSQL2012E
xpress Database=ElasticScaleStarterKit_Shard2]
 226         Alpine Ski House       1           [DataSource=localhost\MSSQL2012E
xpress Database=ElasticScaleStarterKit_Shard2]
 227         Microsoft Corp.        1           [DataSource=localhost\MSSQL2012E
xpress Database=ElasticScaleStarterKit_Shard2]
 229         Microsoft Corp.        1           [DataSource=localhost\MSSQL2012E
xpress Database=ElasticScaleStarterKit_Shard2]
 232         ProseWare, Inc.        1           [DataSource=localhost\MSSQL2012E
xpress Database=ElasticScaleStarterKit_Shard2]
 247         Northwind Traders      1           [DataSource=localhost\MSSQL2012E
xpress Database=ElasticScaleStarterKit_Shard2]
 264         Microsoft Corp.        1           [DataSource=localhost\MSSQL2012E
xpress Database=ElasticScaleStarterKit_Shard2]
 267         Lucerne Publishing     1           [DataSource=localhost\MSSQL2012E
xpress Database=ElasticScaleStarterKit_Shard2]
 276         Northwind Traders      1           [DataSource=localhost\MSSQL2012E
xpress Database=ElasticScaleStarterKit_Shard2]
 278         ProseWare, Inc.        1           [DataSource=localhost\MSSQL2012E
xpress Database=ElasticScaleStarterKit_Shard2]
 285         Contoso Ltd.           1           [DataSource=localhost\MSSQL2012E
xpress Database=ElasticScaleStarterKit_Shard2]
 274         Coho Winery            2           [DataSource=localhost\MSSQL2012E
xpress Database=ElasticScaleStarterKit_Shard2]

(32 rows returned)
Current Shard Map state:
        ElasticScaleStarterKit_Shard0 contains key range [0:100)
        ElasticScaleStarterKit_Shard1 contains key range [100:200)
        ElasticScaleStarterKit_Shard2 contains key range [200:300)

とこんな感じになりました。

SqlDatabaseUtils.cs で使ってる Connection や Command を Oracle とかに差し替えたら Oracle でもいけそうな気がします。

*1:ja-jp にすると日本語になりますが、訳が微妙な気もする