'How to programmatically change tier of Azure SQL Database
We have a large SQL Database running on Azure which is only generally in use during normal office hours, although from time to time, overtime/weekend staff will require performant access to the database.
Currently, we run the database on the S3 Tier during office hours, and reduce it to S0 at all other times.
I know that there are a number of example PowerShell scripts that can be used together with automation tasks to automatically modify the database tiers according to a predefined timetable. However, we would like to control it from within our own .Net application. The main benefit is that this would allow us to give control to admin staff to switch up the database tier during out-of-hours as required without the need for technical staff to get involved.
There are a number of articles/videos on the Microsoft site that mention "scaling up/down" (as opposed to "scaling out/in", i.e. creating/removing additional shards), but the sample code provided seems to deal exclusively with sharding, and not with vertical "scaling up/down".
Is this possible? Can anyone point me in the direction of any relevant resources?
Solution 1:[1]
You can, yes. You have to use the REST API to do the call to our endpoints and update the database.
The description and the parameters of the PUT required to the update is explained here -> Update Database Details
You can change the tier programmatically from there.
Solution 2:[2]
Yes, you can change database tiers using REST API and call Azure endpoints to update the tier.
The parameters to be used for PUT are explained on this msdn page: Update Database
Solution 3:[3]
This can now be done using multiple methods besides using the REST API directly.
https://docs.microsoft.com/en-us/azure/azure-sql/database/single-database-scale
See Azure CLI example
az sql db update -g mygroup -s myserver -n mydb --edition Standard --capacity 10 --max-size 250GB
See PowerShell example:
Set-AzSqlDatabase -ResourceGroupName "ResourceGroup01" -DatabaseName "Database01" -ServerName "Server01" -Edition "Standard" -RequestedServiceObjectiveName "S0"
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Panos |
| Solution 2 | Shantanu |
| Solution 3 | David Cobb |