February 2016 | SansSQL

Thursday, February 18, 2016

Configuring AlwaysOn Availability Groups - Part 2

In my previous post we have learnt on how to configure the failover clustering. This is the first step and an important pre-requisite in configuring AlwaysOn Availability Groups.
In this post we will learn how to Enable and Configure the AlwaysOn Availability Groups using SQL Server 2016.

Before we start configuring the AlwaysOn, we need to enable this Feature.

Open "SQL Server configuration Manager"
Right-Click on “SQL Server” Service and click on “Properties”

Go to “AlwaysOn High Availability” Tab and Check the box “Enable AlwaysOn Availability Groups” and Click OK.

To Create new Availability Group
Open SSMS and connect to the SQL DB Engine.
Expand “AlwaysOn High Availability”
Right-Click on “Availability Groups” and click on “New Availability Group Wizard”

This is will open up the New Availability Group Wizard

Give a name to the Availability Group and choose the required options and click Next.

In this page, choose the databases that you may want to be part of this Availability Group.
Additional Databases can be added later as well.

In the next page, Add the Replica SQL Server instances and configure Endpoints, Backup Preferences and Listener for this Availability Group

In the next page, specify the Synchronization preference.

In the next step, the wizard will validate the configurations done so far

Review the Summary page and Click Finish to start the Availability Group Configuration

Successful completion of this wizard will create the new Availability Group with the specified databases, endpoints and the listener.

Wednesday, February 17, 2016

Configuring AlwaysOn Availability Groups - Part 1 - Configuring Failover Cluster

The AlwaysOn Availability Groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in SQL Server 2012, AlwaysOn Availability Groups maximizes the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or some backup operations.

In the process of configuring AlwaysOn Availability Groups, the first step is to configure Failover Clustering on the participating servers.

To configure the Failover Cluster

Open "Server Manager" and choose "Add Roles and Features"

Select "Failover Clustering" and Add Dependency Features

Add Features and Click Next

Once you click next, the wizard will start the installation

With this, the required failover clustering components are installed.
Now we need to create the cluster and before that we need to validate the participating servers for creating cluster.
Open "Failover Cluster Manager" and choose "Validate Configuration"

Click Next

Add the Servers

Choose to Run All Tests and Click Next

Click Next in the confirmation page

You can ignore the disk related warning in the case of AlwaysOn

Click Finish and Create the new Cluster

Assign a Name and IP to the cluster

Uncheck "Add all eligible Storage to the Cluster" and click next

On the successful completion of this wizard, the process of configuring the cluster will be completed.
Now we are all set to configure AlwaysOn Availability Groups.

In the next post, we will learn how to Enable and Configure the AlwaysOn Availability Groups using SQL Server 2016.