0

Actually In azure we are using Azure SQL Manage Instance. In or organization one MI server having similar 3 database with different name. Now we need to setup SQL jobs which run against all this three database. If we use SQL agent of MI then we need to setup three copy of each job for each db.

So, we are planning to setup elastic agent job to execute single job to run all this three dbs. If anyone having other approach then let me know. if we use this approach then elastic job agent should work with MI databases?

NP007
  • 659
  • 8
  • 21

1 Answers1

1

Actually, Elastic Database Jobs doesn't support Azure SQL managed instance.

The following job scheduling technologies are available in Azure SQL Database:

  • SQL Agent Jobs are classic and battle-tested SQL Server job scheduling component that is available in Managed Instance. SQL Agent Jobs are not available in Azure SQL single databases.
  • Elastic Database Jobs (preview) are Job Scheduling services that execute custom jobs on one or many Azure SQL Databases.

Please reference here: https://learn.microsoft.com/en-us/azure/sql-database/sql-database-job-automation-overview

And for Azure SQL managed instance:

  • Managed Instance will still use a limited version of SQL Server Agent. For more information about Elastic Jobs and SQL Agent, there is more documentation here.

Ref: Elastic Jobs in Azure SQL Database – What and Why

Hope this helps.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23
  • Thank you Leon for your input.Now you have any idea how we can setup single job to run on multiple dbs on same SQL MI? – NP007 May 14 '20 at 07:42
  • @NP007 Sorry, I haven't tried that in SQL MI before, just from the document SQL agent: On Azure SQL Database Managed Instance, most, but not all SQL Server Agent features are currently supported. See [Azure SQL Database Managed Instance T-SQL differences from SQL Server](https://learn.microsoft.com/azure/sql-database/sql-database-managed-instance-transact-sql-information#sql-server-agent) for details. – Leon Yue May 14 '20 at 08:01
  • You may post another question about how to using SQL agent job for Azure SQL MI. – Leon Yue May 14 '20 at 08:03