Automate Azure Synapse Analytics using Azure Automation!

Overview 

Azure Automation is a tool that uses a highly scalable and reliable workflow execution engine to simplify cloud management. It automates the process of creation, deployment, maintenance, and monitoring of the Azure resources. It can be used to automate frequent, time-consuming, and error-prone tasks. Azure automation can help reduce time spent by administrators on Azure resource management. It can also help administrators in reducing their operational cost, errors and boosting efficiency. 

 

Find out more about our Azure DevOps services!
 

The process of automation makes it easy to create and manage Runbooks. Automation executes the Runbook based on its logic, and it supports several types of Runbooks.  

  • Graphical 
  • Graphical PowerShell Workflow 
  • PowerShell 
  • PowerShell Workflow 
  • Python 

Read more about the Azure Automation on the official docs, here 

In this article, I will create a PowerShell Runbook to automate the Azure Synapse Analytics.  

Create Automation Account 

  1. Go to the Azure Portal and search for the Automation Account

image001

Select Automation Account, and you will see another screen. Click on Create and fill in the required attributes.

image003

2. After creating the Automation Account, you will see the option of Runbook in the left menu. By opening this, you will see default/tutorial Runbooks of different types. To Automate the Process of Synapse Analytics, install some required modules in the Automation Account. In the left menu, find and click on the Modules Gallery, search for Az.Accounts and import this module to the Automation Account.

image005

It will take some time to import the module to the account. After this import, repeat this step to import  Az.Synapse, another required module for this automation task. 

3. After importing the required modules, let us create a Runbook.

image007

4. After clicking on Create a Runbook, you will see the editor, paste this code, save the Runbook, and publish it. 

[CmdletBinding()]

param (

   [Parameter(Mandatory=$true)]

   [string]$ResourceGroupName ="rg_ResourceGroup",

   [Parameter(Mandatory=$true)]

   [string]$WorkspaceName = "wp_WorkSpaceName",

   [Parameter(Mandatory=$true)]

   [string]$Operation = "op_Pause"

)

Begin    {

Write-Output "Connecting on $(Get-Date)"

#Connect to Azure using the Run As Account

Try{

$servicePrincipalConnection=Get-AutomationConnection -Name "AzureRunAsConnection"

Connect-AzAccount  -ServicePrincipal -TenantId $servicePrincipalConnection.TenantId -ApplicationId $servicePrincipalConnection.ApplicationId -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint

}

Catch {

if (!$servicePrincipalConnection){

$ErrorMessage = "Connection $connectionName not found."

throw $ErrorMessage

} else{

Write-Output -Message $_.Exception

throw $_.Exception

}

}

# Validation parameters

$ArrayOperations = "Pause","Start","Restart"

If ($Operation -notin $ArrayOperations)

{

Throw "Only Pause, Start, Restart Operations are valid"

}

# Start

Write-Output "Starting process on $(Get-Date)"

Try{

$Status = Get-AzSynapseSqlPool –ResourceGroupName $ResourceGroupName -WorkspaceName $WorkspaceName | Select-Object Status | Format-Table -HideTableHeaders | Out-String

$Status = $Status -replace "`t|`n|`r",""

Write-Output "The current status is "$Status.trim()" on $(Get-Date)"

}

Catch {

Write-Output $_.Exception

throw $_.Exception

}

# Start block

# Start

Write-Output "Starting $Operation on $(Get-Date)"

if(($Operation -eq "Start") -and ($Status.trim() -ne "Online")){

Write-Output "Starting $Operation Operation"

try

{

Write-Output "Starting on $(Get-Date)"

Get-AzSynapseSqlPool –ResourceGroupName $ResourceGroupName -WorkspaceName $WorkspaceName | Resume-AzSynapseSqlPool

}

catch

{

Write-Output "Error while executing "$Operation

}

}

# Pause block

if(($Operation -eq "Pause") -and ($Status.trim() -ne "Paused")){

write-Output "Starting $Operation Operation"

try

{

Write-Output "Pausing on $(Get-Date)"

Get-AzSynapseSqlPool –ResourceGroupName $ResourceGroupName -WorkspaceName $WorkspaceName | Suspend-AzSynapseSqlPool

}

catch

{

Write-Output "Error while executing "$Operation

}

}

# Restart block

if(($Operation -eq "Restart") -and ($Status.trim() -eq "Online")){

Write-Output "Starting $Operation Operation"

try

{

Write-Output "Pausing on $(Get-Date)"

Get-AzSynapseSqlPool –ResourceGroupName $ResourceGroupName -WorkspaceName $WorkspaceName | Suspend-AzSynapseSqlPool

Write-Output "Starting on $(Get-Date)"

Get-AzSynapseSqlPool –ResourceGroupName $ResourceGroupName -WorkspaceName $WorkspaceName | Resume-AzSynapseSqlPool

}

catch

{

Write-Output "Error while executing "$Operation

}

        }

}

End

{

# Exit

Write-Output "Finished process on $(Get-Date)"

}

image009

5. After publishing, click on the start button and enter your Synapse Analytics values, and a job will be created.

image011

6. You can see various options on the job page like its Status, Errors, Exceptions, etc. After completing the job, your Synapse will Resume/Pause through this Runbook.  

7. Now you can add a schedule for your Runbook to completely Automate the process on schedule. On the Runbook page, click on the link to Schedule button in the ribbon and add Schedule and Configure the required parameters.  

Conclusion 

Through Azure Automation, we can manage various resources, automate processes, reduce human interaction and cost. We can also import Runbooks from the Runbook Gallery to control/automate the resources. If you have any questions, feel free to leave a comment below. 

Leave a Reply

Your email address will not be published.