如何以编程方式将sql数据库添加到弹性池? [英] How to add a sql database to an elastic pool programmatically?
问题描述
我有以下控制台应用程序，该应用程序创建一个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);
}
}
}
在实现数据库创建的代码中:DbUtils.CreateDatabaseIfNotExists()－您可能正在使用T-SQL CREATE DATABASE命令在逻辑上创建Azure数据库服务器.当前CREATE DATABASE不支持指定池-但是，预计下个月将对Azure DB进行更新，这将扩展CREATE DATABASE和ALTER DATABASE的功能以也指定池名称.
同时，您可以使用Update SQL Database命令通过 the sharding.css
In the code implementing database creation: DbUtils.CreateDatabaseIfNotExists() -- you are probably using a T-SQL CREATE DATABASE command to Create an Azure database on a logical server. Currently CREATE DATABASE doesn't support specifying the Pool -- however an update to Azure DB is expected within the next month that will extend the functionality of CREATE DATABASE and ALTER DATABASE to specify the Pool name as well. In the meantime, you can make a REST API call from the CreateDatabaseIfNotExists() routine to add the database to the pool once it is created, using the Update SQL Database command: https://msdn.microsoft.com/en-us/library/azure/mt163677.aspx. However, making a rest call from inside your c# can be complex, and is discussed here: http://www.asp.net/web-api/overview/advanced/calling-a-web-api-from-a-net-client . It may be simpler to wait for the upcoming support in the CREATE DATABASE command, which would require a very small modification within your CreateDatabaseIfNotExists() routine. 这篇关于如何以编程方式将sql数据库添加到弹性池?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！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);
}
}
}
