Schedule Pause /Resume For Your Azure Data Warehouse

Introduction

If you want to use Azure Data Warehouse for a couple of hours/days, you can certainly do that by *AUTOMATICALLY PAUSING* your Data warehouse. This will minimize the Azure Cost to a great extent.

In this article, I will explain the nitty gritty of the process step by step. You just need to replicate the steps at your Azure Environment.

Schedule Pause - Resume for your Azure Data Warehouse 
Schedule Pause & Resume

Prerequisites

  • Azure Subscription.
  • Azure SQL Server and Azure Data Warehouse Database

Let’s start!

I am going to use *Azure RunBook* and *Scheduler*, so everything will be under the Azure environment. Let’s start now.

Create Automation Account

We need to create an Azure Automation account for RunBook; you could use your existing automation account as well. Please give a suitable name, choose a subscription, Resource Group, and a location. Please choose the location as per your geography. As I’m from Canada, I choose Central Canada and hit Create, as shown below.

Schedule Pause - Resume for your Azure Data Warehouse 
Automation Account

You will see a screen like below.

Schedule Pause - Resume for your Azure Data Warehouse 
 
Create Credentials

Now, it’s time to create the credentials of the Automation Account. You will find it under ‘Shared Resources’ as shown below.

Schedule Pause - Resume for your Azure Data Warehouse 

Click on Credentials >> ‘Add New Credentials’. I prefer to use a service account with a non-expiring password. Otherwise, you need to change the password frequently.

Schedule Pause - Resume for your Azure Data Warehouse 
Schedule Pause - Resume for your Azure Data Warehouse
 
Connections

Under Connection, you will find ‘AzureRunASconnection’ and information about the Azure environment like ApplicationID, TenantId, SubscriptionID etc.

Schedule Pause - Resume for your Azure Data Warehouse 
 
Variables

I will use the below variables to prevent Hardcoded values:

  • ResourceGroupName
  • ServerName
  • DatabaseName

Please note that we have to use the EXACT name in the POWERSHELL, so please add a new variable for ResourceGroupName and add the name of the Resource Group that is used by your Azure Database.

Now, add Servername (name without .database.windows.net)

And finally, the DatabaseName (the name of your Azure DWH database).

Schedule Pause - Resume for your Azure Data Warehouse 

Usually, here we make mistakes with the name. Please be extra aware of that so as to save your debugging time.

Create Runbooks

Now, go to runbook and click on ‘Create a new runbook’. Give your Runbook a suitable name and Runbook type as ‘PowerShell’.

Schedule Pause - Resume for your Azure Data Warehouse 

And it will open the following window.

Schedule Pause - Resume for your Azure Data Warehouse 

Paste the attached script and click the Test Pane.

Schedule Pause - Resume for your Azure Data Warehouse 

The Test Pane will open like below.

Schedule Pause - Resume for your Azure Data Warehouse 

Hit the Start button. 

Schedule Pause - Resume for your Azure Data Warehouse 

The PowerShell script is as per my need and I put in my comments to make it easily understandable. However, you could modify it as per your business need. 

Thanks for your time, I hope it will be helpful for you guys!

Keep Learning!


IotCoast2Coast
Improving the World of Tomorrow :- We offer application development and support services.