Azure SQL Database scheduled autoscaling

One of the major benefits of Azure is scalability, the cloud has been built to be elastic to be as cost-effective as possible. Azure solutions can be scaled in ways that would never be possible with your own hardware. You are able to scale up and out when the demand arises, and, more importantly, you can scale down and in when you don’t need all the resources anymore, thereby saving a lot of money.

Autoscaling based on a schedule allows you to scale your solution according to predictable resource demand. For example you could require the highest capacity on Monday during peak hours, while the rest of the week the traffic is decreased allowing you to scale down. Outside business hours and during weekends you could then scale down further to the absolute minimum.

Azure SQL Database currently offers four service tiers: Basic, Standard, Premium, and Premium RS. Databases are isolated from each other and portable. You can change between tiers easily and without downtime.
Azure SQL database tiers and performance levels


Scheduled autoscaling with Azure Automation

To automate scaling of Azure SQL Databases based on a schedule I have created an Azure Automation PowerShell Runbook called AzureSQLDatabaseScheduledAutoScaling. You can easily import this runbook into your Azure Automation account and then execute it against your database by entering values for a few parameters that specify your database details, your Automaton Run As Account and a database-specific scaling schedule. A step-by-step guide can be found at the bottom of this post.

Scheduling of the runbook itself can be done by creating a recurring hourly Automation Schedule. A single runbook can have multiple jobs running at one time. This means it is possible to create multiple schedules for the AzureSQLDatabaseScheduledAutoScaling runbook, enabling scaling of multiple databases simultaneously.

Scaling of the database will only be performed in case the runbook execution moment matches a day/time slot specified in the scaling schedule parameter. If the runbook executes and the database is in the desired edition/tier already, no work will be done. Don’t worry about the costs of Azure Automation, the first 500 run minutes are free and after that you pay €0.002 / minute (as per september 2017).

Azure Automation Runbook Architecture

When you start using this solution, keep the following in mind:

  • The length of time for a switch of edition/tier varies, but is generally under 4 seconds, and under 30 seconds in 99% of the time. The duration of the entire scale process depends on both the size and service tier of the database before and after the change. For example a 250 GB database that is changing to, from, or within a Standard service tier, should complete within 6 hours. For a database of the same size that is changing performance levels within the Premium service tier, it should complete within 3 hours.
  • Behind the scenes, changing the edition/tier of a database creates a replica of the original database at the new edition/tier, and then switches connections over to the replica. No data is lost during this process. During the switch over to the replica, connections to the database are disabled, so some transactions in flight may be rolled back. If there are large numbers of transactions in flight at the moment connections are disabled, the length of time for the switch over may be longer.
  • To scale a database down, the database must be smaller than the maximum allowed size of the target service tier.
  • Objects that are not supported in target tiers can become inaccessible. For example when you scale down a Premium or Premium RS database that contains clustered columnstore indexes to Standard or Basic, the tables will be inaccessible until you scale back to Premium (RS) again.
  • Premium RS tier is still in preview and currently not yet supported by the Set-AzureRmSqlDatabase cmdlet that is used in the code of this solution. Trying to scale to it results in the following error message : Cannot bind parameter ‘Edition’. Cannot convert value “Premium RS” to type “Microsoft.Azure.Commands.Sql.Database.Model.DatabaseEdition”. Error: “Unable to match the identifier name Premium RS to a valid enumerator name. Specify one of the following enumerator names and try again: None, Premium, Basic, Standard, DataWarehouse, Free”


The Runbook PowerShell script

The PowerShell script below can be used in an Azure Automation Runbook with type “PowerShell”.

The script contains the following logic:

  1. Authenticate with Azure Automation Run As account.
  2. Get the current day of week and current time, and convert it to the time zone that is specified.
  3. Check which of the specified ‘slots’ in the scaling schedule is currently active.
  4. Scale database to edition/tier that is specified in the scaling schedule.
    If no schedule for the current day/time is found, scale to default edition/tier.
    If database is already in target edition/tier, do nothing.

.SYNOPSIS
Vertically scale an Azure SQL Database up or down according to a
schedule using Azure Automation.

