Solve four common faults of SQL Server through transaction logs (2)

The database image solution has two operating modes. One is "high security mode", which supports synchronization operations. In high security mode, when the session starts, the backup storage synchronizes the backup storage with the master database as soon as possible. Once the database is synchronized, the transaction will be submitted by both partners, this will prolong the transaction latency. The second mode is the high-performance mode. The main difference between the mode and the first mode lies in asynchronous operation. The backup storage tries to synchronize with the log records sent by the master server. The image database may lag behind the main database. However, the interval between databases is usually very small. However, if the workload of the main server is too high or the workload of the image server system is too high, the time interval will increase. In high-performance mode, after the principal server sends a log record to the backup storage, it immediately sends a confirmation message to the client. It does not wait for confirmation from the backup storage. This means that transactions can be committed without waiting for the backup storage to write logs to the disk. This asynchronous operation allows the subject server to run with the minimum transaction latency, but may lose some data. The specific mode is determined by the database administrator based on the company's attitude towards data loss and workload.

It can be seen that the data synchronization solution between the available backup server and the production server is implemented based on transaction logs.

Fault 3: Solves the data consistency problem.

Suppose there is such a situation. In a banking system, a user needs to transfer funds. The transfer operation is completed in two steps. The first step is to deduct the amount in the user account; the second step is to transfer the money to another user. If the first step is successful during the transfer process, but the second step is wrong for some reason. If the account name provided by the user does not match the account name actually transferred, the second operation will fail. The entire transfer operation will end with a failure. But now the problem is that the first deduction action has been completed in the database zhon. In fact, the transfer was not successful, saving the problem of data consistency.

In the actual processProgramSend rollback
Statement, or when the database engine detects an error, it uses the log record to roll back the modifications made by unfinished transactions. That is to say, when the second operation fails, the application sends a rollback
Statement to restore the first operation by using the transaction log rollback function. That is to say, the deduction operation is restored to achieve data consistency. Similar applications are frequently used in database development.

Fault 4: Database point recovery issues.

For example, we have encountered such a fault. The database system suddenly finds a fault at eleven o'clock A.M. and cannot start up. The database system just finished a full backup at last night. In this case, if you only recover data from the full backup, you can only recover the data from last night. Can the data from eleven o'clock A.M. to yesterday evening be recovered?

Actually not. Because any modification made to the database will be saved in the transaction log. As long as the transaction log is not damaged, the database administrator can recover the data at eleven o'clock A.M. The specific operation method is very simple. You just need to use the full backup file to restore the database system. At this time, the data bit in the database is the data at last night. Then, the log recovery function is used to restore the data to eleven o'clock A.M. today. The transaction log can help the Administrator recover data to a specific point in time.