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