Readonly Databases

I was thinking the other day of a useful enhancement to the CREATE/ALTER DATABASE statement, regarding readonly databases; which was to have an additional clause that would either remove the need for a log file or shrink the log file to a minimum size. I logged this as two connect issues here and here.

There are various reasons why you may want to make a database read only, such as if you want to keep a database for historical reasons but want to preserve the data as it was as at given point in time or if the database only contains reference data.

I worked on a system which had two distinct sets of reference data, which would be periodically updated by third parties. When I first encountered the system, this reference data was imported into the main database which meant that the reference data was only updated when the system had a new release (users were not given this facility). This meant that the reference data was always several version behind what the third party had released. Having a readonly database for these database freed them up to be released separately. It also speeds up the releases, as there is no loading of data, just a detach/attach and possibly some updates to the existing data (although that would be necessary regardless of the method used!).

Having a separate readonly database does have at least one draw back though. You can not define foreign key relationships between columns in two different databases; although you can simulate a foreign key by writing a check constraint that uses a function or by using triggers, but these options may not always perform as well. The nature of reference data (in that it is not usually entered as free text) may make it acceptable to defer your referential checks to a maintenance task, although this would be a business decision to use this method.

So why the need for the extra options when ALTERING/CREATING a database? When I re-visited the system before the go-live with the changes for the reference databases, they had a huge log file for the reference database created by the build process. If this had been shipped it would have made the process of creating/installing the reference database much longer than necessary and as the log file would never be used it would also be a waste of disc space.

Ideally I would like the option not to have log files with readonly databases, but if this is not an option then creating one of minimum size would help. Of course this could be abused and therefore restricting it to readonly database would hopefully reduce that risk.