11

Can someone cite me a real world scenario on when changing multiple filegroups to read-only is a good option and when to use them? What benefits are there if you set it to read-only?

On a database with multiple filegroups, do you have to do backups for the whole database and also backup each of the files of that filegroup? Can you also give me an example of when filegroup backups will be used? I don't see why it would be beneficial to backup a filegroup when you can just backup the whole database. Hoping I could get a real world experience where this filegroup backup would be ideal

13

Your question is a bit wide-ranging, however some of the main points include the following.

Multiple file groups, in general:

  • can be a performance improvement, assuming they are physically isolated. Typically, you might create one filegroup for clustered indexes, one filegroup for non-clustered indexes, and one filegroup for heaps. Or you might create multiple filegroups in a partitioning situation.

  • can improve recoverability by enabling piece-meal restore. Piece-meal restore can be used to bring critical production data online quickly while allowing the restore of archival data to not impact online activities. Be aware online piece-meal restore is an "Enterprise-only" option. I've written a blog post showing how to do piece-meal restores over at SQLServerScience.com

  • do not need to be backed-up individually, however they can be backed up individually, which might be necessary for VLDBs. Note, when backing up individual filegroups you must also backup the primary filegroup.

Read-only file groups:

  • Can be used to reduce contention for reporting-only data.

  • Can be used, obviously, to prevent modifications to data.

  • Do not need to have the recovery process ran when the database is brought online. This can substantially improve recovery time for VLDBs.

  • Also, while read-write filegroups cannot be used with NTFS filesystem-based compression, read-only filegroups can. The implication here is that for a very limited set of systems you may be able to see a slight performance improvement for VLDB data that resides on slower disks by compressing the readonly filegroup files on disk. This would of course need to be evaluated on a case-by-case basis vs the CPU overhead necessary to decompress the NTFS clusters as they are read off the disk.

Myths surrounding read-only filegroups:

  • If you have a database with multiple filegroups and you dont utilize filegroup backups, only full and T-log backups, what will be the disadvantage when it comes to restoring this database? – rk13 Apr 13 '16 at 22:22
  • There is no disadvantage to full backups with transaction log backups. You can do backups of single filegroups (as long as you also backup PRIMARY), but you don't need to. – Max Vernon Apr 14 '16 at 17:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.