Solve four common failures of SQL Server through transaction logs (1)

Like Oracle databases, SQL
The Server database also has transaction logs. Transaction logs are mainly used to record all transactions and the changes made to the database by each transaction. Transaction logs are one of the most important data files in the database.

When the system fails, data logs can be used to restore data to solve database faults. As SQL
Server database administrators are very 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: loss 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
Server instance, if there is no transaction log or the transaction log is damaged, the modified data cannot be restored. 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 data synchronization problem of 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. Then SQL
What technology does the 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, SQL
The 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 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 is used as the production server
SQL Server
Database Engine instance. 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.