Freigeben über

Top 7 Questions about Basic Availability Groups

  • Artikel

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.

 

image

 

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.

 

image

 

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.

 

image

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.

 

image

 

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.

 

image image

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.

Pieter

Comments

  • Anonymous
    March 17, 2017
    Thanks Pieter. I needed the info in #5 to help someone doing some HA/DR planning in a forum today.
    • Anonymous
      March 17, 2017
      Glad I could help!Pieter
  • Anonymous
    September 22, 2017
    The the secondary replica is not readable, can we still create snapshots out of secondary relica ?
    • Anonymous
      December 14, 2017
      Hi Ravi,I've updated the blog post. See my answer in point 7 :)RegardsPieter
  • Anonymous
    November 15, 2017
    AWESOME!!! you just made my day!
  • Anonymous
    November 30, 2017
    You can not have more than one BD in each group, so I will create N groups (one group for each BD), my question is: Do I need a different IP for each group? o Can all the groups be accessed by the same IP? Can I access all the groups with the same connection chain?
    • Anonymous
      December 14, 2017
      Hi Roy,I have updated the blog post. See my answer in point 6.RegardsPieter
  • Anonymous
    December 01, 2017
    The biggest limitation when you create multiple BAG, you will get multiple listener/connection string and if the databases are part of one application it will be difficult to make connection string changes within an application.. i see this is as drawback of using BAG.
  • Anonymous
    January 16, 2018
    Can I configure Basic Availability Group between two instances in separate locations but a member of a single WSFC ?
    • Anonymous
      February 13, 2018
      Hi,Yes that is possible. As long as both of the replicas are part of the same Windows Cluster, you can configure your AG.RegardsPieter
  • Anonymous
    February 11, 2018
    Hi Pieter Vanhove,A quick question.I have 2-tier architecture, 2x front end Web servers and 2x Backend DB servers.Backend: I have 2 x SQL 2016 Standard DB nodes running on WSFC on |Windows 2016I have 3 x DBs total, and 3 AGs, 1 DB per AG.At the moment we are using WSFC cluster IP and can access to any DB from any AG. DB are sync and failover has been tested.Is it recommanded to use listeners? lets say for every AG, I configure Listeners as AG1-LIS1, AG2-LIS2, AG3-LIS3 so connections to DBs are pointed to Listener IPs, not to WSFC cluster. Web application Users may increase to thousands, Is it ok to use single WSFC IP ? performance or security point of view?What is your expert advice. Appreciate your quick response.Regards,
    • Anonymous
      February 13, 2018
      Hi,I would recommend to use a group listener.If you're using the Cluster IP address you could have the situation where your cluster IP address is hosted on Node 1 while your AGs are hosted on Node 2.In that case, you're application will not be able to connect to the databases.Your connection will always point to the correct primary replica if you're using the group listener.RegardsPieter
  • Anonymous
    December 06, 2018
    Hi Pieter,I have set up a test environment using SQL Server 2017 Developer and Windows Server 2016 Datacenter Evaluation editions on VMware Workstation, and I am encountering the situation I see in your post in point #3, where in AG2, NODE3 is showing with an "Unknown" status (the question mark icon). In my case, one replica of the AG shows both replicas with a normal state, while the other replica shows itself as normal and the other as unknown. What could be the cause? All network communication, etc, seems to be fine. Thanks!
    • Anonymous
      December 21, 2018
      Hi,Did you check the error log on both servers?Do you see the same issue when you open the dashboard in the SQL Server Management Studio?RegardsPieter