SQL Server Failover cluster+ ALwaysOn (three)
We introduced the configuration of SQL Server Failover cluster in the previous two articles, and described the previous conditions for configuring the new AlwaysOn node, and today we mainly describe the detailed configuration of always. As we mentioned earlier, if you want to implement SQL Server Failover cluster+ alwayson,sql Server Failover Cluster Two nodes or multiple nodes to install a SQL instance, Then AlwaysOn also needs to install a separate instance, although the AlwaysOn node must be joined in Faillover cluster, but to create AlwaysOn you must create AlwaysOn availability group relationships between it and the previous SQL clustered instance. In addition, the AlwaysOn feature is set at the instance level, where there are 2 SQL instances, so the 2 SQL instances need to be set separately. For SQL cluster instances, use SQL Server Configuration Manager settings Once on any of its owner nodes (takes effect after restarting the SQL service).
Let's go back to the architecture diagram above.
Next we configure AlwaysOn high availability, we find prompt error, but there is a guide to how we configure
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 cluster+ ALwaysOn (three)
