Automating SQL Elasticity on Azure

The Problem

Traditionally within your on-premises data centre you might have over/under allocated resources to host your SQL environments. This would result in upfront costs to purchase over spec'd hardware that didn't get used efficiently or you might have potentially under allocated resources, resulting in unsatisfied customers waiting for their database transactions to complete over longer periods of time.

Within Azure you might have come across a PaaS solution called SQL Elastic Pool that allows you to scale up and down the allocated resources. But something that is missing from the inbuilt features is the ability to schedule the scaling up / down of either eDTU or vCores assigned to your pool.

For example, a SQL batch process I recently discovered heavily utilised CPU resources during the evenings only. On weekends, the elastic pool mostly sat idle and during the working day it only had small spikes in CPU usage.

The Solution

To optimise this SQL elastic pool to reduce the overall evening batch processing times and to save on costs when the pool was under-utilised during the working day and on weekends, I wrote a PowerShell runbook that was aligned to a schedule within an Azure Automation Account to change the eDTU value to either 50 (weekends), 200 (working day) or 800 (during the batch process).

PowerShell Script:

Param
	[string]$automationAccount,
? ??
	[Parameter(Mandatory)]
	[ValidateSet('SA','UA')]
? ? [string]$method,
? ??
? ? [string]$UAMI,
	
	[Parameter(Mandatory)]
	[string]$resourceGroup,
? ??
	[Parameter(Mandatory)]
	[string]$serverName,
? ??
	[Parameter(Mandatory)]
	[string]$elasticPoolName,
? ??
	[Parameter(Mandatory)]
	[int]$setDtuSize
)

# Ensures you do not inherit an AzContext in your runbook
Disable-AzContextAutosave -Scope Process | Out-Null


# Connect using a Managed Service Identity
try {
? ? $AzureContext = (Connect-AzAccount -Identity).context
}
catch {
? ? Write-Output "There is no system-assigned user identity. Aborting.";
? ? exit
}


# Set and store context
$AzureContext = Set-AzContext -SubscriptionName $AzureContext.Subscription `
? ? -DefaultProfile $AzureContext
if ($method -eq "SA") {
? ? Write-Output "Using system-assigned managed identity"
}
elseif ($method -eq "UA") {
? ? Write-Output "Using user-assigned managed identity"

? ? # Connects using the Managed Service Identity of the named user-assigned managed identity
? ? $identity = Get-AzUserAssignedIdentity -ResourceGroupName $resourceGroup `
? ? ? ? -Name $UAMI -DefaultProfile $AzureContext
? ??
	
? ? # Validates assignment only, not perms
? ? if ((Get-AzAutomationAccount -ResourceGroupName $resourceGroup `
? ? ? ? ? ? ? ? -Name $automationAccount `
? ? ? ? ? ? ? ? -DefaultProfile $AzureContext).Identity.UserAssignedIdentities.Values.PrincipalId.Contains($identity.PrincipalId)) {
? ? ? ? $AzureContext = (Connect-AzAccount -Identity -AccountId $identity.ClientId).context
? ? ? ??
		
? ? ? ? # Set and store context
? ? ? ? $AzureContext = Set-AzContext -SubscriptionName $AzureContext.Subscription -DefaultProfile $AzureContext
? ? }
? ? else {
? ? ? ? Write-Output "Invalid or unassigned user-assigned managed identity"
? ? ? ? exit
? ? }
}
else {
? ? Write-Output "Invalid method. Choose UA or SA."
? ? exit
}


# Write environment details
Write-Output "elasticPoolName: $elasticPoolName"
Write-Output "serverName: $serverName"
Write-Output "resourceGroup: $resourceGroup"


# Check Dtu
$currentDtuSize = Get-AzSqlElasticPool -ServerName $serverName -ResourceGroupName $resourceGroup | select dtu


# Write Dtu size
Write-Output "Current DTU size: $currentDtuSize"


# Set Dtu size
Set-AzSqlElasticPool -ResourceGroupName $resourceGroup -ServerName $serverName -ElasticPoolName $elasticPoolName -Dtu $setDtuSize -DatabaseDtuMax $setDtuSize


# Retrieve New Dtu size
$newDtuSize = Get-AzSqlElasticPool -ServerName $serverName -ResourceGroupName $resourceGroup | select dtu


# Write Dtu size
Write-Output "New DTU size: $newDtuSize"        

The script was then assigned to a schedule to change the eDTU size based on the peak and off-peak consumption of the elastic pool:

The following parameters were passed into the script:

I also enabled a System assigned identity (as per Microsoft best practice) with minimal RBAC permissions assigned to the resource group of the SQL Elastic Pool to allow changing of the properties as per below:

The script also caters for a User assigned identity if that is your preference within your environment.

The end result:

1. Reduction of the batch processing time from ~60 minutes to under ~30 minutes.
2. An efficiency gain over the course of the working week, whereby the team supporting the batch process could focus on other tasks, due to the batch process halving in time.

I hope this might be useful for you in optimising your SQL Elastic Pool environments and to leverage the power of elasticity within the Azure Cloud.

Until next time ... :-)