What’s on our mind?

Exsilio Solutions is proud to introduce our new blog! Not only is this forum completely integrated with all our social networks, but it is also 100% responsive. Now, you can take Exsilio with you on your phone, tablet, and desktop - redefine what you thought possible!

SQL Azure Job Agent Using Azure Automation

How to use Azure Automation to schedule executions of stored procedures on your SQL Azure database.

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.

  1. Set up an Azure Automation account. This can be done through the Azure portal interface.
    1. On the left menu blade, select Browse -> Automation Accounts.
    2. Click Add on the upper left corner and fill out the information required.
  2. Create credential assets in Azure Automation. These values will be used to access the database and send email notifications.
    1. This tutorial walks you through creating credential assets.
    2. For this project, you will need an email credential and a database credential.
  3. Create an email list to send notifications to.
    1. This tutorial walks you through creating variable assets.
    2. For this email list you should list your emails with a pipe in between each address, for example: “email1@domain.com|email2@domain.com|email3@domain.com”. If you only have one email address, simply put only that one email address in the variable (“email1@domain.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.

jeremyblog1

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.

workflow Test_Sql_Job_Agent
{
$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'
Write-Output($dataTable)
}

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.

jeremyblog2

Select “Add a schedule” and then “Schedule.”

jeremyblog3

Click on  jeremyblog4

In the New Schedule blade enter your schedule details, and click the “Create” button at the bottom of the blade.

jeremyblog5

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.

Users feedback ( 3 )

  1. Tom says:

    Hi Jeremy. Your article and perspective on SQL Azure Job Agent Using Azure Automation is of absolute relevance. It was an interesting read. Even we have recently posted a blog on ‘5 Approaches for Automating Microservices Testing’ on our site. We are excited to get your feedback on the same. Maybe readers of your blog might like reading it too. Here’s the link – http://bit.ly/2bi2rgZ
    cheers,
    Tom

    • Jeremy Borne says:

      Thanks for your comment and the link to your blog, Tom. Your article has some very good points and is very insightful.
      Thanks again,
      Jeremy

  2. Jahangir says:

    Thank you so much. This is exactly what I was looking for. Much appreciated.

Any thoughts?



Loading more content...