Currently there are 3 different ways SQL jobs are getting created in my on-premise instance.
- SQL Jobs - Which will use SSIS Packages (Created Manually)
- SQL Jobs - Which runs the SPs (Created Manually)
- Automated Jobs created by SSRS subscriptions
I am trying to migrate on-premise SQL Server to Azure VM (IaaS) and I found something called Azure Scheduler. Now I am wondering how can I migrate above 3 type of Jobs!
Based on MSDN Forum by one of MVP
The right and recommended way is with Azure Scheduler. Scheduler can be used with success to replace the classical SQL Jobs. Another option is to use Azure Automation to trigger or even run different action on SQL Azure - https://azure.microsoft.com/en-us/documentation/articles/automation-manage-sql-database/.
But SSRS Subscriptions are dynamically generating SQL Jobs! How can I tell SSRS subscriptions not to create SQL Jobs rather to create Azure Scheduler?
Here is screenshot of SQL Jobs created by SSRS. As you see below its created dynamically!
Now how can I change or use azure scheduler here!
I have downloaded Image from blog post https://www.mssqltips.com/sqlservertip/1846/how-to-easily-identify-a-scheduled-sql-server-reporting-services-report/
Update
Since we have Always On setup in Azure, there will be two nodes having same SQL jobs. I want only SQL Jobs from Active node to be executed/run. So can I use Azure Scheduler to monitor the active node and run only SQL Jobs in that node?
Keeping Azure aside
I would like to make my question more simple. How can we handle SQL Jobs in SQLServers which are configured in Always On. As per my understanding always on will have 2 nodes so how we can find active node in this setup? because since both servers are active, jobs of both the servers will run and end up in many issues
My current investigation says SQL Scheduler is not right candidate for replacement of SQL Agents which runs on always ON configuration
Here are links which will provide in-dept details on finding PRIMARY nodes in Always ON and setting up SQL Agents
