Intro to Azure Elastic Databases and how to create them on-the-fly

I've spent a good deal of time away-from-keyboard lately, because it's summer :) Being offline for a while has given me some time to reflect about the path I want to go with my company and my on-call SaaS tool (more about that in some other, future, post).

It's also given me a bit of time to reflect about some technical aspects of running a SaaS product, the choices I've made so far, and changes I want to implement going forward.

One of the things I've been looking at is Azure Elastic Database. My product is run on Azure, and uses a number of the Azure PaaS offerings, including a SQL database. The Elastic Database offering is still in preview, but could potentially be interesting for companies delivering SaaS products based on Azure.

What are Elastic Databases

There are some choices to be made when deciding how to store customer data for a SaaS product. One of them really boils down to whether or not you want your customers to share the same database or not.

As always, there are pros and cons. A shared database design may offer easier maintenance than a design with multiple individual databases, it's also likely to be a more cost efficient option because it offers precise scaling (vertically, up to a point).

On the other hand, individual databases offer data isolation, and better support for individually scaling single databases.

While a multi-database solution might work fine if you manage the database yourself, it can quickly become prohibitively expensive in a cloud environment where resources aren't shared, requiring you to provision (and pay for) each individual database.

To make matters worse you need to scale each database separately, which means you are likely to end up paying more than you need to in order to be able to handle peak usage for every database.

I've tried to illustrate it here with the lighter blue background being "resources", that is available to the databases (and which you pay for).

Here (1) is a large shared multi-tenant database, pretty easy to scale according to actual needs, and (2) is the individually scaled (and paid for) isolated databases, which are probably over-provisioned to accomodate peaks.

Different approaches to SaaS databases

Figure (3) illustrates the Elastic Database approach. Here you still have individual isolated databases, but they run within the same shared pool of resources. This gives you both isolated databases for your customers, as well as being able to scale according to your needs and pay only for the resources you actually consume.

Any reasons I wouldn't want to use it?

Elastic Databases sound great, so are there any reasons I wouldn't want to use it (besides the major rewrite and that they are still in preview)?

Well. There might be a few things we need to check out before jumping aboard.

My main concern is how easy it would be for me to work with, doing things like creating, maintaining and migrating databases. As well accessing data across databases for secondary purposes like analytics. These are things I want to know more about.

In this blog post I will be exploring the first item on that list, how to go about setting up a resource pool and adding new databases to it.

Setting up elastic databases

In order to get started we need to go ahead and set up a new Elastic Database pool. This is either done programatically using the Azure SQL Management Library, Powershell or with a few easy steps directly in the Azure Portal.

1. Create a database server

You can either use an existing database server, or create a new one. Since this is an experiment I'll go ahead and create a new one.

Create New Azure SQL Server

2. Adding an elastic database pool

The next thing we need to do is add a pool of resources that our elastic databases can share. The only thing to notice here is the cost. The lowest performance you can choose is a 100 DTUs, which for me currently comes in at around NOK1000/month (a bit above $100).

Adding a resource pool

Adding databases

So far so good, we now have a resource pool set up, and adding new databases from the Azure Portal is easy enough.

Adding Elastic Databases from Azure Portal

But adding databases manually when we get a new customer may not be the workflow you want, especially if you are running a (less enterprisey) SaaS targeted at people who expect to start using your product right away.

Requiring people to wait for you to set up a database manually before they can start using your product sounds like a good way to lose customers. What we want instead is to be able to provision databases programmatically.

Adding elastic databases programatically

This is where the Azure SQL Management Library comes in. You can check out the source code in the Azure GitHub account.

We can use the management library to create and add databases to our elastic pool. In a real world scenario this would allow us to provision a new database on-demand when a new customer signs up and have it ready for them close to instantly.

So how do we go about doing that?

Getting access to Azure

The first thing we need to do is to figure out how to get permission to actually do things on our Azure account using the management library. This requires a bit of work though, which I have written about a few weeks ago.

If you want to continue, you should go and check out this post first Getting access to Azure programmatically using the Azure SDK Management Libraries. Go through the tutorial and return here when you get to the part about permissions.

Setting permissions

In the article above we only granted ourselves access to read information about the database server. Now we actually want to be able to add a new database. In order to achieve this we do as we did last time, assign a role to our application.

Let's spin up PowerShell and assign the SQL DB Contributor role our application. This allows us to create new databases.

First, make sure you have access to your Azure account.

Add-AzureAccount

Then, select the subscription you want to access and change the mode.

Select-AzureSubscription -SubscriptionId <subscription-id>
Switch-AzureMode -Name AzureResourceManager

Now, assign the role to your application. ObjectId in this case is the Id of your application, if you don't have it, try creating the new database before you assign the role. The error message you get contains the Id you need. When you have it, assign the role to it like so.

New-AzureRoleAssignment -ObjectId '<some-id>' -ResourceGroupName <resource-group-name> -RoleDefinitionName "SQL DB Contributor"

That's it, we are ready to add a new database!

Creating a database and adding it to our Elastic Pool

Let's refer once more to Getting access to Azure programmatically using the Azure SDK Management Libraries. This time we will alter the code a bit. Let's change the main loop.

static void Main(string[] args)
{
    var client = GetSqlManagementClient();

    // The Db we want to use as template
    var originalDb = client.Databases.Get(resourceGroup, "elastic-test", "TestDb1");

    // Create a new database within the same elastic pool
    var parameters = new DatabaseCreateOrUpdateParameters()
    {
        Location = "East US 2",
        Properties = new DatabaseCreateOrUpdateProperties()
        {
            ElasticPoolName = "ElasticPoolTest",
            SourceDatabaseId = originalDb.Database.Id
        }
    };
    var response = client.Databases.CreateOrUpdate(
      resourceGroup, 
      "elastic-test", 
      "TestDb2", 
      parameters
    );
    Console.WriteLine(response.Status);

    Console.ReadLine();
}

Let's go over what happens here.

First we get a management client, and use it to fetch information about a database already in the pool called "TestDb1". I added that database to the pool when I created it.

Then we set up the parameters for creating a new database. Here we specify the location of the database and the name of the Elastic Pool we want to add it to(!). We also specify that we want to use another database as a template through the SourceDatabaseId.

Lastly we use the client to run a CreateOrUpdate-command. It completes in less than a minute, and creates a new database in our pool. Here is what it looks like after the program has run.

Creating Azure Elastic databases programmatically

For this example I have used a completely empty database, so provision time is likely to be different for real world scenarios. Anyway, that's a database created and added to our Elastic Database pool, in only a few lines of code.

Summary

That's it for now. We've discussed what Elastic Databases are, and why they are useful. We have also looked at how we can configure them programmatically, so they make sense in scenarios were fast provisioning of SaaS applications is key.

View Comments