Runing a script after on alwayson failover SQL Server 2012

Question

Runing a script after on alwayson failover SQL Server 2012

j_a_t_h

SSC Veteran

Points: 228
More actions
March 1, 2016 at 9:51 am

#316476

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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

R.P.Rozema SSChampion Points: 12317 More actions · Answer 1

I haven't tried it, so it's just an idea. Have you tried scheduling a SQL Server Agent job at 'SQL Server Agent Startup'?

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

Jon.Morisi SSChampion Points: 12846 More actions · Answer 2

Can you configure the third party app to point to the "SQL Server Network Name"?

JeremyE SSCoach Points: 16213 More actions · Answer 3

You could setup a SQL Agent job on each replica to run the following:

DECLARE @ServerName NVARCHAR(256) = @@SERVERNAME

DECLARE @RoleDesc NVARCHAR(60)

DECLARE @AGName NVARCHAR(256) = 'YourAGNameHere'

SELECT @RoleDesc = rs.role_desc

FROM sys.dm_hadr_availability_replica_states rs

JOIN sys.availability_replicas r

ON rs.replica_id=r.replica_id

JOIN sys.availability_groups ag

ON rs.group_id=ag.group_id

WHERE r.replica_server_name = @ServerName

AND ag.name = @AGName

IF @RoleDesc = 'PRIMARY'

BEGIN

UPDATE ReportDatabase SET RprtDtbseSrvr = @@SERVERNAME;

END

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 4

I'm not sure how "good" of a solution it is, but we've had success by creating a SQL Server Agent alert on Error number 1480 (Database replica role change) on all databases. We have it fire off a SQL job, and e-mail our DBA team.

You'll need that alert set up on your primary and secondary replica(s).

To avoid having both inadvertently fire in the case of a manual failover (or any other failover where both instances stay up), we have the jobs wait 2 minutes (WAITFOR DELAY '00:02:00'), and then wrap the job tasks in an if statement so they only run if they are the primary.

IF (SELECT MASTER.DBO.FN_AG_IsPrimary('<AG NAME>')) = 1

BEGIN

-- DO YOUR FAILOVER STEPS

END

You may need to tweak your delay longer or shorter depending on your environment and needs, but for us we seem to be pretty solid with our setup.

The Redneck DBA

Jon.Morisi SSChampion Points: 12846 More actions · Answer 5

Is this for alwayson failover cluster or alwayson availability groups?