Job Database can be same as Target Database

Question

Hello,

I have a small purge job to be triggered on schedule on one of the table in Azure SQL Database. My database is an unmanaged instanced so we have created Elastic Job Agent which is in preview mode.

 While creating a job, it asks for creation of job database. Since I have only one job which is triggered on scheduled basis, I don't want to create a new database for the same. Can I use the same database that is Target database as Job Database for job creation ?

Please help advice.

Answer 1

Hi Andy,

By design it should be a database instance that is specific as the job database.

The Job database doesn't literally need to be new, but should be a clean, empty, S0 or higher service objective.

There are issues that arise where the elastic job being executed will affect the ability of job database to function correctly. It doesn't have to be a separate server but it should be a unique database specific to hosting the job database schema objects. 

Regards,

Mike

Answer 2

If you have a single simple task then I would consider Azure Automation rather than Elastic Database Jobs.

The need for it to be S0 and empty initially means that you'd have to create the Agent Database and then migrate your current database into that database.  Unless you are using schemas in your own database (not "dbo") then this could get messy and more difficult to secure correctly.

Azure Automation is probably a simpler route in your case.

---

Martin Cairney SQL Server MVP

Answer 3

Andy,

Please let us know if you require additional guidance or wish to discuss this topic further. If you implemented a solution that solves your project requirements and you wish to share that so as to assist others who are considering Azure SQL Database (single instance) Elastic Jobs, please take the time to share.

Regards,

Mike