SQL Server Failover + ALwaysOn

We've written a lot of SQL Server-related articles in the near future, in order to improve the usability of the service, we think of the deployment AlwaysOn, the previous environment just deployed SQL Server Failover Cluster, so decided to put the cloud in a SQL Server to configure AlwaysOn, the idea is to add a node to the local SQL Server Failover cluster, then place the new home node in the Azure cloud and then configure AlwaysOn between the two instances, after deployment, One problem is that the cluster can not automatically failover between the need for manual intervention to be specific later we do more detailed introduction, nonsense will not say more, began to practice the configuration;
Environment Introduction:

Hostname:dc1
Role:dc
ip:192.168.5.20
Domain:ixmsoft.com
Hostname:iscsi
ip:192.168.5.38
Role:storage
Hostname:s1
Role:sql Server 2016
ip:192.168.5.41
Hostname:s2
Role:sql Server 2016
ip:192.168.5.42
Hostname:ao1
Role:sql Server 2016
ip:192.168.5.43
Sql-cluster
192.168.5.46
SQLCluster
192.168.5.47
Ha-lp1
listenip:192.168.5.48

Because we want to do disk sharing, so we use the system comes with iSCSI as a connector;
We first install the configuration iSCSI server:
The first is to mount two disks: one is data:50g, the other is quorum: 10G

Then install the iSCSI target server

After we install, we open iSCSI management---Create an iSCSI virtual disk

We choose a new data disk

We increase the computer IP that needs to allocate the disk
We add two SQL Server servers

Confirmation information

Create complete


Create a new virtual disk again for quorum

Set the disk name

All disks have been added to complete

We're starting to connect to the shared disk from 5.41 through the iSCSI Connection program

Prompt to confirm startup service

Enter the iSCSI server address, Quick link

is connected

Volumes and devices are loaded

We can now see the allocated two disks on the 192.168.5.41.

We also follow the same method as above to make iSCSI links on 192.168.5.42
With the above operations ready, we can start installing the failover cluster;
We first operate the installation on the S1

Installation Complete

After installation, we also install on the second S2, after installation, we open the cluster Manager
Right-click Failover Cluster Manager----Verify the configuration

Two additional SQL Server servers

After the verification has passed, click Finish

After the validation is passed, we can create it;
We define the cluster name and IP
Sql-cluster
192.168.5.46

Confirm the information after

Start creating a cluster

Definition Complete

Two node information

Disk information

Configure Quorum

Advanced arbitration Options

Select all nodes

Select the quorum disk

We also add the first disk to a cluster shared volume

We are ready to install SQL Server 2016

Define the SQL Server network name
SQLCluster

Select Data Disk

Define Cluster network IP
192.168.5.47

Define account information

Define data directory, automatically select disk shared volume directory

Installation Complete

We can see one more role and manage IP in Cluster Administrator

We're going to install a second node

Next you can

By default you can

Confirmation information


Node Additions completed

Test cluster
We switch from Node 1 to Node 2

Switching in

Toggle Complete

We use SSMS for connection testing
We connect using the SQL cluster address

We use the cluster network address link

We look at cluster properties----clustered--true

We then configured ALwaysOn, and we prepared a SQL Server
But also need to join the cluster node;

We are now adding nodes to the cluster nodes, adding a third SQL to the node

Enter the name of the new node

Validation by

Directly increases the node after the test passes

Node Additions completed

We look at the node information again

We then install the standalone SQL instance

We install functional roles

You must name an instance because a default instance has already been created in the cluster, and if you have installed a SQL cluster instance in the cluster, then when you install the SQL instance on the nodes in the cluster (regardless of the stand-alone
or clustered), you can no longer use this instance name. That means you've already installed clustered SQL on cluster nodes 1, 2.
Default instance, you can no longer install a standalone SQL default instance on node 3. In this case, you can select the node
On 3, install a SQL named instance,

Define account information

Server configuration information

Data Catalog we can define locally

Installation Complete

After node three is installed, we find that the service does not have a port, the default amount of SQL Port is 1433, so we modify the default port---SQL Server configuration Management


