How to Set Up Basic Availability Groups in SQL Server 2016


AlwaysOn Basic Availability Groups provide a high availability solution for SQL Server 2016 Standard Edition. This new functionality will replace database mirroring, which is a deprecated feature. Basic Availability Groups are managed and created in the same way like the traditional Enterprise AlwaysOn Availability Group, but there are more limitations which are almost equally when you should run Database Mirroring on a Standard Edition. With this blog post I want to show how easy it is to create a Basic Availability Group and what limitations you can expect.

Prerequisites

The prerequisites are the same as for a traditional Enterprise AlwaysOn Availability Group. You still need to Create a Windows Failover Cluster and Enable AlwaysOn Availability Groups on each instance. More information can be found on Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups

Setup a New Basic Availability Group

You can still use the “New Availability Group Wizard” in the management studio to create your basic Availability Group. Of course, T-SQL and PowerShell can also be used.

Specify the name of the Availability that you want to create.

image

Please also note the new option “Database level Health Detection”. You can now configure AlwaysOn Availability Groups to failover when a database goes offline. This was not possible in previous editions of SQL Server.

image

Select the database that you want to add to the Availability Group. One of the limitations of Basic HA is that you’re only allowed to add 1 database per group. The wizard will return an error when you try to add more than 1 database into the group.

When you specify the replicas for your Availability Group, you will notice 2 other limitations:

  • Limit of two replicas (primary and secondary). After you’ve added the secondary replica, the “Add Replica” button will automatically be disabled.
  • No read access on secondary replica. The wizard only has the “No” option, so you can’t configure a readable secondary.

 

image

Please note that you can configure Synchronous or Asynchronous commit mode. This is a difference compared to database mirroring where you could only use synchronous commit mode in SQL Server Standard Edition. With Basic HA, we allow asynchronous commit mode, to be able to create your secondary replica in Azure. This allows you to create even a basic disaster recovery solution with SQL Server Standard Edition!

The endpoint configuration is still needed and there is no difference compared to the traditional Enterprise Availability Groups

image

Basic HA doesn’t allow you to use a readable secondary, which means that there are no backups possible on the secondary replica. You will notice that the Backup Preferences are completely disabled

image

Basic HA also supports the usage of an Availability Group Listener. Creating the listener is the same procedure as the traditional Enterprise listener. The Basic HA group is limited to 1 listener.

image

 

The next step is setting up the data synchronization between the 2 replicas. Again, there is no difference here between Standard and Enterprise Edition

image

With SQL Sever 2016, you could also use the new option Direct Seeding. This method will seed the secondary replica over the network. It does not require you to backup and restore a copy of the primary database on the replica. However, this is currently not available in the wizard but only with T-SQL.

After you finish the wizard, you have setup your first Basic HA Availability Group.

image


