Let’s save cost of *Azure Data Warehouse* by AUTO PAUSING

Introduction

If you want to use Azure Data Warehouse for a couple of hours/days and want to save cost, 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!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s