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.


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).


18 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.

      -- set this to GUID of king bag
    SELECT  @king_bag = ag_id
    FROM    sys.dm_hadr_name_id_map
    WHERE   ag_name = 'MyKingBag';
      -- 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'
            IF ( SELECT role_desc
                 FROM   sys.dm_hadr_availability_replica_states
                 WHERE  group_id = @loyal_subject
                        AND is_local = 1
               ) <> 'PRIMARY'

    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


    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”.

  9. Nicko
    March 16, 2018

    I created a stored procedure based on Russ’ script within the Master database that is executed every 10 seconds via an Agent job (perhaps there is a better way to do this?). Anyways, here is the stored procedure in case anyone might find it useful:

    CREATE PROCEDURE [dbo].[LoyalAGFailover]
    — Add the parameters for the stored procedure here
    @DBMailProfile VARCHAR(MAX),
    @AvailabilityGroupKingName VARCHAR(MAX)
    — SET NOCOUNT ON added to prevent extra result sets from
    — interfering with SELECT statements.

    — Insert statements for procedure here
    — Set the Database Mail Profile to use on this server
    DECLARE @profile VARCHAR(MAX) = @DBMailProfile;

    — Set the King Availability Group
    DECLARE @ag_king_name VARCHAR(MAX) = @AvailabilityGroupKingName;

    @ag_king = ag_id
    FROM sys.dm_hadr_name_id_map
    WHERE ag_name = @ag_king_name;

    — Create information table of all Loyal Availability Groups that are Secondary
    PRINT ‘Filling information table with all Loyal Availability Groups that are Secondary on this server’
    DECLARE @AGs as table
    RowNum INT,
    Row = ROW_NUMBER() OVER(ORDER BY ag_name ASC),
    AG_Name = m.ag_name,
    AG_ID = m.ag_id,
    AG_Role = s.role_desc
    FROM sys.dm_hadr_availability_replica_states s
    JOIN sys.dm_hadr_name_id_map m
    on m.ag_id = s.group_id
    m.ag_id != @ag_king
    and is_local = 1
    and s.role_desc = ‘Secondary’
    — Specify specific availability groups to fail over automatically with the King availability group
    and ag_name in (‘AG1′,’AG2′,’AG3’); — ********** THIS NEEDS TO BE SET MANUALLY FOR EACH SERVER IF USED **********
    PRINT ‘Informational table has been filled’

    DECLARE @ag_loyal_name VARCHAR(MAX) = ”;
    DECLARE @count INT;

    SELECT @count = COUNT(1) FROM @Ags; — Count of Loyal Availability groups that need to failover
    PRINT ‘There are ‘ + cast(@count as nvarchar(10)) + ‘ Loyal Availability groups that need to failover’
    PRINT ‘Checking if ‘ + cast(@ag_king_name as nvarchar(max)) + ‘ Availability Group is the Primary on this server’
    IF — Check if King Availability Group is Primary on this server
    SELECT role_desc
    FROM sys.dm_hadr_availability_replica_states
    group_id = @ag_king
    and is_local = 1
    ) = ‘PRIMARY’ and @count > 0
    BEGIN — King Availability Group is Primary, Failover any Loyal Availability Groups that are currently Secondary
    PRINT cast(@ag_king_name as nvarchar(max)) + ‘ Availability Group is the Primary on this server, failing over any Loyal Availability Groups that are currently Secondary’
    WHILE @count > 0
    @ag_loyal_id = ag_id,
    @ag_loyal_name = ag_name
    FROM @AGs
    @count = RowNum;
    SELECT @count = @count – 1;
    DECLARE @message VARCHAR(MAX);
    DECLARE @recipients VARCHAR(MAX) = ‘’;
    DECLARE @subject VARCHAR(MAX);

    DECLARE @sqlcmd VARCHAR(max);
    SET @sqlcmd = ‘ALTER AVAILABILITY GROUP [‘ + @ag_loyal_name + ‘] FAILOVER;’;
    EXEC (@sqlcmd);
    PRINT ‘Failover of ‘ + cast(@ag_loyal_name as nvarchar(max)) + ‘ SUCCEEDED, sending email’
    SET @subject = ‘Availability Group Failover to ‘ + @@SERVERNAME + ‘ SUCCEEDED for [‘ + @ag_loyal_name + ‘]’;
    SET @Message = ‘Availability Group Failover to ‘ + @@SERVERNAME + ‘ SUCCEEDED for [‘ + @ag_loyal_name + ‘]’;
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = @profile,
    @recipients = @recipients,
    @subject = @subject,
    @body = @Message;

    PRINT ‘Failover of ‘ + cast(@ag_loyal_name as nvarchar(max)) + ‘ FAILED, sending email’ + ERROR_MESSAGE()
    SET @subject = ‘Availability Group Failover to ‘ + @@SERVERNAME + ‘ Failed for [‘ + @ag_loyal_name + ‘]’;
    SET @Message = ‘Availability Group Failover to ‘ + @@SERVERNAME + ‘ Failed for [‘ + @ag_loyal_name + ‘]’+’

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = ‘alasql0002’,
    @recipients = @recipients,
    @subject = @subject,
    @body = @Message;
    PRINT ‘Either ‘ + cast(@ag_king_name as nvarchar(max)) + ‘ Availability Group is not the Primary on this server, or there are no Loyal Availability Groups that need to fail over – FINISHED’

  10. Jay Joshi
    February 14, 2019

    Can we use WSFC Virtual name and IP Address for the client to connect to all the BAGs?

    When you create a new AG it will automatically create a role in WSFC. So by using the WSFC virtual name and IP address client will be redirected to the primary replica.

Leave a Reply

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

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

Facebook photo

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

Connecting to %s


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