.DESCRIPTION
This Azure Automation runbook enables vertically scaling of
an Azure SQL Database according to a schedule. Autoscaling based
on a schedule allows you to scale your solution according to
predictable resource demand. For example you could require a
high capacity (e.g. P2) on Monday during peak hours, while the rest
of the week the traffic is decreased allowing you to scale down
(e.g. P1). Outside business hours and during weekends you could then
scale down further to a minimum (e.g. S0). This runbook
can be scheduled to run hourly. The code checks the
scalingSchedule parameter to decide if scaling needs to be
executed, or if the database is in the desired state already and
no work needs to be done. The script is Timezone aware.

.PARAMETER resourceGroupName
Name of the resource group to which the database server is
assigned.

.PARAMETER azureRunAsConnectionName
Azure Automation Run As account name. Needs to be able to access
the $serverName.

.PARAMETER serverName
Azure SQL Database server name.

.PARAMETER databaseName
Azure SQL Database name (case sensitive).

.PARAMETER scalingSchedule
Database Scaling Schedule. It is possible to enter multiple
comma separated schedules: [{},{}]
Weekdays start at 0 (sunday) and end at 6 (saturday).
If the script is executed outside the scaling schedule time slots
that you defined, the defaut edition/tier (see below) will be
configured.

.PARAMETER scalingScheduleTimeZone
Time Zone of time slots in $scalingSchedule.
Available time zones: [System.TimeZoneInfo]::GetSystemTimeZones().

.PARAMETER defaultEdition
Azure SQL Database Edition that wil be used outside the slots
specified in the scalingSchedule paramater value.
Example values: Basic, Standard, Premium RS, Premium.
For more information on editions/tiers levels,
http://msdn.microsoft.com/en-us/library/azure/dn741336.aspx

.PARAMETER defaultTier
Azure SQL Database Tier that wil be used outside the slots
specified in the scalingSchedule paramater value.
Example values: Basic, S0, S1, S2, S3, PRS1, PRS2, PRS4,
PRS6, P1, P2, P4, P6, P11, P15.

.EXAMPLE
-resourceGroupName myResourceGroup
-azureRunAsConnectionName AzureRunAsConnection
-serverName myserver
-databaseName myDatabase
-scalingSchedule [{WeekDays:[1], StartTime:”06:59:59″,
StopTime:”17:59:59″, Edition: “Premium”, Tier: “P2″},
{WeekDays:[2,3,4,5], StartTime:”06:59:59″,
StopTime:”17:59:59”, Edition: “Premium”, Tier: “P1”}]
-scalingScheduleTimeZone W. Europe Standard Time
-defaultEdition Standard
-defaultTier S0

.NOTES
Author: Jorg Klein
Last Update: 18/09/2017

param(
[parameter(Mandatory=$true)]
[string] $resourceGroupName, 

[parameter(Mandatory=$true)]
[string] $azureRunAsConnectionName, 

[parameter(Mandatory=$true)]
[string] $serverName, 

[parameter(Mandatory=$true)]
[string] $databaseName, 

[parameter(Mandatory=$true)]
[string] $scalingSchedule, 

[parameter(Mandatory=$true)]
[string] $scalingScheduleTimeZone, 

[parameter(Mandatory=$true)]
[string] $defaultEdition, 

[parameter(Mandatory=$true)]
[string] $defaultTier
) 

Write-Output "Script started." 

#Authenticate with Azure Automation Run As account (service principal)
$runAsConnectionProfile = Get-AutomationConnection `
-Name $azureRunAsConnectionName
Add-AzureRmAccount -ServicePrincipal `
-TenantId $runAsConnectionProfile.TenantId `
-ApplicationId $runAsConnectionProfile.ApplicationId `
-CertificateThumbprint ` $runAsConnectionProfile.CertificateThumbprint | Out-Null
Write-Output "Authenticated with Automation Run As Account." 

#Get current date/time and convert to $scalingScheduleTimeZone
$stateConfig = $scalingSchedule | ConvertFrom-Json
$startTime = Get-Date
Write-Output "Azure Automation local time: $startTime."
$toTimeZone = [System.TimeZoneInfo]::FindSystemTimeZoneById($scalingScheduleTimeZone)
Write-Output "Time zone to convert to: $toTimeZone."
$newTime = [System.TimeZoneInfo]::ConvertTime($startTime, $toTimeZone)
Write-Output "Converted time: $newTime."
$startTime = $newTime 

#Get current day of week, based on converted start time
$currentDayOfWeek = [Int]($startTime).DayOfWeek
Write-Output "Current day of week: $currentDayOfWeek." 

# Get the scaling schedule for the current day of week
$dayObjects = $stateConfig | Where-Object {$_.WeekDays -contains $currentDayOfWeek } `
|Select-Object Edition, Tier, `
@{Name="StartTime"; Expression = {[datetime]::ParseExact($_.StartTime,"HH:mm:ss", [System.Globalization.CultureInfo]::InvariantCulture)}}, `
@{Name="StopTime"; Expression = {[datetime]::ParseExact($_.StopTime,"HH:mm:ss", [System.Globalization.CultureInfo]::InvariantCulture)}} 

