UPDATE January 2024 – I have moved away from Runbooks for scheduling Azure SQL stored procedures towards using logic apps but keep this here as its always good to have alternative methods of completing the same task. Link to Logic Apps Setup
Original Post
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.
