This is a part of series “Stairway to being an Azure SQL DBA“, where I will be covering all the topics that an Azure SQL DBA should know about.
One on the many benefits of using Azure SQL Database is that they can easily scaled up or down with very minimal downtime. The downtime also can be easily handled by having a retry logic in the application as the connections are dropped when making the switch.
We are not talking about scaling out, which means having multiple instance of Azure SQL Database handling the same application but Scale up/down which is increasing/decreasing the resources assigned to the database.
Scaling options for Azure SQL Database
There are multiple options which can be used for scaling the Azure SQL Database, which can be categorized as Autoscale and Dynamic Scalability.
Autoscale is when the service scales automatically based upon the utilization. For doing this you can either use the Elastic Pool, to know about that check Single Database vs Elastic Pool. The second option is using the Serverless model of Azure SQL Database where you can choose the Min and Max vCore that your database can use. It even has the option to pause your database reducing the costing to only the storage being utilized.
Dynamic Scalability is the option to manually scale the database with minimal downtime and in Azure SQL Database you have the option to do that using either Portal, PowerShell, T-SQL or CLI.
Scaling Azure SQL Database using portal
The GUI way of scaling the Azure SQL Database is going to your Azure SQL Database and from there going into the Configure option under the security tab and scaling your Azure SQL Database up/down.
This is how it looks, and you can choose which any Service-tier.
When scaling the Azure SQL Database the thing to consider is you can scale up from any service tier to any other service tier but when scaling down do consider the storage being utilized, as you can not scale down to a tier having smaller max size possible then your database. Also you cannot change to any other model once opted for Hyperscale.
Scaling Azure SQL Database using T-SQL
This is best option to scale your database from inside the SQL Server based upon the usage trend, or the heavy tasks that you need to perform but smaller duration of time. For example when you know you have a stored procedure which will require more resources to get it’s work done.
The T-SQL for scaling the Azure SQL Database is as below:
ALTER DATABASE Db_name Modify (Edition = 'Premium', Maxsize = 512 GB, Service_object = 'P15')There are many editions and service_objects and to know more about it check this ms-docs.
Scaling Azure SQL Database using PowerShell
Again something very useful when automating the scaling of the Azure SQL Database based upon the usage trend that you know about. For example, you have daily or incremental load which uses your database, and that starts at 11:00 pm and usually ends at 3:00 am you can create an Automation Account to trigger the scale up at 10:45 pm and scale it down at 4:00 am.
The PS script looks like below:
Set-AzSqlDatabase -ResourceGroupName "ResourceGroup01" -DatabaseName "Database01" -ServerName "Server01" -Edition "Standard" -RequestedServiceObjectiveName "S0"Based upon the service model there are many options available and to know about that check this ms-docs.
Scaling Azure SQL Database using CLI
Covering this as don’t want to leave this behind, I have never used this but might be you will need it someday. The CLI command for scaling the Azure SQL Database looks like below.
az sql db update -g mygroup -s myserver -n mydb --edition Standard --service-objective S1 --max-size 250GBTo know more about different options available check this ms-docs.
Conclusion
When using the Azure SQL Database and you have utilization of your database based upon some trends, like users login more during business hours or the database is being used for reporting purposes for the majority of time and only for small duration of a day or week it’s being having hammered down when doing the loads. It’s better to utilize the DBaaS to the best and scale the database up/down to save cost, it can save you big money.
being a dba 