Infrastructure at your Service

David Barbarin

SQL Server 2016 : Basic availability groups may fit to your HA needs

In this blog let’s talk about SQL Server and availability groups in standard Edition. I had some customer questions about existing limitations and this is a good opportunity to review them on this blog post. First of all, let’s say that Microsoft has already documented the restrictions here and from my side, I already written a blog post on the subject at the time of the CTP version of SQL Server 2016. In the meantime, some changes occurred and are now included in the RTM version.

As a reminder, the Standard Edition offers limited capabilities of availability groups and it tends to be a replacement of previous feature called mirroring. This is a good news for customers that may finally upgrade their old environment.

The first main limitation concerns the number of replicas capped to only 2 and with no possibility to use the secondary replica for Reporting and backups offloading as well. But we want to use a Standard Edition and it is supposed to be sold at a lower cost right?

Let’s just have a quick check by using the T-SQL script. Generally speaking, I’m not a big fan of the GUI! So if I try to create an availability group with 3 replicas, I will obviously face the following error message:

Msg 35233, Level 16, State 0, Line 2
Cannot create an availability group containing 3 availability replica(s). 
The maximum number of availability replicas in an availability group  with (basic) is 2. 
Reenter your CREATE AVAILABILITY GROUP command specifying fewer availability replicas.

Let’s try to use a secondary replica as read-only replica (ALLOW_CONNECTIONS = ALL)

-- primary replica
CREATE AVAILABILITY GROUP [testGrpStd]   
FOR  
REPLICA ON
N'WIN20121SQL16\SQL16STD'
WITH (ENDPOINT_URL = N'TCP://WIN20121SQL16.dbi-services.test:5023',  
    FAILOVER_MODE = AUTOMATIC,  
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,   
    BACKUP_PRIORITY = 50,   
    SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL),   
    SEEDING_MODE = MANUAL)
,
N'WIN20124SQL16\SQL16STD'
WITH (ENDPOINT_URL = N'TCP://WIN20124SQL16.dbi-services.test:5022',   
    FAILOVER_MODE = AUTOMATIC,   
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,   
    BACKUP_PRIORITY = 50,   
    SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL),   
    SEEDING_MODE = MANUAL);   
GO

Well another error message I got here:

Msg 41199, Level 16, State 8, Line 2
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.

However, I noticed a big change compared to my first test with a CTP version concerning the possibility to add a listener to my availability group. From my point of view, this is a very good improvement compared to the mirroring feature because no changes will be required at the application layer with additional options at the connection string level. Let me prove it with the following script:

USE [master]
GO
ALTER AVAILABILITY GROUP [testGrpStd]
ADD LISTENER N'Test-GrpSTD' (
WITH IP
((N'192.168.5.122', N'255.255.255.0')
)
, PORT=1433);
GO

We can check the listener creation by using DMVs to get the listener configuration as shown below:

blog 103 - STD AG - listener 1

According to my discussions with some customers at our last SQL Server 2016 event, availability groups in Standard Edition may also suggest that more complex infrastructures like multi-subnet availability groups or new special features like direct seeding will not be supported. A first look at the Microsoft documentation doesn’t provide any clues. You can argue that the Microsoft documentation is black-list oriented and if it is not written explicitly in the documentation it should work and you are probably right. But you know, I’m curious and I was interesting to check myself on my lab environment!

For the both suppositions, I can confirm that I didn’t face any issue when creating an availability group with a different setup scenario on the top of multi-subnet WSFC or when using direct seeding. Here the script used to create my availability group with a listener and two IP addresses on different subnet. I also have enabled the direct seeding feature by configuring the SEEDING_MODE parameter to AUTOMATIC as shown below.

-- Availability groups with SEEDING_MODE = AUTOMATIC
CREATE AVAILABILITY GROUP [testGrpStd]   
FOR  
REPLICA ON
N'WIN20121SQL16\SQL16STD'
WITH (ENDPOINT_URL = N'TCP://WIN20121SQL16.dbi-services.test:5023',  
    FAILOVER_MODE = AUTOMATIC,  
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,   
    BACKUP_PRIORITY = 50,   
    SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),   
    SEEDING_MODE = AUTOMATIC)
,
N'WIN20124SQL16\SQL16STD'
WITH (ENDPOINT_URL = N'TCP://WIN20124SQL16.dbi-services.test:5022',   
    FAILOVER_MODE = AUTOMATIC,   
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,   
    BACKUP_PRIORITY = 50,   
    SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),   
    SEEDING_MODE = AUTOMATIC);   
GO

-- Listener with two IP addresses on a different subnet 
ALTER AVAILABILITY GROUP [testGrpStd]
ADD LISTENER N'Test-GrpSTD' (
WITH IP
((N'192.168.5.122', N'255.255.255.0'),
 (N'192.168.50.122', N'255.255.255.0')
)
, PORT=1433);
GO

Now let’s have a look at the availability group configuration and direct seeding parameter value

blog 103 - STD AG - automatic seeding

Here the listener configuration retrieved from the sys.availability_group_listeners DMV:

blog 103 - STD AG - listener 2

Let’s take this opportunity to change the replication type to ASYNCHRONOUS. In fact, I had in mind the mirroring feature when using the asynchronous replication that was only available with Enterprise edition. This is another interesting point and now we may benefit from both asynchronous and synchronous replication mode regardless the edition. By the way this feature is clearly documented in the BOL.

blog 103 - STD AG - async replication

Finally let’s check if direct seeding works as expected. If you don’t know what direct seeding is please refer to my previous blog. Just as reminder, we must first configure direct seeding on the secondary by granting the correct permission to the availability group itself.

ALTER AVAILABILITY GROUP [testGrpStd] GRANT CREATE ANY DATABASE 
GO

Here we go. Let’s finally add the database to my existing availability and let’s see if direct seeding comes into play

ALTER AVAILABILITY GROUP testGrpStd ADD DATABASE test_db;  
GO

To get data from direct seeding session states we may use DMVs like sys.dm_hadr_automatic_seeding and sys.dm_hadr_physical_seeding_stats as well.

blog 103 - STD AG - direct seeding

Sounds good right?

Bottom line

Basic availability groups feature is shipped with limited features. Yes, but in a meantime it will still respond to a large number of customer scenarios where we have to find a balance between high availability options and costs. Referring to some discussions I had with some customers, they don’t need special features provided by availability groups like readable secondaries or offloading backups. They just want to benefit from high availability for only some critical applications. I’m looking forward to see basic availability groups at customer shops!

 

 

 

 

Leave a Reply


1 × seven =

David Barbarin
David Barbarin

Senior Consultant & Microsoft Technology Leader