Using Azure Automation to Pause SQL Data Warehouse

Continuing on with the theme of automation from my last post, I wanted to take a few minutes and talk about Azure SQL Data Warehouses (SQLDW). Back in November, with the release of SQL Sentry v9, we added support for monitoring SQLDW (post by David Benoit (b|t)).

SQL Sentry Azure SQL Data Warehouse Dashboard

Part of my duties here at SQL Sentry include helping to maintain our Azure environment, which includes multiple Azure SQL Data Warehouses. We use and monitor multiple Azure SQL Data Warehouses for development, testing, QA, and more. While having multiple instances of SQLDW is great, it can get expensive very quickly, especially if someone leaves one or more instances up and running over the weekend when they are not being used. 🙂

This is where automation comes to the rescue again! Most of our SQLDWs can be paused after 6:00 PM on weekdays, as well as the entire weekend. Now, I could manually go and pause each individual SQLDW at the end of the day, but what happens if I have plans for dinner or something else during that time? I decided that I needed an automated process to check each SQLDW and pause it if it is running. Using Azure Automation and Azure Runbooks, I was able to create a scheduled task that looks for any running SQLDW and pauses it.

Here are the basic steps to implement the automated solution I came up with:

1. Create a credential in your automation account with access to all SQL Data Warehouses.
2. Create a PowerShell Workflow Runbook with the code below.
3. Create and link the schedule(s) needed for it to run.

The code for the PowerShell Workflow Runbook is liberally commented:

workflow Pause-All-Datawarehouses
{
  $CredentialName = "SQLDW-Cred"
 
  # Get the credential with the above name from the Automation Asset store
  $psCred = Get-AutomationPSCredential -Name $CredentialName
 
  if(!$psCred) {
    Throw "Could not find an Automation Credential Asset named '${CredentialName}'.    
           Make sure you have created one in this Automation Account."
  }
 
  # Login using the above Credential
  Login-AzureRmAccount -Credential $psCred
 
  # Get all SQL DWs in the subscription
  $dws = Get-AzureRmResource | 
    Where-Object ResourceType -EQ "Microsoft.Sql/servers/databases" | 
    Where-Object Kind -ILike "*datawarehouse*"
 
  # Loop through each SQLDW
  foreach($dw in $dws)
  {
    $dwc = $dw.ResourceName.split("/")
 
    # splat reused parameter lists
    $ThisDW = @{
      'ResourceGroupName' = $dw.ResourceGroupName
      'ServerName' = $dwc[0]
      'DatabaseName' = $dwc[1]
    }
 
    $status = Get-AzureRmSqlDatabase @ThisDW | Select Status
 
    # Check the status
    if($status.Status -ne "Paused")
    {
      # If the status is not equal to "Paused", pause the SQLDW
      Suspend-AzureRmSqlDatabase @ThisDW
    }    
  }
}

There are four main parts to the code listed above. The first part retrieves the credential specified, and then logs into Azure using it. After logging in it then looks for any Azure SQLDWs in the subscription. Finally, for each SQLDW it finds, it looks at its status. If the status is not "Paused", it pauses it.

There are many ways that you could alter this script to fit your specific needs. You could add a filter for a specific Resource Group or Server to the Get-AzureRmResource call to limit it to a specific set of SQLDWs. You could also change the Suspend-AzureRmSqlDatabase to Resume-AzureRmSqlDatabase to start up one or more SQLDWs when necessary.

With this simple script and the power of Azure Automation, I am now able to make sure all of our Azure SQL Data Warehouses are paused when they should be, while still having my evenings free to spend time with my family! Hopefully you’ve realized the power of automation by now and have started automating your own environments.

If you want this script in your own environment, I have made it available in the TechNet Gallery. To avoid any formatting issues that may be caused by WordPress please download the full script from the TechNet Gallery link provided.

Let me know what you think! Leave me a comment below, and if you’re at SQLintersection next month, stop by our booth and say hi!

Brian Davis

Brian (@brian78) is a Product Integration Engineer at SentryOne, where he is responsible for a multitude of projects as they relate to strategic partners. He manages multiple environments and is responsible for the automation of the SentryOne platform, as well as cloud projects, including migration and deployment. Brian also analyzes the processes and tools for the development and delivery of our products and makes recommendations on how they can be improved. Having spent over 15 years in the IT industry serving in a multitude of roles from Support to Application Developer, DBA, SQL Developer, and Consultant, Brian is well suited to such a position that crosses many boundaries. Brian is also a Regional Mentor for PASS as well an active member of the Ohio North SQL Server User Group.