I recently had a client ask how to run SQL Server jobs against an Azure SQL Database, and because SQL DB doesn’t have an SQL Agent like most other versions of SQL Server, it isn’t always obvious how to implement. Fortunately, we have several options in Azure and within a “normal” instance.
Options to Run Jobs
The first three options require a non-Azure version of SQL Server to be running and to have connectivity to the Azure SQL DB.
- Linked Server
- Maintenance Plan w/new connection
- Powershell Script
- Azure Services
Elastic DB Jobs
As mentioned by Warner Chaves in the comments, and currently (Nov 2015) in preview, the new Elastic Database Jobs might also work well.
To create a linked server to an Azure DB, you just need to get the ODBC connection string from the Azure portal and use it as the “Provider String” when defining the LS.
Maintenance Plan w/New Connection
Simply create your maintenance plan with all the required logic for your job, then click the “Manage Connections” menu at the top of the screen and define the new connection to be used for this plan.
You can use the Powershell Invoke-SqlCmd functionality and run it as a Powershell job step.
Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "AzurreInstance" -U "UserName" -P "Password"
These change, it seems like, almost daily, so I’ve listed some of the common ways this is currently done.
Interested in working with Scott? Schedule a tech call.