CODEWITHSUNDEEP
entity-framework-coreazure-sql-databaseazure-sql-serverazure-elasticpool
Kassoul
Kassoul

Reputation: 21

How create a database in azure elastic pool with entity framework core?

I would like to create a database with entity Framework core that would be automatically added to my azure elactic pool.

I do that with a DatabaseFacadeExtension that execute SQL command after the db creation like suggested here: Azure SQL Server Elastic Pool - automatically add database to pool

public static async Task<bool> EnsureCreatedAsync(this DatabaseFacade databaseFacade, string elasticPoolName, CancellationToken cancellationToken = default)
        {
            if (!await databaseFacade.EnsureCreatedAsync(cancellationToken)) return false;

            // the database has been created.

            var dbName = databaseFacade.GetDbConnection().Database;

            try
            {
                cancellationToken.ThrowIfCancellationRequested();

                if (!string.IsNullOrEmpty(elasticPoolName))
                {
                    await databaseFacade.ExecuteSqlCommandAsync(new RawSqlString(
                            $"ALTER DATABASE {dbName} MODIFY ( SERVICE_OBJECTIVE = ELASTIC_POOL (name = [{elasticPoolName}] ));"),
                        cancellationToken);
                }

                return true;

            }
            catch
            {
                await databaseFacade.EnsureDeletedAsync(cancellationToken);
                throw;
            }

        }

It's works but I will prefer an atomic operation where the database would be created directly in the Azure Elastic Pool.

Upvotes: 2

Views: 2122

Answers (2)

sabhari karthik
sabhari karthik

Reputation: 1371

I had a very similar issue. Fortunately, I took cues from the previous answer and I improvised on it to arrive at a solution.

I had a common database to manage the application and whenever a new client onboards, I need to create a new database. So, I had to maintain multiple database contexts in my .NET core application. Also, I had migrations for the clientContext ready in my codebase, which just needed

client_db.Database.MigrateAsync();

to create the database. But, I couldn't create it directly under elastic pool as Azure doesn't have any default settings which support that. So, MigrateAsync always created the database outside the pool.

So, I created the database within the pool using T-SQL command from my common database context, followed by MigrateAsync() to migrate all the required schema.

var commandText = "CREATE DATABASE client1 ( SERVICE_OBJECTIVE = ELASTIC_POOL ( name = demoPool ) );";
db.Database.ExecuteSqlCommand(commandText);
clientContext client_db = new clientContext(approved_corporate.Id, _configuration);
client_db.Database.MigrateAsync();

Also I had a custom Constructor in my clientContext to support this:

public clientContext(int client_id, IConfiguration configuration = null)
{
    string client_code = "client" + client_id.ToString();
    connection_string = configuration["ConnectionStrings:Client"].ToString().Replace("client_code", client_code);
}

Upvotes: 1

Leon Yue
Leon Yue

Reputation: 16431

Azure Elastic Pool supports you creates a new database in an existing pool or as a single database. You must be connected to the master database to create a new database.

enter image description here

For more details, please see: Transact-SQL: Manage pooled databases.

Example T-SQL Code:

Creating a Database in an Elastic Pool:

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

Please see: Azure SQL Database single database/elastic pool

You can replace the T-SQL statement and try again.

Hope this helps

Upvotes: 0

Related Questions