Pausing Azure SQL Data Warehouse using an Automation Runbook

  • Article

When a Azure SQL Data Warehouse is paused, all running queries are canceled. If there are queries running that have data movement, rollback operations will likely happen. These rollbacks can take many hours to complete. While these rollback operations are running, the service is not paused. In many cases, allowing the current actions to complete would prevent lost productivity. This runbook does a simple check to see if queries are running before trying to pause the Azure SQL Data Warehouse. If after you call this runbook and the service is still running, it would be recommended to investigate and see what is running then take the needed actions to properly pause the service.

How you use this Runbook

  • First you will need an Azure Automation Account created in your subscription.
  • Second, once you are in your Automation account, go to the Runbooks Gallery and select TechNet Gallery
  • Search for SuspendOrPauseAzureSQLDataWarehouse.
  • Import the runbook.

Implementation Details

There are a few things that are needed when running this runbook:

  1. By default when an Automation Account is created, it creates an AzureRunAsConnection connection that will be used to connect to your Azure resources. You need to make sure that the connection you use if a custom one is created and passed that it has the access to pause the Azure SQL Data Warehouse. The Azure SQL Data Warehouse must have the "Allow access to Azure services" on.
  2. Under the Automation account Credentials blade, you will need to add credentials for a SQL account that will be able to access the Azure SQL Data Warehouse to see if queries are running.
  3. The full name of the SQL Server that is hosting the Azure SQL Data Warehouse. Ex: sqlserver.database.windows.net
  4. And finally the name of the Azure SQL Data Warehouse that you are wanting to suspend / pause.

The AzureRunAsConnection is used to gain access to your Azure Account and the SQL account is used to query the data warehouse to determine if it is safe to pause the data warehouse at this time. If you have more than one Azure SQL Data Warehouse, you can call this runbook to suspend one of them at a time just by passing the proper parameters. Also, Azure Automation will allow runbooks to be ran on a schedule or called via a webhook that allows for further automation.

The script is on Github and on the TechNet Gallery

Thank you to Scott Epperly and Tim Salch, for helping with this solution!