SQL Server AlwaysOn Architecture and principles

SQL Server AlwaysOn Architecture and principles

The AlwaysOn technology supported by SQL Server2012 centralizes the benefits of failover clustering, database mirroring, and log shipping, but not the same. The units of a failover cluster are SQL instances, database mirroring and log shipping units are single user databases, and AlwaysOn supported units are availability groups, and each group can include one or more user databases. That is, once a switchover occurs, all data groups in the availability group are toggled as a whole.

AlwaysOn underlying is still monitored and transferred using the Windows failover Clustering mechanism, so you need to establish Windows Cluster first, except that the databases in the availability group are not necessarily stored on shared storage. Can be stored on a local disk.

Now, let's look at the key features of AlwaysOn:

1. As with failover clustering, a virtual network name is required for a unified connection to the client.

2. A primary server can have up to four secondary servers, a total of five, and the secondary server supports read-only functionality.

3. The secondary server can perform backup and DBCC maintenance commands independently. By configuration, read-only requests from clients can be automatically directed to the secondary server.

4. Data between the primary and secondary servers is encrypted and compressed to improve security and network transfer efficiency.

5: Supports automatic, manual, and forced three failover modes.

6 A dashboard is used to monitor the operating status of AlwaysOn.

7. Multiple-site deployments can be implemented, where primary and secondary sites can span physical networks.

The basic architecture for AlwaysOn

Deploying AlwaysOn High availability groups on the basis of a Windows MSCS failover cluster enables users to install a SQL Server standalone instance on a cluster node, or to install a SQL Server clustered instance, and AlwaysOn requires only all SQL The server instance runs in the same MSCS, but the SQL Server instance itself does not require cluster mode, which is completely different from the instance of the SQL Server2008 cluster. A single-machine mode SQL Server is recommended here, and the advantage is that the availability replica is a standalone instance, so the database copy resides on the local disk where the instance node is running, and if the availability replica is a clustered instance, the database copy is stored on the shared disk.

An availability group is a cluster resource from a Windows cluster perspective, where all databases fail over between nodes as a whole, and of course this excludes system databases, which cannot be added to the High Availability group.

Because of the need for monitoring and staging with the WinDOS cluster, AlwaysOn is subject to some limitations:

All availability replicas in an availability group must be running on a single Windows cluster, and SQL Server instances across different Windows clusters cannot be configured as an AlwaysOn availability group.

All availability replicas for an availability group must be running on different nodes of the Windows cluster. Two different instances running on the same node cannot be used as replicas of the same availability group.

If an availability replica instance is an instance of SQL cluster, any other SQL instance installed on another inactive node of the same SQL cluster cannot be used as its secondary replica.

A database can belong to only one availability group.

AlwaysOn can support up to five replicas, but only one database running on an availability replica is in a writable state. This read-write database is referred to as the primary database (Primarydatabase), and this availability replica is called the primary replica (Primaryreplica). The rest of the replicas are called secondary replicas (Secondaryreplica), the databases on the secondary replicas may be inaccessible, or they can only accept read-only operations (depending on the configuration of the availability group), which are referred to as secondary databases. Once a failover occurs, any secondary replica can become the new primary replica instance. The primary replica continuously sends data changes on the primary database to the secondary replica to enable database synchronization between replicas. Shows the relationship between the replicas in an availability group.

Let's take a look at the relationship between AlwaysOn availability groups and Windows failover clustering in this diagram, where Windows failover clustering uses two subnets, two nodes in the left subnet, and three nodes in the right subnet, which is the first A clustered instance of SQL Server is created on the rightmost two nodes, stored in the shared storage, and the other three nodes are installed on a single machine instance, stored locally, and a total of four instances comprise an AlwaysOn availability group, one primary replica and the other secondary replicas.

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.

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.

This article is from the "Duffy" blog, keep this source http://dufei.blog.51cto.com/382644/1384210

SQL Server AlwaysOn Architecture and principles