Running SQL server jobs against an Azure SQL database

Posted in: Cloud, Microsoft SQL Server, Technical Track

 

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.

  1. Linked Server
  2. Maintenance Plan w/new connection
  3. Powershell Script
  4. 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.

Linked Server

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.

ODBCConnectionString

ODBCConnectionString

 

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.

MaintenancePlan

MaintenancePlan

 

Powershell Script

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"

Azure Services

These change, it seems like, almost daily, so I’ve listed some of the common ways this is currently done.

  1. The Azure Scheduler used in conjunction with mobile services.
  2. Azure Automation

 

Discover more about our expertise in Cloud and SQL Server.

email

Interested in working with Scott? Schedule a tech call.

1 Comment. Leave new

Warner Chaves
November 7, 2015 9:37 pm

And the new elastic database jobs, which natively can run T-SQL or DACPACs against a set of databases.

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *