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).
Russ Thomas - SQL Judo 
This article is really helpful. It would be really great if you can describe the detail steps for each of them.
That probably would be worth more in-depth coverage. Though, there are a lot of great tutorials out there on AG and setting it up in standard is nearly identical. If you have Pluralsight I cover it thoroughly in this course: https://www.pluralsight.com/courses/sql-server-high-availability-disaster-recovery.
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.
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
Yeah, the script goes something like this. I’m posting remotely so I can’t verify exact match, but should be very close.
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.
Another option, if you are handy with powershell, would be to use…. well, powershell.
Thanks Russ I will give it a try. Happy New Year
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.
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.
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…
I would make sure the listener is always considered King BAG. Less to keep track of that way.
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”.
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)
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
— 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;
DECLARE @ag_king UNIQUEIDENTIFIER;
SELECT
@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,
AG_Name VARCHAR(MAX),
AG_ID UNIQUEIDENTIFIER,
AG_Role VARCHAR(MAX)
);
INSERT INTO @AGs
SELECT
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
WHERE
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_id UNIQUEIDENTIFIER;
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
WHERE
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
BEGIN
SELECT
@ag_loyal_id = ag_id,
@ag_loyal_name = ag_name
FROM @AGs
WHERE
@count = RowNum;
SELECT @count = @count – 1;
DECLARE @message VARCHAR(MAX);
DECLARE @recipients VARCHAR(MAX) = ‘SomeEmailAddress@emailaddy.com’;
DECLARE @subject VARCHAR(MAX);
BEGIN TRY
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;
END TRY
BEGIN CATCH
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 + ‘]’+’
‘+ERROR_MESSAGE();
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘alasql0002’,
@recipients = @recipients,
@subject = @subject,
@body = @Message;
END CATCH;
END;
END;
ELSE
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’
END
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.
Yep, that’s essentially what a listener is. I prefer adding it on the SQL side.
Thanks for the answer mate!
But is it reliable to use the WSFC name and IP in place of SQL Server Listener?
Never done it, but check this MSDN blog. He seems confident in its reliability for using it for multiple listener scenarios. https://blogs.msdn.microsoft.com/sqlalwayson/2012/02/03/how-to-create-multiple-listeners-for-same-availability-group-goden-yao/
Nope, that is not what we are looking for. But your article was very helpful. Thanks again.