How to use Azure Automation to optimize Azure Synapse Analytics cost?

Microsoft Azure is rapidly becoming the preferred cloud platform for businesses of all sizes. It offers a wide range of services, ranging from Infrastructure-as-a-Service (IaaS) to Platform-as-a-Service (PaaS) and Software-as-a-Service (SaaS). One of the most popular PaaS offerings is Azure Synapse Analytics, which enables users to store and analyze data quickly and easily. However, one of the downsides of using this service is that it can be expensive if not managed properly. Fortunately, there is a way to optimize your costs: by using Azure Automation. Let’s take a look at how it works.

What is Azure Automation?

Azure Automation is a cloud-based service that helps you automate the manual tasks associated with managing and maintaining your Azure resources. It uses runbooks—sets of PowerShell scripts—to perform specific tasks in response to certain conditions or triggers. For example, you could set up an automated process to shut down virtual machines when they are no longer needed or to send notifications when certain thresholds are met.

How can I use it for Azure Synapse?

You can use Azure Automation to optimize your cost by automating the process of scaling up and down your compute resources based on usage patterns. This ensures that you don’t pay for more resources than necessary but still have enough capacity when demand increases. You can also use it to automate the process of stopping and starting services during off-peak hours when fewer people are using them, which reduces cost without compromising performance or availability. Finally, it can be used to track usage trends over time so that you can adjust your resource allocation accordingly in order to maximize efficiency and minimize costs.

you can use automation scripts to shut down idle databases or remove unneeded data pipelines that are no longer being used. Additionally, automation scripts can be used to automatically scale up or down compute instances in response to usage patterns so that you are only paying for what you need when you need it.

Azure Automation for Scale up

Here we will develop an end-to-end Scale up automation script for Synapse analytics:

#First Install Az PowerShell module
https://learn.microsoft.com/en-us/powershell/azure/install-az-ps?view=azps-9.2.0
##################Run this with Admin previleges if Az.Automation module is not installed###################
$PSVersionTable.PSVersion
Get-InstalledModule -Name Az -AllVersions -OutVariable AzVersions | select Name, Description, version | Format-List
# It should show version 9.2.0 If this AZ Version is not
#installed then run the next three lines while opening the Powershell in #Administrator mode
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser
Install-Module -Name Az-Scope CurrentUser -Repository PSGallery -Force
# If this is alrerady installed and you want to update it please run this
Update-Module Az
###################################################################

#Connect to Azure Account
Connect-AzAccount
#Get the subscription details
Get-AzSubscription
# set the context based on the above commandlet output
Set-AzContext -SubscriptionName "put the subscription name from last line output"

#Set the variables in this place so they can be utilized
$resourcegroupname = "synapse-Demo"
$location = "eastus"

$servername = "server-synapse-demo-sample"
# Set an admin name and password for your database
# The sign-in information for the server
$adminlogin = "ServerAdmin"
$password = "PassW0rd1"
# The ip address range that you want to allow to access your server - change as appropriate
$startip = "0.0.0.0"
$endip = "0.0.0.0"
# The database name
$databasename = "SynapseSampleDb"
$automationAccount="SynapseAutomationAccount"
$runBookName="synapseScaleupRunbook"
# Your path may be different
$path= "C:\Users\Live Network \SynapseRunbooks\ScaleUp"
$powershellFilePath=$path+"\synapseScaleupRunbook.ps1"
# Creating the resourceGroup
New-AzResourceGroup -Name $resourcegroupname -Location $location
#Create the SQL server
New-AzSqlServer -ResourceGroupName $resourcegroupname `
    -ServerName $servername `
    -Location $location `
    -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
#Create Firewall rule
New-AzSqlServerFirewallRule -ResourceGroupName $resourcegroupname `
    -ServerName $servername `
    -FirewallRuleName "AllowSome" -StartIpAddress $startip -EndIpAddress $endip
#Create a Database
    New-AzSqlDatabase `
    -ResourceGroupName $resourcegroupname `
    -ServerName $servername `
    -DatabaseName $databasename `
    -Edition "DataWarehouse" `
    -RequestedServiceObjectiveName "DW100c" `
    -CollationName "SQL_Latin1_General_CP1_CI_AS" `
    -MaxSizeBytes 10995116277760
