When database administrators switch over to using SQL Azure, they may quickly find that it lacks the SQL Job Agent. Not to worry — Azure Automation can come to your rescue. Azure Automation allows users to schedule almost any task and today I’ll show you an easy way to use Azure Automation to schedule executions of stored procedures on your SQL Azure database.
First things first, Azure Automation utilizes PowerShell. PowerShell is a powerful language that allows users to automate tasks and manage configurations. That being said, we can begin setting up by following the steps below.
- Set up an Azure Automation account. This can be done through the Azure portal interface.
- On the left menu blade, select Browse -> Automation Accounts.
- Click Add on the upper left corner and fill out the information required.
- Create credential assets in Azure Automation. These values will be used to access the database and send email notifications.
- This tutorial walks you through creating credential assets.
- For this project, you will need an email credential and a database credential.
- Create an email list to send notifications to.
- This tutorial walks you through creating variable assets.
- For this email list you should list your emails with a pipe in between each address, for example: “firstname.lastname@example.orgemail@example.comfirstname.lastname@example.org”. If you only have one email address, simply put only that one email address in the variable (“email@example.com”).
Now, let’s start talking about the code. We will write two PowerShell scripts that will be saved as Runbooks in Azure Automation. We are going to code one runbook that will execute any stored procedure on your database and another runbook that will call the first runbook with the required parameters to execute your desired stored procedure. Download this pdf for a detailed explanation of the first PowerShell script.
Below is the stored procedure that I will be calling for my test.
The stored procedure is very simple. It takes in 3 parameters: @TruncateFirst, a bit that determines if the target table is truncated, @AdminName, a string that is entered into the table, and @ValueOverride, the value that will be entered into the table.
The code in the second runbook we will create will call the SQL_Job_Agent runbook we previously coded and will execute the stored procedure above.
$dataTable = SQL_Job_Agent -sqlServer ‘MySqlServer' -Database ‘MySqlDatabase' -SprocName 'dbo.Job_Agent_Test_Sproc' -Parameters '@TruncateFirst = ''Star wars''' -SqlCredentialName 'MySqlDatabase Credential' -SendErrorEmail $true -FromEmailCredentialName 'Email Credential' -ToEmailAssetName 'TestingUsersEmailList'
Note that the parameters of the runbook must be all in the same line of code as the runbook call.
Finally, we have to set up the schedule to run this runbook. We will only schedule the Test_Sql_Job_Agent runbook as it calls the other. To add a schedule, select the Test_Sql_Job_Agent runbook and click on the Schedules tile.
Select “Add a schedule” and then “Schedule.”
In the New Schedule blade enter your schedule details, and click the “Create” button at the bottom of the blade.
Finally, click the OK button at the bottom of the Schedule Runbook blade, and you are all set to go. You can test the runbook by selecting it and clicking the start button at the top of the blade.
And like that you have a tool to schedule the execution of any stored procedure on your SQL Azure database with email error notifications! With similar code, you can just as easily transfer data between databases or execute any SQL query on your database. Thank you for taking the time to read this, and I hope it helps in your work.