Sql-server – Creating SQL Server Job on each replica in availability group

availability-groupssql server

I have an availability group with 2 nodes (one primary and another secondary). The availability database has a stored procedure which is used to create a SQL Server job dynamically, based on user input. With this the jobs are always created only on the primary server. How can I create the same job on each replica in the availability group?

The listener redirects to primary server and the stored procedure on the primary server creates the job on the primary server only. I want to know if there is a way to modify the procedure on the primary server so that it can create jobs on other replicas in the availability group? While running the job, I will put a simple check, so that the job will run only on the current primary server only.

Best Answer

Why not use a master server for the SQL Server agent? You could even use a third server to create and schedule the jobs on both the servers and you get a central repository and log server for the jobs. A small check in the agent job will make certain that it's running on the active server.