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.