Phidiax Tech Blog

The question of database compression with BizTalk comes up quite frequently.This article identifies the areas in which database compression is suitable for BizTalk databases and which are not supported.

In general a database can be compressed only when it is configured for Read Only access.

Databases that can be used in Read Only mode can be compressed:

The following database compression methods are not supported:

Compressed NTFS Volumes are not supported when databases are writable:

Compressed FAT and NTFS Volumes are not supported because compressed volumes interfere with transactions support: SQL Server databases are not supported on compressed volumes

Although it is physically possible to add SQL Server databases on compressed volumes, Microsoft does not recommend it, nor do they support it. The underlying reasons for this include the following:

Performance

Databases on compressed volumes may cause significant performance overhead. The amount will vary, depending on the volume of I/O and on the ratio of reads to writes. However, over 500 percent degradation was observed under some conditions.

Database recovery

Reliable transactional recovery of the database requires sector-aligned writes, and compressed volumes do not support this scenario. A second issue concerns internal recovery space management. SQL Server internally reserves preallocated space in database files for rollbacks. It is possible on compressed volumes to receive an "Out of Space" error on preallocated files, and this interferes with successful recovery

The article, Microsoft SQL Server I/O subsystem requirements for the tempdb database, consists of the explanation of why NTFS compression can interfere with the reliability of the transactional nature of database file writes and why they can lead to database corruption.

It is clear why NTFS compression is not a good idea for databases, as well as the negative impact to the system.

The article is targeted for the tempdb, but in the article is explains why it is relevant to all databases, not just the tempdb.

Transactional sector rewrite operations
The storage location for the tempdb database must act in strict accordance with established disk drive protocols. In all ways, the device on which the tempdb database is stored must appear and act as a physical disk providing read after write capabilities. Transaction sector rewrite operations may be an additional requirement of specific implementations. For example, SQL Server does not support database modifications by using NTFS file system compression because NTFS compression can rewrite sectors of the log that have already been written and considered hardened. 

A failure during this type of rewrite can cause the database to be unusable, damaging data that SQL Server already considered secure. Transactional sector rewrite operations are pertinent to all SQL Server databases that include the tempdb database.

A growing variety of extended storage technologies use devices and utilities that can rewrite data that SQL Server considers secure. For example, some of the emerging technologies perform in-memory caching or data compression. In order to avoid severe database damage, any sector rewrite must have full transactional support in such a way that if a failure occurs, the data is rolled back to the previous sector images. This guarantees that SQL Server is never exposed to an unexpected interruption or data damage condition.