Should static database data be in its own Filegroup?

Question

Hi folks,

I'm creating a new DB and have a bunch of static data that won't change. If it does, it will be a manual process AND it will happen very rarely.

This data is a mix of varchars and Geographies.

I'm guessing it could be around 100K or so in total, over 4 or so tables.

Questions

1. Should I put these on a READ ONLY filegroup
2. Can I create the tables in the designer and define the filegroup during creation? Or is it only possible via a script?
3. Once the data is in the table (on a read only filegroup), can I change it later? Is it really hard to do that?

thanks.

Answer 1

It is unlikely to hurt to put the data in to a read only space but I am unsure you will gain significantly. A read-only file group (or tablespace in Oracle) can give you 2 advantages; less to back-up each time a full backup is taken and a higher level of security over the data (e.g. it cannot be changed by a bug, accessing the DB via another tool, etc). The backup advantage is most true with larger DBs where backup windows are tight so putting a small amount of effort into excluding file groups is valuable. The security one depends on the nature of the site, data, etc. (if you do exclude the read-only space from regular backups make sure you get a copy on any retained backup tapes. I tend to backup up read-only spaces once a month.)

I am not familiar with designer.

Changing to and from read only is not onerous.

Answer 2

It is worth it for VLDB (very large databases) for assorted reasons. For 100,000 rows or 100 KB, I wouldn't bother.

This SQL Server support engineering team article discusses one of the associated "urban legends".

There is another one (can't find it) where you need 300 GB - 1B of data before you should consider multiple files/filegroups.

But, to answer specifically

1. Personal choice (there is no hard and fast rule)
2. Yes (edit:) In SSMS 2005, design mode, go to Indexes/Key, "data space specfication". The data lives where the clustered index is. WIthout a clustered index, then you can only do it via CREATE TABLE (..) ON filegroup
3. Yes, but You'll have to ALTER DATABASE myDB MODIFY FILEGROUP foo READ_WRITE with the database in single user exclusive mode

Answer 3

I think anything you read here is likely to be speculation, unless you have any evidence that it's been actually tried and recommended - to me it looks like a novel but unlikely idea. Do you have some reason to suspect that conventional practices will be unsatisfactory? It should be fairly easy to just try it and find out. Post your results if you get a chance.