I will start by referencing an important MSDN article I found:
For those of you who don't already know, a multibase differential backup is a single differential database backup whose base is multiple file or filegroup backups.
excerpt from the article:
When a Multibase Differential Might Be Useful
In rare situations, a multibase differential may make sense. For example, a database administrator might use a full database differential with a full set of file backups. A whole set of file backups replaces a single database backup, creating a multibase differential. Depending on database usage, the differential might be fairly small. In any case, restoring a file follows the same procedure: Restore the file backup first and then the full database differential.
In this case, a multibase differential is easier to manage than several separate differential file backups; and the backup and restore steps remain simple. There is no confusion over which backups to restore, because all files are treated the same.
If you use multiple bases for differential backups, keep the multibase strategy as simple as possible. Avoid strategies that use complex multibase differentials, especially ones that require that some files or filegroups be treated differently from other files or filegroups at restore time. For example, avoid intermixing full database backups, file backups, and database differentials. Also, avoid grouping files differently in the full database backup and the differential backups, unless the groupings are very simple and predictable, as in the previous example.
In our case, we have a 3 TB database with 40 filegroups. We create a separate filegroup for each day. The large majority of writes is always on the current day with some occasional edits going back no more than a week. Therefore, we always have 33 of 40 filegoups that are essentially (but not technically) read-only.
Given that architecture, I have 3 questions:
Answer by Rob Farley ·
Sure - I think it's a good candidate too.
You should definitely mark filegroups as read-only if they really are. It means that querying on them won't require locks, so things will just work faster.
Using compression is an interesting. Data compression in SQL Server only compresses the database when it tries to write more information to the page, so you'd have to rebuild all the data in the filegroup to see the benefits. The answer to whether this is worthwhile is "It depends". You'd have to try to weigh up the benefit of having the data squeezed into less space. It may mean more data can fit into the server's RAM, but if you'd rather your RAM was full of current, writable data, then it might not impact you so much.