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":""}