Read-Only Databases

You can gain significant performance gains if you have the luxury of placing your database in read-only mode. Since you don't risk reading 'dirty' data, SQL Server can skip much of the locking it would normally perform. This is handy in a Decision Support System (DSS) where you may only be updating the data nightly from a large extract, and then running reports throughout the day on the system. In this scenario, you could take the database out of read-only mode before the data load and then place it back in the mode after the load occurs.

Tip 

Consider placing aged data into a read-only database. For example, place data for invoices over a year old in a separate database, because the data is not updated. You can still merge that data with current data by using the UNION ALL clause.

To place your database in read-only mode, use the ALTER DATABASE command as follows:

ALTER Database pubs  SET READ_ONLY

Anyone who attempts to update data thereafter receives the following error:

Server: Msg 3906, Level 16, State 1, Line 1 Could not run BEGIN TRANSACTION in database 'pubs' because the database  is read-only.

The error is also returned to users who attempt to modify the schema while the database is in read-only mode. You can take the database out of this mode by using the READ_WRITE keyword as shown in the following syntax:

ALTER Database pubs SET READ_WRITE

Any users who attempt to connect while the database is being switched between modes receive the following error:

Server: Msg 952, Level 16, State 1, Line 1 Database 'pubs' is in transition. Try the statement later. 

start sidebar
In the Trenches

When taking a database in and out of read-only mode, you may experience the following error:

Server: Msg 5061, Level 16, State 1, Line 1 ALTER DATABASE failed because a lock could not be placed on database  'pubs'. Try again later. Server: Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.

Usually, the error is generated by one of the following causes:

  • Another process may be trying to place the database in read-only mode at the same time.

  • Someone is viewing the database in Enterprise Manager or in the Object Browser.

When you're going to place a database in read-only mode, consider using the script I covered in the 'Disconnecting Users' section of this chapter. This avoids problems by killing all the connections before you change the database status.

end sidebar




SQL Server 2000 for Experienced DBAs
ppk on JavaScript, 1/e
ISBN: 72227885
EAN: 2147483647
Year: 2005
Pages: 126

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net