Change The Azure SQL Pricing Tier Using SQL Query

In my previous blog, I have shown how to get the DTU, CPU, memory percentage of an Azure SQL database using SQL query. Here in this blog, I will show you how to change the tier of the SQL server using SQL query.

Prerequisite

To execute this query, you need an Azure subscription and a SQL server hosted there.

In action:

To change the tier, you can directly change the same from the Azure portal. Go to your SQL database in the portal. Go to Settings - Configure. Select your desired tier and click on Apply. Based on the size of your database it will take time to update the pricing tier.

Now, if you don't have the access to the Azure portal, how you are going to do the same operation? The answer is executing a SQL query. So, let's execute it.

To change the pricing tier, you need the database name, and the updated pricing tier (Edition & Service object).

The edition is the tier like Basic, Standard, Premium and Service objects are DTUs, like Basic, S0, S1, S2, S3, etc.

So the query will be like the below.

ALTER DATABASE [<dbName>] 
    MODIFY(EDITION='<edition>' 
    , SERVICE_OBJECTIVE='<serviceObjective>')

With data, the query will be like,

ALTER DATABASE [TestAzureDB] 
    MODIFY(EDITION='Standard' 
    , SERVICE_OBJECTIVE='S1')

The Basic edition has only Basic as a service object. In the Standard edition, we have S0 to S12 service objectives. For the Premium tier, you have P1 to P15 service objects.

While you will be executing the query, the pricing tier selection section in the Azure portal will be disabled to change. After the execution was done, you can see the changes in the pricing tier in the Azure portal.