Infrastructure at your Service

David Barbarin

SQL Server 2016 : availability groups and the new potential support for standard edition

In my first blog about availability groups with SQL Server 2016, I talked quickly about the new interesting option: DB_FAILOVER. In this blog post, I will continue by introducing the new potential support of availability groups in a standard edition (based on the last Microsoft Ignit news). Yes, this sounds a great news because it will increase the scope of possible customers but bear in mind that it concerns potentially the standard edition (not in its last shape I guess) and we may expect some limitations. Let’s have a look at the potential limitations in this blog post.

First of all, you’ll notice a new option called “Basic Availability Group” from the configuration wizard as shown below:

 

blog_50_-_0_-_basic_availability_group_option

At this point we can wonder what “Basic Availability Group” means exactly? Let me speculate: this option allows us to simulate the availability groups feature in standard edition. I guess, this option will disappear with the first SQL Server 2016 RTM release. In addition, the word “Basic” tends to suggest some limitations, so let’s try to configure what I will call a BAG (Basic Availability Group) in this blog post.

The first thing I noticed is that the availability group will include only one database. In others words, adding a second database is not possible and you will face the following error from the GUI:

blog_50_-_1_-_bag_adding_databases

Ok, let’s continue. This time, the next limitation concerns the read-only capabilities on the secondary replicas which are not supported with BAGs. From the GUI, I have no other choices than “No”.

blog_50_-_2_-_bag_ro_secondary

Likewise, if I try to change the Readable Secondary value for the SQL162 instance, I will also face the following error message:

ALTER AVAILABILITY GROUP BAG
MODIFY REPLICA ON 'SQL162'
WITH
(
       SECONDARY_ROLE
       (
             ALLOW_CONNECTIONS = READ_ONLY
       )
)

 

Msg 41199, Level 16, State 8, Line 1
The specified command is invalid because the AlwaysOn 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.

 

Next, configuring backup preferences is not a possible option from the GUI. All parameters are greyed as shown below:

blog_50_-_3_-_bag_backup_preference

Go ahead and after installing my availability group, I noticed that the backup preferences policy was setup to Primary.

Finally, configuring a listener is also not supported on BAGs. Again, all configuration options are not available from the GUI. However, adding a listener after implementing the availability group, gives us the opportunity to enter the listener information but it will raise an error message at the final step:

blog_50_-_6_-_lstner_error

What about adding a third replica with BAG?  In fact, we’re limited to 2 replicas and we are not able to add another either from the GUI because the option is also greyed or from script because it will raise the following error message:

 

– Adding a third replica
ALTER AVAILABILITY GROUP [BAG]
ADD REPLICA ON N'SQL163'
WITH
(
       ENDPOINT_URL = N'TCP://SQL163.dbi-services.test:5022',
       FAILOVER_MODE = MANUAL,
       AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
       BACKUP_PRIORITY = 50,
       SECONDARY_ROLE
       (
             ALLOW_CONNECTIONS = NO
       )
);
GO
 
Msg 35223, Level 16, State 0, Line 21
Cannot add 1 availability replica(s) to availability group ‘BAG’. The availability group already contains 2 replica(s),
and the maximum number of replicas supported in an availability group is 2.

 

To summarize BAG comes with a lot of restrictions. So, when you create an availability group (on standard edition), you will able to benefit:

  • Only 2 replicas with either synchronous or asynchronous replication capabilities (the both are available with the current CTP2)
  • One and only one database per availability group
  • Backup capabilities only on the primary
  • New DB_FAILOVER option

However you will not able to use:

  • Failover capabilities by using the listeners (the listeners are not available with BAG)
  • Read-only capabilities (database snapshots are available with evaluation editions but is that going the case with the future standard edition?)

What about client failover capabilities in this case? Indeed, as said earlier, we cannot rely on the listener in order to switch over transparently to a new replica but in the same time, we are able to configure automatic failover for the availability group itself. A basic connectivity test (from a custom powershell script) after switching my availability group to a different replica raised the following error message:

The target database, ‘bag’, is participating in an availability group and is currently not accessible for queries. Either data movemen
t is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in th
e availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the A
LTER AVAILABILITY GROUP statement in SQL Server Books Online.

At this point, I expected to get at least the same failover mechanism provided with mirroring feature (assuming that DAG is the future replacement of DBM as said at the last Microsoft Init in Chicago). Does it mean that we’ll have to add the failover partner attribute in the connection string from the client side? Let’s try by modifying the connection string of mypowershell script:

$dataSource = “SQL161";
$dataPartner = "SQL162";
$user = "sa”;
$pwd = "xxxxx";
$database = "bag";
$connectionString = "Server=$dataSource;uid=$user; pwd=$pwd;Database=$database;Integrated Security=False;Failover Partner=$dataPartner;Initial Catalog=bag”;

– Test with SQL161 as the primary

blog_50_-_8_-_connectionstring_test

– Test After switching my availability group from SQL161 to SQL162

blog_50_-_7_-_connectionstring_test

Ok it seems to work correctly now.

In conclusion, the Basic Availability Group feature seems to be designed to replace the well-known mirroring feature, which is now deprecated, but with the limited-scope advantages of the availability groups. I believe we’ll have other opportunities to discuss about this feature in the near future because at this point, it has a good chance to not yet be carved in stone.

 

Leave a Reply


3 × = twenty four

David Barbarin
David Barbarin

Senior Consultant & Microsoft Technology Leader