The principle and available mode of AlwaysOn synchronization

A new generation of read-write separation technology--alwayson

As early as SQL Server 2005, Microsoft has implemented a database of query separation technology----publish subscriptions. However, the synchronization performance of production libraries and query libraries is poor, and performance problems often occur, so it has been criticized by people in large-scale production environment.

Starting with SQL Server 2012, Microsoft is gradually using AlwaysOn technology instead of publishing subscriptions. AlwaysOn as a new technology architecture introduced in SQL Server 2012, performance is much higher than published subscriptions, and the most obvious difference is that it leverages the principle of efficient memory read to implement log delivery. The following is a detailed understanding of AlwaysOn's synchronization benefits through AlwaysOn synchronization principles and available modes.

AlwaysOn Synchronization principle

AlwaysOn is a technology for synchronizing the entire library, and all member servers maintain a set of identical copies of the database. When the data on the primary replica changes, the data is synchronized to the secondary replica in real time. This is very similar to database mirroring.

The whole process of AlwaysOn data synchronization is described in detail, so let's take a look at what each step means.

① the logwiter of the primary replica logs the transaction modified log information into an in-memory log buffer before writing to the physical log file (log cure);

② the Logscanner of the primary replica reads the log block from the cache or the log file and sends it to the AlwaysOn log block decoder;

Note: The decoder searches for operations in the log that require special handling, such as file stream operations, document growth, and so on.

③ The primary replica transmits the log block over the network to the secondary replica;

④ and ⑤

After the secondary replica accepts the log block, Logwiter logs the transaction modification to a log buffer in memory, then writes the physical log file (log cure), and if the secondary replica is in synchronous mode, after the log is cured, the message must be fed back to the primary copy. The primary replica can commit the transaction after it has received a cure for the secondary replica, and if the secondary replica is in asynchronous mode or the primary replica is in asynchronous mode, the primary replica commits the transaction regardless of whether the secondary replica completes the log cure, which is described in detail in the introduction of the available modes;

The ⑤ Redo (Redo) thread re-interprets the transaction recorded in the log on the secondary replica. A redo thread communicates with the primary replica at every fixed point in time, informing it of its own work progress. The primary replica is able to know how far apart the data on both sides is.

AlwaysOn VS Publish Subscriptions

We know that a transaction log publication subscription is not typically used for synchronization of the entire database, but that some objects in the library are synchronized, while AlwaysOn is the entire database, and from the point of view of the data volume, AlwaysOn has more data to synchronize, so why is it performing better?

Let's look at the following two ways to find out the answer:

1. Synchronizing objects

A synchronization object that publishes a subscription is a transaction log that has been written to disk, but not all of the transaction logs are published, only those that are marked for publication are published, so it not only requires a read disk, but also scans all logs for a transaction to filter to the logs marked for publication. If the log of this transaction is very numerous and the log to be published is very small, the efficiency of the log reader will be very low;

While the majority of AlwaysOn synchronization objects are in the memory log buffer, the log scanner does not need to read the disk or simply read a small number of disks, and AlwaysOn is the whole library synchronization, as long as the primary replica generated log will be synchronized to the secondary replica, do not need to log filtering, so not only read faster, And the efficiency is still high.

Note: The log for AlwaysOn synchronization is more than the transaction log publication subscription, but does not necessarily take up network bandwidth from a network perspective, because in AlwaysOn, the compressed log is passed on the network, and the publish subscription is not optimized for compression.

2. Synchronization process

In a publish subscription, the log cannot be directly from the publishing library to the subscription library, and must be brokered through the distribution library, resulting in a large amount of disk IO and network consumption per process;

While AlwaysOn is point-to-point data synchronization, the log is sent directly from the primary replica to the secondary replica, no transit is required, and the transfer process is simple and efficient.

AlwaysOn Availability Mode

As described above in the introduction of the AlwaysOn synchronization principle, we consider it relatively simple, only consider the synchronization of the log.

If you want to combine transactions for overall consideration, AlwaysOn synchronization-more precisely the available mode-should be divided into asynchronous commit mode and synchronous-commit mode.

The availability mode is a property of each availability replica in AlwaysOn, which determines whether the primary replica needs to wait for a secondary replica to solidify the transaction log records to disk before committing the transaction, and if it waits, the AlwaysOn available mode is synchronous-commit mode, or the asynchronous-commit mode ”。

Asynchronous commit Mode

An availability replica that uses this availability mode is called an asynchronous-commit replica.

A transaction can be committed when the secondary replica is in asynchronous-commit mode or although the secondary replica is in synchronous-commit mode, but when the primary replica is in asynchronous-commit mode, the primary replica does not need to confirm that the secondary replica has completed the log cure. Therefore, the primary database transaction commits are not affected by the secondary database and the wait is generated. However, updates to the secondary database may lag behind the primary database and may cause some data loss if a failover occurs. Therefore, this available mode is suitable for situations where the availability replica is distributed far away.

Synchronous commit Mode

An availability replica that uses this availability mode is called a synchronous-commit replica. Synchronous-commit mode requires the primary and secondary replicas to be set up as synchronous-commit replicas.

In synchronous-commit mode, the primary replica must confirm that the secondary replica has completed the log cure to commit the transaction (no need to wait for the secondary replica to complete the log redo), so that the data on both sides is always synchronized. However, the cost of this guarantee is that the transaction commits on the primary database have a lag time. It can be said that synchronous-commit mode emphasizes high availability in relation to performance.

Principles and available modes for AlwaysOn synchronization