Suspected traps for SQL Server AlwaysON synchronization mode

One of the most important features of SQL Server 2012 rollout, AlwaysOn, is a new feature set before cluster and mirror, which solves the problem of cluster dependent shared storage, It also solves the problem that the mirror cannot be read in real time and the connection string needs to be added to transfer IP.

And the functionality of AlwaysOn multi-replicas provides the possibility of a read-write separation, just imagine if the primary replica is more stressful, can the read operation be directed to the secondary copy? The answer is generally yes, please note that is general!

AlwaysOn has two synchronous modes, synchronous and asynchronous, which is, of course, synchronous, which I think is real-time, so I configured read-only routing to use this feature.

Unfortunately, this synchronization is not a real-time synchronization of the data, and when the primary replica data changes, the secondary copy in synchronous mode does not immediately fetch the changed data.

The experiment is as follows:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

EXEC sp_addlinkedserver @server = N‘Secondary‘, @srvproduct = N‘‘,     @provider = N‘SQLNCLI‘, @datasrc = N‘192.168.200.201‘;EXEC sp_addlinkedsrvlogin ‘Secondary ‘, ‘false ‘, NULL, ‘sa‘, ‘sqlcn.com‘USE DemoDB goCREATE TABLE tb_alwayson     (       id INT IDENTITY              PRIMARY KEY ,       name VARCHAR(200)     )INSERTINTO tb_alwayson         ( name )         SELECT NEWID()SELECTCOUNT(*) FROMtb_alwayson WAITFOR DELAY ‘00:00:00.900‘ SELECTCOUNT(*) FROMSecondary.DemoDB.dbo.tb_alwayson

Using the connection server, this is a very good understanding of the test method, in my environment, you will find that in the auxiliary copy to take the change of data, about 900ms to ensure that, under 900ms, there is no guarantee, even under 300ms, did not appear once can sync the situation.

This is the sync mode, so you don't have to be a little bit defensive .

So how exactly does this synchronization mode sync?

The answer is this: it guarantees that the transaction log is synchronous, which guarantees that no data loss is guaranteed, but there is no guarantee that data changes will not be delayed, since the secondary replica receives the trans log from the primary replica, first easing it to the local log Cache, and then forcing hardening to the local LDF, The master copy is then informed that you can commit, but note that hardening to the local LDF at this point is not local data has changed, because the secondary replica hardens the trans log to local, and it uses an asynchronous process to redo these trans Log generated page changes to the data file, which determines that the redo operation is not possible than the hardening of the log early, so the data delay is positive.

In the SQL Server 2012 implementation and management guidelines, the AlwaysOn synchronization process is as follows:

There is a thread called log writer in any SQL Server, and when any SQL user submits a data modification transaction,
is responsible for recording the log information for this modification into a log buffer in memory for the first time. Then write the physical log file (log cure).
Therefore, for any database, there will be records of all data changes in the log file.
for a database configured as an AlwaysOn primary replica, SQL Server establishes a worker thread called log scanner for it.
This thread is specifically responsible for reading log records from the log buffers or log files, packaging them into log blocks, and sending them to each secondary replica.
The data on the primary replica can be propagated continuously to the secondary replica due to its uninterrupted work.
on secondary copy on , there will also be two threads to complete the corresponding data update action, which are Cure (Harden) and Redo (Redo) . The
cure Thread writes the log block from the primary replica log scanner to the log file on the secondary replica's disk (this process is called "curing").
While the redo thread, it is responsible for reading the log blocks from disk, translating the log records into data modification operations, and completing them on the secondary replica's database.
When the redo thread finishes its work, the database on the secondary replica is consistent with the primary replica. AlwaysOn is the mechanism by which the synchronization between replicas is maintained. The
Redo thread communicates with the primary replica 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.

These threads are independent in their work to achieve higher efficiency. Log scanner is responsible for transferring the block of logs without waiting for log writer to complete the journal Cure; the secondary replica completes the log cure and sends a message to the primary replica, informing that the data has been delivered without waiting for the redo to complete. Its design goal is to minimize the performance impact of the extra operations that AlwaysOn brings on normal database operations.

The fact is clear, the principle of synchronization determines the data delay, want to use AlwaysOn to do read and write separation of friends, consider the time you can tolerate delay!

In addition, Microsoft do you dare in the official online documentation and various technical conferences on the synchronous mode of non-data real-time synchronization to mention it?