You then create an AlwaysOn availability group relationship between it and the previous SQL cluster instance. In addition to the AlwaysOn feature is set at the instance level, here you have a total of 2 SQL instances, so you need to the 2
Each SQL instance is set up separately. For SQL cluster instances, use SQL Server on any of its owner nodes
Configuration Manager is set once (takes effect after restarting the SQL service).

With SSMs right-clicking--alwayon High avaliablity There is a hint that the AlwaysOn feature must be enabled for the server instance before the availability group can be created on this instance, and to enable Alowayson, open the SQL In the server Configuration Manager, right-click the SQL Server instance name, select Properties, and then use the AlwaysOn High Availability tab of the SQL Server Properties dialog box, we link the cluster address, click always higher availability, Tips on how to open it.
Note: After we use SSMS to connect to SQL Server, in the Server Properties dialog box, click General Page. The Hadr Enable property
One of the following values is displayed: True if the always on availability organization is enabled, False if it is always disabled in the availability group.

So we're going to turn on the function

SQL Server Service---Properties--right-click

We switched the SQL Server service's login account to a domain account

We tick enable Alwayon availability Group

Application--After confirmation, the database service needs to be restarted

Restarting service

AlwaysOn for the second server when the node switches to Node 2, the first automatic tick, so do not tick, and when the role is not the operation of the node, we will find that Lwayson high availability is not operational;
We can tell by the information on the system prompt.

We look at the status of the role again: The following state is normal because AlwaysOn high availability is enabled

In this case, you can choose to install a SQL named instance on the node 3 and then create an AlwaysOn availability group relationship between it and the previous SQL cluster instance.
In addition, the AlwaysOn feature is set at the instance level, where you have a total of 2 SQL instances, so you need to set up the 2 SQL instances separately. For SQL cluster instances, use SQL Server on any of its owner nodes
Configuration Manager is set once (takes effect after restarting the SQL service).
We also first open the AlwaysOn High Availability feature for node three

We use SSMs to link instances

We all know that high availability is DB-based, so we need to create a database: HAGourpDB1

Create a table at the same time, Perinfo

We insert data

We started to create high availability groups under cluster instances

Tick database layer run status detection to define the name of the high availability group: HA-GP1

Prompt requires full backup first

So let's just back up.

Full Backup and Backup type

Backup complete

We also back up log

We need to restore the backed up database and log at three nodes at a time

Restore status: Restore with NORECOVERY

Recovery complete

Database state not restored mode

Redo Transaction Log

Also select Restore status

Recovery complete

We continue to create a high availability group that meets the criteria to continue to the next

We add copies

Whether the primary replica or the secondary replica chooses synchronous-commit mode, the readable secondary of the secondary replica is selected as Yes. Only for the later read-only secondary database preparation.

AlwaysOn and mirroring all use endpoint (endpoints) for data transfer. AlwaysOn uses endpoints to communicate with the secondary replica for log transfer and Heartbeat lines

Backup priority tick prefer secondary. This means that limited consideration is given to backup on the secondary replica. The primary replica is used only if there is no secondary replica. The secondary replica's priority level is set to 100, while the primary replica is 50.

Our listening ports are created later

Confirm to---Yes

This place is the way to choose to initialize the database. If you choose full, you need to provide a shared address, AlwaysOn automatically backs up the database and then restores it to the secondary replica of the target. Here we choose join only, so
We need to back up the database and restore it to the target's secondary database in advance----Join only

After we start the next step, we look at the status

Create complete


We expand the database high availability group

We view the role of a higher availability group role

