I only want to use my Azure Data Warehouse for a couple of hours per day. I want to automatically pause and resume my Azure DWH to minimize the Azure costs. How do I do that in Azure?
 |
| Pause and Resume Azure Data Warehouse |
Solution
You could solve this with a scripting language like PowerShell and run that PowerShell script each morning and evening with SQL Server Agent or Windows Scheduler, but for this solution I will use Azure Runbook with its scheduler. So the entire solution runs in Azure.
* Update Oct 2 2020: scripts with AZ modules *
1) Automation Account
First we need an Azure Automation Account to run the Runbook. If you don't have one or want to create a new one, then search for Automation under Monitoring + Management and give it a suitable name, then select your subscription, resource group and location. For this example I will choose West Europe since I'm from the Netherlands.
2) Credentials
Next step is to create Credentials to run this runbook with. This works very similar to the Credentials in SQL Server Management Studio. Go to the Azure Automation Account and click on Credentials in the menu. Then click on Add New Credentials. You could just use your own Azure credentials, but the best options is to use a service account with a non-expiring password. Otherwise you need to change this regularly.
3) Connections
This step is for your information only and to understand the code. Under Connections you will find a default connection named 'AzureRunAsConnection' that contains information about the Azure environment, like the tendant id and the subscription id. To prevent hardcoded connection details we will retrieve these fields in the PowerShell code.
4) Variables
An other option to prevent hardcoded values in your PowerShell code it to use Variables. We will use this option to provide the Resource Group, SQL Server Name and Database Name. Go to Variables and add a new variable for ResourceGroupName and add the name of the Resource Group that is used by your Azure DB. Then repeat this for ServerName (name only without .database.windows.net) and DatabaseName (the name of your Azure DWH database).
5) Runbooks
Now it is time to add a new Azure Runbook for the PowerShell code. Click on Runbooks and then add a new runbook (There are also four example runbooks of which AzureAutomationTutorialScript could be useful as an example). Give your new Runbook a suitable name and choose PowerShell as type.
6) Edit Script
After clicking Create in the previous step the editor will we open. When editing an existing Runbook you need to click on Edit to edit the code. You can copy and paste the code below to your editor. Study the green comments to understand the code.
Note 1: This is a very basic script. No error handling has been added. Check the AzureAutomationTutorialScript for an example. Finetune it for you own needs.
Note 2: There are often two versions of an method like Get-AzureRmSqlDatabase and Get-AzureSqlDatabase. Always use the one with "Rm" in it (Resource Managed), because that one is for the new Azure portal. Without Rm is for the old/classic Azure portal.
Note 3: Because Azure Automation doesn't support Out-Null I used an other trick with the $null =. However the Write-Outputs are for testing purposes only. Nobody sees them when they are scheduled.
Note 4: The code the Resume is commented out with a hashtag. Either use the two suspend rows or the two pause rows.
7) Testing
You can use the Test Pane menu option in the editor to test your PowerShell scripts. When clicking on Run it will first Queue the script before Starting it. Running takes several minutes (a bit longer then changing the tier).
8) Publish
When your script is ready, it is time to publish it. Above the editor click on the Publish button. Confirm overriding any previously published versions.
9) Schedule
And now that we have a working and published Azure Runbook, we need to schedule it. Click on Schedule to create a new schedule for your runbook. For this pause script I created a schedule that runs every working day on 9:00PM (21:00). The resume script could for example be scheduled on working days at 7:00AM. Now you need to hit the refresh button in the SQL Databases overview in Azure to see if it really works. It takes a few minutes to run, so don't worry too soon.
Summary
In this post you saw how you can pause and resume your Azure Data Warehouse to save some money in Azure during the quiet hours. The screenshots only show the pause script, but the resume script is commented out. For pause and resume you have a couple of options. You could create one script with a parameter that indicates a pause or resume. An other option is to use a fancy if-construction that uses the current status and/or time to decide whether you need to pause or to resume. Or just create two separate scripts for pause and resume.
1) Automation Account
First we need an Azure Automation Account to run the Runbook. If you don't have one or want to create a new one, then search for Automation under Monitoring + Management and give it a suitable name, then select your subscription, resource group and location. For this example I will choose West Europe since I'm from the Netherlands.
 |
| Azure Automation Account |
2) Credentials
Next step is to create Credentials to run this runbook with. This works very similar to the Credentials in SQL Server Management Studio. Go to the Azure Automation Account and click on Credentials in the menu. Then click on Add New Credentials. You could just use your own Azure credentials, but the best options is to use a service account with a non-expiring password. Otherwise you need to change this regularly.
 |
| Create new credentials |
3) Connections
This step is for your information only and to understand the code. Under Connections you will find a default connection named 'AzureRunAsConnection' that contains information about the Azure environment, like the tendant id and the subscription id. To prevent hardcoded connection details we will retrieve these fields in the PowerShell code.
 |
