Scaling Windows Azure SQL Database to Your Operational Needs

Windows Azure SQL Database does not warn us when we are close to the Database MAXSIZE. If you are like me, you probably don’t want to pay for stuff you don’t use and your probably don’t want your application to stop responding. The only way to effectively accomplish this, is to monitor your Windows Azure SQL Databases on a regular basis.

This has happened to me quite a few times so far… and its just so irritating!

To remedy this recurring pain, I created a SqlDatabaseAutoScaler, which monitors my SQL Database size. If for some reason my database has shrunk, it will try to reduce the Database MAXSIZE and adjust the Database Edition. On the other hand, if the database grows beyond a certain size, it will augment the Database MAXSIZE and adjust the Database Edition.

The code from this Post is part of the Brisebois.WindowsAzure NuGet Package

To install Brisebois.WindowsAzure, run the following command in the Package Manager Console

PM> Install-Package Brisebois.WindowsAzure

Get more details about the Nuget Package.

Windows Azure SQL Database pricing is based on the MAXSIZE per month.

Pricing as of March 2013

We charge a monthly rate for each SQL Database you utilize, but we amortize that database fee over the month and calculate your fee on a daily basis. For databases greater than 1 GB, you will be billed in the next whole gigabyte increment. For example, if you utilized two Business Edition databases, one that was 4.4 GB and one that was 14.4 GB for 1 day during a billing month, you would be charged for a 5 GB and 15 GB database for that day, which would total $2.65. Below are the calculations:

5 GB: ($9.99 for the first GB + $3.996 per GB for the next 4 GB) / 31 days = $.84

15 GB: ($45.954 for the first 10 GB + $1.996 per GB for the next 5 GB) / 31 days = $1.81

SqlDatabaseAutoScaler

The following is an example of how to instantiate and use the SqlDatabaseAutoScaler. When instantiating the SqlDatabaseAutoScaler, you will be required to pass as parameters the name of the database and the interval used to execute the scaling task.

class Program
{
  static void Main(string[] args)
  {
    Trace.Listeners.Add(new ConsoleTraceListener());
   
    var scaler = new SqlDatabaseAutoScaler("myDatabaseName",
                                            TimeSpan.FromMinutes(5));
    scaler.Start();
   
    System.Console.ReadLine();
  }
}

When the SqlDatabaseAutoScaler notices that a modification is required and that it is within the acceptable database size, it will apply the recommendations it got from querying the database.

When the SqlDatabaseAutoScaler notices that nothing needs to be done, it skips the modification actions and will wait for the next interval.

The SqlDatabaseAutoScaler implements an IntervalTask. The IntervalTask source code is listed below. It is responsible for maintaining the specified interval. The SqlDatabaseAutoScaler implements the code that is executed on each interval. It uses the ReliableModel to communicate with the database using an exponential back off strategy. Furthermore, it uses an EmptyDbContext, whose code is also listed below, to execute code directly against the database.

The Recommendations are logged using a Logger which persists the events to Windows Azure Table Storage Service.

public class SqlDatabaseAutoScaler : IntervalTask
{
 private readonly string databaseName;
 private readonly int absoluteMaxSize;

 public SqlDatabaseAutoScaler(string databaseName, 
                                 TimeSpan interval,
                                 int absoluteMaxSize = 150) 
     : base(interval)
 {
     this.databaseName = databaseName;
     this.absoluteMaxSize = absoluteMaxSize;
 }

 protected override void Execute()
 {
   var bd = CloudConfigurationManager.GetSetting("DatabaseConnectionString");
   var sp = "EXEC [dbo].[GetDatabaseSizeRecommendation] @databasename = {0}";
 
   var recommendation = ReliableModel.Query(model =>
       {
           return model.Database.SqlQuery<DatabaseSizeRecommendation>(
                                   sp,
                                   databaseName)
                                   .FirstOrDefault();
       },() => new EmptyDbContext(bd));

   ReportRecommendations(recommendation);
       
   if (recommendation.CurrentMaxSize == recommendation.MaxSize)
       return;
   if (recommendation.CurrentMaxSize == absoluteMaxSize)
       return;
   if (recommendation.MaxSize > absoluteMaxSize)
       return;

   Report("Applying Recommendations");

   var m = CloudConfigurationManager.GetSetting("MasterDatabaseConnectionString");

   ReliableModel.DoWithoutTransaction(model =>
   {
       model.Database.ExecuteSqlCommand("ALTER DATABASE ["
                                       + databaseName
                                       +"] MODIFY (EDITION='"
                                       + recomendation.Edition
                                       +"', MAXSIZE=" 
                                       + recomendation.MaxSize 
                                       + "GB)");
   }, () => new EmptyDbContext(m));
 }

