Pause Azure SQL Data Warehouse(s) with Azure Automation

Folks it as been a while… You are knee deep in your Development and Testing activities and you just noticed that you have forgotten to Pause your Azure SQL Data Warehouse(s)… while you were not using it! Thus incurring significant costs towards your next Azure billing cycle. This post will quickly show you how to use Azure Automation and PowerShell in order to remediate this. You may not have access to create an Azure Automation service, but you can share this with your Azure Administrator and/or DevOps Team Lead.

Context: Azure SQL Data Warehouse a massively parallel processing (MPP) architecture, separates compute from storage which enables you to scale compute independently of the data in your system. I recommend you read and watch the following SQL Data Warehouse Architecture to lear more.

With decoupled storage and compute, SQL Data Warehouse can:

• Independently size compute power irrespective of your storage needs.
• Grow or shrink compute power without moving data.
• Pause compute capacity while leaving data intact, so you only pay for storage.
• Resume compute capacity during operational hours.

Let’s get started…
First you will need to download and save a local copy of the following PowerShell script “PauseAzureSQLDataWarehouses.ps1” from GitHub (Gist)

Next you need to create an Automation Account: Management Tools - Automation
*****It’s important that “Yes” is selected (default) for “Create Azure Run As account”. Run As accounts are used to provide authentication for managing resources in Azure with the Azure cmdlets.

Once your new resource is deployed, you will need to add a new Runbook: Runbooks - Add a runbook.

Select: “Import an existing runbook”, in “Runbook file” locate the PowerShell script you downloaded earlier to your local environment, give your runbook a name and the select “Create”.

Your new runbook should appear in the list with a Authoring Status of New, click on (select) your new runbook (PauseAzureSQLDataWarehouses).

Next select “Edit”.

And select “Publish” to publish your runbook.

This will bring you back to your runbook where you can now either “Start” (run) or create a “Schedule”.
To test this out, make sure you have at least one SQL Data Warehouse running, if so select “Start”.

This will bring you to the Job summary page, once the job completes you will be able to view the output that will outline the SQL Data Warehouse(s) that have been paused. Check to see if your running instance(s) of SQL Data Warehouse(s) have been paused 😉

To schedule a runbook: Schedule - Link a schedule to you runbook - Create a new schedule. Fill out the required information and select “Create”.

That’s it. This will ensure that all of your SQL Data Warehouses are paused and not incurring any compute cost when you are not using them.
Enjoy!