如何以编程方式将sql数据库添加到弹性池

Question

我有以下控制台应用程序，它创建一个ShardManagerDB，并在主数据库上为每个公司创建一个数据库。我可以在azure上看到在服务器上创建的数据库，但是它们不在弹性池上。

问题:1. 这是可行的与当前的API?2. 如果没有，还有其他推荐的方法吗?

using System.Data.SqlClient;
using mynm.Data;
using System.Linq;
using mynm.Models.GlobalAdmin;
namespace mynm.DbManagementTool
{
    class Program
    {
        static void Main(string[] args)
        {
            SetupSSM();
        }
        //This will create the Shard Management DB if it doesnt exist
        private static void SetupSSM()
        {
            SqlConnectionStringBuilder connStrBldr = new SqlConnectionStringBuilder
            {
                UserID = SettingsHelper.AzureUsernamedb,
                Password = SettingsHelper.AzurePasswordDb,
                ApplicationName = SettingsHelper.AzureApplicationName,
                DataSource = SettingsHelper.AzureSqlServer
            };
           DbUtils.CreateDatabaseIfNotExists(connStrBldr.ConnectionString, SettingsHelper.Azureshardmapmgrdb);
           Sharding sharding = new Sharding(SettingsHelper.AzureSqlServer, SettingsHelper.Azureshardmapmgrdb, connStrBldr.ConnectionString);
           CreateShardPerCompany(sharding);
        }
        private static void CreateShardPerCompany(Sharding sharding)
        {
            SqlConnectionStringBuilder connStrBldr = new SqlConnectionStringBuilder
            {
                UserID = SettingsHelper.AzureUsernamedb,
                Password = SettingsHelper.AzurePasswordDb,
                ApplicationName = SettingsHelper.AzureApplicationName,
                DataSource = SettingsHelper.AzureSqlServer
            };
            UnitOfWork unitOfWork = new UnitOfWork();
            ConfigurationDBDataContext context = new ConfigurationDBDataContext();
            context.Empresas.Add(new Empresa()
            {
                Id = 1,
                Nombre = "company name 1",
                NIT = "873278423",
                NombreRepresentanteLegal = "myself",
                TelefonoRepresentanteLegal = "32894823",
                NombreContacto = "myself",
                TelefonoContacto = "32423"
            });
            context.SaveChanges();
            var listofEmpresas = unitOfWork.EmpresaRepository.Get().ToList();
            foreach(Empresa empresa in listofEmpresas)
            {
                DbUtils.CreateDatabaseIfNotExists(connStrBldr.ConnectionString, empresa.NIT);
                sharding.RegisterNewShard(SettingsHelper.AzureSqlServer, empresa.NIT, connStrBldr.ConnectionString, empresa.Id);
            }
        }
    }
}

sharding.css

internal class Sharding

  {
        public ShardMapManager ShardMapManager { get; private set; }
        public ListShardMap<int> ShardMap { get; private set; }
        // Bootstrap Elastic Scale by creating a new shard map manager and a shard map on 
        // the shard map manager database if necessary.
        public Sharding(string smmserver, string smmdatabase, string smmconnstr)
        {
            // Connection string with administrative credentials for the root database
            SqlConnectionStringBuilder connStrBldr = new SqlConnectionStringBuilder(smmconnstr);
            connStrBldr.DataSource = smmserver;
            connStrBldr.InitialCatalog = smmdatabase;
            // Deploy shard map manager.
            ShardMapManager smm;
            if (!ShardMapManagerFactory.TryGetSqlShardMapManager(connStrBldr.ConnectionString, ShardMapManagerLoadPolicy.Lazy, out smm))
            {
                this.ShardMapManager = ShardMapManagerFactory.CreateSqlShardMapManager(connStrBldr.ConnectionString);
            }
            else
            {
                this.ShardMapManager = smm;
            }
            ListShardMap<int> sm;
            if (!ShardMapManager.TryGetListShardMap<int>("ElasticScaleWithEF", out sm))
            {
                this.ShardMap = ShardMapManager.CreateListShardMap<int>("ElasticScaleWithEF");
            }
            else
            {
                this.ShardMap = sm;
            }
        }
        // Enter a new shard - i.e. an empty database - to the shard map, allocate a first tenant to it 
        // and kick off EF intialization of the database to deploy schema
        // public void RegisterNewShard(string server, string database, string user, string pwd, string appname, int key)
        public void RegisterNewShard(string server, string database, string connstr, int key)
        {
            Shard shard;
            ShardLocation shardLocation = new ShardLocation(server, database);
            if (!this.ShardMap.TryGetShard(shardLocation, out shard))
            {
                shard = this.ShardMap.CreateShard(shardLocation);
            }
            SqlConnectionStringBuilder connStrBldr = new SqlConnectionStringBuilder(connstr);
            connStrBldr.DataSource = server;
            connStrBldr.InitialCatalog = database;
            // Go into a DbContext to trigger migrations and schema deployment for the new shard.
            // This requires an un-opened connection.
            using (var db = new ElasticScaleContext<int>(connStrBldr.ConnectionString))
            {
                // Run a query to engage EF migrations
                (from b in db.Terceros
                 select b).Count();
            }
            // Register the mapping of the tenant to the shard in the shard map.
            // After this step, DDR on the shard map can be used
            PointMapping<int> mapping;
            if (!this.ShardMap.TryGetMappingForKey(key, out mapping))
            {
                this.ShardMap.CreatePointMapping(key, shard);
            }
        }
    }

Answer 1

在实现数据库创建的代码中:DbUtils.CreateDatabaseIfNotExists()——您可能正在使用T-SQL CREATE database命令在逻辑服务器上创建Azure数据库。目前CREATE DATABASE不支持指定池，但是Azure DB预计将在下个月更新，扩展CREATE DATABASE和ALTER DATABASE的功能，也可以指定池名。

同时，您可以从CreateDatabaseIfNotExists()例程调用REST API，以便在创建数据库后将其添加到池中，使用Update SQL database命令:https://msdn.microsoft.com/en-us/library/azure/mt163677.aspx.

然而，在c#内部进行rest调用可能会很复杂，在这里讨论:http://www.asp.net/web-api/overview/advanced/calling-a-web-api-from-a-net-client。在CREATE DATABASE命令中等待即将到来的支持可能更简单，这需要在CreateDatabaseIfNotExists()例程中进行非常小的修改。