0

I am working on a solution for a quite specific problem with environments (SQL Server using AO, with two replicas) having SSRS in scale-out, each node has MSSQL and SSRS installed, and when there is a Failover for MSSQL, one account is removed from the SSRS SCALE-out, for that reason I need to apply a workaround that I have already available, however now I have to find the exact moment to apply it, I have two options,

  1. first is to create a SQL Agent job that runs every x minutes and does apply the fix
  2. find a way to capture the failover event and apply the fix. has anyone created a SQL Server trigger to capture Always On failovers?
2
  • 1
    What does "one account is removed from the SSRS SCALE-out" mean? Sep 16, 2022 at 0:34
  • I have a local windows account that the WMS application uses to authenticate to SSRS, whenever a new replica takes the primary role, the SSRS stops recognizing the user as valid and removes it, I had a case open with MS and basically, I was told this was behavior by design, so every time a failover occurs I have to add it back with the permissions the account needs. we have already discarded the option of using AD account for this as the vendor do not support it.
    – Carlos DBA
    Sep 16, 2022 at 13:01

2 Answers 2

0

For "on event": In SQL Server Agent, try setting up an Alert for whichever of the errors you want an action to be taken on.

You can specify the "Response" to be a SQL Server agent job, which can take actions at the command line if need be (xp_cmdshell is one option for this).

LETTING SQL SERVER HAVE COMMAND LINE ACCESS IS DANGEROUS


Safer, of course, is to run some job every N seconds to check on the status. You could have Windows Scheduler or whatever enterprise scheduler you might use have a job that uses sqlcmd to query the database status, and from there do whatever else you need to, either inside SQL with sqlcmd or outside it with your scheduling system.

0

You can create a Job on SQL Server Agent. Then create an Alert:

enter image description here

Give in a name and errornumber 1480:

enter image description here

Navigate to Response and add your just created job:

enter image description here

1
  • Thank you Peter for taking the time to answer, I had forgotten about the question, this was one of the approaches I have used, also I made use of a schedule to use the same job to perform all the heavy lifting
    – Carlos DBA
    Nov 21, 2022 at 15:24

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.