Share via

  • Article

Read-Only Filegroups and Compression

Filegroups can be marked as read-only. Any existing filegroup, except the primary filegroup, can be marked as read-only. A filegroup marked read-only cannot be modified in any way. Read-only filegroups can be compressed.

For tables that must not be modified, such as historical data, put them on filegroups and then mark the filegroup as read-only. This prevents accidental updates.

Using NTFS Compression with Read-Only User-defined Filegroups and Read-Only Databases

SQL Server supports NTFS compression of read-only user-defined filegroups and read-only databases. You should consider compressing read-only data in the following situations:

  • You have a large volume of static or historical data that must be available for limited read-only access.

  • You have limited disk space.

Administrative Considerations

  • Only Windows NTFS compression is supported. For more information, see the Windows NTFS file system documentation.

  • Data compression is supported for user data stored in read-only user-defined (.NDF) filegroups. Primary filegroups and transactions logs cannot be compressed unless the database itself is read-only.

  • To compress the user-defined filegroup files, the database must be offline. For more information, see ALTER DATABASE (Transact-SQL).

  • To compress a file, or verify the compression status of a file, use Windows COMPACT command or Windows Explorer. For more information, see the Windows documentation.

  • SQL Server system databases, such as master, model, msdb, resource and tempdb, cannot be compressed.

  • SQL Server backup and restore operations support compressed filegroups. No modifications to your backup and restore implementation are required. The operating system compression is transparent to back up and restore operations.

  • ALL SELECT statements and read-only operations are fully supported. The execution time of these statements will be slower than equivalent statements on noncompressed filegroups.

  • To modify the compressed data, the files must be uncompressed and the filegroup set to read/write.

  • Both the primary and user-defined filegroups of a read-only database can be compressed. The associated files must be compressed by using NTFS compression.

  • Attach database operations are supported on compressed filegroups. The filegroup must be set to read-only before detaching the database. Before attaching the database, the files must be compressed.

Secondary Filegroup Compression Example

In this example, sales order transactions are moved from a production database to SalesArchive, an archive database stored on a read-only, compressed filegroup. The data is searched intermittently for sales analysis and forecasting, but is never modified.

  1. In preparation for moving data from the production database, the database administrator (DBA) creates a read/write filegroup Sales01 on SalesArchive. Sales01 is located on an NTFS directory.

  2. The DBA copies the sales order transactions from the production database to archive the tables that were created on the Sales01 filegroup.

  3. After all users have been drained from the SalesArchive database, the DBA alters the database and sets the Sales01 filegroup to read-only and then takes the database offline.

  4. Using the Windows COMPACT command, the DBA compresses the Sales01.ndf file. For more information, see the Windows NTFS documentation.

  5. The DBA brings the SalesArchive database online. The data is now compressed and available for read-only access.