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.