About the transaction log in SQLServer (5) ---- the role of logs in high availability and disaster recovery

This is the fifth article in the series and a supplement to the previous log series. If you do not have a systematic understanding of the basic concepts of logs, refer to the articles in this series: about transaction logs in SQLServer (1) ---- physical and logical architecture of transaction logs talking about transaction logs in SQLServer (2) ---- repair of transaction logs

This is the fifth article in the series and a supplement to the previous log series. If you do not have a systematic understanding of the basic concepts of logs, refer to the articles in this series: about transaction logs in SQL Server (1) ---- physical and logical architecture of transaction logs talking about transaction logs in SQL Server (2) ---- transaction logs are being repaired

This is the fifth article in the series and a supplement to the previous log series. If you do not know the basic concepts of logs, refer to the articles earlier in this series:

Transaction logs in SQL Server (I)-physical and logical architecture of transaction logs

Transaction Log in SQL Server (2)-Role of transaction log in data modification

Transaction Log in SQL Server (III)-Role of log in simple recovery mode

Transaction Log in SQL Server (IV)-Role of log in full recovery mode

 

Introduction

Logs ensure durability and data consistency. You can use logs to Undo and Redo data. Therefore, using logs, SQL Server can not only achieve disaster recovery, the log Redo can also be used to achieve high availability. This article describes the functions of logs in the high availability of SQL Server and their roles in disaster recovery.

 

Log corruption

Logs may be damaged due to the fault of the I/O subsystem. When logs are damaged, if you have a slight understanding of the log and can save the data if the log is damaged, so it must be very good :-). Let's take a look at the recovery situations when several logs are damaged.

 

1. The database is shut down normally and logs are damaged.

When the database is shut down normally, log corruption is not so important, because at this time, all the dirty data corresponding to the committed transactions in the database has been CheckPoint to the physical disk, therefore, data inconsistency does not exist. Therefore, if the MDF and NDF files are intact, you can directly specify the FOR ATTACH_REBUILD_LOG parameter and append it, as shown in 1.

If the database is shut down normally, simply attach it.

 

However, it is worth noting that using this method to append the database will automatically recreate the log file. The size of the log file is 0.5 MB, that is, two VLF files, which will automatically increase to 10%, therefore, you need to manually set the log size to avoid too many VLF cases.

 

2. The database is shut down abnormally and logs are damaged.

Before talking about this situation, Let's first look at several statuses that the database can be in, as shown in a complete model 2.

. The state of the database.

The specific transition relationships of the preceding statuses are beyond the scope of this article. Hong Kong virtual hosts, but here I will emphasize the two statuses that are closely related to log corruption: RECOVERY_PENDING and SUSPECT.

If the database is not shut down normally, that is, there is still data that has not been CheckPoint to the disk. If the database is to be started, it must go through the Recovery process. If the log is damaged, the Recovery process cannot be performed, data inconsistency may occur.

At this time, the database may be in one of the following two states:

RECOVERY_PENDING: You need to run crash recovery, but the process cannot begin due to resource waiting. For example, the log is completely damaged.

SUSPECT: crash recovery has started, but cannot be completed

 

Therefore, to handle this situation, you must determine whether data loss is allowed based on your business environment. You can choose to recover data from the backup or change the database status to EMERGENCY. The EMERGENCY mode means that the database skips the crash recovery stage, the virtual host, and the Hong Kong virtual host. Although the database can be accessed, there will be inconsistent data transactions, if only some data pages are inconsistent, but if the transaction for modifying the table structure exists, the database architecture may be inconsistent. If you do not have an appropriate backup set, you can only restore the data in this way. Setting the database to EMERGENCY mode is simple, as shown in code list 1.

Alter database AdventureWorks2012 SET EMERGENCY