Execute Azure SQL Statements Periodically via Azure Automation

It might be handy to automate some batch processing of your relational database like Azure SQL database to offload the complexity from your web applications. For example, Kontext uses this approach to update stats in the backend database. This article shows you an example of using Azure Automation account workbook to execute SQL statements against an Azure SQL database.

Azure automation approaches

There are many different approaches to automate processes on Azure. The following diagram shows some of typical ways to do that. 

There are also other tools like Airflow in Azure VMs or other scheduling tools installed in your Azure resources. 

Create Azure Automation Account runbook

Create automation account

Before you can create run books, create an Azure automation account first.  

Create runbook

Once the account is created, navigate to the account blade. Click Runbooks under Process Automation section.

Click button 'Create a runbook'. 

Input all the details as necessary. Make sure you choose PowerShell Workflow as runbook type. 

Create 'Create' button to create.

Input PowerShell script for the process. The following example shows how Kontext use PowerShell to update user points:

<#
.SYNOPSIS
    Calculate Flex User stats 

.DESCRIPTION
    This runbook calculates flex user stats based on content counts.

.PARAMETER SqlServer
    String name of the SQL Server to connect to

.PARAMETER SqlServerPort
    Integer port to connect to the SQL Server on

.PARAMETER Database
    String name of the SQL Server database to connect to

.PARAMETER SqlCredential
    PSCredential containing a username and password with access to the SQL Server  

.EXAMPLE
    Use-SqlCommandSample -SqlServer "somesqlserver.cloudapp.net" -SqlServerPort 1433 -Database "SomeDatabaseName" - SqlCredential $SomeSqlCred

.NOTES
    AUTHOR: Raymond Tang
    LASTEDIT: Jan 16th 2022. 
#>

workflow kontext-flex-user-stats-calc
{
    Param(
        [parameter(Mandatory=$False)]
        [string] $SqlServer = '***.database.windows.net',
        
        [parameter(Mandatory=$False)]
        [int] $SqlServerPort = ***,
        
        [parameter(Mandatory=$False)]
        [string] $Database = '***',
        
        [parameter(Mandatory=$False)]
        [string] $SqlCredentialName = '***-credential'
    )

    # Get the username and password from the SQL Credential
    $SqlCredential = Get-AutomationPSCredential -Name $SqlCredentialName
    $SqlUsername = $SqlCredential.UserName
    $SqlPass = $SqlCredential.GetNetworkCredential().Password
    
    inlinescript {
        # Define the connection to the SQL Database
        $Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$using:SqlServer,$using:SqlServerPort;Database=$using:Database;User ID=$using:SqlUsername;Password=$using:SqlPass;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;")
        
        # Open the SQL connection
        $Conn.Open()

        # Define the SQL command to run.
        $SqlText = 
@"   
update Users set TotalPoints=***;
"@
        $Cmd=new-object System.Data.SqlClient.SqlCommand
        $Cmd.Connection = $Conn
        $Cmd.CommandText = $SqlText
        $result = $Cmd.ExecuteNonQuery()
        
        Write-Output $result
        Write-Output "Calculated flex content stats."
        # Close the SQL connection
        $Conn.Close()
    }
}

The script is common .NET approach to execute T-SQL statements using System.Data.SqlClient for SQL Server databases. It accepts multiple parameters (with default values):

• Server address
• Port number
• Default database
• Credential

With these details, a database connection is then established which was then used to execute SQL statements. You can use the statements to call procedures, run DML statements or any other statements your credential has access to run. 

About the credential

A credential in this context is a user name with password. The credential name must exist in your Azure automation account Credentials shared resource. As shown in the screenshot below, you can click 'Add a credential' button to create a credential. 

The credential creation form looks like the following screenshot:

Once all the content details are created for the runbook, you can save it.

Create a scheduler

You can create a scheduler to execute the job periodically. Refer to 'Create a scheduler' section in page Azure Automation Account Runbooks.

Subscribe to Event grid events

You can subscribe to Event grid events to trigger the process. Refer to Integrate Azure Automation with Event Grid - Azure Event Grid | Microsoft Docs for one example.