Comments (31)

  1. Paolo says:

    If I want use “Basic Availability Group” with SharePoint, can I create one BAG for every database? I can point to one “listener” of first database in BAG and use it for all (so “pause” node or automatic failover if the first nodes is not working) ? It is supported?

    1. Hi,

      This is a setup that you can use yes.
      But using only one listener could give issues if you should use the database-level failover trigger.
      The group could failover to another replica while the other groups are staying on the primary replica.

      Regards
      Pieter

  2. Thomas says:

    Is there a limit on how many basic availability groups you can create on one server? As you can only have one database per group.

    1. Hi Thomas,

      There is no real hard limit of the number of AGs. For SQL Server Enterprise Edition, Microsoft has extensively tested with 10 AGs and 100 DBs per physical machine.
      But again, it really depends on the amount of worker threads, the hardware that you use, the network performance, the amount of transactions, …
      Thoroughly test your environment with a production-like workload to ensure it can handle peak workload capacity within your application SLAs.

      Regards
      Pieter

  3. Nikhil C. says:

    Hello Pieter,
    Great article.
    Would you also happen to know how many Basic Availability Groups can we have per server?
    So it’s only 1 Database per BAG (Primary + Secondary), but is it possible we can create 5 BAG which will have 5 different databases then?

    1. Hi,

      Yes, that is possible. You can create multiple groups and each group will contain just 1 database.
      There is no real hard limit of the number of AGs. For SQL Server Enterprise Edition, Microsoft has extensively tested with 10 AGs and 100 DBs per physical machine.
      But again, it really depends on the amount of worker threads, the hardware that you use, the network performance, the amount of transactions, …
      Thoroughly test your environment with a production-like workload to ensure it can handle peak workload capacity within your application SLAs.

      Regards
      Pieter

  4. Mark says:

    Thank you for this article. Did MS do a backflip from CTP and disallow snapshot of secondary replica in RTM?

    1. Hi Mark,

      Not that I’m aware of. Database Snapshot is an Enterprise feature, so this will never be possible with Basic Availability Groups.

      Best Regards
      Pieter

      1. HP says:

        Thanks Pieter. This is great article. But, i do have 3-4 BAG. And, i would like to have a script which will check for any BAG is not primary then do the fail-over to other server. Can you please help with that?

        1. Hi,

          I’m sure if you use the system function sys.fn_hadr_is_primary_replica https://msdn.microsoft.com/en-us/library/dn249345.aspx in combination with the ALTER AVAILABILITY GROUP MyAg FAILOVER command https://msdn.microsoft.com/en-us/library/hh231018.aspx you can create that script yourself 😉

          Regards
          Pieter

  5. Joel bagyaraj says:

    Hi All,

    I have tested the basic availability Group in SQL server 2016 standard edition.
    more than 16 Availability groups I have created and tested. it is working.

  6. Ted says:

    Hi, with SQL Server 2016 Standard BAGs you cant read from the secondary. So what are the options if you need to report off a secondary? when using BAGs? Cheers.

    1. Hi Ted,

      The only option that you have is taking a snapshot of that database on your secondary.
      Same like you can do when you are using Database mirroring https://msdn.microsoft.com/en-us/library/ms175511.aspx

      Regards
      Pieter

  7. kalyan says:

    How did you create Basic Availability Group without using TSQL Stmts ??

    Configuration
    An Always On basic availability group can be created on any two SQL Server 2016 Standard Edition servers. When you create a basic availability group, you must specify both replicas during creation.
    To create a basic availability group, use the CREATE AVAILABILITY GROUP transact-SQL command and specify the WITH BASIC option (the default is ADVANCED). For more information, see CREATE AVAILABILITY GROUP (Transact-SQL). At this time, there is no UI support to create basic availability groups in SQL Server Management Studio.
    System_CAPS_ICON_note.jpg Note

    The limitations of basic availability groups apply to the CREATE AVAILABILITY GROUP command when WITH BASIC is specified. For example, you will get an error if you attempt to create a basic availability group that permits read access. Other limitations apply in the same manner. Refer to the Limitations section of this topic for details.

    1. Hi,

      In fact, the version of SQL Server Management Studio 2016 supports the configuration of Basic Availability groups.
      You can find the latest version of SSMS on https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms
      Regards
      Pieter

  8. Sergej says:

    Hi!
    does “Basic Availability Group” supports databases with filestream ?

    1. Hi,

      Since it’s not mentioned in the limitations of Basic HA and FILESTREAM and FileTable with Always On Availability Groups is supported https://msdn.microsoft.com/en-us/library/hh510261.aspx this should work.

      Regards
      Pieter

  9. Vyaghreswarudu Kotturi says:

    Thanks for the nice article. I read BAG is limited to two nodes. I felt they mean limited two replicas. I may be wrong. Can I have SQL FCI as one replica here? Or Can I have SQL FCI as primary replica? Did any one test this?

  10. vims says:

    Hi I have configured AG ( two server ) in sql server 2016 standard edition and add 35 database in AG . both are working fine .
    Litner per databases needed or only one .
    I have create one listner in one database but after failover my connection string not work .
    Before failover listner work .
    My database failover then after my secondary db primary , But my connection string not work .
    Please advise

    1. Hi,

      What was the error that you got?
      Did you check the SQL Server Error Log after the failover? Normally you should see a lot of information what is going on in your AG.

      Regards
      Pieter

  11. George says:

    Hi, I understand one database per AG group, what about listener, one per cluster or one for each AG group ? I have 10 databases, 10 AG groups, can I have 10 AG listeners ?
    Thanks a lot

    1. Hi George,

      Yes, you can create for each group a different listener.
      Please also check

      Regards
      Pieter

  12. sroghen says:

    Hi,
    Got a scenario which i cannot get an answer for.
    Have 2 sitesA and B, connected with dedicated line.

    SiteA host the primary DB. Network goes down while the DB are still active.
    I understand the location of the listener becomes important. Ideally I am looking to make the DB primary for its region when network goes down. I dont know if this is possible.

    1. If listener is on SiteC, which still has connectivity to both sites. It means the DB in SiteA remains as primary. No changes. The downside the clients on SiteB cannot communicate to the primary DB while network is down.

    2. Now if listener is on SiteB. Network goes down. Will the SiteB DB now become primary? What happens to SiteA DB?

    1. Hi,

      The listener is always active on the primary replica.You cannot chose the location of the listener as it always points to the primary replica.
      If the network goes down been the replica, no failover will occur and your database will stay online.

      Regards
      Pieter

  13. Mat says:

    Hi Pieter,
    I hope you can help. We have SQL 2016 Standard and I have configured Basic Availability. There are about 45 databases I have used individual BAG for each database (this is the only option for SQL Standard). I was able to add 35 databases into the basic availability and the 35 databases are synchronizing very well. However, now I can’t add any more database when I try to expand Availability Group in SSMS explorer I get error “The wait operation timed out”. I have checked the status of the availability group through T-SQL and shows everything is healthy and I don’t get any other error. Please let me know if you have any idea how to get around this.

    Cheers

    1. Hi Mat,

      Can you check if you don’t have thread exhaustion on your system?
      Also check the Thread Usage by Availability Groups in this article

      Regards
      Pieter

  14. Paul Maguire says:

    Hi Pieter, I’ve setup the Basic AGs. Am trying to setup alerting. I tried the usual Alerts I would use on Full AGs to send an operator email with a change in role, failover occurs. But these don’t seem to work with Basic AGs. Have you any experience thoughts?

    Thanks, Paul

    1. Hi Paul,

      Can you provide me more information? Do you get any error? Is the alert configured correctly? Do you see that the alert is triggered if a failover occurs?
      Perhaps an old blog post of me Alerting on Database Mirroring could also help.

      Regards
      Pieter

  15. Khoi Thinh says:

    Thanks for your tutorial. I know that this is a limitation in SQL Server Standard version, but what’s the meaning of this Basic AG when you can’t read to 2nd replica?

    1. Hi,

      Basic AG means that you get basic (limited) High Availability functionality in SQL Server Standard Edition.
      If you want to use readable secondary you should use Enterprise Edition.

      Best Regards
      Pieter

Skip to main content