Setup and Implement SQL Server 2016 Always On Basic Availability Groups

By:   |   Comments (4)   |   Related: > Availability Groups


In a previous tip on Configure SQL Server Database Mirroring Using SSMS, we have seen how we can configure Database Mirroring to achieve local high availability for SQL Server databases. We need to upgrade our SQL Server 2008 R2 databases before extended support ends. However, we can only upgrade to Standard Edition due to licensing cost restrictions. And Database Mirroring has been deprecated since SQL Server 2012. What is our next best course of action?


When the Availability Group feature was introduced in SQL Server 2012, it was only available in Enterprise Edition. It made upgrading databases configured with Database Mirroring a challenge, especially when the principal and the mirror SQL Server instances are running Standard Edition.  The only other option for providing high availability to databases running in Standard Edition is to implement a 2-node SQL Server failover clustered instance. But this requires shared storage and other external dependencies like Active Directory and DNS. So, even in SQL Server 2014, customers still use Database Mirroring.

SQL Server 2016 made it possible to run Availability Groups in Standard Edition. Although Availability Groups still required Active Directory and DNS as requirements for running Windows Server Failover Clusters (WSFC), it didnít take long for Windows Server 2016 to be released to the public, thereby, allowing creation of WSFC without Active Directory. Refer to this tip on how to deploy a Windows Server 2016 Failover Cluster without Active Directory. This made Availability Group a true viable replacement for Database Mirroring.

Setup and Implement SQL Server Always On Basic Availability Groups

The prerequisites for deploying Basic Availability Groups are the same as what they have been since their introduction in SQL Server 2012. But because they are on Standard Edition, there are some limitations. I will highlight those limitations as we go along the process of setting up and configuring Basic Availability Groups. Also, the WSFC has already been created and the Always On High Availability feature enabled on the instances that I will use as replicas.

Earlier releases of SQL Server 2016 and SQL Server Management Studio have an explicit option to specifically create Basic Availability Groups, like the one shown below. This is no longer the case.

new availability group

At the moment, there is no option to explicitly create a Basic Availability Group using SQL Server Management Studio (I hope the product team will bring that option back). But if you are creating an Availability Group with a Standard Edition instance using SQL Server Management Studio, the wizard will automatically detect it and create it as a Basic Availability Group.

The environment that I am using in this tip is as follows:

  • A 2-node WSFC joined to an Active Directory domain
  • Uses a file share as a witness type
  • SQL Server 2016 with Service Pack 1 Standard Edition
  • SQL Server uses Active Directory domain accounts as service accounts

Launch the New Availability Group Wizard to create the Basic Availability Group.

  1. From within Object Explorer, expand the AlwaysOn High Availability node and the Availability Groups node
  2. Right-click the Availability Group node and select the New Availability Group Wizard command. This opens the New Availability Group Wizard dialog box
object explorer
  1. In the Specify Availability Group Options dialog box, type the name of the Availability Group in the Availability group name: textbox. Notice the difference between this dialog box and the one I mentioned earlier Ė the Basic Availability Group checkbox is no longer there.
specify availability group options
  1. In the Select Databases dialog box, select the database that you want to include in your Availability Group.
select databases

Limitation #1: You can only join one database per Basic Availability Group. You will get this warning message should you choose more than one database.

Click Next.

  1. In the Specify Replicas dialog box,
    1. In the Replicas tab, click on the Add Replica button to add the SQL Server instance that you want to configure as a secondary replica. Unlike Database Mirroring in Standard Edition, you can configure a secondary replica in a Basic Availability Group in Asynchronous mode.

Also, notice the following.

specify replicas

Limitation #2: You can only have two replicas Ė primary and secondary - per Basic Availability Group. The Add Replica button will automatically get disabled after adding the second replica.

specify replicas

Limitation #3: You cannot enable the secondary replica as a readable secondary. No is the only option when you click on the drop-down list under Readable Secondary.

  1. In the Backup Preferences tab, notice how everything is disabled. I do wish that they also grayed out the Exclude Replica checkboxes.
specify replicas

Limitation #4: You cannot run backups on the secondary replica.

  1. Proceed to create the Listener name in the Listener tab.
    1. Select the Create an availability group listener option
    2. Type the Listener DNS name and Port number
    3. Select Static IP in the Network Mode: drop-down list
    4. Provide the virtual IP address by clicking the Add Ö button

This is another benefit of Basic Availability Group over Database Mirroring. In the past, you would need to add the Failover Partner attribute in the database connection string to automatically redirect client applications to the Database Mirroring partner after a failover. Another workaround is creating a DNS alias and assigning the IP address of the principal database server to it. Because the Availability Group listener name moves together with the primary replica, there is no need to create a DNS alias (since the listener name is technically a DNS alias) nor use the Failover Partner attribute in the database connection string. The client application will automatically get redirected to the current primary replica.

new availability replicas
  1. In the Select Initial Data Synchronization dialog box, you can choose to either use the new Automatic Seeding feature, the usual Full database and log backup option, Join only option or Skip the initial data synchronization option. This example uses Automatic Seeding since the database is relatively small. Refer to this tip for more information about Automatic Seeding.

Click Next.

select initial data synchronization
  1. In the Validation dialog box, make sure that all checks return successful results. Click Next.
  1. In the Summary dialog box, verify that all the configuration settings are correct. Click Finish to create the Availability Group.


You can view the Availability Group dashboard for the state and configuration information.

basic ag

There really isnít that much difference in setting up and configuring Basic Availability Group when you compare it with the way it has been done since SQL Server 2012. The main thing here is that the setup and configuration experience is driven by the limitations. After all, you are working with Standard Edition with limited features. The best thing about it is there is now a viable replacement for Database Mirroring in SQL Server 2016.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, November 20, 2019 - 5:03:08 PM - bass_player Back To Top (83151)


SQL Server 2016 can support up to compatibility level 100 (SQL Server 2008 R2). The real question is whether or not your application and the queries will function as expected on SQL Server 2016. The only way to find out is to test.

Monday, September 23, 2019 - 5:40:23 AM - Zarko Back To Top (82539)


Is there a limitation for DB compatibility level? We're currently using SQL Server 2014 and our DBs are cmpt lvl 120. If we want to use BAGs, we need to upgrade to SQL Server 2016 but can we keep DBs at cmpt lvl 120?

Tuesday, June 26, 2018 - 2:27:47 PM - bass_player Back To Top (76422)

Combining FCI and AG further complicates the architecture and the operational processes. Your operations team needs to be prepared to manage it. Otherwise, it will create more unnecessary issues.

Tuesday, June 26, 2018 - 10:50:19 AM - Kelly Back To Top (76421)

What are the pros and cons to using SQL Server FCI and Availability groups together? Iím currently doing this on a 3 node WSFC. Two nodes are configured Active/Passive for the FCI and the 3rd node is part of an AG that is in another geo location for our DR copy.

get free sql tips
agree to terms