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.
6. Do I need a listener for each group?
If you have multiple databases on you instance and each database is part of 1 single group, do you need to create a group listener for each group.
The short answer is "you don't". If you create a single listener on 1 group, you can use it for other database that are part of another group.
In the example below, you can see I have only 1 listener but 3 groups. The salesdb is not part of the group where the listener is configure but I can still access the database.
Of course, there is a drawback on this. As soon as the group BASIC_AG1 does a failover, the salesdb will not be accessible anymore because the listener is now pointing to another replica. To avoid this "split" situation you can manually failover the other groups to the other replica as well or you should create a group listener on each group.
7. Can I take a snapshot of the database that is running on the secondary replica?
Yes, the snapshot works on the secondary replica. This behavior is the same when you should have used database mirroring in the past. You could also take a snapshot of the mirrored database.
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.