Use Azure Management API SDK in an Entity Framework custom database initializer

A post over on Stack Overflow got me thinking about how you can override the default behaviour of the Entity Framework code first database initializer so that the tier of the database created is something other than the deprecated 'Web' tier. Here's one way to go about it.

Required bits

There are a few things to get going here - you'll need to add the the Microsoft Azure SQL Database Management Library nuget package to your solution which will install a bunch of dependencies required to interact with the Azure Management API.

You should also familiarise yourself with how to create and use Management Certificates which will be required for all interactions with the Azure Management API.

Once you've looked through that I suggest having a good read of Brady Gaster's blogs on using the Management API in which he gives some good overviews on working with Azure SQL Database AND on how you can go about uploading your Management Certificate to an Azure Website.

For the purpose of the remainder of this post we'll be using the sample MVC / EF code first sample application which can be downloaded from MSDN's code site.

Now you've done that, let's get started...

Create a custom EF initializer

Entity Framework provides a nice extensibility point for managing initialisation of databases amongst other items (primarily to allow you to use the latest hipster database of choice and roll your own supporting code) and we're going to use a simple sample to show how we could change the behaviour we're seeing above.

In the below sample we create Standard tier databases - we could just as easily change this to a configuration element and modify which database we wish to create. Note that I load a lot of information from configuration - in the below sample I can deploy those configuration elements at the Cloud Service level and manage via the Azure Management Portal. I could just as easily leave them in the web.config if I wanted to.

ContosoCustomDatabaseInitializer.cs

public class ContosoCustomDatabaseInitializer : IDatabaseInitializer<SchoolContext>
{
    public void InitializeDatabase(SchoolContext context)
    {
        if (context.Database.Exists())
        {
            if (!context.Database.CompatibleWithModel(true))
            {
                context.Database.Delete();
            }
        }

        CreateAzureSqlDatbase();

        // could check that the DB exists
        // could execute SQL on the server now if it does
    }

    private void CreateAzureSqlDatbase()
    {
        // could parse the connection string and extract these values if we wanted to.
        var serverName = CloudConfigurationManager.GetSetting("AzureSqlDatabaseServerName");
        var databaseName = CloudConfigurationManager.GetSetting("AzureSqlDatabaseName");

        var subscriptionId = CloudConfigurationManager.GetSetting("AzureSubscriptionId");
        var certificateThumprint = CloudConfigurationManager.GetSetting("AzureManagementCertThumprint");

        // would need to upload your management certificate along with your web application
        var certificate = LoadCertificate(certificateThumprint);

        var credentials = new CertificateCloudCredentials(subscriptionId, certificate);

        using (var client = new SqlManagementClient(credentials))
        {
            var database = client.Databases.Get(serverName, databaseName).Database;

            if (database == null)
            {
                var creationParameters = new DatabaseCreateParameters
                {
                    Name = databaseName,
                    Edition = "Standard",
                    MaximumDatabaseSizeInGB = 10,
                };

                client.Databases.Create(serverName, creationParameters);
            }
        }
    }

    private X509Certificate2 LoadCertificate(string certThumprint)
    {
        // load the certificate from the appropriate location.
        // There's a bunch of samples online on how to do this.
        // See code sample here for how: http://msdn.microsoft.com/en-us/library/azure/dn505701.aspx
        return new X509Certificate2();
    }
}

A sample of what appears in the configuration (this is from a web.config):

<appSettings>
  <add key="AzureSqlDatabaseServerName" value="t95xxttjmj"/>
  <add key="AzureSqlDatabaseName" value="SchoolDemo"/>
  <add key="AzureSubscriptionId" value="00000000-0000-0000-0000-000000000000"/>
  <add key="AzureSubscriptionCertThumbprint" value="61b463082dcb0198aab451c14efb7ff4b83a42b4"/>
</appSettings>

In our global.asax of our web application we then need to include the following code:

Database.SetInitializer(new ContosoCustomDatabaseInitializer());

At this point when EF attempts to fire up a new database instance it will call our custom code and initialise a new database on the specified server using the management libraries.

Hopefully in a future release we'll see an update to the default database setting to use the new Standard tier instead.