How to programatically create Sql Azure database of type Basic/Standard edition through Enity Framework code first

Question

I use EF 6. My existing code is :

public void CreateOrUpdateCompanyDb(string companyDbName)
{
        try
        {
            string connectionString = _connectionStringProvider.GetConnectionString(companyDbName);
            DbMigrationsConfiguration cfg = CreateMigrationsConfig(connectionString);
            cfg.AutomaticMigrationsEnabled = false;
            cfg.AutomaticMigrationDataLossAllowed = false;
            DbMigrator dbMigrator = new DbMigrator(cfg);

            dbMigrator.Update();
        }
        catch (MigrationsException exception)
        {
            _logger.Error(string.Format("Error creating company database '{0}'",companyDbName), exception);
        }
}

with connection string as follows :

Server=tcp:xxx.database.windows.net,1433;Database=companyDbName;User ID=xxx@xxx;Password=xxx;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"

which creates the database for the particular company. But the problem is that the created database is from the now retired Web Edition but I want to create Basic/Standard/Premium edition.

How should I manipulate the connection string so that the edition of the database is the desired one?

Vikas Gupta · Accepted Answer · 2015-01-02 18:13:06Z

8

+50

The Edition of Sql Azure Database is something you can specify in Create Database command. AFAIK, you can't specify it using the connection string.

Syntax -

CREATE DATABASE database_name [ COLLATE collation_name ]
{
   (<edition_options> [, ...n]) 
}

<edition_options> ::= 
{
      MAXSIZE = { 100 MB | 500 MB | 1 | 5 | 10 | 20 | 30 … 150…500 } GB  
    | EDITION = { 'web' | 'business' | 'basic' | 'standard' | 'premium' } 
    | SERVICE_OBJECTIVE = { 'shared' | 'basic' | 'S0' | 'S1' | 'S2' | 'P1' | 'P2' | 'P3' } 
}
[;]

Given that, for your scenario, two options come to mind -

Before using DbMigrator, explicitly write code which creates the database, if it does not exist using traditional ADO.Net.
The other option which comes to mind, but I don't know enough about and you could dig into if you want to, is to somehow find a way to hook into EF, so that you could customize the Create Database command it must generate.

edited Jan 2, 2015 at 18:13

answered Dec 31, 2014 at 22:28

Vikas Gupta

4,4801 gold badge23 silver badges42 bronze badges

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

3 Comments

Ognyan Dimitrov Over a year ago

I will test it and if it works fine I will mark your answer. Thank you for your time to answer the question.

Ognyan Dimitrov Over a year ago

For all who stumble upon : another approach here and another one here

Tim Iles Over a year ago

Further to @OgnyanDimitrov's comment above, yep, I found the IDatabaseInitializer approach from social.technet.microsoft.com/Forums/office/en-US/… to work best for me.

Collectives™ on Stack Overflow

How to programatically create Sql Azure database of type Basic/Standard edition through Enity Framework code first

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related