How to programmatically create an Azure SQL Database in an Elastic Pool?

Question

I want to write code in C# which would programmatically create and add an Azure SQL database into an existing elastic pool. I have looked into the Elastic Database Client Library, but it does not handle creation of databases, only registering existing databases as shards, which I would definitely make use of.

Is it possible to create the database by using something simple like SqlClient, or maybe this can be done by using the Azure SQL Management SDK or some other option?

Answer 1

You can use Transact SQL to create the database and add it to an elastic pool in one statement. In this example, we are creating a new database in a pool named S3M100:

CREATE DATABASE db1 ( SERVICE_OBJECTIVE = ELASTIC_POOL ( name = S3M100 ) ) 

You can also use Transact-SQL to first create the database.

CREATE DATABASE YourNewDB ( EDITION = 'GeneralPurpose' );

It can be a copy of another database.

CREATE DATABASE YourNewDB AS COPY OF OldDB;

After that you can move it to any elastic pool.

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