Troubleshoot 4 common SQL Server failures using the transaction log

When a system fails, it can be used to recover data to resolve database failures as long as there is a data log. As a SQL Server database administrator, it is important to understand the role of data log files and how to use them to troubleshoot common failures in some databases. Since the transaction log is so important, what can he do with it? Kothe, the author here to tell you how the transaction log can be used to solve what the fault.

Failure one: Loss caused by unexpected shutdown of server

As the saying goes, days and accidents. The database server will lose some data if the server restarts because of a sudden power outage or some other reason. This is mainly because the data in the database is changed, and the data is not written to the hard disk at the first time. In order to improve the efficiency of the database, it is often written to the data cache first, and the change is written to the transaction log. Wait until a certain situation the database system will write data to the hard disk file.

At this point, if the database server system suddenly fails, the database system may not have the modified data in the cache to write to the hard disk, that is, the data file has not completed the transaction changes. If this is the case, the modified data cannot be recovered if there is no transaction log or corruption of the transaction log when the instance of SQL Server is started. However, if the transaction log is available, the system throws each database to perform a recovery operation when the instance is started. Every modification that is recorded in the roll forward date to the data file that may not have been written to. Each outstanding transaction found in the transaction log is rolled back to ensure the integrity of the database data.

So when the database server fails unexpectedly, it is best for the database administrator to confirm that the transaction log is available. If the transaction log is corrupted, then you need to recover the transaction log before restarting the database instance. Otherwise, the database instance is not able to recover the data properly at reboot. This must be noted in the case of problems with server burst distribution. Otherwise, the integrity of the database data is likely to be compromised.

Failure two: Solve the data synchronization problem of backup Database

Sometimes, for database high availability purposes, you need to deploy a database server outside of the production server. This standby server can be enabled immediately when a production server fails to become available. It is therefore necessary to ensure the synchronization of data between the production server and the standby server. So what technology does the SQL Server database use to synchronize data between this production server and the backup server? To put it simply, the transaction log is replicated to achieve data synchronization. Specifically, the SQL Server database provides two solutions, data mirroring and log shipping respectively. These two scenarios are implemented on the basis of transaction log replication.

In a log shipping scenario, the production server sends the active transaction log of the production database to one or more target servers. Each secondary server restores the log to its local secondary database, which enables consistency of data between the standby server and the production server. With log shipping, you can automatically send transaction log backups from the primary database on the primary server instance to one or more secondary databases on a separate secondary server instance. Transaction log backups are applied to each secondary database, respectively. An optional third server instance, called a Shi server, records the history and status of backup and restore operations, and can also raise alerts when these operations cannot be performed as scheduled. The primary server in the log shipping configuration is an instance of the SQL Server database engine that is the production server. The primary database is the database on the primary server that you want to back up to another server. All log shipping configuration management through the database is performed in the primary database. Also note that if a log shipping scheme is used, there is a limit to the working mode of the production server. The production database must use the full recovery model or the bulk-logged recovery model. Switching the database to the simple recovery model causes log shipping to stop working.

A standby server can contain backup copies of databases from several different production servers. For example, a group may have three database servers, each running a critical database system. In this case, you can use only one secondary server without having to use three separate secondary servers. Backups on three primary systems can be loaded into the backup system, reducing the amount of resources required and saving money, or the workload of the database administrator.

You can also troubleshoot data synchronization between production servers and standby servers through a database mirroring scenario. Each update of the production database is immediately regenerated in a separate, full backup database. The principal server instance immediately sends each log record to the mirror server instance, and the mirror server instance applies the incoming log records to the mirrored database, which continues rolling forward. Database mirroring is the preferred software solution for improving database availability. Mirroring is implemented on a per-database basis and applies only to databases that use the full recovery model. Database mirroring is not supported in the simple recovery model and the bulk-logged recovery model. Therefore, all bulk operations are always logged in full. Database mirroring can use any supported database compatibility level. In database mirroring mode, the principal server and the mirror server communicate and collaborate as partners. Two partners play a complementary role in the session: the principal role (production server) and the mirroring role (backup server). At any given time, a partner acts as a production server role and another partner plays the standby server role. If the production server role fails, the backup server role will immediately replace the failed production server role into the production server role. To achieve high availability of the database.

The database mirroring scheme has two mirrored operating modes. One is "High security mode", which supports synchronization operations. In high security mode, when the session starts, the mirror server synchronizes the mirror database with the principal database as soon as the database is synchronized, and the transaction is committed on both partners, which prolongs transaction latency. The second operating mode, high performance mode, is that the main difference from the first pattern is the asynchronous operation. The mirror server attempts to synchronize with the log records sent by the principal server. The mirrored database may lag slightly behind the principal database. However, the time interval between databases is usually small. However, the time interval increases if the principal server's workload is too high or the mirror server system is overloaded. In high-performance mode, the principal server sends a confirmation message to the client immediately after it sends a log record to the mirror server. It does not wait for the mirror server to confirm. This means that transactions do not need to wait for the mirror server to write the log to disk to commit. This asynchronous operation allows the principal server to run with minimal transaction latency, but some data may be lost. What kind of pattern is used, the database administrator should be determined according to the attitude and workload of the enterprise treating data loss.

It is visible that the data synchronization solution between the available backup server and the production server is based on the transaction log.