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.