How to Set up an Always On Availability Group in SQL Server 2016

In this article, we look at the process setting up an Always On Availability Group in SQL Server Standard Edition in a stepwise manner.

SQL Server Standard EditionWith the coming of AlwaysOn Availability Groups in the Standard Edition of SQL Server 2016, the deprecated feature of Database Mirroring will be replaced. The Standard Edition of 2016 supports Basic Availability Groups, which is another name for the AlwaysOn Availability groups in the Enterprise edition. Although setting up Basic Availability Groups is very similar to setting up AlwaysOn Availability Group in the enterprise edition, there are slight differences between the two. The Basic availability group seems to be having more limitations as compared to AlwaysOn, it allows for only two nodes (primary and secondary), it requires all databases to have their own Availability Groups, etc.

Basic Availability Group (BAG) might be having a few limitations, but setting it up is quite similar to setting up AlwaysOn, here are the steps for setting up a BAG.

  1. Set Up An Always On Availability Group In SQL Server 2016 Standard Edition

    Enable Windows Clustering – This is like a prerequisite for enabling BAG for all instances of SQL Server. You will first have to initiate a Windows Failover cluster and then enable AlwaysOn for all instances. This is important because the Availability Groups (AGs) feature in SQL Server is built over Windows Failover Cluster.

  1. Validate Candidates in the Cluster – Go to Server Manager, click on Tools > Failover Cluster Manager. Select Validate Configuration, to begin the Wizard, after selecting names of servers, you want to be a part of the Availability Group, click Next. Validation might take long, depending on the complexity of the environments, amount of replicas, etc.
  1. Failover Cluster Validation Report – This report might end up showing that your cluster failed, it is Ok, do not worry. You can click on View Report, to view the full report, click on the first error, and you will see details along with a possible solution. Read the problems carefully, and solve all issues before you proceed.

*Once you have fixed the issues, you are advised to rerun the Validation Wizard, so that your Failover Cluster Report is always looking good, because that is what Microsoft will look at, if you ever ask for help with AGs.

  1. Creating A Cluster – When you receive your Cluster Failover Report, check the ‘Create Cluster Now’ check box, and click on Finish. Your Cluster is now ready, add a cluster name again, and click on Next.
  1. Configure Cluster Quorum Settings – Once your cluster is ready, you will have to add a witness, Right click on cluster name then go to More Actions and choose the Configure Cluster Quorum Settings. In the appearing window, go to ‘Select Quorum Witnesses’, choose a disk or file share witness. Click on ‘OK’ to exit the wizard.
  1. Setting up BAG – Launch SQL configuration for all replicas, go to SQL Server Services, right click on SQL Server, and select properties, for all SQL instances.
  1. Creating AGs – From the New Availability Group Window, select ‘BAG’. You will now have to specify replicas, which will then be followed by your Synchronization Preferences. This will end the AG Wizard. You can see the complete window, with all details in the SQL Server Management Studio

While setting up a BAG will go a long way in maintaining business continuity, procuring a mdf repair tool would go a long way in completely avoiding any data loss due to a SQL Crash.

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including access fix and sql recovery software products. For more information visit www.datanumen.com

Comments are closed.