Russ Thomas – SQL Judo

The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded

A Group of Basic Availability Groups

Can a group of basic availability groups mimic an enterprise level availability group for far less cost?

With SQL Server 2016 we have finally been able to have some form of AlwaysOn AG with SQL Server Standard edition.  Quite a relief since mirroring has been deprecated.  At our site we have several AlwaysOn implementations already in place on Enterprise, but not every server justifies that kind of expense.  However, just because we don’t want to spend several grand on licensing doesn’t mean we can do without HA/DR.  So, I repeat, it was a bit of a relief when Basic Availability Groups were released with SQL 2016.

For a recent project that required HA/DR but couldn’t justify Enterprise edition we decided to take the plunge on 2016’s Basic Availability Groups.

For a quick rundown of the watered down feature set – basically what you don’t get with a Basic Availability Group (BAG) – the major points are as follows:

  • You can only have 2 nodes.
  • Only one database can be in the group.
  • You can not have the secondary be in read only.
  • You can not take backups from the secondary.

I find it funny that the technology is called availability groups inferring that all your protected databases can travel as a group, but the basic implementation on SQL Standard only supports one database.  It’s no longer an availability group but a database with high availability.  But, whatever.  It’s better than nothing.

At first glance at this list of “can’t”s we thought however, this won’t work at all.  One, we have four databases that support an SOA application (ignore the acronym redundancy please) – and we need to ensure that all four fail together.  We don’t want to use mirroring, after all, it’s been deprecated.  We do however want to remain on SQL Standard.  Further, we need a listener, because our applications don’t respond well to failover-partner specifications in the connection string due to the lag time of figuring out who is in control of the pair.  We’d also rather not deal with a witness.

What if we get creative?  The question before the court is?  Can you implement more than one BAG on a single instance that all use the same underlying cluster.  Further, can you assign a listener?  My thought is probably, after all, it’s just a virtual identity.  But, would the other BAGs on the same pair of servers benefit if a listener were configured for just one of the BAGs.  We could pick one as King BAG.  Would it work?

The real world answer is yes, and yes.  We just finished setting up four availability groups on a two node pair.  There is a single cluster in place and all four BAG setups work just fine on that one cluster.  We picked one BAG to be King BAG and configured a listener.  Sure enough, all other BAGs on the instance can utilize that same listener, (like I said, it’s just a virtual identity).  We just have to be careful that all primaries are always on the same node.

That leads us to the final question.  How do we ensure they all travel together like a normal Enterprise Edition AG Group would (ignore acronym redundancy please). 

Well, since it’s all on the same cluster, under 95 percent of the failure circumstances, the cluster will move them all – even though they are technically different AGs.  For those rare cases where they might get out of sync, such as a manual move, we implemented a job that regularly checks where King BAG is.  It then verifies that all other BAGs on the instance are also on the same primary instance as King BAG and if not, manually moves them.  This is a running agent job that constantly checks.

bags

Finally, this is all set up on a multi-subnet environment.  One node is in one datacenter.  The other node is in the other datacenter.  So, a background concern if multisubnet would work with a BAG was also resolved.  It does.

To be clear.  It was a bit of a pain to set up.  But now that it’s in place the only real perceptible difference between multiple BAGs on Standard as opposed to a single AG Group (ignore acronym redundancy please) on Enterprise is that we can’t use a read only secondary, and that our AG nodes have a few more entries in SSMS plus the overhead in management that requires on human staff.  The underlying cluster still does a pretty good job of treating them like one group.

All in all, it’s pretty slick and allows us to achieve exactly what we wanted on the cheap.  And, before you ask.  Yes, we tested failovers.  Both manual and forced (by killing the primary node).

12 comments on “A Group of Basic Availability Groups

  1. BAG
    December 9, 2016

    This article is really helpful. It would be really great if you can describe the detail steps for each of them.

  2. BAG
    December 13, 2016

    I dont have an access to pluralsight. Would it be possible for you to just send the code for checking all BAGs on the instance are also on the same primary instance as King BAG and if not, manually moves them.

  3. Stefan
    December 29, 2016

    Excellent article. I have the exact same situation with basic aoag. Is there any possibility that you can share the script you are using for checking King bag?
    Regards Stefan

  4. Russ Thomas ( @SQLJudo )
    December 29, 2016

    Yeah, the script goes something like this. I’m posting remotely so I can’t verify exact match, but should be very close.

    DECLARE @king_bag UNIQUEIDENTIFIER;
      -- set this to GUID of king bag
    SELECT  @king_bag = ag_id
    FROM    sys.dm_hadr_name_id_map
    WHERE   ag_name = 'MyKingBag';
    
    DECLARE @loyal_subject UNIQUEIDENTIFIER;
      -- other bag
    SELECT  @loyal_subject = ag_id
    FROM    sys.dm_hadr_name_id_map
    WHERE   ag_name = 'MyOtherBag';
    
    
    USE master;
    IF ( SELECT role_desc
         FROM   sys.dm_hadr_availability_replica_states
         WHERE  group_id = @king_bag
                AND is_local = 1
       ) = 'PRIMARY'
        BEGIN
    
            IF ( SELECT role_desc
                 FROM   sys.dm_hadr_availability_replica_states
                 WHERE  group_id = @loyal_subject
                        AND is_local = 1
               ) <> 'PRIMARY'
                BEGIN
    
                    ALTER AVAILABILITY GROUP MyOtherBAG FAILOVER;
    
                END;
    
        END;
    

    You would then just add a new if block for each of the other BAGs on the set. Make sure the names line up to what you’ve selected.

  5. Russ Thomas ( @SQLJudo )
    December 29, 2016

    Another option, if you are handy with powershell, would be to use…. well, powershell.

  6. Stefan
    December 29, 2016

    Thanks Russ I will give it a try. Happy New Year

  7. Gary
    January 13, 2017

    Russ,

    You’d mentioned that the listener is actually a virtual identity, presumably in Windows Failover Cluster. If so, how is this configured? I’m trying to use a single listener across multiple BAGs and have not been able to figure out how to do so. If this is configured through Failover Cluster Manager, should “alter availability group X add listener…” step be skipped and the virtual identity added directly to the cluster as a resource? If so, how is this accomplished?

    My apologies if this is simple or I’m completely misunderstanding.

    • Russ Thomas ( @SQLJudo )
      January 13, 2017

      You would configure right through SSMS or TSQL for one of your basic AGs. Once you get it configured for one, it will work for any other basic AG on that server instance. To be clear, it does create a cluster resource as well, and this can be done directly on the cluster, but it’s waaaay easier to just do it through SQL Server. The key is, you only have to do it on one. The others will automatically get the same benefit.

      • Gary
        January 13, 2017

        Thanks, Russ. The confusion stems from the listener only appearing under one the BAG’s “Availability Group Listeners” folders in SSMS. I have connected through the listener and can verify that all databases on the instance, regardless of BAG membership, are being listed. Thanks.

        Just so I understand, the BAG which has the listener associated with it (in other words, the BAG with the listener in “Availability Group Listeners” folder in SSMS) is the “King BAG” which should be used for failover, correct? Perhaps it doesn’t matter…

      • Russ Thomas ( @SQLJudo )
        February 20, 2017

        I would make sure the listener is always considered King BAG. Less to keep track of that way.

  8. Shane O'Neill
    March 23, 2017

    Thanks Russ, this is actually the only resource I’ve found that actually states plainly and clearly “Yes, you can have multiple BAGs on the same instance”.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Information

This entry was posted on July 25, 2016 by in HA/DR.
%d bloggers like this: