0

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.

Paul White
  • 94,921
  • 30
  • 437
  • 687
Akshay Jain
  • 1
  • 1

3 Answers3

1

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.

Spörri
  • 4,734
  • 15
  • 28
0

I'd consider using something other then SQL Server Agent, something that can connect to the Listener. We schedule jobs via SSIS for example so we don't have to duplicate jobs.

If you do want to create the job on each server, you need to consider how you want it manage where it runs. It will fail on a non-readable listener etc

gbn
  • 70,237
  • 8
  • 167
  • 244
0

There's nothing out of the box that does this.

The Master Server that @Spörri suggested is commonly used, however requires an additional 4 cores of SQL licensing (unless you're using virtual machines and licensed the whole host). The other thing I've seen is people using Task Scheduler on one server which runs sqlcmd to connect to the current active server to run.

Licensing an extra server wasn't an option for us, so I created a job that runs on all the replicas and replicates all jobs (including itself) from the primary server. The replication job runs every hour. The job boils down to some simple calls to stored procs in msdb to create/delete jobs, schedules, etc. Each job created must be able to be run on all other servers (or stop itself from running by checking if it's currently the primary server).

The other advantage of having the jobs on all servers is that you remove the single point of failure that a master server has (if the master server goes down, so do all your jobs).

You can check out the whole series on the job here.

Greg
  • 3,292
  • 5
  • 33
  • 57