All geek questions in one place

Azure SQL Server Elastic Pool - automatically adds database to pool

We currently have Azure SQL Server, and every time we create a new web application, we use EF Core Code First to create the database. However, after creating the database, we manually go to the Azure portal and add the newly created database to the Elastic Pool inside Azure SQL Server. Is it possible to somehow automate the process so that each newly created database, either through a portal, is generated using EF or something else, it will be automatically added to the pool?

+5
source share
2 answers

You can use Transact-SQL to programmatically move an existing Azure SQL database to a resilient pool.

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

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

All components of an elastic database for SQL server are listed as follows: adding sharding via the Elastic Database SDK includes the following components (some of which are available only in Lazur): • Client library with an elastic database Manage data distribution and map Tenants to databases. • Elastic database pools (Azure only) Allocate a pool of resources that can be used for different databases. Allows you to use the database for consumer resources at its own rate, and not for each database that has a certain amount of available resources.

• Request an elastic database. Ability to query all tenants in the Multi-Tenant database.

• Work with resilient databases Package and reliably deploy database maintenance operations or database schema changes in multiple databases.

• Elastic transactions The process modifies several databases in atomic and isolated form.

• Split merge tools using an elastic database Allows you to move fragments between databases involved in the wireframe

The important concepts using this library are that Shard can have one or mapping for storing Shardlets (or mappings from ShardMapManager). In fact, ShardMapManager supports two collections: Shards (getShards) and Mapping (GetMappings). Mappings - ShardKeys MAPPED to Shard ...

You have two types of Shard key definitions: RangeMapShard and ListMapShard - both that inherit ShardMap (which contains the ShardMapType property to determine the subtype that created the ShardMap instance).

RangeMapShard - Hold the RANGE key to display ONE. 100-200 client (200 one is higher in value than what is allowed in the range (shitty way of determining it in my opinion ... but this is MS documentation), so they mean 100-199, 200-299 using ranges 100- 200 and 200-300.

ListMapShard - Hold one Shardlet key for each Shardlet. Thus, 1 ShardKey value is 1 ShardMap (A shard may contain one or more Hams), so Shard may contain mappings ...) ...

Thus, you must map your fragment key to the fragment map, and then link the fragment map to the fragment, and the fragment must be connected to the database ... remember, you can create a database that does not have the fragments (or schema) defined inside him.

I found that I wanted to think about it differently, starting with the database and inserting data into this database. IMO, Elastic DB SDK works differently, you define a Shardlet (tenant), map it to a shard (schema), and then insert a Shard (schema) into the database.

NTN.

+2
source

Source: https://habr.com/ru/post/1273121/

More articles:


All Articles