Configuring Read-Only Content Databases

When you dig into SharePoint 2010 Upgrade you’ll find out about this new feature called “Read-Only Content databases.”  This feature actually came in SharePoint 2007 SP2.  The feature is something you should be aware of for scenarios not only relating to upgrade, while upgrade is the most common scenario.

We use to set our SQL databases read only when doing database attach between 2003 to 2007 anyway, so what has really changed?

There are a few ways to set your SharePoint content read only.

1. Read or Read/Write Lock your site collection from STSADM

2. Read or Read/Write Lock your site from Central Admin UI

3. Configure the SQL Content database to Read only

Configuring SQL to set the database Read-Only:

From SQL Management Studio, you right click on the content database you want to set read only choose properties, then click options in the tree menu, then scroll down and in the state box change read-only to true.  Seems easy enough…

You’ll then see it grayed out in your UI

If you were to configure a SharePoint Content database in SQL to read only in 2007 before SP2 you’d find that you’d get ugly errors that would say contact your administrator if people would upload a document or try to edit a list.  Imagine someone using a form and spending a long time working on a blog or something complex.  They could be pretty upset and have no idea what’s going on.

Now post SharePoint 2007 SP2 including WSS 3.0 SP2 you’ll find that when you set your databases to read only you now get three things.  

1. SharePoint will promote the SQL read only lock to a read lock for the Site Collection. 

2. You get a both have a simplified UI to minimize the write options and to minimize confusion.

Before Read-only

After Read-only

3. You’ll get friendly error messages that the Server is in Maintenance mode where the UI could not be UI trimmed as the content in your database remains static.

 

On the SharePoint 2010 side you’ll now actually see the status of the database as read only or not.  You can tell just by how much you see it in the UI that it will be much more common for people to use:

 

When would you use read only databases?

• When combined with Database attach for SharePoint 2010 Upgrade
• Using it on SharePoint 2010 Patching to upgrade the binaries only
• For SharePoint 2007 high availability migrations or consolidations
• Other Hybrid SharePoint 2010 Upgrade scenarios

Other Upgrade Considerations:

Note that PreUpgradeCheck will also check the status for read-only databases.  You should NOT use read only content databases with In Place upgrade. 

With a hybrid upgrade approach if you want to just upgrade the binaries and services you should simply detach the databases first.

In the alternate hybrid approach you would configure the databases as read only on your source, while the copy of the farm has read write databases while it’s being upgraded.

Also note, with database attach any database you attach should be read write while it’s being upgraded.  The original source which can be a copy could be configured as read-only.

Additional references:

There’s a good summary in “Run a farm that uses read only databases” on TechNet of what is trimmed in the UI and where you get access denied in the farm.  Also good recommendations on checking your ULS logs for failing timer jobs and how to disable timer jobs that are set to write to the content database that might be failing.

There’s a KB article: http://technet.microsoft.com/en-us/library/dd793608.aspx that goes into detail around the issues introduced with Read only content databases in  farms prior to SP2