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.
No comments:
Post a Comment