#Create Automation Account
New-AzAutomationAccount -Name $automationAccount -Location "East US" -ResourceGroupName $resourcegroupname
#Enable System assigned identity
Set-AzAutomationAccount -Name $automationAccount -ResourceGroupName $resourcegroupname -AssignSystemIdentity
$objID=(Get-AzAutomationAccount `
-ResourceGroupName $resourcegroupname `
-Name $automationAccount).Identity | select PrincipalId

#Grant contributor permission to Azure automation's system managed identity
New-AzRoleAssignment -ObjectId $objID.PrincipalId `
-ResourceName "server-synapse-demo-sample" `
-ResourceGroupName $resourcegroupname -RoleDefinitionName "Contributor" `
-ResourceType "Microsoft.Sql/servers"

Now we will create a Powershell runbook and save it on the desktop at the specific location. We will use that location path in the $path variable above.

#Copy this code in a powershell file and use the path in the $powershellFilePath variable
#RUN Book Code
    <#
    .DESCRIPTION
        This runbook will use System managed Identity

    .NOTES
        AUTHOR: Rajaniesh Kaushikk
        LASTEDIT:
#>

"Please enable appropriate RBAC permissions to the system identity of this automation account. Otherwise, the runbook may fail..."

try
{
    "Logging in to Azure..."
    # this commandlet uses System managed identity to connect to Synapse
    # we have already provided the contributor role to Managed identity of Automation account to Synapse server
    Connect-AzAccount -Identity
	Set-AzSqlDatabase -ResourceGroupName "synapse-rg" -DatabaseName "synapse" -ServerName "synapseserverkp" -RequestedServiceObjectiveName "DW200c"
}
catch {
    Write-Error -Message $_.Exception
    throw $_.Exception
}

This PowerShell will import the runbook from the desktop location to the Azure Automation account.

#Here we will copy the runbook from your local desktop to Azure Automation account
Import-AzAutomationRunbook  -AutomationAccountName $automationAccount -Name $runBookName -Path  $powershellFilePath `
 -Published -ResourceGroupName $resourcegroupname -Type PowerShell

Here are some important useful commands which were used in the process of developing the automation script:

# If you are not sure what resource is it use this commandlet to find it.
Get-AzResource -ResourceGroupName $resourcegroupname
#For Creating a blank Runbook and writting a code afterward you can use next line
New-AzAutomationRunbook -Name $runBookName -ResourceGroupName $resourcegroupname -AutomationAccountName $automationAccount -Description "This runbook is used for Scaling up the synapse" -Type PowerShell

# Important commands to exporting the runbook from Azure to specific folder
Export-AzAutomationRunbook -ResourceGroupName $resourcegroupname  `
-AutomationAccountName $automationAccount -Name $runBookName `
-Slot "Draft" -OutputFolder $path

Now we have the automation account and runbook script is ready and the automation account managed identity has the required permissions on Azure synapse to make changes, so now we will create a schedule to fire this PowerShell runbook:

1. Open the runbook and click on to link to schedule.

2. Now click a link to schedule.

3. Now add a schedule by clicking the add a schedule + sign

4. Create a schedule to trigger the job. It could be a recurring schedule with specific days in a week.

Conclusion

Azure Automation is a powerful tool for optimizing the cost of your Azure Synapse Analytics platform while still ensuring that performance and availability remain a top priority. By automating the scaling process based on usage patterns and taking advantage of its ability to track usage trends over time, you can save money while still getting the most out of the features offered by Microsoft’s cloud platform. With Azure Automation, you can rest assured that your costs are being managed effectively while still enjoying the benefits of using this powerful analytics solution from Microsoft. As always, make sure to monitor your usage closely in order to get the most out of this system!