SQL Server AlwaysOn

This topic introduces the steps for configuring instances of SQL Server  AlwaysOn availability groups

What is a cluster?

A server cluster is a group of independent servers running Windows Server 2003, Enterprise Edition and working together as a single system to provide high availability of services for clients

When a failure occurs on one computer in a cluster, resources are redirected and the workload is redistributed to another computer in the cluster

What is SQL Server AlwaysOn Availability Group?

     AlwaysOn technology is the process of having one primary replica server that can have its primary database(s) mirrored up to four separate secondary replicas in different locations

     Unlike database mirroring that has its limitations, AlwaysOn features combines the failover clustering and database mirroring technologies and goes one step further by providing read only database on the secondary replicas!!!

Some of the limitation that database mirroring had are resolved by AlwaysOn:

  • Only one single failover partner at a time (1 to 1)
  • Doesn’t protect complex multiple-database applications such as SharePoint, which had many databases that need to failover simultaneously
  • Had to choose between synchronously or asynchronously options but not at the same time
  • The databases on the mirror server are in a state of constant recovery, thus cannot access the data, leaving a server inactive

With AlwaysOn, the advantages are as follows:

  • Can failover multiple databases as a unit for complex applications
  • Can use either synchronously or asynchronously options on either replica
  • Allows automatic failover of groups of related or non-related databases
  • The secondary replicas can unload of the primary replica with backups, DBCC commands, and reporting

Prerequisites:

  • Must install Window Failover cluster Services on each node
  • Must have a shared folder for each server to access for backups
  • Must take a full backup of the primary database(s)
  • All server must be in full recovery mode
  • Set up cluster

Availability Databases features

  • When adding the database to the group it must be an online
  • Data synchronization is the process by which any change to the primary database is reproduced on the secondary database
  • The role of a given replica determines whether it hosts read-write databases or read-only databases
  • The primary replica hosts read-write database
  • The secondary replica hosts read-only databases
  • Under asynchronous-commit mode, the primary replica commits transactions without waiting for acknowledgement that an asynchronous-commit secondary replica has hardened the log. Some data loss possible.
    • Under synchronous-commit mode, before committing transactions, a synchronous-commit primary replica waits for a synchronous-commit secondary replica to acknowledge that it has finished hardening the log. committed transactions are fully protected
  • During a failover the primary and the secondary servers change roles
  • Three forms of failover exist—automatic, manual, and forced (with possible data loss)
  • You can provide client connectivity to the primary replica of a given availability group by creating an availability group listener.
  • An availability group listener provides a set of resources that is attached to a given availability group to direct client connections to the appropriate availability replica.
  • An availability group listener is associated with a unique DNS name that serves as a virtual network name (VNN), one or more virtual IP addresses (VIPs), and a TCP port number
FeatureLog ShippingDatabase MirroringAlwaysOn Availability Groups
Maximum number of secondary databasesunlimited1 Mirror4 Secondaries
Requires Windows ClusteringNoNoYes, however the SQL Servers can be stand-alone
Automatic failoverManualYes, requires witness server and high-safety modeYes, one
Groups of databasesNoNo, each database is configured separatelyMultiple databases can be grouped to failover together
Can be used for reportingStand By mode (limited)Only against a database snapshot of the  mirrorYes
Offload backupsNoNoYes
Active database is calledPrimaryPrincipalPrimary
Connection to active databasemanualRequires special connection string for failoverRedirection handled by Windows Clustering with a virtual name. Special connection string not required
Connection string to mirror or secondary databaseN/AN/ACan be configured with an ApplicationIntent connection string to automatically redirect or can use traditional connection string with server name.
--REGISTER ALL SERVERS FIRST

--NOTE: DATABASE CREATED WITH FULL RECOVERY MODE

CREATE DATABASE [Rep1]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Rep1', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Rep1.mdf' , 
SIZE = 8192KB , 
FILEGROWTH = 65536KB )

 LOG ON 
( NAME = N'Rep1_log', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Rep1_log.ldf' , 
SIZE = 8192KB , 
FILEGROWTH = 65536KB )
GO

USE [master]
GO
ALTER DATABASE [Rep1] SET RECOVERY FULL WITH NO_WAIT
GO

--NOTE: DATABASE CREATED WITH SIMPLE RECOVERY MODE


CREATE DATABASE [Rep2]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Rep2', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Rep2.mdf' , 
SIZE = 8192KB , 
FILEGROWTH = 65536KB )

 LOG ON 
