locked
Set Service Tiers when create Azure SQL Database from VS C# Entity Framework RRS feed

  • Question

  • Is it possible to set the Microsoft Azure SQL Database service tiers when creating a new database from Visual Studio in C# when using EF code first design? Currently, I can connect to the Azure SQL server and create table with no problem but for some reason the databases will be created in Web which is the service tier that going to be retired. I would like to set the default service tiers to either be Basic, Standard, or Premium depends on the needs. I'm using Entity Framework to create query that go against the Azure SQL Database.
    Tuesday, November 11, 2014 11:08 PM

Answers

  • Hi ChiChi20,

    Unfortunately, EF relies on the default parameters when it creates a new database, and the default in Azure SQL DB is currently to create a Web edition database. You can choose to overwrite the default behavior by creating your own database initializer for EF. That initializer than issues a CREATE DATABASE statement - see the following example which you can easily plug into the BloggingContext tutorial sample code for testing - or simply replace BloggingContext with your DbContext.

        public class BloggingContextCustomInitializer : IDatabaseInitializer<BloggingContext>
        {
            public void InitializeDatabase(BloggingContext context)
            {
                if (!context.Database.Exists())
                {
                    SqlConnectionStringBuilder connstrBldr = new SqlConnectionStringBuilder(context.Database.Connection.ConnectionString);
                    connstrBldr.InitialCatalog = "master";
    
                    using (SqlConnection conn = new SqlConnection(connstrBldr.ConnectionString))
                    {
                        conn.Open();
                        SqlCommand cmd = conn.CreateCommand();
                        cmd.CommandText = "CREATE DATABASE [" + context.Database.Connection.Database + "] (EDITION = 'standard')";
                        cmd.ExecuteNonQuery();
                    }
    
                    Database.SetInitializer(new CreateDatabaseIfNotExists<BloggingContext>());
                    context.Database.Initialize(force: true);
                }
            }
        }

    Let us know how this works out for you.

    Thanks,

    Torsten


    This posting is provided "AS IS" with no warranties, and confers no rights.



    Tuesday, November 25, 2014 1:50 AM

All replies

  • Hi ChiChi20,

    Thanks for posting here.

    We are working on this issue and we will get back at the earliest.

    Girish Prajwal

    Wednesday, November 12, 2014 4:49 AM
  • Hi,

    Thanks for posting here.

    I am trying my best to give solution here.

    Microsoft Azure SQL Database service tiers (editions) to dial-in cloud database performance and capabilities to suit your application.

    The Entity Framework 6 Tools are included in Visual Studio 2013. If you are using Visual Studio 2012, the tooling is  available on the Microsoft Download Center.

    If you perform a custom install of Visual Studio you will need to ensure Microsoft Web Developer Tools or Microsoft SQL Server Data Tools is selected, otherwise the EF Tools will not be installed.

    I suggest you to follow this link for more details

    http://msdn.microsoft.com/en-us/data/ee712906#tooling

    http://msdn.microsoft.com/en-us/library/azure/dn741340.aspx

    http://azure.microsoft.com/en-us/updates/general-availability-sql-database-basic-standard-and-premium-service-tiers/

    Girish Prajwal

    • Proposed as answer by Girish Prajwal Friday, November 14, 2014 3:04 AM
    • Marked as answer by Mekh Subba Friday, November 14, 2014 5:12 AM
    • Unmarked as answer by ChiChi20 Friday, November 14, 2014 6:18 PM
    • Unproposed as answer by ChiChi20 Friday, November 14, 2014 6:18 PM
    Wednesday, November 12, 2014 8:49 AM
  • Hi ChiChi20,

    Any update on this issue.

    Can you give us more input if the issue persists.

    Girish Prajwal

    Monday, November 24, 2014 5:01 AM
  • Hi ChiChi20,

    Unfortunately, EF relies on the default parameters when it creates a new database, and the default in Azure SQL DB is currently to create a Web edition database. You can choose to overwrite the default behavior by creating your own database initializer for EF. That initializer than issues a CREATE DATABASE statement - see the following example which you can easily plug into the BloggingContext tutorial sample code for testing - or simply replace BloggingContext with your DbContext.

        public class BloggingContextCustomInitializer : IDatabaseInitializer<BloggingContext>
        {
            public void InitializeDatabase(BloggingContext context)
            {
                if (!context.Database.Exists())
                {
                    SqlConnectionStringBuilder connstrBldr = new SqlConnectionStringBuilder(context.Database.Connection.ConnectionString);
                    connstrBldr.InitialCatalog = "master";
    
                    using (SqlConnection conn = new SqlConnection(connstrBldr.ConnectionString))
                    {
                        conn.Open();
                        SqlCommand cmd = conn.CreateCommand();
                        cmd.CommandText = "CREATE DATABASE [" + context.Database.Connection.Database + "] (EDITION = 'standard')";
                        cmd.ExecuteNonQuery();
                    }
    
                    Database.SetInitializer(new CreateDatabaseIfNotExists<BloggingContext>());
                    context.Database.Initialize(force: true);
                }
            }
        }

    Let us know how this works out for you.

    Thanks,

    Torsten


    This posting is provided "AS IS" with no warranties, and confers no rights.



    Tuesday, November 25, 2014 1:50 AM