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.
