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?
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.