Azure SQL Server Elastic Pool - automatically add database to pool

Question

Currently we have an Azure SQL Server and each time we create new web app, we use EF Core Code First to generate the database. However after the database is created we manually go to Azure portal and add the newly created database to the Elastic Pool inside the Azure SQL Server. Is it possible to somehow automate the process so that each newly created database either via portal or generated using EF or whatever, it will be automatically added to the Pool?

Answer 1

You can use Transact-SQL to programmatically move an existing Azure SQL Database into an elastic pool.

ALTER DATABASE db1   
MODIFY ( SERVICE_OBJECTIVE = ELASTIC_POOL ( name = pool1 ) ) ;  

Answer 2

You have to use the Elastic database client library to manage the creation of the DB so that the ShardMapManager can register the database. Note that I said database (or shard in Elastic DB terminology) NOT Tenant (or Shardlet in Elastic DB terminology).

The entire Elastic DB components are as follows for SQL server, sharding was added via the Elastic Database SDK which involves the following components (some of which are available only in Azure): • Elastic Database Client Library Manage the data distributions and map Tenants to databases. • Elastic Database Pools (Azure only) Allocation of a pool of resources that can be shared across a number of databases. Allows for database to consumer resources at their own rate, rather than each database having a specific amount of resources that are available.

• Elastic Database Query The ability to query across all Tenants within a Multi-Tenant database.

• Elastic Database Jobs Package and reliably deploy database maintenance operations or database schema changes to multiple databases.

• Elastic Transactions Process changes to several databases in an atomic and isolated way.

• Elastic Database Split-Merge Tools Allows for the movement of shards between databases that are participating in the sharding framework

Important concepts using this library is that a Shard can have one or mapping to hold Shardlets (or mappings from the ShardMapManager). In fact the ShardMapManager maintains two collections: Shards (getShards), and Mapping (GetMappings). Mappings are ShardKeys are MAPPED to the Shard...

You have can two types of Shard Key definitions: RangeMapShard, and ListMapShard - both which inherit ShardMap (which contains a property ShardMapType to define the subtype that instantiated the ShardMap).

RangeMapShard - hold a RANGE of keys for ONE mapping.. e.g. customer 100-200 (200 is one higher in value than what is allowed in the range (crappy way of defining it in my opinion... but that is MS documentation) so what they mean is 100-199, 200-299 is defined using ranges of 100-200, and 200-300.

ListMapShard - hold one Shard Key for each Shardlet. So 1 value of a ShardKey is equal to 1 ShardMap ( a Shard can hold one or more Shardlets - so a Shard can hold mappings... )...

So you have to map your shard key(s) to a shard map, and then associated the shard map to a shard, and the shard has to be associated with a Database .... remember, you can create a database that does not have a shard (or schema) defined within it.

I found that I wanted to think of this the other way around, starting from the DB, and sticking in the data into that DB. IMO, the Elastic DB SDK works the other way, you define the Shardlet (tenant), map that to shard(schema), and then stick the Shard(schema) into a database.

HTH.