Lesson Learned #2: Do you need SQL Server Agent for Azure SQL DB?
Many questions came to us, about SQL Server Agent for Azure SQL DB. Unfortunately, we don’t have the SQL Agent service in the same way that you have in OnPremise, but we have a multiple solutions for it:
- If you are able to maintain the SQL Agent, you could create a linked server against to Azure SQL Database and you could run any jobs any stored procedure to the SQL Database.
- Using a batch command or powershell, you could run any process, but, in this case you need to implement the business logic in order to manage the issues. You may run this batch command, using a virtual machine on Azure or try to implement any special worker role to implement it, if you need more options.
- You have a solution called Elastic Jobs where you could run any powershell command and apply to any database with similar behavior of SQL Agent.
- SQL SERVER Integration Services could be another solution, because any process could be called against the Azure Database.
- Azure Functions , Common scenarios for Azure Functions Timer-based processing.Azure Functions supports an event based on a timer using CRON job syntax. For example, you could execute code that runs every 15 minutes and cleans up a database table based on custom business logic.
- Finally other possible solution could be use an Azure Runbook.
Comments
- Anonymous
October 10, 2016
We do now have a answer for SQL Agent for Azure SQL Database, it is the feature called Elastic Jobs. We are in preview with the feature and I would be happy to on board anyone who is interested. Elastic jobs is optimized with functionality specifically for Azure SQL Database. We have also extended the API of Elastic job to include T-SQL very similar to SQL Agent which will reduce friction of migrations.