36

I have one SQL Agent maintenance job which checks the index fragmentation within a database and rebuilds indexes if required.

This is running well in my test server (Microsoft Sql Server 2012). But my production server is in Azure. Now I want to schedule that job to Azure.

SQL Agent does not exist in Azure SQL Database so how can I schedule a SQL job in Azure DB?

Luke Girvin
  • 13,221
  • 9
  • 64
  • 84
user1006544
  • 1,514
  • 2
  • 16
  • 26
  • [Azure Logic Apps](https://learn.microsoft.com/en-us/azure/logic-apps/logic-apps-overview) is replacing Azure Scheduler, which is being retired. To schedule jobs, try Azure Logic Apps instead. – Wuf_fang Dec 10 '18 at 18:03
  • 1
    Although this question is old and closed, it is still one of the top results indexed in Google. So for anyone who still ends up here, for updated information please see Microsoft's Azure SQL Database Documentation (under SQL Server Agent) which now shows that it is indeed supported for Managed Instance servers: https://learn.microsoft.com/en-us/azure/sql-database/sql-database-features – J.D. Mar 28 '19 at 03:45

6 Answers6

23

Since this question was first asked, there is now another alternative to handle this problem:
Azure Functions

Here are a couple of examples that could easily be modified to call a stored procedure that rebuilds your indexes

Also see

A few things to keep in mind with Azure functions

Edit September 5, 2021 to add additional information

It should be noted that if you need SQL Agent, you have another option now. I would suggest reading up on Azure SQL Managed Instances. You can see a comparison of Azure SQL to Azure SQL Managed instance here in the Microsoft Documentation. With Azure SQL Managed Instances, your transition to the cloud could be a lot simpler since a lot of the on-premise features you are used to are already there (including SQL Server Agent, DB Mail, etc.).

David Yates
  • 1,935
  • 2
  • 22
  • 38
  • 1
    An additional advantage of hosting your azure function in an app service plan is you can have jobs that run as long as you like, whereas the serverless-compute consumption plan has an absolute max of 10 minutes and your job could get killed at any time after that. – Larry Smith Jun 01 '20 at 21:14
8

This feature has been rejected by Microsoft (link no longer available).

To quote their response:

Today in Azure there are several alternatives,

  1. SQL Database Elastic Jobs https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-overview

  2. The Azure job scheduler http://www.windowsazure.com/en-us/services/scheduler/

  3. The new preview of Azure Automation http://azure.microsoft.com/en-us/services/automation/.

  4. SQL Server in a VM

Option 1 requires an additional dedicated cloud service, which increases cost. Option 2 is free (I think) as long as you don't run more than once per hour.

Dunc
  • 18,404
  • 6
  • 86
  • 103
5

Azure SQL does not support sql jobs. From documentation:

Microsoft Azure SQL Database does not support SQL Server Agent or jobs. You can, however, run SQL Server Agent on your on-premise SQL Server and connect to Microsoft Azure SQL Database.

WebJobs: If you have a website you can create webjob and run it on schedule. See more here

Other alternatives - Scheduling job on SQL Azure

b2zw2a
  • 2,663
  • 15
  • 15
  • 2
    the doc is obsolete – liang Oct 23 '20 at 03:10
  • this is inaccurate now. Azure supports sqlagent jobs directly on managed instance & via Elastic Jobs - resources and tutorials since at least 2021 https://www.youtube.com/watch?v=JIMgqkXZFOQ – simon coleman Jul 01 '22 at 09:14
0

Another option is rovergo, a service that allows you to schedule sql jobs with a cron expression. This is nice because you don't have to create a web job or azure function. You can simply schedule a sql script.

(I'm a developer on rovergo)

jrummell
  • 42,637
  • 17
  • 112
  • 171
  • By looking at the pricing plan, rovergo doesnt seem to support long running jobs, such as index maintenance – Henningsson Mar 27 '19 at 12:03
  • @Henningsson there is a custom plan available if the standard plans won't fit your needs. We'll certainly take that scenario into consideration! – jrummell Mar 27 '19 at 13:47
  • @Henningsson we're in the process of updating our pricing plans, and we'd love to hear your feedback. If you have a few minutes, feel free to reply here or contact us at https://www.rovergo.io/Contact – jrummell Mar 29 '19 at 15:59
0

You can use Azure automation to schedule jobs on an Azure-DB like the on premise SQL Agent.

See https://azure.microsoft.com/nl-nl/blog/azure-automation-your-sql-agent-in-the-cloud/ for more information.

Rolf Schouteten
  • 1
0

Available for a couple of years now, Elastic Jobs for azure db...

docs: https://learn.microsoft.com/en-us/azure/azure-sql/database/job-automation-overview?view=azuresql

tutorial: https://www.youtube.com/watch?v=JIMgqkXZFOQ

Currently seems to use the 2017 version of the sqlagent sp (or a close approximation), but elastic links are now already pointing to SQL2022 preview which contains a newer version of the agent sps

simon coleman
  • 349
  • 2
  • 5