 private void ReportRecommendations(DatabaseSizeRecomendation recomendation)
 {
   var sb = new StringBuilder();
   
   sb.AppendFormat("Current Database Size :{0}", recommendation.CurrentSize);
   sb.AppendLine();
   sb.AppendFormat("Current Database Max Size: {0}", recommendation.CurrentMaxSize);
   sb.AppendLine();
   sb.AppendFormat("Recommended Database Max Size: {0}", recommendation.MaxSize);
   sb.AppendLine();
   sb.AppendFormat("Recommended Database Edition : {0}", recommendation.Edition);

   Report(sb.ToString());
 }

 protected override void Report(string message)
 {
     Logger.Add("SQLDatabaseAutoScaler","Event", message);
 }
}

EmptyDbContext

The EmptyDbContext is used to for direct access to the database. I could have used a ReliableSqlConnection instead of this. The DbContext approach was me being lazy.

public class EmptyDbContext : DbContext
{
    public EmptyDbContext()
    {
            
    }

    public EmptyDbContext(string connectionString)
        : base(connectionString)
    {
            
    }
}

IntervalTask

The IntervalTask is a base class I use to create Tasks that run on specific intervals.

public abstract class IntervalTask : WorkerProcess
{
    private Task internalTask;
    private readonly CancellationTokenSource source;
    private readonly TimeSpan interval;

    protected IntervalTask(TimeSpan interval)
    {
        this.interval = interval;
        source = new CancellationTokenSource();
    }

    public void Start()
    {
        if (internalTask != null)
            throw new Exception("Task is already running");

        internalTask = Task.Run(() =>
            {
                while (!source.IsCancellationRequested)
                {
                    TryExecute();

                    Report("Heart Beat");
                }
            }, source.Token);
    }

    private void TryExecute()
    {
        try
        {
            Task.Delay(interval)
                .ContinueWith(_ => Execute())
                .Wait();
        }
        catch (Exception ex)
        {
            Report(ex.ToString());
            if (Debugger.IsAttached)
                Trace.TraceError(ex.ToString());
        }
    }

    protected abstract void Execute();
    protected abstract void Report(string message);

    public void Cancel()
    {
        source.Cancel();
        internalTask = null;
    }
}

GetDatabaseSizeRecommendation Stored Procedure

This stored procedure queries that database and verifies the current size of the database. Then it tries to find the best MAXSIZE for the database. It can choose a smaller size if the database MAXSIZE is too big for the actual database size requirements. Why pay more when you could pay less? If the current database size + 0.1 GB pushes the database size over the current MAXSIZE, the stored procedure will recommend scaling up to the next increment. This can actually be a good thing. Yes you will be paying more, but if your application needs the extra space for a single day during your month, you will only pay for the extra space based on the number of days that where used.

CREATE PROCEDURE [dbo].[GetDatabaseSizeRecomendation]
    @databasename AS VARCHAR(64)
AS

    DECLARE @dbsize AS FLOAT
    
    SELECT @dbsize = SUM(reserved_page_count)*8.0/1024/1024
    FROM sys.dm_db_partition_stats

    DECLARE @increment AS FLOAT = 0.1
    
    DECLARE @maxSize AS BIGINT
    SELECT @maxSize = CONVERT(
                        BIGINT,
                        DATABASEPROPERTYEX (
                                @databasename,
                                'MaxSizeInBytes'))

    SELECT @maxSize = @maxSize / 1024 / 1024 / 1024
    
    --1 | 5 | 10 | 20 | 30 | 40 | 50 | 100 | 150
    SELECT @dbsize = @dbsize + @increment

    DECLARE @newMaxSize AS INT
    SELECT @newMaxSize = 150
        
    IF(@dbsize < 100)
    BEGIN
        SELECT @newMaxSize = 100
    END

    IF(@dbsize < 50)
    BEGIN
        SELECT @newMaxSize = 50
    END

    IF(@dbsize < 40)
    BEGIN
        SELECT @newMaxSize = 40
    END

    IF(@dbsize < 30)
    BEGIN
        SELECT @newMaxSize = 30
    END

    IF(@dbsize < 20)
    BEGIN
        SELECT @newMaxSize = 20
    END

    IF(@dbsize < 10)
    BEGIN
        SELECT @newMaxSize = 10
    END

    IF(@dbsize < 5)
    BEGIN
        SELECT @newMaxSize = 5
    END

    IF(@dbsize < 1)
    BEGIN
        SELECT @newMaxSize = 1
    END

    DECLARE @edition AS VARCHAR(50) = 'Business'
    IF(@newMaxSize < 10)
    BEGIN
        SELECT @edition = 'Web'
    END
        
    SELECT @dbsize AS [CurrentSize], 
           CONVERT(INT,@MaxSize) AS [CurrentMaxSize], 
           @newMaxSize AS [MaxSize], 
           @edition AS [Edition]

Be sure to include the following settings in your app.config or cloud configurations

 <appSettings>
    <add key="StorageConnectionString" value="[Storage Connection String]" />
    <add key="DatabaseConnectionString" value="[DB Connection String]" />
    <add key="MasterDatabaseConnectionString" value="[DB Connection String]" />
  </appSettings>