SQL Azure – Using Azure Automation and Runbooks to Schedule an Azure SQL Stored Procedure

Its vital to be able to run stored procedures on a schedule it can be extremely important for doing daily housekeeping.

This site was where I learned how to do this.
How to Schedule an Azure SQL Database’s Stored Procedure

Runbooks sit in Azure Automation and a Runbook consists of a script that performs a task.

Runbooks can be written in Python or Powershell. See the last part of this post for an important additional issue.

"Data Source=01server.database.windows.net;Initial Catalog=database;Integrated Security=False;User ID=01userid;Password=JK12jl423;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False"
# Instantiate the connection to the SQL Database
$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Data Source=servername.database.windows.net;Initial Catalog=database;Integrated Security=False;User ID=userid;Password=password;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False"

$sqlConnection.Open()
Write-Output "Azure SQL database connection opened"

# Define the SQL command to run
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.CommandTimeout = 120
$sqlCommand.Connection = $sqlConnection

Write-Output "Issuing command to run stored procedure"

# Execute the SQL command
$sqlCommand.CommandText= 'exec [dbo].[storedProcedure]'
$result = $sqlCommand.ExecuteNonQuery()
Write-Output "Stored procedure execution completed"

# Close the SQL connection
$sqlConnection.Close()

Write-Output "Run completed"

Honestly the hardest part is getting the code setting up the scheduling after that is relatively easy.

WARNING : Runbooks have dynamic IPs so although you can set grant access to an IP your runbook will only run for a certain period before it stops working unless you either have a Hybrid Runbook Worker setup OR you allow Azure services and resources to access this server (which isn’t recommended) The next post I will do is my initial notes on how to configure a Hybrid Runbook Worker.