SQLTeam.com | Weblogs | Forums

Runing a script after on alwayson failover SQL Server 2012

sql2012

#1

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.


#2

You an use SQLCMD


#3

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


#4

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


#5

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


#6

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?


#7

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.


#8

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.


#9

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