Run a farm that uses read-only databases (SharePoint Server 2010)

 

Applies to: SharePoint Server 2010, SharePoint Foundation 2010

This article describes how to run a Microsoft SharePoint Server 2010 farm in which some or all databases are set to be read-only (a read-only farm).

A farm is considered to be read-only if any of the following are true:

  • All content databases are set to read-only.

  • Service application databases are set to read-only.

    Note

    The Search service application does not function when its databases are set to read-only.

The functionality and user experience in a read-only farm depends on which databases are set to read-only.

A farm that uses read-only content and service application databases is likely to be part of a disaster recovery environment or a highly available maintenance, update, or upgrade environment.

Procedures in this task:

  • Prepare users for the read-only experience

  • Set content databases to be read-only

  • Set service application databases to be read-only

Prepare users for the read-only experience

If you plan to provide users with access to a read-only site or farm, you should set expectations for what they will be able to do on the site and how the user interface (UI) will differ.

Sites that use read-only content databases

The user experience of a site that uses a content database that is set to read-only is characterized by the following:

  • Common tasks that do not require writing to the content database are fully available.

  • Most of the common tasks that require writing to the content database are not available, either because they have been disabled in the UI, or because the user is no longer allowed to apply changes.

  • Some common tasks that require writing to the content database appear to be available, but return errors.

For detailed information about the user experience on a read-only site, see User experience on read-only sites (SharePoint Server 2010).

Farms that use read-only service application databases

The user experience of a farm that uses service application databases that are set to read-only is characterized by the following:

  • Common tasks that do not require writing to the service databases are fully available.

  • All common tasks that require writing to the service databases appear to be available, but return errors.

Set content databases to be read-only

Before you set content databases to be read-only, you may need to determine which content database is associated with a particular site collection.

To determine which content database is associated with a site collection

  1. Verify that you meet the following minimum requirements: See Add-SPShellAdmin.

  2. On the Start menu, click All Programs.

  3. Click Microsoft SharePoint 2010 Products.

  4. Click SharePoint 2010 Management Shell.

  5. At the Windows PowerShell command prompt (PS C:\>), type the following command, and then press ENTER:

    Get-SPContentDatabase -Site <Site URL>

    -Site specifies the site collection for which you want to know the associated content database.

    The command returns the content database associated with the site.

For more information, see Get-SPContentDatabase.

Note

We recommend that you use Windows PowerShell when performing command-line administrative tasks. The Stsadm command-line tool has been deprecated, but is included to support compatibility with previous product versions.

You can use the following procedure to set read/write content databases to be read-only by using SQL Server Management Studio. You can also use the Transact-SQL ALTER DATABASE statement to set content databases to be read-only. For more information, see ALTER DATABASE (Transact-SQL) (https://go.microsoft.com/fwlink/p/?LinkId=148619).

Important

Do not perform this procedure on databases in a failover environment that have been log-shipped or mirrored.

To set content databases to be read-only

  1. Verify that you have the following administrative credentials: You must be a member of the db_owner fixed database role in each database.

  2. Open SQL Server Management Studio.

  3. Right-click the content database that you want to change to read-only, and then click Properties.

  4. Select the Options page, and, in the Other options list, scroll to the State section.

  5. In the Database Read-Only row, click the arrow next to False, select True, and then click OK.

  6. Repeat for all other content databases.

    Note

    When a database is set to read-only, all connections except the one that is setting the read-only flag are stopped. After the read-only flag is set, other connections are enabled.

The site collection that is associated with a read-only content database is automatically set to be read-only if the locking status of the site collection was previously None, No Additions, or Read-Only. If the locking status of the site collection was previously No Access, it remains No Access when the database locking status is changed.

Set service application databases to be read-only

It is possible to set any service application database to be read-only. However, some service applications do not function when their databases are set to read-only, including those associated with Search and Project Server.

To set service application databases to be read-only

  1. Verify that you have the following administrative credentials: You must be a member of the db_owner fixed database role in each database.

  2. Open SQL Server Management Studio.

  3. Right-click the database that you want to change to read-only, and then click Properties.

  4. Select the Options page, and, in the Other options list, scroll to the State section.

  5. In the Database Read-Only row, click the arrow next to False, select True, and then click OK.

  6. Repeat for other service application databases as appropriate.

    Note

    When a database is set to read-only, all connections except the one that is setting the read-only flag are stopped. After the read-only flag is set, other connections are enabled.