SQL Server and Read-Only Databases

By Chris Miller on 5 July 2000 | Tags: Administration

This article discusses creating read-only databases to improve performance throughput. This can be especially handy in a web environment where many users want to view information.

Sometimes there are good reasons to have read-only databases. For internet applications, it's pretty common to have a consolidated database that provides information for users. The traditional internet model is a model which provides a lot of data to users who normally only send a small amount of data to the web. It's pretty common to have a lot of data which is read only, or at least that can be segregated into a read-only section (archive) and read-write section.

If you've got a read only database, mark it read-only in SQL Server. If you do that, then SQL Server will make some assumptions about the database, like that the database doesn't have anybody making changes on it, and SQL Server will disable locking in the database. That means less overhead, and it should speed things up a bit.

Also, if you go one step farther and actually make the database files read-only you can back up the database without shutting SQL Server down while the files are in use. Just directly back up the MDF and LDF files, and they can be restored and reattached. To mark the files read only, detach the database using sp_detach_db, use the "attrib" command to mark the files read only, and then use sp_attach_db to reattach the database.

There are a few caveats here. If you mark a database read only, it is very really and truly read only. That means that any auto-shrink options are not going to happen, and neither are automatic statistics. DBCC commands to fix database corruption will not work. As a result, you'll need to make sure that the database is already shrunk (dbcc shrinkdatabase) and that the database has all of the statistics and indexes it needs to run efficiently.

In addition, if you elect to copy the database to another path and reattach it, you'll need to make sure that the files are not marked read only or they won't attach. Why? Well, the path for the LDF file is stored inside the MDF file, so if the path needs to change, for example from one drive letter to another, then the MDF file will need to change in order to store the new path, and the LDF file will need to change in order to add the transaction log record for the change to the MDF file (funny how these things work).