Improvement
Assume that you are using SQL Server 2017 on Linux. In certain scenarios, SQL Server may experience data loss on systems that use volatile caches. The loss occurs because of unforeseen circumstances like power failure before the cached data is written to a stable media. To prevent such scenarios, a forced flush mechanism is introduced in Cumulative Update 6 (CU6) for SQL Server 2017 on Linux. If your storage subsystem cannot guarantee durable writes across power loss, we recommend that you apply CU6 for SQL Server 2017 on Linux. This update enables the following:
-
The forced flush behavior during database write activity like checkpoint and transaction log writes.
-
The configuration setting writethrough=1 and alternatewritethrough=1 options by default. These default settings allow SQL Server to make sure that writes are durably flushed to block devices. Alternate writethrough is an option to optimize durability flush requests to the file system. For more information about the two settings, see the following table:
|
Name |
Default |
Description |
|
writethrough |
1 |
Valid values are 0 and 1. 1 = Translates FILE_FLAG_WRITE_THROUGH requests into O_DSYNC opens. 0 = Prevents translation of FILE_FLAG_WRITE_THROUGH requests to O_DSYNC opens. |
|
alternatewritethough |
1 |
Valid values are 0 and 1. 1 = Enables optimized flushing by the Host Extension for FILE_FLAG_WRITE_THROUGH requests. Write(s) on files optimize(s) calls to fdatasync for the block device. 0 = Disables the alternate flush optimization. The file is opened by using O_DSYNC and the underlying file system performs the necessary write, flush requests. Note The alternatewritethrough setting only apply when writethrough=1. |
More Information
For more information, see Performance best practices and configuration guidelines for SQL Server 2017 on Linux for handling high frequency write workloads and database file placement recommendations.
SQL Server installations running on storage systems that guarantee writes are O_DIRECT safe can enable trace flag (TF) 3979 to disable the forced flush behavior and set the alternatewritethrough and writethrough options in mssql.conf to ZERO. This returns SQL Server 2017 to pre-CU6 behavior.
Note A storage system can make sure that any cached or staged writes are considered safe and durable by guaranteeing that writes issued to the device are kept on a medium that will persist across system crashes, interface resets and power failures, and the medium itself is hardware redundant.
Here are more details about SQL Server behavior for file I/O with these changes:
-
Database (.mdf) and transaction log (.ldf) files don't use writethrough and alternatewritethrough by default in CU6 as they use the forced flush behavior. TF 3979 disables the use of the forced flush behavior for database and transaction log files and will use the writethrough and alternatewritethrough logic.
-
Other files that are opened by using FILE_FLAG_WRITE_THROUGH in SQL Server, such as database snapshots, internal snapshots for database consistency checks (CHECKDB), profiler trace files, and extended event trace files, will use the writethrough and alternatewritethrough optimizations.
Resolution
This update is included in the following cumulative update for SQL Server:
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:
References
Learn about the terminologythat Microsoft uses to describe software updates.
