Azure Cost Optimizations: Azure SQL

Azure SQL is a fantastic service that takes much of the complication of managing/implementing SQL away from users.  As all Azure services, you pay for what you use, and due consideration needs to be given to ensuring you are not paying too much for your needs.  As with most of the PaaS services, Azure SQL scales on one of three factors: DTUs (read performance), Disk Size, and features.  The goal of this post is to chat a little bit about potential Azure SQL cost optimizations.

1) Tune your SQL for performance

One great newer feature of Azure SQL is the query performance/insights and automatic tuning capabilities that have been baked into the service.  While I would not say that these features replace a DBA, you can certainly gain a ton of insight into what is going on.  I have personally used the tools here to look at long running queries and identify bottlenecks to developers who could then work to optimize their code.


For more detailed information about how to use these features, please see the following links:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-query-performance
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-advisor

Tuning your database can help with your DTU requirements.

2) Consider Pooling

Much like how app service plans have been defined, there are two core options to deploying Azure sql.  The first is a single database.  Think of this as a single web app on a dedicated app service plan.  In this mode, the SQL server is guaranteed all the resources assigned to it.  The second mode is elastic pool.  Here, you provision a group of resources and then deploy multiple databases to this pool.  Resources of the pool are shared by all databases, allowing for cost optimizations at the expense of the noisy neighbour problems that arise.


3) Scale down your databases during non-peak usage

Azure SQL is a service, and therefore, it is always on.  However, it is also billed by the hour.  You can make use of powershell commandlets to scale your databases to your requirements.  This works great with non-linear workloads, dev/test situations, and also for load testing purposes.

The command to use for v2 resources Set-AzureRmSqlDatabase.  When I implemented a scaling script I used the Find-AzureRMResource command to find the database via name and then set the appropriate scale.

        $findResult = Find-AzureRmResource -ResourceNameContains $sql -ResourceType "Microsoft.Sql/servers/databases"
        if (-not ($findResult -eq $null)){
            $serverName = $findResult.Name.Substring(0,$findResult.Name.IndexOf('/'))
            Set-AzureRmSqlDatabase -DatabaseName $sql -Edition $edition -RequestedServiceObjectiveName $requestedServiceObjectiveName -ServerName $serverName -ResourceGroupName $findResult.ResourceGroupName

Azure SQL has come a long way since it was first implemented.  New features such as the query insights can really help customers tune their databases and keep costs down.  As always, scaling services when required will generally net the biggest bang for your buck.