I have a 2 node AG setup (Not an FCI). I need to configure an alert to trigger a job based on a string found in the error log showing that the node is now the new primary.
The string I want to use is:
'primary_pending' to 'primary_normal'
This is what shows in the error log when the secondary node becomes primary. I want the alert to kick off a SQL job I created to automate our SSRS to use the new primary replica.
Below is the alert I setup, but it is not triggering properly. I do have the alert configured to execute the job when this alert is triggered.
When a failover happens, the job does not kick off. I'm guessing it may be due to the error number used. I found a blog post about using this, but can't seem to find it again. The error log does not display any error number in the line with this text, so I think that is the issue.
How can I get an alert to trigger based on text it finds in the error log, or is there a better way to do this?
I do have conditional logic to check for primary / secondary status in all my jobs, but I can't use that for this job as I want it to only execute 1x (when it first becomes primary).