SQL Server 2016 AlwaysOn Availability Group Enhancements: Basic Availability Group on Standard Edition
Basic Availability Group on Standard Edition
Historically, SQL Server database mirroring offered high availability of a SQL Server database. AlwaysOn availability groups has supplanted database mirroring as the future of high availability in SQL Server, meanwhile, database mirroring has been marked for deprecation. As database mirroring is deprecated, SQL Server 2016 introduces AlwaysOn Basic Availability Groups, offered as a database mirroring replacement, offering a similar feature profile.
Basic availability groups provide the capability to use AlwaysOn availability groups with SQL Server Standard Edition servers, with the same limitations that database mirroring imposes today.
Basic Availability Group Limitations
Basic availability group ‘limitations’ are those that reflect legacy SQL Server mirroring functionality. In other words, these limits on a basic availability group result in behavior that ‘mirrors’ SQL Server database mirroring.
- A basic availability group may not contain replicas on instances older than SQL Server 2016.
- Only one database may exist in a basic availability group.
- The AUTOMATED_BACKUP_PREFERENCE setting of the availability group will be restricted to ‘PRIMARY’.
- The secondary replica is not readable:
- Any backup operations on the secondary will fail.
- CHECKDB will not be allowed on a secondary.
- The replicas in a basic availability group may be configured for synchronous or asynchronous commit.
- Basic availability groups may form a hybrid availability group (one replica on premise and one in an Azure virtual machine).
Create a Basic Availability Group
A basic availability group can be created two ways: using the CREATE AVAILABILITY GROUP syntax with the new BASIC keyword, or using the availability group wizard.
CREATE AVAILABILITY GROUP (Transact-SQL)
You can create a basic availability group using the Transact-SQL CREATE AVAILABILITY GROUP. Here is the syntax:
CREATE AVAILABILITY GROUP <My_AG> WITH [BASIC | ADVANCED]...
The following is the syntax to create a basic availability group named agBasic containing database agdb and hosted on replicas SQL16N1 and SQL16N2:
CREATE AVAILABILITY GROUP [agBasic]
WITH (BASIC)
FOR DATABASE [agdb]
REPLICA ON
N'SQL16N1' WITH (ENDPOINT_URL = N'TCP://SQL16N1.AGDC.COM:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT),
N'SQL16N2' WITH (ENDPOINT_URL = N'TCP://SQL16N2.AGDC.COM:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);
Create an Availability Group using the New Availability Group Wizard
When creating an availability group on a standard edition of SQL Server using the new availability group wizard, a basic availability group is created. When creating an availability group on an enterprise edition of SQL Server an advanced availability group is created.
When creating a new basic availability group using the new availability group wizard certain features will be disabled that are not compatible with this restricted version of the availability group. For example, in the Add Replica dialogue once two replicas have been added, the Add Replica button grays out. Also, under Backup Preferences, those settings are grayed out as well.
 |
 |
DMVs for Basic Availability Groups
How can I tell if the availability group is Basic or Advanced? Query sys.availability_groups for the basic_features column, if it is set to 1, the availability group is a basic availability group.
select name, basic_features from sys.availability_groups
FAQ for Basic Availability Groups
When I attempt to create an availability group or alter an availability group, message 41199 is returned Message 41199 is reported when you attempt to create an availability group or modify an availability group in such a way that is not compatible with basic availability groups. For example, since basic availability groups do not support readable secondary, attempting to create an availability group and configure the SECONDARY_ROLE for a replica fails:
Msg 41199, Level 16, State 8, Line 11
The specified command is invalid because the Always On Availability Groups allow_connections feature is not supported by this edition of SQL Server. For information about features supported by the editions of SQL Server, see SQL Server Books Online.
Here is another example, attempting to join a replica running on an Enterprise Edition of SQL Server to an existing basic availability group fails:
Msg 41199, Level 16, State 2, Line 1
The specified command is invalid because the Always On Availability Groups join availability group (basic) feature is not supported by this edition of SQL Server.
For information about features supported by the editions of SQL Server, see SQL Server Books Online.
Msg 41158, Level 16, State 3, Line 1
Failed to join local availability replica to availability group 'agbasic'. The operation encountered SQL Server error 41199 and has been rolled back.
Check the SQL Server error log for more details. When the cause of the error has been resolved, retry the ALTER AVAILABILITY GROUP JOIN command.
When I try to create a basic availability group it fails Basic availability groups can only be created on a standard edition of SQL Server. Attempting to create a basic availability group in SQL Server Enterprise Edition will fail:
Msg 534, Level 15, State 1, Line 3
'BASIC' failed because it is not supported in the edition of this SQL Server instance 'SQL16N1'. See books online for more details on feature support in different SQL Server editions.
I cannot set Readable Secondary to anything other than No This is expected. SQL Server Management Studio restricts settings like readable secondary to those values that are supported by basic availability groups:
How do I upgrade from a basic availability group to an advanced availability group? There is no upgrade path to change a basic availability group to an advanced availability group. This comes into play if you edition upgrade SQL Server from standard edition to enterprise edition.
The availability group will still be a basic availability group and restricted in the ways a basic availability group is restricted. In order to remove these restrictions, the availability group must be dropped and recreated. Attempting to issue ALTER AVAILABILITY GROUP SET (ADVANCED) is not recognized by SQL Server.
For more information see the Basic Availability Groups page:
Comments
- Anonymous
October 22, 2016
Great post - FAQ section made it even more informative !I couldn't find any resources to know if Direct Seeding is supported on Basic AG - Appreciate if someone shed light on this point !Thanks much.- Anonymous
November 28, 2016
Yes, direct seeding on a basic ag worked for me.
- Anonymous
- Anonymous
October 27, 2016
So I configure a bag but want to report off the secondary, sorry no can do. So what the heck can I do other than buy enterprise which is never gonna happen. Use snapshots? No sorry you need enterprise again! Oh dear, report of the primary is my only option? - Anonymous
November 20, 2016
All I can say is SERVICE PACK ONE has saved the day!!!!!!!!!!!! - Anonymous
December 07, 2016
Do Basic Availability Groups in a multi subnet configuration support transactional replication? The redirect appears to try to establish the principal by querying the original publisher, which with basic availability groups is not readable when failed over - Anonymous
June 22, 2017
Thanks for the explanation. - Anonymous
March 02, 2019
This is especially frustrating because the majority of the docs don't specify "Enterprise Required". I spent a day trying to figure out an issue with mirroring configuration that turned out to be a bug in SSMS and now I find that Basic Availability with a readable secondary "is not supported by this edition of SQL Server". Seriously Microsoft? I guess it's back to log shipping unless someone here can tell me how to utilize these great new features and have a readable secondary. Oh and don't get me started on the fact that you can only have one database per availability group in Standard. All this is making MySQL or Percona look that much better. Take note Microsoft.