How to Schedule an Azure SQL Database’s Stored Procedure

by Masood Pehlvi 3 minute read

Azure SQL database does not have a SQL Agent service component which can be used to create and schedule jobs. The Agent is still available on SQL Server on-premises and is often used to schedule the running of stored procedures and other objects (i.e. SSIS/SSAS packages, etc). So, what can we do if we want to schedule a stored procedure on Azure SQL?

Introduction

Process Automation is a part of the Azure Automation service and offers the capability to orchestrate processes using runbooks that can be authored graphically, via PowerShell, or via Python.

In this article, I will focus on runbooks that use PowerShell commands to make a connection to the Azure SQL database and execute a stored procedure found on that database. Then we will create a schedule for that runbook, which in effect substitutes as a schedule for the stored procedure. If you have a stored procedure in Azure SQL that needs to run on a schedule, then you can follow the steps below in order to automate that run.

Process Automation in Azure

Create a runbook

  1. To create a runbook, first open your Automation account and then click Runbooks under the Process Automation section:
    stored-procedure-1
  2. Click ‘Create a runbook’.
  3. Enter a name and a description, select the ‘Runbook type’ to be PowerShell, and click ‘Create’.
  4. Copy the following script into your Runbook editor:

Note

  • The “Write-Output” statements are meant for troubleshooting and are not necessarily needed; alternatively, you can pipe them to an output file which can then serve as a log file
  • You will need to provide your servername, database, userid, password, and the storedProcedureName in the script
  • It is a good idea to test run your script, during which time the stored procedure will itself will run – run your script by clicking Start in the runbook overview page and managing the run in the configuration page of the runbook
Write-Output "Run started"

# 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"

Create a schedule

5.       To create a schedule, click Schedules under the Process Automation section of the same Automation account:
stored-procedure-2

6.       Click ‘Create a schedule.

7.       Enter name, description, appropriate run times, and click ‘Create’.

Associate the schedule with the runbook

8.       Back on the Runbook, click schedule and choose the schedule that you created above.

Runbook run logs

9.       To look through prior runs’ logs, click Jobs under the Process Automation section of the same Automation account:
stored-procedure-3

Congratulations, you have just schedule an Azure SQL database’s stored procedure using runbooks of type PowerShell.