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
