Automatically scaling up and down between pricing tiers in Azure SQL databases

We want a certain level of performance for our Azure databases, but sometimes we don't need that performance 24/7. And if we don't need it, we don't want to pay for it!

So, I put together a script that can be run regularly (perhaps hourly) and has a set of specific business hours during which we want to be scaled up, along with the tiers that we want to be scaled to. The business hours are set up to be 7 AM to 10 PM. Then during non-business hours, we scale down to the appropriate level. It takes into consideration weekends/weekdays as well.

This is currently running with SQLCMD on a VM, but we'll probably switch to Azure Automation eventually.

The heart of the code is the @PricingTierTarget table (done as a table variable in the actual code below). It has all the DB names, and the scale up/scale down information.

Note that you use this code at your own risk! I would particularly be aware of switching between editions (for instance, switching from Standard to Basic). This will affect the point in time to which you can restore the database (among other features that differ between the editions).

 /*
----------------------------------------------------------------------------
Switch Azure databases between pricing tiers depending on whether the current time is business hours or not

If it's during business hours, then mode is ScaleUp.
If it's not during business hours, then mode is ScaleDown.

Our main database (I'm calling it MainDB database here) is scaled between Standard S2 to Standard S4.
All other databases are scaled down to Basic Basic and must be manually scaled up

Note - this code will run and complete, but the databases take longer to switch tiers. That 
may take a minute or two (depending on size).
To see when it actually finishes, you can check the table sys.dm_operation_status.

----------------------------------------------------------------------------
*/


Set nocount on

Declare 
    @Now datetime = GetDate()
    ,@SQL nvarchar(3000) = ''
    ,@BusinessHoursStartTime tinyint = 7
    ,@BusinessHoursEndTime tinyint = 20
    ,@ScalingMode varchar(20) = 'ScaleDown'
    ,@SingleQuote  nchar(1)    = char(39)
    ,@NewLine char(2) = char(13) + char(10)
    ,@Message nvarchar(3000)
   

Declare
    @PricingTierTarget table (
        DBName sysname
        ,ScaleUpEdition varchar(20)
        ,ScaleUpServiceObjective varchar(20)
        ,ScaleDownEdition varchar(20)
        ,ScaleDownServiceObjective varchar(20)
        ,CurrentEdition varchar(20)
        ,CurrentServiceObjective varchar(20)
        ,TargetEdition      varchar(20)
        ,TargetServiceObjective varchar(20)
        ,NeedAlterDatabase  bit default 0
    )

-- Convert to Central Standard Time    
Select @Now = @Now AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time'

-- Determine if we should scale up or scale down
If 
    -- Is it a business day?
    ((Datename(dw,@Now) in ('Monday', 'Tuesday','Wednesday','Thursday','Friday')) 
    and 
    -- Are we in business hours?
    (Datepart(hour, @Now) between @BusinessHoursStartTime and @BusinessHoursEndTime))
    Begin
    Set @ScalingMode = 'ScaleUp'
End
    
-- Populate @PricingTierTarget table    
Insert into @PricingTierTarget (DBName,ScaleUpEdition,ScaleUpServiceObjective,ScaleDownEdition,ScaleDownServiceObjective)
values
-- DBName                   ScaleUpEdition  ScaleUpServiceObjective   ScaleDownEdition    ScaleDownServiceObjective
('MainDB'              , 'Standard'    , 'S4'                    , 'Standard'        , 'S2')
,('MainDBDev'          , 'Basic'       , 'Basic'                 , 'Basic'           , 'Basic')
,('MainDBStaging_Map'  , 'Basic'       , 'Basic'                 , 'Basic'           , 'Basic')
,('MainDB_WebDev'      , 'Basic'       , 'Basic'                 , 'Basic'           , 'Basic')

-- If there are NEW databases, set them up as basic/basic
Insert into @PricingTierTarget (DBName,ScaleUpEdition,ScaleUpServiceObjective,ScaleDownEdition,ScaleDownServiceObjective)
Select Name, 'Basic'       , 'Basic'                 , 'Basic'           , 'Basic'
From sys.databases 
Where 
    Name not in (Select DBName from @PricingTierTarget)
    and Name <> 'Master'

-- Get what the databases are currently set to - the current Edition and Service Objective
Update PricingTierTarget
Set 
    CurrentEdition = slo.Edition
    ,CurrentServiceObjective = slo.Service_Objective
from @PricingTierTarget PricingTierTarget
    Join sys.databases db
        on db.Name = PricingTierTarget.DBName
    Join sys.database_service_objectives slo    
        on db.database_id = slo.database_id 
        
-- Loop through the databases, finding those databases that need to be altered because they are not
-- at the appropriate pricing tier 
If @ScalingMode = 'ScaleUp' Begin
    Update @PricingTierTarget
    Set
        TargetEdition = ScaleUpEdition
        ,TargetServiceObjective = ScaleUpServiceObjective
        ,NeedAlterDatabase = 1
    Where 
        CurrentEdition <> ScaleUpEdition
        or 
        CurrentServiceObjective <> ScaleUpServiceObjective
End

else if @ScalingMode = 'ScaleDown' Begin
    Update @PricingTierTarget
    Set
        TargetEdition = ScaleDownEdition
        ,TargetServiceObjective = ScaleDownServiceObjective
        ,NeedAlterDatabase = 1
    Where 
        CurrentEdition <> ScaleDownEdition
        or 
        CurrentServiceObjective <> ScaleDownServiceObjective
End

-- Start setting up @Message
Select @Message = @NewLine + @NewLine + 'Current time in Central Standard time zone: ' + convert(varchar(20), @Now) + @NewLine 

-- Generate the Alter Database script where NeedAlterDatabase is True
Select @SQL = @SQL + 
    'Alter database ' + DBName + ' modify (Edition = ' 
        + @SingleQuote + TargetEdition + @SingleQuote 
        + ',SERVICE_OBJECTIVE = ' 
        + @SingleQuote +  TargetServiceObjective + @SingleQuote + ')'
        + @NewLine
From @PricingTierTarget
Where 
    NeedAlterDatabase = 1

If @@Rowcount > 0 Begin    
    -- If databases exist that need their pricing tier changed, print a message and run the alter database script
    Select @Message = @Message + 'Running SQL: ' + @NewLine + @SQL
    Print @Message
    Exec sp_executesql @SQL

End
Else Begin
    -- No databases need to be changed - just print message
    Select @Message = @Message + 'No databases need to have their pricing tier changed'
    Print @Message
end