Azure Automation Runbooks are useful for a vast amount of tasks. One of those is downscaling and upscaling multiple Azure SQL Databases at specific times, but what if you need to prevent one or more databases from performing that task?

The why

In this specific example we got one Azure SQL Server named azfsql01 with a staging area, a data mart and a data warehouse. During the evening the whole ETL process runs so we scale up the databases for full performance to finish before the next workday. Once the ETL process is finished we can scale down the databases again to save on costs. But sometimes it can be a wish from the Business Intelligence department to prevent both downscaling and upscaling of one or more databases for a certain period of time. One example for this case is a failure in the ETL process at night for which it has to be rerun in the morning.

The how

But how can you achieve that with a runbook? So one of the options is to use a simple SQL table with only one value of either True (Deny run) or False (Allow run). This table is read by the runbook before executing the normally taken actions. I’ll explain the process below.

Create a SQL login and user

In order to allow the runbook to verify the table value lets create a SQL login and user first.

/* APPLY ONCE - CREATE LOGIN */
CREATE LOGIN sa_disablerunbooks WITH PASSWORD = ‘P@ssw0rdP@ssw0rd’
GO
/* Apply to DM, DWH and STAGING DB - CREATE USER */
CREATE USER sa_disablerunbooks FROM LOGIN sa_disablerunbooks;
GO
Create a table

Next we’ll create the table with a predefined value

/* Apply to DM, DWH and Staging DB - CREATE TABLE */
CREATE TABLE DisableRunbooks (
Value BIT NOT NULL
);
/* Default value is 0 as in false. 1 would equal true. */
INSERT INTO DisableRunbooks (Value)
VALUES (0)
Grant select permissions on the table

The previously created user will be given select permissions on the table. The table will be used just for the Disable Runbook purpose. This allows the employee to set the table value to True or False.

/* Apply to DM, DWH and STAGING DB - GRANT SELECT PERMISSION */
GRANT SELECT ON DisableRunbooks TO sa_disablerunbooks
GO
Set Azure automation credentials

Next set the sa_disablerunbooks credential within the Automation Account which you’ll later use in the runbook for checking against the SQL table.

automation-runbook-actions-01
Configure the automation runbook

Then we set our Powershell automation runbook to first check the value of the DisableRunbooks table and depending on the outcome perform the required actions. The code below only shows the DM part but feel free to get creative using it for multiple databases.

# Get the Azure automation credentials 
$sqlCredential = Get-AutomationPSCredential -Name “DisableRunbooks”

# Connect to the Datamart database object
$dmConn = New-Object System.Data.SqlClient.SqlConnection(Server=tcp:azfsql01.database.windows.net,1433;Initial Catalog=AZF-SDB-DM;Persist Security Info=False;User ID=$($sqlCredential.UserName);Password=$($sqlCredential.GetNetworkCredential().Password);MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;)
$dmConn.Open()

# Get the DisableRunbooks value from the table
$dmCheckValue = New-Object system.Data.SqlClient.SqlCommand(SELECT Value from DisableRunbooks, $dmConn)
$dmDs=New-Object system.Data.DataSet
$dmDa=New-Object system.Data.SqlClient.SqlDataAdapter($dmCheckValue)
[void]$dmDa.fill($dmDs)
$dmDisableRunbook = $dmDs.Tables.Value
$dmConn.Close() 

# Scale up the Datamart database when the DisableRunbooks value is false
If ($dmIgnored -eq "True"){Write-Output "Database AZF-SDB-DM set to be ignored. No further actions performed for this database."}
Else {Set-AzureRmSqlDatabase -ResourceGroupName “AZF-RSG-DWH” `
-DatabaseName “AZF-SDB-DM” `
-ServerName “azfsql01” `
-RequestedServiceObjectiveName “S3” }

Synopsis

There you have it. The BI employee has control over the execution of the runbook simply by modifying the DisableRunbook table value. Do you find this useful for other purposes? Make sure to let me know and give feedback.

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: