Is there a way to set the default Azure SQL Database pricing tier for an Azure SQL Server

Question

When using a Database Initializer for Entity Framework 6 that creates a database when none exists with an Azure SQL server connection the Database created is created using the vCore pricing model.

Ideally it just needs to be an S0 DTU instance, and I need to find a way of specifying it as such.

Is there away to specify the default database type on the Azure SQL server? Or can something magic be added to the connection string to inform it of the type of database instance to create?

Beira

Also consider adding the new database to an existing elastic pool. That way you don't get billed for each database seperately. learn.microsoft.com/en-us/azure/sql-database/… – Customhouse

Answer 1

Credit: Torsten Grabs (MSFT) @ MSDN

https://social.msdn.microsoft.com/Forums/windows/en-US/7d7b5cd8-5878-4241-a674-33336010f081/set-service-tiers-when-create-azure-sql-database-from-vs-c-entity-framework?forum=ssdsgetstarted

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

Adding on to this, please see https://www.sqlshack.com/overview-of-create-database-statement-in-azure-sql-server/

CREATE DATABASE DemoDB</p>
( EDITION = 'standard', SERVICE_OBJECTIVE = 'S0', MAXSIZE = 500 MB ) ;

Dicarlo

Answer 2

If you are using EF Core migrations, use the model builder extensions HasServiceTier, HasPerformanceLevel and HasDatabaseMaxSize to specify Azure SQL database options:

https://learn.microsoft.com/en-us/ef/core/providers/sql-server/azure-sql-database

Jura