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-07 13:14:29Z

16

Answer recommended by Microsoft Azure Collective

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 7, 2019 at 13:14

answered Aug 7, 2019 at 11:15

Alberto Morillo

15.8k3 gold badges29 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bN_ Over a year ago

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

Alberto Morillo Over a year ago

That's really great. Thank for sharing this.

Leon Yue · Accepted Answer · 2019-08-08 01:08:04Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related