As mentioned in my post on VLDBs, one of the key pieces of a high performing solution is the use of Read-only Filegroups. If a significant part of your data is not changing, then why not tell SQL Server about it? This will speed up your daily backup processing and reduce the overhead of reading data. It will also prevent accidental updates. Let’s take a look…
The concept of Filegroups was introduced in SQL Server 7.0 for performance and administrative reasons when using large databases. In SQL Server 2005, many new features used the Filegroup object including Data Partitioning, Online Restores, Piecemeal Restores and Partial Backups. All these features help with VLDBs but it’s the latter feature that we are going to discuss here. Say you have a large database where a large percentage of the data is historical and therefore not changing. It would be wasteful to have to backup the entire database every night. Wasteful in time, wasteful in storage. In SQL Server you can store read-only tables in a particular Filegroup and mark the Filegroup as Read-only.
When you use the BACKUP DATABASE statement in SQL Server 2005 (and beyond) you can use the READ_WRITE_FILEGROUPS option and the Read-only Filegroups will not be backed up. Your backup statement will obviously run a great deal faster and use a great deal less space. This assumes that the entire database has been backed up already so we have a least one backup of the read-only data to fall back on. When a Filegroup is marked as Read-only, SQL Server will not bother with Page or Row locks on the tables or indexes contained in them. This reduces SQL Server overhead and improves performance. Since the data is not changing, index fragmentation does not occur so maintenance, such as rebuilding or reorganizing, is unnecessary. That saves time and effort also. Also, now in SQL Server 2008, you can mark a Filegroup as Read-only without having exclusive access to the entire database.
In the case of restoring an updateable filegroup, you will need to restore all the transaction log backups made since the last partial backup including the most current “tail” log backup (assuming you are not using the simple recovery model). This will ensure that all the most recent updates are recovered and that the multiple Filegroups are all in sync.
Of course, all backup strategies should be fully tested regularly by running fire-drill restore cycles. This is because of Flanigan’s Law. Haven’t heard of Flanigan’s Law?
“Murphy was an optimist…”.
I think Flanigan was a DBA…
Cheers
Brian
