Urgent AlwaysOn Availability Groups Bug

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.

Previous Post
How to Query Extended Events Target XML
Next Post
Managing Oracle Performance with Statistics

4 Comments. Leave new

  • Thanks for the quick update Brent..

    Reply
  • 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

    Reply
  • Eric Simbozel
    June 15, 2015 4:42 am

    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

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}