Transactional integrity and SQL Server recovery

caching mechanisms can’t guarantee writes across a power cycle or similar failure point. They

only guarantee the completion of the sector write operations. As the storage devices continue to

grow in size, the caches become larger, and they can expose larger amounts of data during a

failure.

For more information on FUA support by Linux distribution and its effect on SQL Server, see

Server On Linux: Forced Unit Access (FUA) Internals.

Transactional integrity is one of the fundamental concepts of a relational database system.

Transactions are atomic units of work that are either fully applied or fully rolled back. The SQL

Server write-ahead transaction log plays a vital role in implementing transactional integrity.

Any relational database system must also handle a concept closely related to transactional

integrity: recovery from unplanned system failure. Several nonideal, real-world effects can cause

this failure. On many database management systems, system failure can result in a lengthy,

human-directed manual recovery process.

In contrast, the SQL Server recovery mechanism is automatic and operates without human

intervention. For example, SQL Server could be supporting a mission-critical production

application, and experience a system failure due to a momentary power fluctuation. Upon

restoration of power, the server hardware restarts, networking software loads and initializes, and

restarts. As SQL Server initializes, it automatically runs its recovery process based on

data in the transaction log. This entire process occurs without human intervention. When client

workstations restart, users find all of their data present, up to the last transaction they entered.

Automatic recovery and transactional integrity work together to reduce the time and effort

required to restore operations after a failure. If a write caching controller isn’t properly designed

for use in a data-critical transactional DBMS environment, it can compromise the ability of SQL

Server to recover, potentially corrupting the database. This problem can occur if the controller

intercepts SQL Server transaction log writes and buffers them in a hardware cache on the

controller board, but doesn’t preserve these written pages during a system failure.

２

Warning

If cached writes are discarded due to a system reset, database corruption can occur even if a

UPS is present. Always ensure write caches are backed by battery or equivalent technology

to guarantee data persistence.