If you’re using AGs, don’t apply these patches:
- SQL 2012 SP2 CU3
- SQL 2012 SP2 CU4
- SQL 2014 CU5
until you read this Microsoft post about a breaking bug.
Your AG may stop synchronizing due to blocking between user queries and a system session. The fix is to disable automatic failover, restart the primary, and enable automatic failover again.
Carry on.
4 Comments. Leave new
Thanks for the quick update Brent..
Hi Brett
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.
Thanks for any imput you may have. I don’t think I am the only DBA facing this issue.
Miles Schor
Miles – rather than doing Q&A here on an unrelated blog post, I’d recommend using a Q&A site like http://dba.stackexchange.com. Explain what you need there, and other users may be able to answer it for you. Hope that helps!
Hello,
you could try this solution :
IF EXISTS (SELECT 1
FROM sys.databases AS d
LEFT JOIN sys.dm_hadr_availability_replica_states AS r
ON r.replica_id = d.replica_id
LEFT JOIN sys.availability_groups AS g
ON g.group_id = r.group_id
WHERE d.database_id = DB_ID(‘myDb’)
AND (r.role_desc = ‘PRIMARY’ OR r.role_desc IS NULL)
AND d.state_desc = ‘ONLINE’)
BEGIN
drop table test;
END
Enjoy