2

In setting up our HA environment (AlwaysOn AG), I came across the add-in ssms_2012_ag_addin_setup.exe that I got from SQLSkills. http://www.sqlskills.com/blogs/jonathan/synchronize-availability-group-logins-and-jobs/

This is very helpful in tackling the challenge of syncing the uncontained objects from the primary to secondary replicas. This add-in does not include the sync of operators, alerts or credentials. It also does not account for the need to allow any SQL job to execute on failover ONLY. I can add the following to the first job-step to accomplish this.

DECLARE @ServerName NVARCHAR(256) = @@SERVERNAME
 DECLARE @RoleDesc NVARCHAR(60)

SELECT @RoleDesc = a.role_desc
 FROM sys.dm_hadr_availability_replica_states AS a
 JOIN sys.availability_replicas AS b
 ON b.replica_id = a.replica_id
 WHERE b.replica_server_name = @ServerName

IF @RoleDesc = ‘PRIMARY’

BEGIN

— Job Step Logic

END

ELSE
 EXEC msdb..sp_stop_job –Job Name

Do you have any better solutions to automating these needs when using AwaysOn Availability Groups as a HA solution.

2

Native functionality, no.

AlwaysOn Availability Groups have the containment of database. Server objects (Agent jobs, logins, alerts, etc.) just simply don't play a HADR role with availability groups.

Whenever you run into the need to ensure that server-level objects exist on all replicas, or that they only execute on [particular_replica] then you will have to have this custom logic.

  • Thomas, I have run the ssms_2012_ag_addin_setup_exe. But it did not added the addin in always ON. Am I missing any step after . – user70067 Jul 7 '15 at 12:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.