AlwaysOn Basic Availability Groups provide a “basic” high availability solution for SQL Server 2016 Standard Edition. The functionality is the same as the traditional (Enterprise Edition) AlwaysOn Availability Groups but there are more limitations:
The most important limitations are:
- Limit of two replicas (primary and secondary)
- No read access on secondary replica
- One database per group
More detailed information can be found here.
I received a lot a questions of customers and on my previous blog post about those limitations and possible configurations; so I wanted to share the top 5 questions in this blog post.
1. Can I create more than 1 availability group?
Yes, you can create more than 1 availability group with SQL Server 2016 Standard Edition. Each group that you create can only contain 1 database. As you can see in the screenshot below, I’ve created as an example 3 availability groups. Each group contains 1 database and I’ve configured for each group a different group listener.
2. Can I use automatic failover?
This is not mentioned in the limitations which means automatic failover is supported.
As you can see in the screenshot below, I’ve configured my availability group with Synchronous commit mode and Failover Mode to Automatic. You can also use Asynchronous commit mode, which is different compared to database mirroring. Of course, asynchronous commit mode doesn’t allow automatic failover.
3. Can I configure more than 2 replicas?
No, you cannot configure more than 2 replicas per availability group. But that doesn’t mean you can’t configure more instances as a replica in different groups. Make sure that the replicas are all part of the same Windows Failover Cluster. As an example, I’ve made a configuration with 3 instances.
Node1 is primary replica for BASIC_AG1 and BASIC_AG3 and it’s secondary replica for BASIC_AG2.
Node 2 is currently not configured as a primary but only as secondary for BASIC_AG1 and BASIC_AG3.
Node 3 is primary replica for BASIC_AG2 and is currently not configured as secondary replica.
As you can see, multiple configurations are possible. But please do keep in mind that technically, you can create these kind of configurations but it will be a nightmare to administer… If it is truly a critical application, I would question how critical it is to not receive the other Enterprise Edition benefits.
4. Does a Basic Availability Group support databases with FILESTREAM?
Since this is not mentioned in the limitations of Basic AGs, I consider this as supported. In my test environment, I’m using the new test database WideWorldImporters which is actually using FILESTREAM because it’s using In-Memory OLTP tables. More information on FILESTREAM and FileTable with AlwaysOn Availability Groups can be found here.
5. Can I have SQL FCI as a replica?
Basic Availability Groups are limited to 2 replicas, 1 primary and 1 secondary. A replica is an instance of SQL Server. So, it doesn’t matter if your instance is clustered. This means that 1 or both of the replicas can be a failover cluster instance in a Basic Availability Group. The only thing that you have to be aware of is that Automatic Failover will not work in this case as this is handled by the FCI. But that’s the same behavior as in Enterprise Edition. In my example, I’ve created 1 FCI SQL\INST which is currently my primary and 1 stand-alone secondary. And as you can see, it works like a charm.
If you should have any other questions about Basic Availability Groups, please let me know. I’m happy to test it and figure it out.