( NAME = N'Rep1_log', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Rep2_log.ldf' , 
SIZE = 8192KB , 
FILEGROWTH = 65536KB )
GO

USE [master]
GO
ALTER DATABASE [Rep2] SET RECOVERY SIMPLE WITH NO_WAIT
GO

--NOTE: DATABASE DOES NOT HAVE A FULL BACKUP

CREATE DATABASE [Rep3]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Rep3', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Rep3.mdf' , 
SIZE = 8192KB , 
FILEGROWTH = 65536KB )

 LOG ON 
( NAME = N'Rep1_log', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Rep3_log.ldf' , 
SIZE = 8192KB , 
FILEGROWTH = 65536KB )
GO

USE [master]
GO
ALTER DATABASE [Rep3] SET RECOVERY FULL WITH NO_WAIT
GO


--TAKE FULL BACKUPS OF REP1 AND REP2 SERVER BUT NOT REP3 SERVER TO A SHARED FOLDER!!!

BACKUP DATABASE [Rep1] 
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\Rep1.bak' 
WITH NOFORMAT, INIT
GO

BACKUP DATABASE [Rep2] 
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\Rep2.bak' 
WITH NOFORMAT, INIT
GO

--POPULATE DATA FROM ADVENTUREWORKS DATABASE TO REP1,REP2,REP3 USING EXPORT WIAZRD


--START THE ALWAYS ON SET UP 

USE [master]
GO

DROP AVAILABILITY GROUP [123];

GO


restore database rep1

restore database rep4

drop database rep1
drop database rep4

The following steps need to be performed in this order:

  1.  Add Windows Failover Clustering (WSFC) to each replica (server)
  2. Configure WSFC on your primary replica
  3. Configure SQL Server on each replica
  4. Configure Logins & Replicas
  5. Install AlwaysOn

1. Add Windows Failover Clustering:

On each replica, open Server Manager > click Add Roles & Features > select Add Failover Clustering > click Install

Proceed through the wizard, and when you get to the Select Features page, select the Failover Clustering checkbox.

And if you don’t already have .NET Framework 3.5.1 or greater installed on your server, select that checkbox as well to install. (If you do need to install the .NET Framework, you will need to reboot the machine after installing).

SQL Server AlwaysOn

Proceed next through the wizard and click Install to finish the wizard. You will need to do this on every replica in your AlwaysOn group. 2. Configure Windows Failover Cluster (WSFC)

On your primary replica, open the Server Manager console > open Failover Cluster Manager

Click on Validate Configuration.

SQL Server AlwaysOn
SQL Server AlwaysOn

Add the names of all the SQL Servers you want to configure as replicas in your AlwaysOn group. 

On the Testing Options page, click Run all tests (recommended). It is normal to see some warning messages, especially if you decide to use shared storage.

SQL Server AlwaysOn
SQL Server AlwaysOn

After the validation and summary is complete, the Create Cluster Wizard will open.

In the Access Point for Administering the Cluster dialog box, enter the virtual cluster name (not the server or instance name), and the virtual IP address of your cluster.

SQL Server AlwaysOn

Proceed next through the wizard, and your cluster will be created. The secondary nodes will be added to the cluster, and your cluster should now show up on all replicas (through Failover Cluster Manager). You don’t have to go through these steps on the other replicas…you’re all done with setting up the cluster.

3. Configure SQL Server

Assuming you have already installed SQL Server 2012 or 2014 Enterprise edition on all of your replicas, and have installed it as stand-alone instances, we are ready to configure SQL Server. On each of your replicas, open SQL Server Configuration Manager.

SQL Server AlwaysOn

Expand the SQL Server Network Configuration node, and click on Protocols for MSSQLSERVER. You will see in the right panel of the dialog box the TCP/IP entry. Right click on the TCP/IP entry and select Enable

SQL Server AlwaysOn

While you are still in SQL Server Configuration Manager, right click on SQL Server Services to open the Properties dialog box.   Navigate to the AlwaysOn High Availability tab, and select Enable AlwaysOn Availability Groups checkbox.

Restart the SQL Server Service after making these changes.

Do these steps on all of your replicas.

4. Configure Logins and Replicas

If it isn’t there already, add your SQL Service account (which should be a domain account – not the local machine service account) as a SQL login through SQL Management Studio (SSMS).

Add your SQL Service account to the Administrators group on each replica (via Computer Management)

SQL Server AlwaysOn

Give connect permissions to the SQL Service account through SSMS: Right click on the SQL Service login to open the Properties dialog box. Navigate to the Securables page, and make sure the Connect SQL Grant box is checked. You will do this on every replica. 

