Can I Alter SQL Azure ServiceObjective (Pricing Tier) Using SQL

Question

So I use Visual Studio Database project to publish my database to Azure.

By Default this publishes the database as S0 pricing tier. I then have to log into the Portal and change the Pricing tier to S1.

From this answer I can see someone set the ServiceObjective property when creating a database like so:

CREATE DATABASE 
[{databaseName}] 
(MAXSIZE={maxSize}, EDITION='{edition}',SERVICE_OBJECTIVE='{serviceObjective}'

After Changing the Pricing tier in the portal I run this command:

SELECT DATABASEPROPERTYEX('MyDatabaseName', 'ServiceObjective')

and it returns S1 so I am sure ServiceObjective is the right property

But my question is can I set this property after Creating a Database using Alter

Answer 1

Can I Alter SQL Azure ServiceObjective (Pricing Tier) Using SQL

It seems this is possible(i didn't tested yet),docs state this as well: Changing the edition, size and service objective for an existing database

ALTER DATABASE [db1] 
MODIFY
 (EDITION = 'Premium', MAXSIZE = 1024 GB, SERVICE_OBJECTIVE = 'P15');

Below is the total syntax for SQLAZURE alter

-- Azure SQL Database Syntax  
ALTER DATABASE { database_name }  
{  
    MODIFY NAME =new_database_name  
  | MODIFY ( <edition_options> [, ... n] )   
  | SET { <option_spec> [ ,... n ] }   
  | ADD SECONDARY ON SERVER <partner_server_name>  
      [WITH (\<add-secondary-option>::= [, ... n] ) ]  
  | REMOVE SECONDARY ON SERVER <partner_server_name>  
  | FAILOVER  
  | FORCE_FAILOVER_ALLOW_DATA_LOSS  
}  

<edition_options> ::=   
{  

      MAXSIZE = { 100 MB | 250 MB | 500 MB | 1 … 1024 … 4096 GB }    
    | EDITION = { 'basic' | 'standard' | 'premium' | 'premiumrs' }   
    | SERVICE_OBJECTIVE =   
                 {  'S0' | 'S1' | 'S2' | 'S3'| 'S4'| 'S6'| 'S7'| 'S9'| 'S12' |
                 | 'P1' | 'P2' | 'P4'| 'P6' | 'P11'  | 'P15' | 
                 | 'PRS1' | 'PRS2' | 'PRS4' | 'PRS6' |
                 | { ELASTIC_POOL (name = <elastic_pool_name>) }   
                 }   
}  

<add-secondary-option> ::=  
   {  
      ALLOW_CONNECTIONS = { ALL | NO }  
     | SERVICE_OBJECTIVE =   
                 {  'S0' | 'S1' | 'S2' | 'S3' | 'S4'| 'S6'| 'S7'| 'S9'| 'S12' |
                 | 'P1' | 'P2' | 'P4'| 'P6' | 'P11' | 'P15' |
                 | 'PRS1' | 'PRS2' | 'PRS4' | 'PRS6' |  
                 | { ELASTIC_POOL ( name = <elastic_pool_name>) }   
                 }   
   }  

 [;]

Answer 2

You can run an ALTER DATABASE statement and need to specify as a minimum the Edition and the Service Objective as shown below:

ALTER DATABASE [DBName] MODIFY(EDITION='Standard', SERVICE_OBJECTIVE='S2')

Hope this helps.

Answer 3

In addition to other answers, you can wrap the SQL commands in an Azure Logic App and give it a schedule. And since you can change the tier with SQL, you can easily set it up to scale up and down periodically, e.g. switch to 100 DTUs every morning, and switch back to 10 DTUs every evening.