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.
Best Answer
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.