Solve four common failures of SQL Server through transaction logs

When the system fails, data logs can be used to restore data to solve database faults. As an SQL Server database administrator, it is important to understand the role of data log files and how to use them to solve common database faults. Since the transaction log is so important, what can it do? I will talk about the faults that transaction logs can use to solve.

Fault 1: losses caused by accidental server shutdown

As the saying goes, the sky is unpredictable. If the database server is accidentally shut down due to sudden power failure or other causes, some data loss may occur after the server is restarted. This is mainly because after the data in the database is changed, the data will not be written to the hard disk immediately. To improve the running efficiency of the database, data is often written to the data cache first, and the change is written to the transaction log. The database system will write the data to the hard disk file only after a certain period of time.

At this point, if the database server system suddenly fails, the database system may not write the modified data in the cache to the hard disk, that is, there are unfinished modifications in the data file. If this is the case, the modified data cannot be recovered when the SQL Server instance is started without transaction logs or the transaction logs are corrupted. However, if the transaction log is available, when the instance is started, the system will lose each database for recovery. Each modification to the data file that may not have been written to the data file that is rolled forward to the record. Every unfinished transaction found in the transaction log is rolled back to ensure the integrity of the database data.

Therefore, when the database server fails unexpectedly, the database administrator should be able to check whether the transaction log is available. If the transaction log is corrupted, You need to restore the transaction log and then restart the database instance. Otherwise, the data cannot be restored normally when the database instance is restarted. Pay attention to this point in the case of unexpected server failures. Otherwise, the integrity of the database data may be damaged.

Fault 2: solve the problem of data synchronization in the backup database

Sometimes, for the purpose of high Database Availability, You need to deploy another database server outside the production server. When the production server fails, you can immediately enable the backup server. Therefore, data synchronization between the production server and the backup server must be ensured. So what technology does the SQL Server database use to synchronize data between the production Server and the backup Server? Simply put, data is synchronized through the replication of the transaction log. Specifically, the SQL Server database provides two solutions: data mirroring and log transmission. Both solutions are implemented based on transaction log replication.

In the log transfer scheme, the production server sends the activity transaction logs of the production database to one or more target servers. Each secondary server restores the log to its local secondary database to achieve data consistency between the backup server and the production server. Use log transmission, you can automatically back up transaction logs in the "primary database" on the "primary server" instance to one or more "secondary databases" on the separate "secondary server" instance ". Transaction Log backup is applied to each secondary database. The optional third server instance (called the "monitoring shi server") records the history and status of backup and restore operations, and can trigger an alarm when these operations cannot be performed as planned. The master Server in the log shipper configuration serves as the SQL Server database engine instance of the production Server. The primary database is the database on the primary server that you want to back up to other servers. All log shipper configuration management performed through the database is performed in the master database. In addition, it should be noted that if the log transfer solution is used, there are limits on the working mode of the production server. Production Databases must use the full recovery mode or large-capacity log recovery mode. If you switch the database to the simple recovery mode, log transmission stops.

A backup server can contain backup copies of databases on multiple production servers. For example, a group company may have three database servers, each of which runs a key database system. In this case, you can use only one secondary server, instead of three independent secondary servers. Backup on the three primary systems can be loaded into the backup system, reducing the required amount of resources and saving money, or the workload of the database administrator.

In addition, you can use the database image solution to solve the problem of data synchronization between the production server and the backup server. Each update of the production database is immediately regenerated in an independent and complete backup database. The main server instance immediately sends each log record to the backup server instance. the backup server instance applies the incoming log record to the backup database and rolls it forward. "Database image" is the preferred software solution for improving database availability. Images are implemented based on each database and only apply to databases that use the full recovery mode. The simple recovery mode and the large-capacity log recovery mode do not support database images. Therefore, all large-capacity operations are fully logged. Database images can use any supported database compatibility level. In "database mirroring mode", the master server and the backup server serve as partners for communication and collaboration. The two partners play complementary roles in the session: the main role (production server) and the image role (Backup Server ). At any given time, one partner plays the production server role, and the other plays the backup server role. If the production server role fails, the backup server role immediately replaces the faulty production server role and changes to the production server role. To achieve high Database Availability.

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: solving data consistency problems

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 process, if the application issues a ROLLBACK statement or the database engine detects an error, it will use the log to roll back the modifications made by the unfinished transactions. That is to say, when the second operation fails, the application must issue a ROLLBACK statement to use the transaction log ROLLBACK function to restore the first operation. 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-in-time recovery

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.

1. How to perform MySQL database table Fault Detection
2. Troubleshooting of SQL Server nonexistent or access denied
3. Create an SQL Server 2000 failover Cluster