Priyanka Devre
SQL Server AlwaysOn High Availability - new availability group validation

Configuring a SQL Server AlwaysOn High Availability Group

March 6, 2019 by

In this article, we will learn the step by step configuration of SQL Server AlwaysOn High availability Group for two nodes. Once nodes are added to the cluster group, we will able to use the AlwaysOn feature in the SQL server.

In the following steps, we will create a Service account to connect SQL server instances. The Service account will be used to connect the SQL instance from both the node servers.

Create a Service Account

We will log in to the Domain controller and perform the following steps.

Open Server Manager, Tools and Click Active Directory Users and Computers.

Create a Service Account

In Active Directory Users and Computers window, Right-Click Users > New > User as shown in the following Fig.

Active directory, new user

In this step, we will create a Service account username, Specify the service account login name and Click Next

create a Service account username

Specify a Password and tick the options for User cannot change password and Password never expires. We need to select Password never expires option because, if the password expired, the service account login would fail, and it will cause a problem to the high availability configuration.

SQL Server AlwaysOn High Availability - password expiration

Click Finish to complete the service account creation process.

SQL Server AlwaysOn High Availability - finish adding object

Configure Administrator permission, windows user and login for the service account

Once the Service account created on the Domain controller, we need to add service account as a member of the administrator account on both nodes.

Connect to both node servers SQL1 and SQL2 and perform below steps

  • Add Service account as an Administrator
  • Create a Windows user for the Service account
  • Create a Windows authentication login for a service account login in the SQL database instance

Open Server manager and Select Tools and Computer Management

SQL Server AlwaysOn High Availability - Add service account

In the Computer management window, Select Groups and Right-Click Administrators and Go to Properties

Configure Administrator permission, windows user and login for the service account

Add User, Enter the Service account login name and Check names and Click OK

Note – Once clicked on the Check Names, it may ask to enter username and password pop-up window. In the pop-up window, we need to register a domain controller username and password and make sure the location should select as a directory.

Select user, Computers, Service Accounts or Groups

In the following Fig. We will create the Service account as a windows user in Control Panel > User Account > Click on Manage User Accounts.

Enter a Service account name and domain name and Click Next, Select Administrator and Finish

Manage user accounts

Next, we will create a windows authentication login for the service account as shown in the following fig.

Next, we will create a windows authentication login for the service account

Take remote desktop connection of SQL1 with the service account windows user, connect to the SQL Server database engine instance SQL1 with default windows authentication login as shown in the following fig.

Enable AlwaysOn Feature

Connect to server

Once SQL Server instance is connected, we need to decide database which will take part in SQL Server AlwaysOn High Availability.

In this demonstration, we will create a new database STUDENT, and then we will create a table and add a few records into it as shown in the following fig.

Create a new database

Next, Expand SQL Server AlwaysOn High Availability option, we will get an error while expanding the AlwaysOn option

The AlwaysOn feature must be enabled for the server instance ‘SQL1’ before you can create an availability group on this instance. To enable AlwaysOn, open the SQL Server Configuration Manager, select SQL Server Services, right-click the SQL Server instance name, select Properties, and use the AlwaysOn High Availability tab of the SQL Server Properties dialog. (ObjectExplorer)

The AlwaysOn feature must be enabled for the server instance ‘SQL1’ before you can create an availability group on this instance. To enable AlwaysOn, open the SQL Server Configuration Manager, select SQL Server Services, right-click the SQL Server instance name, select Properties, and use the AlwaysOn High Availability tab of the SQL Server Properties dialog. (ObjectExplorer)

To enable AlwaysOn feature, Open SQL Server Configuration Manager, Right-click SQL Server instance and go to properties, Select AlwaysOn High Availability tab section and tick checkbox for Enable AlwaysOn AvailabilityGroups

Note: – We also need to make sure nodes where we will enable AlwaysOn feature is part of Windows Failover Cluster.

enable AlwaysOn feature

Once we enabled, Apply and Click OK. Restart the SQL Server services.

