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:

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:

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”.

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
       )
)

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

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:

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:

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

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:

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

– Test After switching my availability group from SQL161 to SQL162

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.

By David Barbarin