| Azure Connections |
4) Variables
An other option to prevent hardcoded values in your PowerShell code it to use Variables. We will use this option to provide the Resource Group, SQL Server Name and Database Name. Go to Variables and add a new variable for ResourceGroupName and add the name of the Resource Group that is used by your Azure DB. Then repeat this for ServerName (name only without .database.windows.net) and DatabaseName (the name of your Azure DWH database).
 |
| Add variables |
5) Runbooks
Now it is time to add a new Azure Runbook for the PowerShell code. Click on Runbooks and then add a new runbook (There are also four example runbooks of which AzureAutomationTutorialScript could be useful as an example). Give your new Runbook a suitable name and choose PowerShell as type.
 |
| Add Azure Runbook |
6) Edit Script
After clicking Create in the previous step the editor will we open. When editing an existing Runbook you need to click on Edit to edit the code. You can copy and paste the code below to your editor. Study the green comments to understand the code.
 |
| Edit the PowerShell code |
# PowerShell code # Don't continue in case of an error $ErrorActionPreference = "Stop" # Connect to a connection to get TenantId and SubscriptionId $Connection = Get-AutomationConnection -Name "AzureRunAsConnection" $TenantId = $Connection.TenantId $SubscriptionId = $Connection.SubscriptionId # Get the service principal credentials connected to the automation account. $SPCredential = Get-AutomationPSCredential -Name "SSISJoost" # Login to Azure ($null is to prevent output, since Out-Null doesn't work in Azure) Write-Output "Login to Azure using automation account 'SSISJoost'." $null = Login-AzureRmAccount -TenantId $TenantId -SubscriptionId $SubscriptionId -Credential $SPCredential # Select the correct subscription Write-Output "Selecting subscription '$($SubscriptionId)'." $null = Select-AzureRmSubscription -SubscriptionID $SubscriptionId # Get variable values $ResourceGroupName = Get-AutomationVariable -Name 'ResourceGroupName' $ServerName = Get-AutomationVariable -Name 'ServerName' $DatabaseName = Get-AutomationVariable -Name 'DatabaseName' # Get old status (for testing/logging purpose only) $OldDbSetting = Get-AzureRmSqlDatabase -DatabaseName $DatabaseName -ServerName $ServerName -ResourceGroupName $ResourceGroupName $OldStatus = $OldDbSetting.Status #Write-Output "Resuming $($DatabaseName). Current status: $($OldStatus)" #$null = Resume-AzureRmSqlDatabase -DatabaseName $DatabaseName -ServerName $ServerName -ResourceGroupName $ResourceGroupName Write-Output "Resuming $($DatabaseName). Current status: $($OldStatus)" $null = Suspend-AzureRmSqlDatabase -DatabaseName $DatabaseName -ServerName $ServerName -ResourceGroupName $ResourceGroupName # Show done when finished (for testing/logging purpose only) Write-Output "Done"
Note 1: This is a very basic script. No error handling has been added. Check the AzureAutomationTutorialScript for an example. Finetune it for you own needs.
Note 2: There are often two versions of an method like Get-AzureRmSqlDatabase and Get-AzureSqlDatabase. Always use the one with "Rm" in it (Resource Managed), because that one is for the new Azure portal. Without Rm is for the old/classic Azure portal.
Note 3: Because Azure Automation doesn't support Out-Null I used an other trick with the $null =. However the Write-Outputs are for testing purposes only. Nobody sees them when they are scheduled.
Note 4: The code the Resume is commented out with a hashtag. Either use the two suspend rows or the two pause rows.
7) Testing
You can use the Test Pane menu option in the editor to test your PowerShell scripts. When clicking on Run it will first Queue the script before Starting it. Running takes several minutes (a bit longer then changing the tier).
 |
| Testing the script in the Test Pane |
8) Publish
When your script is ready, it is time to publish it. Above the editor click on the Publish button. Confirm overriding any previously published versions.
 |
| Publish the Runbook |
And now that we have a working and published Azure Runbook, we need to schedule it. Click on Schedule to create a new schedule for your runbook. For this pause script I created a schedule that runs every working day on 9:00PM (21:00). The resume script could for example be scheduled on working days at 7:00AM. Now you need to hit the refresh button in the SQL Databases overview in Azure to see if it really works. It takes a few minutes to run, so don't worry too soon.
 |
| Add Schedule |
Summary
In this post you saw how you can pause and resume your Azure Data Warehouse to save some money in Azure during the quiet hours. The screenshots only show the pause script, but the resume script is commented out. For pause and resume you have a couple of options. You could create one script with a parameter that indicates a pause or resume. An other option is to use a fancy if-construction that uses the current status and/or time to decide whether you need to pause or to resume. Or just create two separate scripts for pause and resume.
