Read-only File Groups Setup

Every file group except the primary file group can be tagged as read-only. If the entire database is read-only, the primary file group can be set as read-only. INSERT, UPDATE and DELETE statements executed against objects stored in read-only file groups will fail any way. Any statement attempting to create an object on the read-only file group will also fail. Note, however, that you can modify the structure of the existing objects residing on the read-only file group. For example, you can add or drop columns from an existing table.

SQL Server does not lock any resources while executing SELECT statements against a read-only file group. SELECT statements against objects in a read-only file group can be somewhat faster than the same statements executed against objects in a read-write file group.

You can tag a file group as read-only using ALTER DATABASE statement. Before you modify the file group you must ensure that no other users are connected to the database. The following statements place customers database in a single-user mode and mark FG2003H1 file group as read-only:

USE [master]  
GO  

ALTER DATABASE [customers] 
SET  SINGLE_USER WITH ROLLBACK IMMEDIATE  
GO  

ALTER DATABASE [customers] MODIFY FILEGROUP [FG2003H1] READONLY  
GO  

ALTER DATABASE [customers] SET  MULTI_USER
Go

If later, you need to make changes back for this file group:

ALTER DATABASE [customers] MODIFY FILEGROUP [FG2003H1] READWRITE

Read-only file groups can be compressed which can help you reserve more disk space for files containing write-intensive data. SQL Server supports only Windows NTFS compression. Using compression doesn’t require any changes to backup / restore operations performed against the file group. However, that SELECT statements going against compressed file groups will be slower than the same statements going against uncompressed files because SQL Server need more time to de-compress the data.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s