Scaling an Azure SQL Database

Azure SQL Single Database SKUs

Note: Prices will vary over time and depending on your agreement with Microsoft. The key takeaway here is that costs go up as performance goes up.

Note: For simplicity on the per hour value, a month is calculated as 30days or 720hrs.

Note: For simplicity vCore based purchasing and Elastic-Pool configurations have been omitted

Lets just assume you need good performance for your sales team during the day, they need an S4 instance with 200 DTUs.

From 6pm to 6am, the phone lines are closed and only management are likely to be tinkering.

Running an S4 instance 24/7 would cost around $368.08/m or $4417/y

If you scaled down to an S0 overnight, you would save $174.84/m or $2098/y

Just under 50% saving!

Lets just assume you have a data-refresh job that takes 24hrs on an S0 instance with 10DTUs, the rest of the time your website pulls data every now and again.

If you ran the same job on an S6 instance with 400DTUs, in theory it should take around 40 minutes. (it's not an exact science)

An S6 instance would cost you $1.02 for the hour's uplift.

That kind of shift makes the impossible possible!

Running an S6 instance 24/7 would cost $8834/y

Running an S0 instance 23/7 and an S6 for 1hr per day would cost $578/y

The key piece of TSQL code looks as simple as this:

ALTER DATABASE [DBName] MODIFY (SERVICE_OBJECTIVE = 'S6');

You will need to wrap this code in some error checking if running it automatically.

You may also need to wait until the scaling process has completed. The SQL command will return instantly.

The following code wraps some basic error checking around the scaling code.

The code scales the SQL Database to an S6 instance, does a resource intensive task (SELECT @@VERSION) and then reverts the database to it's original SKU.

In production you would want to add additional error checking, logging, exception handling etc. You wouldn't want to be stuck on a P15 SQU until the bill drops on your doormat.

--Setup Variables
DECLARE @DBName VARCHAR(128) = 'SalesDB'
DECLARE @NewObjective VARCHAR(100) = 'S6'

-------------------------------------------------

--Show Setup Messages
DECLARE @DBNameMessage VARCHAR(100) = 'Database: ' + @DBName
RAISERROR (@DBNameMessage, 0, 1) with nowait;

DECLARE @TargetMessage VARCHAR(100) = 'Target Service Objective: ' + @NewObjective
RAISERROR (@TargetMessage, 0, 1) with nowait;

DECLARE @CurrentServiceObjective VARCHAR(100)
DECLARE @CurrentServiceObjectiveMessage VARCHAR(100)
SELECT	 @CurrentServiceObjective = so.service_objective
		,@CurrentServiceObjectiveMessage = 'Current Service Objective: ' + so.service_objective
FROM	sys.databases db
JOIN	sys.database_service_objectives so ON db.database_id = so.database_id
WHERE	db.name LIKE @DBName
RAISERROR (@CurrentServiceObjectiveMessage, 0, 1) with nowait;

BEGIN TRY

	--Don't do anything if the Service Objective is already set
	IF (CASE WHEN @NewObjective LIKE @CurrentServiceObjective THEN 1 ELSE 0 END) = 0 BEGIN
		--------------------------------------------------------------- 
		--Scale the Database
		DECLARE @sql1 VARCHAR(1000) = 'ALTER DATABASE [' + @DBName + '] MODIFY (SERVICE_OBJECTIVE = ''' + @NewObjective + ''');'
		EXEC (@sql1);
		RAISERROR ('Scale Started', 0, 1) with nowait;
		---------------------------------------------------------------

		--Wait for the Scale process to complete
		WHILE (not exists (
				 SELECT	 db.name
						,so.database_id
						,so.edition						--Basic, Standard, Premium, Data Warehouse
						,so.elastic_pool_name
						,so.service_objective			--Basic,  ElasticPool, S0, S1, S2, S3, S4, S6, S7, S9, S12, P1, P2, P4, P6, P11, P15, DW100 - DW30000c
				FROM	sys.databases db
				JOIN	sys.database_service_objectives so ON db.database_id = so.database_id
				WHERE	db.name LIKE @DBName
				AND		so.service_objective LIKE @NewObjective
			))
		BEGIN
			WAITFOR DELAY '00:00:02';
		END

		--Show Completion Messages
		RAISERROR ('Scale Completed', 0, 1) with nowait;

		DECLARE @NewServiceObjective VARCHAR(100)
		SELECT	@NewServiceObjective = 'New Service Objective: ' + so.service_objective
		FROM	sys.databases db
		JOIN	sys.database_service_objectives so ON db.database_id = so.database_id
		WHERE	db.name LIKE @DBName
		RAISERROR (@NewServiceObjective, 0, 1) with nowait;
	END
	ELSE BEGIN
		--Show Message to say nothing was changed
		DECLARE @SameSameMessage VARCHAR(100) = 'Target Service Objective abd Current Service Objective are the same ('+ @CurrentServiceObjective + '). No Scaling Done!'
		RAISERROR (@SameSameMessage, 0, 1) with nowait;
	END

	--------------------------------------------------------------- 
	---------------------------------------------------------------
	-- Do a resource intensive process that will make use of your new found powers
	-- Refresh Data / Process invoicing etc.
	BEGIN TRY
		SELECT @@VERSION
	END TRY
	BEGIN CATCH
		DECLARE @ProcessErrorMessage VARCHAR(MAX) = (SELECT 'Process Error: ' + ERROR_MESSAGE())
		RAISERROR (@ProcessErrorMessage, 0, 1) with nowait;
	END CATCH
	---------------------------------------------------------------
	--------------------------------------------------------------- 


	--------------------------------------------------------------- 
	--Revert the Database
	DECLARE @sql2 VARCHAR(1000) = 'ALTER DATABASE [' + @DBName + '] MODIFY (SERVICE_OBJECTIVE = ''' + @CurrentServiceObjective + ''');'
	EXEC (@sql2);
	RAISERROR ('Scale Back Started', 0, 1) with nowait;
	---------------------------------------------------------------

	--Wait for the Scale process to complete
	WHILE (not exists (
			 SELECT	 db.name
					,so.database_id
					,so.edition						--Basic, Standard, Premium, Data Warehouse
					,so.elastic_pool_name
					,so.service_objective			--Basic,  ElasticPool, S0, S1, S2, S3, S4, S6, S7, S9, S12, P1, P2, P4, P6, P11, P15, DW100 - DW30000c
			FROM	sys.databases db
			JOIN	sys.database_service_objectives so ON db.database_id = so.database_id
			WHERE	db.name LIKE @DBName
			AND		so.service_objective LIKE @CurrentServiceObjective
		))
	BEGIN
		WAITFOR DELAY '00:00:02';
	END

	--Show Completion Messages
	RAISERROR ('Scale Back Completed', 0, 1) with nowait;

	DECLARE @FinalServiceObjectiveMessage VARCHAR(100)
	SELECT	@FinalServiceObjectiveMessage = 'Final Service Objective: ' + so.service_objective
	FROM	sys.databases db
	JOIN	sys.database_service_objectives so ON db.database_id = so.database_id
	WHERE	db.name LIKE @DBName
	RAISERROR (@FinalServiceObjectiveMessage, 0, 1) with nowait;

END TRY
BEGIN CATCH
	DECLARE @ScalingErrorMessage VARCHAR(MAX) = (SELECT 'Scaling Error: ' + ERROR_MESSAGE())
	RAISERROR (@ScalingErrorMessage, 0, 1) with nowait;
END CATCH

The Simple PowerShell to Scale a Database is:

Set-AzSqlDatabase -ResourceGroupName "ResourceGroup01" -DatabaseName "Database01" -ServerName "Server01" -Edition "Standard" -RequestedServiceObjectiveName "S6"

A list of useful SQL PowerShell Commands can be found here: set-azsqldatabase