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:
<#.SYNOPSISCalculate Flex User stats.DESCRIPTIONThis runbook calculates flex user stats based on content counts..PARAMETER SqlServerString name of the SQL Server to connect to.PARAMETER SqlServerPortInteger port to connect to the SQL Server on.PARAMETER DatabaseString name of the SQL Server database to connect to.PARAMETER SqlCredentialPSCredential containing a username and password with access to the SQL Server.EXAMPLEUse-SqlCommandSample -SqlServer "somesqlserver.cloudapp.net" -SqlServerPort 1433 -Database "SomeDatabaseName" - SqlCredential $SomeSqlCred.NOTESAUTHOR: Raymond TangLASTEDIT: 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().Passwordinlinescript {# 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 $resultWrite-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.