As shown in the following fig. we will able to use SQL Server AlwaysOn High Availability feature. Right-click Availability Groups and Click New Availability Group.

Configuring Availability Group

An AlwaysOn Availability Group contains a set of user databases; we need to select user databases which will be part of the Availability group. These databases called as an Availability database.

Configuring Availability Group

Specify Availability Group name and click Next

Specify Availability Group name

As shown in fig. we will use database STUDENT. To proceed further, we need to perform one essential pre-requisite step, a Full backup of the database. So, take a Full backup of STUDENT and click Next

take a Full backup of STUDENT

Next, Specify Replica step has four sections. Replica, Endpoint, Backup Preferences, and Listener

Replica– Replica is a server. There are one primary replica and multiple replicas. In SQL server 2012, It supports up to 4 secondary replicas, while in SQL Server 2014, it supports up to 8 replicas.

The primary replica is primary source server or production server. A secondary replica is a server which maintain a backup copy of the primary server availability database. On the Primary replica, it allows to perform Read and write operations while on the secondary replica only read operations.

In this case, SQL1 is our primary replica and SQL2 is a secondary replica. So, we will add SQL2 as a secondary replica as shown in the following fig.

Initial Role – It specifies the role of replica whether Primary or secondary.

Automatic Failover – It failovers functional role from primary replica to secondary replica by an automated method with no data loss.

Synchronous Commit – In the Synchronous-commit mode, failover can happen by either Automatic or manual way with no data loss. So, in this case, we will use synchronous commit with automatic failover.

Add replica

Once clicked Add Replica, connect to the server, Select SQL2 and Connect

Connect to Server

Once we add secondary server SQL2 as a secondary replica, the primary role of SQL2 is secondary.

Specify replicas

In the Endpoint section, it will show configured endpoint URL, port, endpoint name, and the service account name for SQL1 and SQL2 nodes.

Specify replicas - end point section

Backup Preferences – Backup preferences indicate the backup location of the availability group. It provides four backup options.

  • Prefer Secondary – Automatic backup will occur on a secondary replica
  • Secondary only – Automatic backup must occur on a secondary replica
  • Primary – Automatic backup must occur on a primary replica
  • Any Replica – Backup will occur on any replica

On the secondary replica, we can only take a log backup, and Copy-only backup, Differential, and Full back up are not allowed on a secondary replica. While on the primary replica, we can perform Full, log, and Differential backups.

Specify replicas - Backup preferences

Listener– Listener is a server through which nodes get to communicate with each other. The Listener has all information about availability group. We need to select Create an Availability group listener option and specify Name, Port and static IP as shown in the following fig.

Specify replicas - Create an availability group listener

In Data Synchronization step, Select Full where it performs Full and Log backup and puts it into shared location path.

Create a Shared location

Connect to node SQL1 and choose desired drive location and folder, Right-click folder and Go to Properties, Select Security and click Share. It will show a pop-up window to add a user to whom, share permission need to grant.

Provide the service account name and Click Add; it will show pop-up window to check names, once service account name found in the domain name, click Share.

Create a shared location

Create a shared locatation - check names

Select initial data synchronization

In the Validation step, SQL Server performs validations against any required actions, and It will provide warning or error messages for missing required steps.

SQL Server AlwaysOn High Availability - new availability group validation

It performed all validation steps successfully, and wizard operation completed as shown in the following fig.

SQL Server AlwaysOn High Availability - Results

After successful completion, we will verify the database at secondary replica SQL2. As we can observe, Database STUDENT is in Synchronizing state, and it is in sync with the primary replica, and we can read data successfully.

SQL Server AlwaysOn High Availability - verify the database at secondary replica

Table of contents

Domain Controller and Domain Client Node setup for AlwaysON High Availability
iSCSI, iSCSI Initiator, Quorum Configuration and SQL Server Cluster Installation
Configuring a SQL Server AlwaysOn High Availability Group
Priyanka Devre
168 Views