Schedule SQL Jobs Using Azure Automation account

With the migration to SQL Azure happening on a fast pace i get lot of queries from my clients on how to schedule a SQL Agent job on a SQL Azure database In this Blog We will see how to schedule a SQL how to schedule a SQL Job using Azure Automation Account.

Environment:-

I am going to use my Test server 'Vinothtestsqlserver' and Test database 'VinothSampleDatabase' for this example.

Login into to Azure Subscription of the SQL Server and create new Automation Account from Azure MarketPlace

Once the Automation Account is created, Scroll through the left tab and select Runbooks under Process Automation,

First Step is to create a Credential for the SQL connection, Similar to Credential in SQL Agent or Data Sources in SSIS,  Under Shared Resources on you Automation Account Page, Select Credentials and Create a new Credential as below.

I have created a Credential Named BlogTestCredential for this example,

Now Create New Run book-This is similar to creating a new SQL agent Job

I have named the Runbook 'VinothTestRunBook' and Will be using Powershell Workflow. SQL Jobs can be created with either Powershell or Powershell Workflow.

Once the Runbook is created Your Script Editor will open, My aim for the SQL Job is to run 'SELECT * FROM sys.tables' from the VinothSampleDatabase every Hour.

I have used the below Powershell Script to achieve the output, The Script take Servername and Database as Parameter, then Uses BlogTestCredential using Get-Credential cmdlet to connect the SQL database. It then uses SQLConnection and SQLCommand Class to run the SQL.

In the Above Example I have used SQL Adapter to get the output and display, But in case you want to run a Store Proc or run Updates or Deletes use the Appropriate method(eg. ExecuteReader(), ExecuteNonQuery(), ExecuteScalar() etc.. More details on SQLCommand class and method in the below MSDN Link.

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand%28v=vs.110%29.aspx

Once you Finish Scripting Test the Script by Clicking the Test Pane Button, where you can test the script by specifying the parameters(Server name and DB Name in this case) as below.

Once your testing Successful, Publish your run book by clicking the publish button this is same as creating a SQL Agent Job without a Schedule. 

Now once your Runbook is publish we can create a Schedule, Go back to Runbook Page on the Portal and select Schedules and create a new schedule as below I have created similar to SQL Agent job to run this script every hour,

Once you Create the Schedule Specify the Parameters for the Run book Schedule as below,

That's about it your job is now ready to run, To check the Job history Go to Workbook page in the Portal and click Jobs

Now you can see summary of a completed job, for detailed information click on the summary

And You can see the Jod Run's detailed info, To see the output click the output pane and you can see the job output.

There you go now you have perfectly working SQL Agent Environment in Azure. Powershell Workflow Allows you you run Steps in Parallel and in sequence we will see in detail on future blogs.