We then create a listener
After AlwaysOn is created, the client needs to connect, in order for the application to transparently connect to the primary replica without the impact of a failed failover, we need to create a listener that is a virtual network name that can access the availability group through this virtual network name. Instead of worrying about which node is connected, it automatically forwards the request to the primary node, and when the primary node fails, the secondary node becomes the primary node, and the listener automatically listens to the master node.
A listener includes a virtual IP address, a virtual network name, a port number of three elements, once created, the virtual network name is registered in DNS, and an IP Address resource and a network Name resource are added for the Availability group resource. Users can use this name to connect to the availability group. Unlike a failover cluster, the real instance name of the primary replica can also be used to connect, in addition to the virtual network name.
SQL Server2012 earlier versions of SQL Server attempt to bind the IP and port only when the instance is started, but SQL SERVER2012 allows a new IP address, network name, and port number to be bound at any time when the replica instance is in health. As a result, you can add listeners to the availability group at any time, and this action takes effect immediately. After you have added a listener, you can see a message similar to stopping and starting the listener on the virtual network name in the error log of SQL Server.
It is important to note that the SQLBrowser service does not support listener. This is because when an application connects to SQL Server using Listener's virtual network name, it is accessed in the form of a default instance (only the hostname, no instance name), so the client simply does not attempt to use the SQLBrowser service.

Define the listener name and IP
Name: Ha-lst;
IP address: 192.168.5.48;
Port is 1433

Definition Complete

We are looking at the role, we will find a corresponding management address

After the definition is complete, we can view the display panel of the highly available row group

We can view the status of the high Availability Group through the display panel

Next we switch, we need to pay attention to a problem before switching: Switch the time can not be in the cluster Manager to switch, need to switch under the High Availability group, or there will be problems, even if the switch succeeds, some data will also appear problems
We first view the node owner in the cluster Manager

Additionally, when we connect to the cluster nodes, we find that the nodes of the availability replicas under the High availability group belong to the secondary node;

Next we're ready to start switching, we use SSMS to connect to a third node instance
View the current availability group under the third node in the secondary replica state

Let's start switching.

Select Primary Replica

Confirmation information

Transfer complete

We'll look at the third node of the AO1 AG State and it's the master copy.

We'll switch from master to standby.

Select a new primary replica

Link copy

Start connection

Link successful

Confirm Transfer Information

Transfer complete

We're inserting a piece of data from the SQLCluster.

Then view the data from the AO1

We insert data hints from AO1, the database is read-only, so data cannot be inserted

The reason is that because the current node belongs to the second node, if it is readable and writable, the node needs to be transferred to the primary replica node to

We will ao1\alwayon under the AG ha-gp1 from the copy to the main copy we insert the data again

Transfer complete


We try to insert the data again

We see whether data is synchronized from the SQLCluster cluster node

We are again inserting data into the SQLCluster node, prompting the error
The reason is that the node belongs to AO1

But we look at the data and the data inserted from the current node from AO1 can still be synced to SQLCluster

Data synchronization between replicas
AlwaysOn must maintain data consistency between replicas, and when the data on the primary replica changes, it is synchronized to the secondary replica. Here AlwaysOn is done in three steps:
Step 1: The primary replica records the changed data;
Step 2: Transfer the records to each secondary replica;
Step 3: Perform the data change operation once on the secondary replica.
The specific implementation is as follows:
On both the primary and secondary replicas, SQL Server starts the appropriate thread to complete the corresponding task. For a typical SQL Server server, where high availability is not configured, the thread of log writer runs, and when a data modification transaction occurs, the thread is responsible for logging the log information for this operation to the log buffer before writing to the physical log file. However, if the Alwaysony primary replica database is configured, SQL Server will create a thread called Log scanner for it to work uninterrupted, to read the log from the log buffer or log file, package it into a log block, and send it to the secondary replica. Therefore, the data can be guaranteed to be changed and sent to each auxiliary copy continuously.
There is a cure on the secondary replica and redo two threads to complete the data update operation, the cured thread writes the log block of the primary replica log scanner to the log file on the secondary copy disk, so called cure, then the redo thread is responsible for reading the log block from disk, repeating the operation of the log record again. The data on the primary and secondary replicas is now consistent. The redo threads communicate with the primary replica at regular intervals, informing them of their work progress. The primary replica thus knows the difference between the data on both sides. Log scanner is responsible for the transfer of logs block, do not need to wait for log writer to complete the post-curing, the secondary copy after the completion of the log will send a message to the primary replica, to inform the completion of the data transfer, and do not need to wait for the redo to complete, so that their independent design is to The effect that AlwaysOn brings on the performance of the database.

SQL Server Failover + ALwaysOn