Make sure all your replicas Allow Remote Connections. You can do this through SSMS in the instance Properties, or by using sp_configure.

EXEC sp_configure ‘remote access’, 1;

GO

RECONFIGURE;

GO

 Make any necessary adjustments to your Windows Firewall, to allow the replicas to communicate with each other.

Create a File Share (through Server Manager) that your SQL Service account and all your replicas can access with read/write permissions. This file share will be used for the initial backup/restore process that happens when your databases are joined to the AlwaysOn group during setup.   There are other options to join your databases to the AlwaysOn group, if you prefer not to create this temporary File Share. In fact, if your databases are large I would recommend using one of the other options, in which you would restore the databases yourself on all of the secondary replicas, instead of having the wizard do this step. We’ll look at that step in a minute…

5.  Install the AlwaysOn Availability Group

Make sure full backups have been run on each database.

Make sure all databases are in Full Recovery mode.

Remove these databases from any tlog backup maintenance during the installation of AlwaysOn (you can add them back later). You don’t want tlog backups happening on these databases while the AlwaysOn group is being created.

SQL Server AlwaysOn

On your primary replica, open SQL Management Studio (SSMS) and expand the AlwaysOn High Availability folder. Right click on Availability Groups and select New Availability Group Wizard… to open the wizard: 

SQL Server AlwaysOn

First, you will specify your AlwaysOn group name. Name it something descriptive and unambiguous: 

SQL Server AlwaysOn

Next, you will select the databases you want to include in your AlwaysOn group. All of the databases in your instance will show up in this list…you don’t have to include all of them in your group… select only the ones to be included in the AlwaysOn group. 

SQL Server AlwaysOn

Also, next to each database is a blue link that signifies whether your database is ready to be included into your group or not. If the link does not say ‘Meets prerequisites’, then you can click on the link to get a more in-depth explanation of what you need to do. 

SQL Server AlwaysOn

Correct any discrepancies, and then select the databases to include in the AlwaysOn group: 

Next, is the Specify Replicas page where you will add the replicas to be included in your AlwaysOn group. Add and connect the replicas by clicking the Add Replica… button.

SQL Server AlwaysOn

For each replica, you will need to specify whether you want Automatic or Manual Failover, Synchronous or Asynchronous Data Replication, and what type of Connections you will allow your end users to connect with. 

SQL Server AlwaysOn

On this Specify Replicas page, there are several tabs at the top. The second tab is the Endpoints tab. On this tab verify that the port number is 5022. If you have more than one instance on your server, you might need to create another endpoint. Click here for further explanation: http://blogs.msdn.com/b/alwaysonpro/archive/2013/12/09/trouble-shoot-error.aspx  

SQL Server AlwaysOn

Next tab is the Backup Preferences tab.   This is where you will choose where you want your backups to occur, and how you prioritize which replica will run your backups. 

The last tab in the page is the Listener tab. Here you will select the Create an availability group listener button.

Enter the DNS name, which is the name that will be used in your application connection string.

Enter port number 1433.

SQL Server AlwaysOn

And enter the IP address for your listener. This should be an unused IP address on your network. 

SQL Server AlwaysOn

Next page in the wizard is the Select Initial Data Synchronization page. Here is where you will join your databases to the AlwaysOn group. The Full option is the Microsoft default option, and is the one that uses the File Share.   The other two options (Join and Skip) are fine too, especially if you have large databases. With these other two options, you will restore the databases yourself, to each secondary replica.   But this example uses the Full option, so you will browse to and select the File Share created earlier. And remember the SQL Service account and all replicas must have read/write permission to the File Share. 

SQL Server AlwaysOn

Next, ensure that your Validation checks return successful results. If you get any errors, you need to stop and correct these before proceeding. 

In the Summary page, verify that all your configuration settings are correct, and click Finish.

SQL Server AlwaysOn
SQL Server AlwaysOn

The Results page will show the progress of the installation. Verify that all tasks have completed successfully.   Because there was no quorum set up while creating the WSFC earlier, we are seeing a warning message here….this will not cause the installation to fail. 

After the results are complete, and everything has finished successfully, you can now see the AlwaysOn Availability Group created in SSMS:

The Availability Group Name: AGroup_Dev

All the Replicas, and whether they are primary or secondary

All the Databases included in the AlwaysOn group

And the Listener created for the group.

This AlwaysOn Group will also be visible on all of the replicas as well.

SQL Server AlwaysOn

Also See:

SQL Server 2019