Using Azure Automation to Pause SQL Data Warehouse

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 DashboardSQL 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!

Comments ( 4 )

  • Steven Edouard says:

    This is an awesome post. For folks not interested in maintaining Azure Automation scripts, you can do this with a calendar interface using VMPower, a 3rd party automation tool.

  • john says:

    I don't know if this helps anyone but this error was happening for me.

    To solve it you just need to make sure the credential is called SQLDW-Cred and that it can access the resources it needs to

  • Brendan Roche says:

    Also getting Aqib's error, would love to see if you are getting the same issue. Thanks

  • Aqib says:

    Hi,
    I am getting this error after execution:
    Get-AzureRmResource : Object reference not set to an instance of an object
    Please help!

Leave A Comment

Your email address will not be published.

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