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?

Alberto Morillo · Accepted Answer · 2019-08-07T13:14:29.343

11

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 ) ) ;

edited Aug 07 '19 at 13:14

answered Aug 07 '19 at 11:15

Alberto Morillo

13,893
2
24
30

4

Awesome, you can also combine AS COPY OF with SERVICE_OBJECTIVE in one instruction like this : CREATE DATABASE NewDb AS COPY OF [TemplateDb] ( SERVICE_OBJECTIVE = ELASTIC_POOL (name = [poolName]) ) – bN_ Nov 08 '19 at 15:49
That's really great. Thank for sharing this. – Alberto Morillo Nov 08 '19 at 16:01

score 3 · Answer 2 · answered Aug 08 '19 at 01:08

Please see this tutorial: Create a new elastic database pool with C#.

It gives you the c# code example to Create a new database in a pool:

// Create a database: configure create or update parameters and properties explicitly
DatabaseCreateOrUpdateParameters newPooledDatabaseParameters = new DatabaseCreateOrUpdateParameters()
{
    Location = currentServer.Location,
    Properties = new DatabaseCreateOrUpdateProperties()
    {
        Edition = "Standard",
        RequestedServiceObjectiveName = "ElasticPool",
        ElasticPoolName = "ElasticPool1",
        MaxSizeBytes = 268435456000, // 250 GB,
        Collation = "SQL_Latin1_General_CP1_CI_AS"
    }
};

var poolDbResponse = sqlClient.Databases.CreateOrUpdate("resourcegroup-name", "server-name", "Database2", newPooledDatabaseParameters);

If you already have Azure SQL databases, you can reference Monitor and manage an elastic database pool with C#.

For example, Move a database into an elastic pool:

// Retrieve current database properties.

currentDatabase = sqlClient.Databases.Get("resourcegroup-name", "server-name", "Database1").Database;

// Configure create or update parameters with existing property values, override those to be changed.
DatabaseCreateOrUpdateParameters updatePooledDbParameters = new DatabaseCreateOrUpdateParameters()
{
    Location = currentDatabase.Location,
    Properties = new DatabaseCreateOrUpdateProperties()
    {
        Edition = "Standard",
        RequestedServiceObjectiveName = "ElasticPool",
        ElasticPoolName = "ElasticPool1",
        MaxSizeBytes = currentDatabase.Properties.MaxSizeBytes,
        Collation = currentDatabase.Properties.Collation,
    }
};

// Update the database.
var dbUpdateResponse = sqlClient.Databases.CreateOrUpdate("resourcegroup-name", "server-name", "Database1", updatePooledDbParameters);

Hope this helps.

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

2 Answers2