0

I currently have a 2 node AG with automatic failover enabled. Some of the databases in the AG are also using change data capture, as such we have CDC_capture and CDC_cleanup jobs. The jobs are triggered to start when the agent starts. I have added a check to make sure the node the job is running on is primary, and that is also working fine. My issue occurs when a failover happens. It seems the agent does not re-start or start on failover so how can I make sure whenever my replica fails over the cdc jobs start? Is there a way to trigger an agent job on failover?

3
  • 2
    You could create a new alert (under the agent) which could use the Error Number 1480 and then trigger executing the job(s) you need executing on the firing of that alert.
    – rvsc48
    Commented Aug 12, 2022 at 17:01
  • @rscv48 I'm not familiar with the error number 1480 is that thrown somewhere every time a failover occurs or something like this?
    – deadLock
    Commented Aug 12, 2022 at 18:02
  • Correct - a 1480 is thrown when an AG Failover occurs,
    – rvsc48
    Commented Aug 12, 2022 at 20:31

1 Answer 1

0

Creating an alert under the agent which uses the number 1480 works as discussed. Whenever a failover occurs an "ErrorNumber" 1480 is thrown. I set the alert to trap error number 1480 and execute the job I needed to fire when a 1480 was detected. It still seems clunky, and I will look for alternate solutions but this will indeed work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.