Shulou(Shulou.com)06/02 Report--

What this article shares with you is about the principle of SQL SERVER Alwayson and how to troubleshoot. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

SQL SERVER Alwayson is a form of SQL SERVER distributed database, which may not be used by many companies, and is a good solution for rapid development and high availability. But in use, there will also be the problem of TROUBLE. Today, let's talk about the principle of ALWAYS ON of SQL SERVER and some faults. I hope it will be helpful to you.

SQL SERVER's Always on is based on PAXOS protocol, but in the final analysis, WINDOWS Failover Cluster should also be based on PAXOS (if there is something wrong, I hope WINDOWER will correct me)

This diagram is an architecture diagram of SQL SERVER ALWAYS ON 2016-2017. SQL SERVER 2017 supports the architecture of one master and eight slaves, but generally speaking, the use of one master and two slaves is a mainstream. So what's the difference between SQL SERVER's ALWAYS ON and MYSQL's MGR?

1 MYSQL MGR supports multi-master mode, but SQL SERVER does not.

2 SQL SERVER's AWO supports synchronous and asynchronous modes MYSQL's MGR you can think of as a strongly consistent synchronous mode.

3. Both SQL SERVER and MYSQL are replicated by logging.

4 MYSQL's MGR is based on overall database replication ORACLEER can understand, while SQL SERVER's cluster is not based on INSTANCE but based on database (not a database understood by ORACLE, ORACLE's ER can be understood as a set of SCHEMA, user-owned tables), from this point of view SQL SERVER is still relatively flexible and friendly.

Now, let's talk about the principle of ALWAYS ON in depth.

The above diagram well illustrates the synchronization process of the whole information of ALWAYS ON.

1 SQL SERVER brushes the LDF log to disk, and at this time the LDF log must be copied to the slave node in the same order as the log write to the master library. (does this think of MYSQL's BINLOG, but it's not quite the same? why do you think about it? it says, oh, if I can't remember, it will be written at the end of the article.)

2 the log will be copied to the corresponding log queue of the slave library, and then the captured thread will run all the time to synchronize the transmitted data. If there is a problem with replication for some reason, then the LOG SEND queue will be established.

3 the replication queue of information in each database is taken out and transferred to the slave database over the network.

4 accept from the library and cache the data quickly

5 LOG is physically FLUSH to the LDF file of the slave library, and an ACK is given to the master library (which reminds me of the semi-synchronization of MYSQL)

6 start the REDO thread and brush the data into the MDF NDF file.

It looks simple, but the actual work is no easier than MYSQL's BINLOG replication.

In addition, there should be flow control in the master and slave copy, which contains 8192 MESSAGES in one packet. At the same time, it is also controlled for the database level. A database transmits a maximum of 11200 MESSAGES at a time (whichever is the case, it is estimated that you are not SQL SERVER DBAER. This question will also be mentioned at the end of the article). These two standards are on a first-come-first-served basis, if the other party does not reply. The service that sends the LOG will wait for the other party to reply, and after receiving so many logs, the sender will continue to send. At the same time, there is also a hidden sending standard is the LSN number, the difference between master and slave libraries, of course, through the time of last commit, we can also judge what the synchronization status between master and slave nodes is. For more information, please see the hard_database_replica_states of SQL SERVER DMV.

At the same time, because of the FAILOVER function of ALWAYSON, the purpose of FAILOVER is to judge whether the current handover host coincides with the LSN of the slave library, so that two parameters RTO and RPO, which are used to judge the performance of the AWO, can be used to determine whether the AWO can switch quickly if it encounters a host failure. Reminds me of the function of MYSQL's MHA and the meaning of its existence. There is no more detailed explanation here, just be interested in GOOGLE, a bunch of explanations and scripts.

Common faults or problems in ALWAYSON

Data latency, which is a (there are two types of AWO, asynchronous and synchronous). Even if you use synchronous replication, there is actually a time difference between master and slave (especially in the case of Ihop O and poor network performance).

In SQL SERVER, such a problem is called HIGH HADR_SYNC_COMMIT.

Well, what caused this question? which films might have such a problem?

The transaction is initialized at the primary node

The master replication does not transaction logs and is sent to secondary replication

Copy and accept the log from the library and harden it and send it to the slave library

In the figure below, there are likely to be performance problems, but in vernacular

1 big business

2 bad network

3 Bad Imax 0

At the same time, you can see how long the delay is through the transaction delay and transaction mirror synchronization in the DATABASE REPLICA of the SQL SERVER performance monitor.

So break a concept is SQL SERVER AWO synchronous replication, master and slave data must be 100% consistent, NO NO NO I looked at the current production library, MYSQLER can be understood as behind master of course SQL SERVER high-end, time shows, the difference is clear.

All right, answer the questions above that some people don't know.

I don't know what's the difference between MYSQL 5.6replication and MYSQL 5.7replication. Here we compare MYSQL 5.6multithreaded replication with SQL SERVER AWO replication. It can be analogous, because it is a library and a thread (SQL SERVER AWO seems to be the same). MYSQL 5.7to8.0can be multithreaded replication, and the replication mode of GR cannot be compared with AWO slowly. In addition, SQL SERVER replication is based on database logs, while MYSQL replication is based on BINLOG (FILTER database replication that is also filtering and SQL SERVER replication is still not the same, so this point can not be compared.

By the way, give SQL SERVER an advertisement SQL SERVER 2019 directly integrates SPARK, and those who do a large database can pay attention to it. Although it may not be so good, at least one more choice, short and fast, with an average amount of data, may still enjoy a wave of "Manulife".

The above is the principle of SQL SERVER Alwayson and how to troubleshoot. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.