Runing a script after on alwayson failover SQL Server 2012

Hi
Here is the issues. I am unable to use Power Shell for security reasons, and I need a script to run after a failover. The script updates a table in one of the database to allow a third party app to connect on the new server that the database has failed over to. The script is simple

update ReportDatabase set RprtDtbseSrvr = @@SERVERNAME;
Go

How do I do this without Power Shell.

You an use SQLCMD

Create a SQL Server Agent job and set the schedule to start automatically when SQL Server agent starts.

The SQL agent does not restart each time the database fails over, it is up and running on both nodes all the time.

how would out get the sqlcmd to run automatically when the database fails over?

How do you recognize that a fail over has occurred? SQLCMD is a command line application, so you can run it from anywhere where you have the capability to trigger a command line job from. Perhaps one of the trigger types in the Windows Task Scheduler?

Right - so you are left with creating an alert in SQL Server agent and firing off your script when the alert is triggered. You will need to alert on the AG - Database role change and then execute your code.

1 Like

Hi, this sames to have worked, created the Alert under the agent. and for the response I have it execute a job with the script. I used the error number: 1480, but I was also thinking about 19406. Which do people think is the best one to use.

I am not familiar with that error number - so I couldn't say for sure.