How to Scale Azure SQL Database?

When using the Azure SQL DB 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.

Azure SQL Database can easily be scaled up or down with very minimal downtime, which is one on the main benefits of using Azure SQL DB. The downtime also can be easily handled by having a retry logic in the application as the connections are dropped when making the switch. In this blog we will see and learn scaling (increasing/decreasing) the resources assigned to the database.

Scaling Options for Azure SQL DB

We can categorize scaling options for Azure SQL DB as:

1. AutoScale: This service scales automatically based upon the utilization. For doing this you can either using the Elastic Pool or 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.

2. Dynamic Scalability: Using this option you can manually scale the database with minimal downtime and in Azure SQL Database you have below options to do that:

A) Scaling Azure SQL DB using Portal

B) Scaling Azure SQL DB using Azure CLI

C) Scaling Azure SQL DB using T-SQL

D) Scaling Azure SQL DB using PowerShell

A) Scaling Azure SQL DB using Portal

You can perform scaling of your Azure SQL database using Azure Portal(GUI). Click on the database; find Configure option under the settings tab and scale up/down your Azure SQL Database.

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.

B) Scaling Azure SQL DB using Azure CLI

The CLI command for scaling the Azure SQL Database can be scripted as:

az sql db update -g mygroup -s myserver -n mydb --edition Standard --service-objective S1 --max-size 250GB

Based upon the service model there are many options available, check out ms-docs

C) Scaling Azure SQL DB 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. check out ms-docs

D) Scaling Azure SQL DB 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 10:30 pm and usually ends at 2:30 am you can create an Automation Account to trigger the scale up at 10:15 pm and scale it down at 3:30 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, check out ms-docs

That's all in this post. If you liked this blog and interested in knowing more about Azure, Please Like, Follow, Share & Subscribeto www.ImJhaChandan.com.