search

DanielLoth / SQLServerJobRunner

MIT License
5 stars 2 forks source link

Support for Availability Groups - jobs not deployed to replicas #6

Open DaveDustin opened 1 year ago

DaveDustin commented 1 year ago

If you were to target a database that was part of a SQL Server availability group, the SQL Agent jobs would only be created on the primary replica.

This means that when there is a failover, the jobs would not be present on the other nodes and necessary maintenance might be missed.

DaveDustin commented 1 year ago

Difficult to do directly in SQL or via SQLPackage, but perhaps guidance on using something similar to https://docs.dbatools.io/Sync-DbaAvailabilityGroup could be included

DanielLoth commented 1 year ago

I'm actually wondering if a push-pull model could work with some initial DBA intervention at the outset.

Instead of pushing to N replicas each time, what if initially a procedure was added to the master database for every node, along with an accompanying agent job?

The agent job could periodically invoke the master database procedure. That procedure could scan non-system databases for the existence of JobRunner.Config. Where that table exists it can then check if all defined jobs within the table have been created.

This would mean that the SqlPackage.exe deployment doesn't actually create the SQL Agent job. It'd instead specify the particulars and rely on this per-node job to discover those particulars and carry out the work. Essentially it'd be issuing instructions via JobRunner.Config.