Udostępnij za pośrednictwem

How to Set Up Basic Availability Groups in SQL Server 2016

  • Artykuł

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

  • Anonymous
    June 14, 2016
    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?
    • Anonymous
      June 28, 2016
      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.RegardsPieter
  • Anonymous
    June 28, 2016
    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.
    • Anonymous
      July 20, 2016
      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.RegardsPieter
  • Anonymous
    July 04, 2016
    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?
    • Anonymous
      July 20, 2016
      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.RegardsPieter
  • Anonymous
    July 13, 2016
    Thank you for this article. Did MS do a backflip from CTP and disallow snapshot of secondary replica in RTM?
    • Anonymous
      July 19, 2016
      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 RegardsPieter
      • Anonymous
        December 13, 2016
        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?
  • Anonymous
    October 01, 2016
    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.
  • Anonymous
    October 25, 2016
    The comment has been removed
    • Anonymous
      March 14, 2017
      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.aspxRegardsPieter
      • Anonymous
        June 15, 2018
        But database mirroring is deprecated, why would I build something like that in 2018.
        • Anonymous
          December 21, 2018
          Hi,Not sure what you mean because this is not related to database mirroring.RegardsPieter
  • Anonymous
    December 21, 2016
    How did you create Basic Availability Group without using TSQL Stmts ?? ConfigurationAn 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 NoteThe 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.
  • Anonymous
    February 07, 2017
    Hi!does "Basic Availability Group" supports databases with filestream ?
  • Anonymous
    February 23, 2017
    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?
  • Anonymous
    February 27, 2017
    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
    • Anonymous
      March 14, 2017
      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.RegardsPieter
  • Anonymous
    April 25, 2017
    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
    • Anonymous
      April 27, 2017
      Hi George,Yes, you can create for each group a different listener.Please also check RegardsPieter
  • Anonymous
    June 06, 2017
    The comment has been removed
    • Anonymous
      August 26, 2017
      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.RegardsPieter
  • Anonymous
    July 20, 2017
    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
    • Anonymous
      August 26, 2017
      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 RegardsPieter
  • Anonymous
    August 23, 2017
    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
    • Anonymous
      August 26, 2017
      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.RegardsPieter
  • Anonymous
    December 24, 2017
    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?
    • Anonymous
      January 01, 2018
      The comment has been removed
  • Anonymous
    November 28, 2018
    Do I need to buy 2 SQL Standard Server License when using BAG Always ON functionality, or is one sufficient as secondary node is not readable?
    • Anonymous
      December 21, 2018
      Hi,If you have SA, you get 1 fully passive node for free.RegardsPieter