PowerShell Script For Data Warehouse
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.
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.
Automation Account
You will see a screen like below.
Create Credentials
Now, it’s time to create the credentials of the Automation Account. You will find it under ‘Shared Resources’ as shown below.
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.
Connections
Under Connection, you will find ‘AzureRunASconnection’ and information about the Azure environment like ApplicationID, TenantId, SubscriptionID etc.
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).
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’.
And it will open the following window.
Paste the attached script and click the Test Pane.
The Test Pane will open like below.
Hit the Start button.
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!
