Syncing Uncontained Objects with AlwaysOn Availability Groups

Question

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.

Answer 1

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.