If you’re like me, you’ve probably done some work with SQL Server Agent jobs to schedule backups or other tasks like truncating logs. Since I have a preference to log virtually every action in the applications I build, I end up setting up jobs to truncate or otherwise archive logs after a certain period of time. As the forces of the cloud become ever more pervasive, it leads us to the question of how to schedule these same types of tasks in Azure. Luckily like most things, Microsoft has an answer for this, and it is Azure Automation. Complete coverage of Azure Automation is outside the scope of this article. For my purposes here, it is suffice to say that Azure Automation is a is a SaaS offering that provides the ability to run Powershell scripts called Runbooks that execute tasks.
Runbooks can be authored, scheduled and managed all within the Azure portal either through a graphical editor, or by directly editing code. I’ll cover the basics here, that will allow you to author your very own “SQL Server Agent job in the cloud”! We’ll be using the non graphical editing approach.
To get started, create your Automation account. There are several prerequisites and permissions that you can read about. Once in the home page for your automation account, select ‘Runbooks’ from the main menu to begin authoring your first Runbook.
You’ll then be taken to the landing blade for Runbooks. Click ‘Add a runbook’ and follow the steps to create a new Runbook. You have the option here of importing a Runbook as well. I’ll be creating a Runbook of type ‘Powershell’. You can optionally select Graphical Editor or a Workflow type.
Once this is created, you’ll be taken back to the Runbooks main blade. Select a Runbook and you’ll be taken to the details blade of that Runbook. Its here from the details blade that we can edit, schedule and publish our Runbook. Select ‘Edit’ to begin editing your Runbook code in your browser.
The Powershell scripting language that you’ll use allows for parameters to be passed in to your script. I simply modified one of the samples available here that runs a SQL command statement, to delete from my log table where the log records are older than a certain date. The parameters I used include:
1. The name of the server I want to connect to
2. The port I’ll connect on, 1433
3. The database name I’ll run my query against
4. The table I will run my query against
5. A credential ‘Asset’ that you have to configure separately. This is a user name and password for authenticating against the database server and database. To configure a credential, navigate to the Runbook main blade and follow the steps under the ‘Credentials’ menu. It will allow you to create a saved user name and password.
Once you’re done editing your runbook code you can either click ‘Revert to Published’ which will discard your changes and roll back to your published version, if you have already published a script. Or, you can publish the script. Once you publish the script you can run it manually. You can see the results of the last job ran in the details blade of the Runbook, by clicking ‘Jobs’.
To attach a schedule to the job, go back to the Runbooks main blade and click ‘Schedules’. Here I’ve created a schedule called ‘Weekly’. I can set the schedule in the edit blade.
I then attach the schedule to the job in the details blade of either the Runbook.
When you want to run the Runbook manually, click ‘Start’ from the Runbook details blade. You’ll be prompted to provide values for your variables.
That’s it. Your job is now able to be scheduled and run. Again, the code I used is modified from the sample here. You could theoretically pass a variable to determine how far back you want to delete the logs in your table.
Outputs the number of records in the specified SQL Server database table.
This runbook demonstrates how to communicate with a SQL Server. Specifically, this runbook
outputs the number of records in the specified SQL Server database table.
In order for this runbook to work, the SQL Server must be accessible from the runbook worker
running this runbook. Make sure the SQL Server allows incoming connections from Azure services
by selecting 'Allow Windows Azure Services' on the SQL Server configuration page in Azure.
This runbook also requires an Automation Credential asset be created before the runbook is
run, which stores the username and password of an account with access to the SQL Server.
That credential should be referenced for the SqlCredential parameter of this runbook.
String name of the SQL Server to connect to
Integer port to connect to the SQL Server. Default is 1433
String name of the SQL Server database to connect to
String name of the database table to output the number of records of
Credential asset name containing a username and password with access to the SQL Server
Use-SqlCommandSample -SqlServer "somesqlserver.databases.windows.net" -SqlServerPort 1433 -Database "SomeDatabaseName" -Table "SomeTableName" -SqlCredentialAsset sqluserCredentialAsset
AUTHOR: System Center Automation Team <---- Original Author #>
[string] $SqlServer = '',
[int] $SqlServerPort = 1433,
[string] $Database = '',
[string] $Table = 'log',
[string] $SqlCredentialAsset = ''
$SqlCredential = Get-AutomationPSCredential -Name $SqlCredentialAsset
if ($SqlCredential -eq $null)
throw "Could not retrieve '$SqlCredentialAsset' credential asset. Check that you created this first in the Automation service."
# Get the username and password from the SQL Credential
$SqlUsername = $SqlCredential.UserName
$SqlPass = $SqlCredential.GetNetworkCredential().Password
# Define the connection to the SQL Database
$Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$SqlServer,$SqlServerPort;Database=$Database;User ID=$SqlUsername;Password=$SqlPass;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;")
# Open the SQL connection
# Define the SQL command to run. In this case we are getting the number of rows in the table
$Cmd=new-object system.Data.SqlClient.SqlCommand("delete from $Table where date < DATEADD(day, -180, GETDATE())", $Conn)
# Execute the SQL command
# Output the count
# Close the SQL connection