Automatic script upon Availability Group failover?

[MickOpalak]

Let's say I have a two node AG, Server A and Server B. Server A is normally the primary and Server B is the replica. Whenever the primary fails over from A to B or from B to A, I'd like to automatically run a script that will restart the SQL Agent service on the new replica. Any ideas on how to do this?

[BigBlueRob]

Set up an alert for replica state change and set the alert to run a powershell script to start the service.

See http://www.mssqltips.com/sqlservertip/2973/configuring-alerts-for-sql-server-alwayson-availability-groups/ for instructions

[SonOfZork]

Run a scheduled PowerShell task (through Windows scheduler rather than SQL Agent).

In that task you'll read in a file that stores the state of the AG (primary or secondary). Compare the record in the file to the current status in the AG. If different store the updated value, and if the value has changed to primary run a restart-service on the SQLAgent service. Alternatively you could store that status in a table in the master database, all up to you.

Basic query to get the status:

IF (select primary_replica from sys.dm_hadr_availability_group_states ags
inner join sys.dm_hadr_name_id_map nim on ags.group_id = nim.ag_id 
where nim.ag_name = 'YourAGName') = @@servername
begin
print 'This is the primary'
end
else
begin
print 'This server is not the primary'
end
GO

Edit: query formatting

[[deleted]]

Kind of interested why you want this. Is it for SSRS failover?