# Get the database object
$sqlDB = Get-AzureRmSqlDatabase `
-ResourceGroupName $resourceGroupName `
-ServerName $serverName `
-DatabaseName $databaseName
Write-Output "DB name: $($sqlDB.DatabaseName)"
Write-Output "Current DB status: $($sqlDB.Status), edition: $($sqlDB.Edition), tier: $($sqlDB.CurrentServiceObjectiveName)" 

if($dayObjects -ne $null) { # Scaling schedule found for this day
    # Get the scaling schedule for the current time. If there is more than one available, pick the first
    $matchingObject = $dayObjects | Where-Object { ($startTime -ge $_.StartTime) -and ($startTime -lt $_.StopTime) } | Select-Object -First 1
    if($matchingObject -ne $null)
    {
        Write-Output "Scaling schedule found. Check if current edition & tier is matching..."
        if($sqlDB.CurrentServiceObjectiveName -ne $matchingObject.Tier -or $sqlDB.Edition -ne $matchingObject.Edition)
        {
            Write-Output "DB is not in the edition and/or tier of the scaling schedule. Changing!"
            $sqlDB | Set-AzureRmSqlDatabase -Edition $matchingObject.Edition -RequestedServiceObjectiveName $matchingObject.Tier | out-null
            Write-Output "Change to edition/tier as specified in scaling schedule initiated..."
            $sqlDB = Get-AzureRmSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName
            Write-Output "Current DB status: $($sqlDB.Status), edition: $($sqlDB.Edition), tier: $($sqlDB.CurrentServiceObjectiveName)"
        }
        else
        {
            Write-Output "Current DB tier and edition matches the scaling schedule already. Exiting..."
        }
    }
    else { # Scaling schedule not found for current time
        Write-Output "No matching scaling schedule time slot for this time found. Check if current edition/tier matches the default..."
        if($sqlDB.CurrentServiceObjectiveName -ne $defaultTier -or $sqlDB.Edition -ne $defaultEdition)
        {
            Write-Output "DB is not in the default edition and/or tier. Changing!"
            $sqlDB | Set-AzureRmSqlDatabase -Edition $defaultEdition -RequestedServiceObjectiveName $defaultTier | out-null
            Write-Output "Change to default edition/tier initiated."
            $sqlDB = Get-AzureRmSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName
            Write-Output "Current DB status: $($sqlDB.Status), edition: $($sqlDB.Edition), tier: $($sqlDB.CurrentServiceObjectiveName)"
        }
        else
        {
            Write-Output "Current DB tier and edition matches the default already. Exiting..."
        }
    }
}
else # Scaling schedule not found for this day
{
    Write-Output "No matching scaling schedule for this day found. Check if current edition/tier matches the default..."
    if($sqlDB.CurrentServiceObjectiveName -ne $defaultTier -or $sqlDB.Edition -ne $defaultEdition)
    {
        Write-Output "DB is not in the default edition and/or tier. Changing!"
        $sqlDB | Set-AzureRmSqlDatabase -Edition $defaultEdition -RequestedServiceObjectiveName $defaultTier | out-null
        Write-Output "Change to default edition/tier initiated."
        $sqlDB = Get-AzureRmSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName
        Write-Output "Current DB status: $($sqlDB.Status), edition: $($sqlDB.Edition), tier: $($sqlDB.CurrentServiceObjectiveName)"
    }
    else
    {
        Write-Output "Current DB tier and edition matches the default already. Exiting..."
    }
} 

Write-Output "Script finished."


Step-by-step setup guide

Navigate to the Azure portal and create an Azure Automation account in the same Azure Subscription as where the Azure SQL Database that you want to scale resides. Make sure the Run As account has been created. When you create a new Automation account in the Azure portal, it automatically creates a new service principal(Run As account) and assigns it the Contributor role-based access control (RBAC) role in the subscription by default. These rights are sufficient to scale Azure SQL Databases.

I have uploaded my PowerShell script to TechNet Script Center so you can easily import it as Runbook. Navigate to your Automation Account and click the “Runbooks Gallery” menu item. Make sure you filter the Gallery Source on “Script Center” and search for “Azure SQL Database scheduled autoscaling”. Open the item from the gallery and click Import.
Runbooks Gallery

Before you can use the runbook you have to publish it first. Click on “Overview” if you are not already on that page and then click “Edit”.
Edit Runbook

Now click “Publish”.
Publish Runbook

The runbook is now ready to use. Next step is to schedule it to run hourly, of course with your desired parameters. Click “Schedules” and then “Add a schedule”.
Schedule Runbook

Give your schedule a name, set the start time on a whole hour, the Recurrence on “Recurring”, Recur every “1 Hour” and Set expiration to “No”.
Recurring Schedule Runbook

Now configure the parameters. Explaination can be found under “The Runbook PowerShell script” earlier in this article.
Runbook parameters

When you now navigate to “Jobs” you get an overview of all executions.
Runbook Jobs

When you open a job and look at the Output, you will see all details of that particular run. For example, I can see my database was scaled down from P1 to S0 on friday at 18:00:
Runbook Job Output

Process Azure Analysis Services databases from Azure Automation

In my last blog post I showed how to trigger Azure Data Factory (ADF) pipelines from Azure Automation. I also mentioned the option to process an Azure Analysis Services cube from Azure Automation. For example right after your ADF data processing finishes, which will probably be a common use case. In this blog post I show you how you can use the Analysis Services PowerShell provider, also known as SQLASCDMLETS, from Azure Automation.

Create custom SQLASCMDLETS module
The SQLASCDMLETS are not (yet) available in the PowerShell Gallery so unfortunately it’s not possible to import the cmdlets straight into Automation like I did with the ADF cmdlets in my previous blog post. Instead we have to create our own module which will contain the SQLASCMDLETS and its dependencies.

UPDATE: You can now use the SqlServer PowerShell Gallery module. Creating a custom module like described below in the blue text is not required anymore. Instead, follow the 3 steps below and continue reading below the blue text.

1. Navigate to https://www.powershellgallery.com/packages/SqlServer/

2. Click the “Deploy to Azure Automation” button

3. Import the SqlServer module to your Azure Automation account


The required files come with SQL Server Management Studio (SSMS) which you can download and install for free. It’s important to note you need the latest version (140) of the SQLASCDMLETS which is shipped with the latest Release Candidate of SSMS. Download and install it.

If you try to use the previous version of the SQLASCDMLETS (130) you will get an error in Automation because it tries to authenticate with a claims token while only windows authentication is supported by the 130 version of SQLASCDMLETS: “The value ‘ClaimsToken’ is not supported for the connection string property”.

After installing SSMS you should now be able to see the following directory: C:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules\SQLASCMDLETS

Copy the SQLASCMDLETS folder to a temporary location, for example C:\SQLASCMDLETS.
You will need the following files:

  • Microsoft.AnalysisServices.PowerShell.Cmdlets.dll
  • SQLASCMDLETS.PSD1

We also need the libraries SQLASCMDLETS depends on. Search your computer for the following files and copy paste them to the C:\SQLASCMDLETS folder. Make sure you copy them from a folder that has “140” in the path so you are sure you have the correct version.

  • Microsoft.AnalysisServices.Core.dll
  • Microsoft.AnalysisServices.Tabular.dll
  • Microsoft.AnalysisServices.dll

Now zip the entire folder, make sure the name is “SQLASCMDLETS.zip”.


Import custom SQLASCMDLETS module to Azure Automation
Navigate to your Azure Automation account in the Azure portal.

Click Assets:       

Click Modules:       

Click Add a module to import a custom module:       

Now upload the SQLASCMDLETS.zip file:       

The zip file will be extracted:       

Wait until the extraction finished and the status changes to Available. Click on the module name:       

You now see the available activities including the one we will use to process the Azure Analysis Services Database.



Create Azure Automation Credential
Now we need to create a Credential to be able to automatically login and run our PowerShell script unattended from Azure Automation.

Navigate to Assets again and then click Credentials:
clip_image016

Click “Add a credential” and enter an organization account that has permissions to process your Azure Analysis Services database. Make sure you enter the User Principal Name (UPN) and not a Windows AD account. It is often the email address and may look like jorgk@yourorganizationalaccountname.com. Give the new Credential a name, I chose “adpo-auto-cred”. It will be referenced in the PowerShell script below.
clip_image018


Create Automation Runbook
You can use the simple PowerShell script below to process your Azure Analysis Services database from Azure Automation. It will use the “adpo-auto-cred” credential to authenticate and will process your database using the Invoke-ProcessASDatabase SQLASCMDLETS function.
Replace “dbname” with your database name and “server” with your server, e.g. asazure://westeurope.asazure.windows.net/yourserver and you are good to go.

Copy/paste the script below to a Windows PowerShell Script (.ps1) file and name it “ProcessASDatabase.ps1”.


$AzureCred = Get-AutomationPSCredential -Name “adpo-auto-cred”

 

Add-AzureRmAccount -Credential $AzureCred | Out-Null

 

Invoke-ProcessASDatabase -databasename “dbname” -server “server” -RefreshType “Full” -Credential $AzureCred 


Navigate to your Azure Automation account in the Azure Portal and click “Runbooks”:
clip_image020

Click “Add a runbook”:
clip_image022

Click “Import an existing workbook” and select the ProcessASDatabase.ps1 file to import the PowerShell script as Runbook:
clip_image024

Runbook ProcessASDatabase is created. Click it to open it:
clip_image026

A Runbook must be published before you are able to start or schedule it. Click Edit:
clip_image027

Before publishing, test the Runbook first. Click on the “Test pane” button and then click Start:
clip_image029

The script executed successfully:
clip_image031

Connect to your Azure AS sever with SSMS and check the database properties to be sure processing succeeded:
clip_image033

Now publish the Runbook:
clip_image035

That’s it, you now have an Azure Automation Runbook that you can schedule, monitor and integrate with your other data platform related tasks!

Orchestrate Azure Data Factory pipelines and other Azure Data Platform management tasks using Azure Automation

Azure Data Factory (ADF) is a great SaaS solution to compose and orchestrate your Azure data services. It works fine to create, schedule and manage your data pipelines but it has limitations that can make it hard to use in some scenarios. The two main ones are:

1.       Some tasks that you regularly want to perform can’t be accomplished with ADF.
A few examples are:

·         Start/Pause an Azure SQL Data Warehouse

·         Start/Pause an Azure Analysis Services instance

·         Process an Azure Analysis Services cube

·         Manage on-premises resources

 

2.       Triggering (running) ADF pipelines on demand.
For most people it is hard to switch from the well-known SQL Agent jobs – in which it’s really easy to trigger SSIS processes and execute other tasks – to
scheduling ADF pipelines. You now have to create activity windows and define data slices that are dependent on the availability of data sets. Most would like to be able to trigger an ADF pipeline either on demand or when some other task finished successfully.

The tasks listed at point 1 – and almost everything else you can think of – can be done using Azure PowerShell with Azure Resource Manager. Microsoft does an awesome job here: every new SaaS solution has great ARM support from the start. To make use of PowerShell in Azure and more importantly to automate your scripts and processes, Azure Automation comes in to play. It makes it possible to run all your PowerShell scripts in Azure as a SaaS solution. It is an orchestrator in which you can execute all kinds of Data Platform related operations, both in Azure and on-premises as hybrid workers, enabling hybrid Data Platform orchestration scenarios.

Triggering ADF pipelines on demand is a bit trickier. An “Execute now” command in PowerShell does not exist, which is understandable if you know how the scheduling and execution mechanism of ADF has been build. I don’t think it will be easy to change this, unless some major changes to ADF will be made. Fortunately, there is a workaround! If you ever deployed a new ADF pipeline you might have noticed that after deployment, pipelines start immediately when they have an activity window with a date/time in the past. Because it is possible to update the activity window date/time of a pipeline using PowerShell, it’s also possible to trigger a pipeline’s execution by changing its activity window date/time to some value in the past.

In this blog post I will show how you can trigger an ADF pipeline on a daily basis and monitor its execution using PowerShell code that runs in an Azure Automation runbook. Because executing other tasks (point 1) using PowerShell is easy, it becomes possible to orchestrate advanced Azure Data Platform tasks, with the execution of an ADF pipeline as just a part of the total process.

Azure Setup

In this example I’ve used the Azure setup shown below. The ADF pipeline contains one simple copy activity that copies a file from one blob storage container to another.

·         Resource Group

o   Blob Storage

§  Container: input

§  Container: output

o   Azure Data Factory

o   Azure Automation

Azure Data Factory Setup

I created the Azure Data Factory pipeline with the Copy Data wizard:

clip_image001[4] 

I configured the pipeline to “Run regularly on schedule” with a recurring pattern of “Daily”, “every 1 day” (see the blue rectangle in the screenshot below).

Choosing “Run once now” would set the pipeline property “PipelineMode” to “OneTime” and would disable the ADF scheduler. Initially you might think this seems to be the option you want to use as we want to trigger the pipeline ourselves on demand, but unfortunately that configuration has some limitations:

·         The ADF diagram view does not show one-time pipelines. This makes it impossible to monitor the pipeline using the Monitor & Manage dashboard.

·         One-time pipelines can’t be updated. This would block us from updating the activity window properties to trigger the pipeline.

The “Start date time” is automatically set to yesterday with the current time. This triggers the pipeline to start running immediately after deployment; as explained ADF automatically triggers pipelines with a start date/time in the past. In this case, we want to trigger the execution ourselves, so set the date to some date in the future (see the red rectangle in the screenshot below).

clip_image003[4]

For this pipeline I chose to create a simple example that copies a file from one blob storage container to another. Of course, you can design your pipeline anyway you prefer, e.g. using the Copy Data wizard or Visual Studio. Just make sure you schedule it with a daily recurrence.


Azure Automation Setup

The next step is to create a new Azure Automation account. Within that account we need to import the Data Factory PowerShell cmdlets, because the standard set of PowerShell cmdlets in Automation do not contain any ADF related functions.

Navigate to Assets:

clip_image004[4]

Click Modules:
clip_image005[4]

Browse the gallery:
clip_image006[4]

 

Search for “Data Factory”, select AzureRM.DataFactories and click Import:
clip_image007[4]

AzureRM.DataFactories should now appear in your list of available modules:
clip_image009[4]


Create Credential

Now we need to create a Credential to be able to automatically login and run our PowerShell script unattended from Azure Automation.

Navigate to Assets again and then click Credentials
clip_image010[4]

 

Click “Add a credential” and supply a user account that has the required permissions to access your Azure Data Factory. You can use the organizational account you use to login to the Azure Portal. It might look like jorgk@yourorganizationalaccountname.com or something like that.
clip_image012[4]

Automation Runbook
We are now ready to create the Automation Runbook which will trigger the Azure Data Factory pipeline, by updating its Active Period to a date/time in the past.

The script performs the following steps:

1.       Authenticate with the Automation Credential

2.       Connect to the Azure Data Factory

3.       Update the pipeline active period to yesterday

4.       Unpause the pipeline; execution will begin

5.       Monitor the pipeline execution

6.       Pause the pipeline

Copy/paste the script below to a Windows PowerShell Script (.ps1) file and name it “TriggerAdfPipeline.ps1”.


 

# Variables; modify 

$rgn = “AzureDataPlatformOrchestration” #Resource Group Name

$acn = “adpo-auto-cred” #Automation Credential Name

$dfn = “adpo-adf” #Data Factory Name

$pln = “CopyPipeline-cu6” #PipeLine Name

$dsn = “OutputDataset-hgv” #DataSet Name (output dataset of pipeline that needs to be produced)

 

# To test from PowerShell client, uncomment the 2 rows below and provide subscription ID

#Login-AzureRmAccount

#Set-AzureRMContext -SubscriptionId “00000000-0000-0000-0000-000000000000”

 

# Authenticate

# To test from PowerShell client, comment out the 2 rows below

$AzureCred = Get-AutomationPSCredential -Name $acn

Add-AzureRmAccount -Credential $AzureCred | Out-Null

 

# Get data factory object

$df=Get-AzureRmDataFactory -ResourceGroupName $rgn -Name $dfn

If($df) {

       Write-Output “Connected to data factory $dfn in resource group $rgn.”

}

 

# Create start/end DateTime (yesterday)

$sdt = [System.DateTime]::Today.AddDays(-1) #Yesterday 12:00:00 AM

$edt = [System.DateTime]::Today.AddSeconds(-1) #Yesterday 11:59:59 PM

 

# Update active period to yesterday

$apr=Set-AzureRmDataFactoryPipelineActivePeriod -DataFactory $df -PipelineName $pln -StartDateTime $sdt -EndDateTime $edt

If($apr) {

       Write-Output “Pipeline $pln of data factory $dfn updated with StartDateTime $sdt and EndDateTime $edt.”

}

 

# Unpause pipeline

$rpl = Resume-AzureRmDataFactoryPipeline -DataFactory $df -Name $pln

If($rpl) {

       Write-Output “Pipeline $pln resumed.”

}

 

# Create arrays that hold all possible data factory slice states

$failedStates = “Failed Validation”, “Timed Out”, “Skip”, “Failed”

$pendingStates = “Retry Validation”, “PendingValidation”, “Retry”, “InProgress”, “PendingExecution”

$finishedStates = “Ready”

 

# Wait while data factory slice is in pending state

While (Get-AzureRmDataFactorySlice -DataFactory $df -DatasetName $dsn -StartDateTime $sdt -EndDateTime $edt | Where -Property State -In $pendingStates) {

       Write-Output “Slice status is:”

       Get-AzureRmDataFactorySlice -DataFactory $df -DatasetName $dsn -StartDateTime $sdt -EndDateTime $edt | Select -Property State

       Write-Output “Wait 15 seconds”

       Start-Sleep 15

}

 

# Since data factory slice is not pending (anymore), it is either failed or finished

If(Get-AzureRmDataFactorySlice -DataFactory $df -DatasetName $dsn -StartDateTime $sdt -EndDateTime $edt | Where -Property State -In $failedStates){

       Write-Output “Slice failed.”

       Get-AzureRmDataFactorySlice -DataFactory $df -DatasetName $dsn -StartDateTime $sdt -EndDateTime $edt | Select -Property State

} ElseIf (Get-AzureRmDataFactorySlice -DataFactory $df -DatasetName $dsn -StartDateTime $sdt -EndDateTime $edt | Where -Property State -In $finishedStates) {

       Write-Output “Slice finished.”

} Else {

       Write-Output “No State found?”

       Get-AzureRmDataFactorySlice -DataFactory $df -DatasetName $dsn -StartDateTime $sdt -EndDateTime $edt | Select -Property State

}

 

# Pause pipeline

$spr = Suspend-AzureRmDataFactoryPipeline -DataFactory $df -Name $pln

If($spr){

       Write-Output “Pipeline $pln paused.”

}

 


Navigate to your Azure Automation account in the Azure Portal and click “Runbooks”:

clip_image013[4]

Click “Add a runbook”:

clip_image014[4]

Now select the TriggerAdfPipeline.ps1 file to import the PowerShell script as Runbook:

clip_image016[4]

 

Runbook TriggerAdfPipeline is created. Click it to open it:

clip_image018[4]

 

A Runbook must be published before you are able to start or schedule it. Click Edit:

clip_image020[4]

 

Before publishing, test the Runbook first. Click on the Test pane button and then click Start:

clip_image022[4]

 

The pipeline slice ran successfully:

clip_image024[4]

Now publish the Runbook:

clip_image026[4]

You can now create webhooks, schedule and monitor your Runbook jobs:

clip_image028[4]

Extending the script with additional tasks

It’s easy to perform other Azure Data Platform related tasks using PowerShell and Automation Runbooks. Because the script I created waits for the ADF pipeline to end, you can easily execute other tasks before or after the ADF’s execution. The following examples will most likely be useful:

·         Pause/Start Azure SQL Data warehouse

o   Documentation

o   PowerShell Gallery

·         Pause/Start Azure Analysis Services

o   PowerShell Gallery

·         Process Azure Analysis Services

o   See my blog post Process Azure Analysis Services databases from Azure Automation

Update: Triggering/calling pipelines for a second time

The script that is provided in this blog posts only works if you call your pipeline not more than one time a day. If you want to trigger your pipeline multiple times a day this solution needs a minor modification.

  1. The pipeline schedule should be “every 15 minutes” instead of daily
  2. Update the “TriggerAdfPipeline.ps1” script to make it work for “second time”:

Change:

# Create start/end DateTime (yesterday)

$sdt = [System.DateTime]::Today.AddDays(-1) #Yesterday 12:00:00 AM

$edt = [System.DateTime]::Today.AddSeconds(-1) #Yesterday 11:59:59 PM


To:

# Create start/end DateTime

$sdt = Get-Date

$sdt = $sdt.AddMinutes(-2) #Now –2 minutes

$edt = Get-Date

$edt = $edt.AddMinutes(-1) #Now –1 minute