This article describes how to use Microsoft SQL Server 2005 or Microsoft SQL Server 2008 log shipping to create a disaster recovery farm in a geographically distributed data center for Microsoft Office SharePoint Server 2007 with Service Pack 2 (SP2). By using this configuration, you can provide a disaster recovery site that provides current search results when a failover occurs. The article assumes that you are familiar with the concepts and terms presented in Plan for availability (Office SharePoint Server).

It often takes many teams or roles in an organization to create and configure a secondary data center and farm. In order to configure and test the secondary environment, you need to confer with the administrators of the authentication providers, the SQL Server database administrators, and all affected SharePoint farm administrators. This article is intended primarily for SharePoint farm administrators to help them do the following:

Log shipping enables you to configure SQL Server to continually send transaction log backups on from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each secondary database individually. Continually backing up the transaction logs from a primary database and then copying and restoring them to a secondary database keeps the secondary database almost synchronized with the primary database. Log shipping can also include an optional third server instance, known as the monitor server, that records the history and status of backup and restore operations and raises alerts if these operations do not occur as scheduled.

Log shipping consists of three jobs. Each job performs one of the following operations:

Backs up the transaction log at the primary server instance

Copies the transaction log file to the secondary server instance

Restores the log backup on the secondary server instance

The following diagram describes log shipping.

For more information, see the SQL Server Books Online article Log shipping (http://go.microsoft.com/fwlink/?LinkId=151252).

SQL Server log shipping can be used to send content databases, including My Sites databases, and single sign-on (SSO) databases from one farm that is running Office SharePoint Server 2007 with SP2 to one or more geographically dispersed secondary farms.

Important:

Although you can configure log shipping on a version other than Office SharePoint Server 2007 with SP2, we recommend that you use Office SharePoint Server 2007 with SP2 because it provides the following benefits:

When a content database is put in read-only mode, the site collections associated with that database are also put in read-only mode, and the user interface is changed to remove activities that require database changes.

Search treats content databases that have been detached and reattached to the same Web application in Office SharePoint Server 2007 with SP2 as known data sources, and performs incremental crawls, instead of full crawls. This is important, because in log shipped environments we recommend that you frequently detach and reattach content databases on the secondary farm to update the configuration database on the secondary farm so that the configuration database can recognize new or removed site collections. The new capability to perform incremental crawls after reattaching a database significantly reduces crawl time, and increases search currency.

We assume that the main purpose of a secondary farm is primarily disaster recovery. However, if you create a secondary farm that is running Office SharePoint Server 2007 with SP2, you can expose sites on the log-shipped secondary farm to users. You can either distribute a hosts file that points to the sites on the secondary farm, or define a dedicated alternate access mapping for each Web application on the secondary farm that you want to expose with a secondary namespace; for example, http://secondary.contoso.com or http://readonly.contoso.com. The sites that you expose will not expose write functionality to users. This article assumes that you are running Office SharePoint Server 2007 with SP2. For more information, see Run a farm that uses read-only databases (Office SharePoint Server).

Note:

If you create a secondary farm that is not running Office SharePoint Server 2007 with SP2, we recommend that you not expose any sites to users. Log-shipped farms that do not have Office SharePoint Server 2007 with SP2 installed are read-only, but provide no clear warnings for users who attempt to write data to the site. For more information about the issues in using Office SharePoint Server with a read-only content database, see the Knowledge Base article: Using Microsoft Windows SharePoint Services with a content database that is configured as read-only in Microsoft SQL Server (http://go.microsoft.com/fwlink/?LinkID=117362).

The following diagram describes a scenario with two data centers and two farms configured to use log shipping. In this scenario, the disaster recovery data center hosts a read-only secondary farm.

There are two logical farms, one in each data center. Each farm is a separate installation, with separate configuration and Central Administration content databases, and separate Shared Services Providers (SSPs). Only the content databases and SSO database are log shipped from the primary data center to the secondary data center. SSP A provides search in the primary farm, and SSP B provides search in the secondary farm. A configuration database refresh script (C) is run on the secondary farm. As the diagram shows, it is important to coordinate the timing of three processes in the secondary environment so that they do not overlap:

Processing log shipped databases

Search crawling

Configuration database refresh script

This topology can be repeated across many data centers, if you configure SQL Server log shipping to one or more additional data centers.

This section describes limitations on the use of log shipping with Office SharePoint Server 2007 with SP2.

By default, the failover process for log shipping is manual. You can create scripts to automate failover.

In the event of an unplanned failover, some data loss is possible, depending on the frequency of log shipping and the time of failure. It is possible to lose data from the last log shipping interval before a failure.

The configuration database cannot be log shipped to another farm because it contains computer-specific information. You must maintain the same customizations and configuration settings on both farms manually.

The search database cannot be log shipped successfully to the secondary farm because the search database, index file, and SSP database must be synchronized. To ensure search availability on a failover farm with log-shipped databases, you can use either of the following solutions:

Configure and run an SSP that is configured to provide search within the failover farm. This solution can provide search availability immediately after the secondary farm is up and running, and is suitable for large corpuses. This article describes how to configure and run a search SSP within the failover farm.

Restore the SSP from the primary farm to the failover farm by using SharePoint built-in backup and recovery. This solution provides search availability after the SSP has been restored, and search has crawled the content again. If the interval required for recovering the SSP is within the recovery time objective of the farm, you might want to consider this solution. This solution is not described in detail in this article. For more information about how to back up and restore the search SSP, see Back up and restore SSPs (Office SharePoint Server 2007).

We do not recommend log shipping databases other than content databases and SSO databases; for example, Microsoft Office Project Server 2007 databases. For databases not previously mentioned, we recommend backing up and restoring to the failover farm.

Updates for Office SharePoint Server must be applied to binaries in both the primary and secondary farms, but can be applied to the databases in the primary farm, and then log shipped to the secondary farm. This paper does not cover patching in detail, but an overview of the process is as follows:

Pause log shipping.

Detach the content databases from the Web application in the secondary farm through Central Administration or a script.

Update both farms, starting with the primary farm.

Important:

Make sure that the update process has fully completed and is successful on both the primary farm and the secondary farm. The databases on the secondary farm are not updated by the upgrade process — they are updated by log shipping.

Start log shipping.

Because attempts to attach non-updated databases to the secondary farm fail and can leave the farm in an unsupported state, ensure that one or two cycles of log shipping are complete before you attach the log-shipped content databases in the secondary farm.

Optional. You can also use the following query to determine whether the database schema from the primary farm was fully replicated to the secondary farm before you attach the databases.

USE <contentdb>

GO

SELECT * FROM Versions

The query returns versions numbers in the following format.

00000000-0000-0000-0000-000000000000

The last version in the list is the most-recently installed version of Office SharePoint Server 2007.

Important:

Microsoft generally does not support running queries against the databases that are used by SharePoint Products and Technologies. The previous query is an allowed exception because it deals with metadata about the database. Direct queries can adversely affect system performance and reliability. For more information about the effects of direct changes to databases, see the Knowledge Base article Support for changes to the databases that are used by Office server products and by Windows SharePoint Services (http://go.microsoft.com/fwlink/?LinkID=105589)

Analyze the amount of data that is being log shipped, so that you correctly set the backup, copy, and restore job intervals for log shipping. The amount of data being log shipped is affected by the daily amount of change in the content databases. In our experience, a typical farm undergoes change of 2 percent to 4 percent, but with maintenance changes, the level of change can reach 5 percent to 7 percent at peak times. To determine the amount of change in the content databases in the system, for each content database that you log ship, calculate the sum of changes in the transaction log backups over a given time interval, and calculate the percentage of change compared to the size of the primary database.

We have found that it is better to back up and copy many small transaction logs instead of a few large transaction logs. We recommend that you schedule log backups and copying at frequent intervals. You can restore the transactions logs at less-frequent intervals. You might want to start by using backup and copy intervals of 5 minutes, and a restore interval of 15 minutes. SQL Server 2008 includes the capability to have log shipping intervals that are less than a minute. For more information, see Scheduling Sub-Minute Log Shipping in SQL Server 2008 (http://go.microsoft.com/fwlink/?LinkId=151253)

You might encounter performance issues if the time that the system takes to ship logs consistently exceeds the time that is required to create new logs; that is, if you are always falling behind in the log shipping schedule. This kind of problem can be caused by throughput or latency issues. If you have throughput and latency issues, we recommend that you consider using Windows Distributed File System Replication (DFSR) with Active Directory directory service that is running on Windows Server 2003 R2 or Active Directory Domain Services (AD DS) running on Windows Server 2008 to replace the log-shipping copy job. For more information about how to use DFSR, see Overview of the Distributed File System Solution in Microsoft Windows Server 2003 R2 (http://go.microsoft.com/fwlink/?LinkId=150764) and DFS Step-by-Step Guide for Windows Server 2008 (http://go.microsoft.com/fwlink/?LinkId=150765).

The following graph compares the throughput provided by various replication technologies that can be used to copy log-shipped transaction logs.

For SQL Server log shipping with Office SharePoint Server 2007 with SP2, members of the team must have the following permissions:

To configure Office SharePoint Server 2007 with SP2 with log shipping and perform the procedures in this article, a team member must be a member of the Farm Administrators SharePoint group.

To configure SQL Server log shipping and perform the procedures in this article, a team member must be a member of the sysadmin fixed server role on each server instance.

When a database administrator set up a log-shipped database, the SQL Server logins and permissions for the database to be used with a SharePoint farm are not automatically configured in the master and msdb databases on the log-shipped server. Instead, you must configure the permissions for the required logins. These permissions include, but are not limited to, the following:

The Central Administration application pool account must be a member of the dbcreator and securityadmin fixed server roles.

All application pool accounts and the search services and default content access accounts must have SQL Server logins, although these accounts are not assigned to SQL Server fixed server roles or fixed database roles.

Members of the Farm Administrators SharePoint group must also have SQL Server logins and must be members of the same roles as those of the Central Administration application pool account.

The backup and restore directories in the log shipping configuration must adhere to the following requirements:

For the backup job to be successful, the SQL Server service account on the primary server instance and the proxy account of the backup job (by default, the SQL Server Agent account on the primary server instance) must have read/write permissions to the backup directory.

For the copy job to be successful, the proxy account of the copy job (by default, the SQL Server Agent account on the secondary server instance) must have read permissions to the backup directory and write permissions to the copy directory.

For the restore job to be successful, the SQL Server service account on the secondary server instance and the proxy account of the restore job (by default, the SQL Server Agent account on the secondary server instance) must have read/write permission to the copy directory.

We make the following assumptions about the environment in the secondary data center:

The secondary data center includes a running domain controller and the appropriate authentication provider. For more information, see Installing an Additional Domain Controller (http://go.microsoft.com/fwlink/?LinkId=150832).

If a deployment includes Microsoft Exchange 2007 integration features, the secondary data center contains a log-shipped or otherwise replicated Exchange 2007 server. For more information, see High Availability (http://go.microsoft.com/fwlink/?LinkId=150839).

The failover farm must have the following characteristics:

A separate configuration database and a separate Central Administration content database must be installed and maintained on the failover farm, which means that all configuration changes in the primary farm must be replicated manually in the failover farm.

Information stored in the configuration database includes the following.

Activated features

Diagnostic logging settings

Administrator-deployed form templates

E-mail settings

Alternate access mapping settings

External service connection settings

Antivirus settings

Farm-level search settings

Application pool settings, including service accounts (all accounts that run as Web applications, including the crawler account and the search account)

HTML viewer settings

Blocked file types

Recycle Bin settings and other Web application general settings

Content deployment settings

Timer job settings

Crawler impact rules

Usage analysis processing settings

Database names and locations

Web application names and databases. Be sure to document the content database names associated with each Web application.

Default quota templates

Workflow management settings

Note:

If you have configured alternate access mapping for the primary farm, it is very important to configure it identically on the secondary farm at the point of failover. To document alternate access mapping settings, export them to a text file by using the command stsadm -o enumalternatedomains.

All customizations, such as features, solutions, site templates, and third-party solutions such as IFilters, must be deployed on both farms. We recommend that you package all customizations as solutions to enable rapid deployment. For more information, see Deploy customizations.

The primary server and secondary servers must be running the same edition of SQL Server 2005 or SQL Server 2008. Log shipping is available in the Standard, Developer, and Enterprise editions.

If you plan to expose the log-shipped secondary farm to users, configure alternate access mapping with a secondary namespace for the secondary farm; for example, http://secondary.contoso.com or http://readonly.contoso.com. For more information, see Configure alternate access mapping. You replace this alternate access mapping with a mapping that is identical to the primary farm on failover.

Document all of the configuration settings so that they can be applied to the secondary farm. For more information, see Prepare to back up and restore a farm (Office SharePoint Server 2007). Make sure, especially, to document the alternate access mapping settings by exporting them to a text file; use the command stsadm -o enumalternatedomains to export the settings.

Document all of the customizations. The customizations will be easiest to reapply to the secondary farm if they are packaged as solutions. For more information, see Deploy customizations.

We recommend that if you have enough equipment, you configure the same number of front-end Web servers and databases as the primary farm. If you have insufficient equipment, you can use fewer servers in the secondary farm, but it may be unable to handle the same load as the primary farm.

Click Connect and connect to the instance of SQL Server that you want to use as the secondary server. By default, the name of the secondary database is the same name as the database from the primary server.

On the Initialize Secondary Database tab, select Yes, generate a full backup of the primary database and restore it into the secondary database (and create the secondary database if it doesn’t exist).

On the Copy Files tab, in the Destination folder for copied files box, type the path of the folder on the secondary server into which you want the transaction logs backups copied.

On the Restore Transaction Log tab, in the Database state when restoring backups section, select Standby mode and clear Disconnect users in the database when restoring backups.

Click OK.

We recommend that you save settings in a script. In the Database Properties dialog box, click Script Configuration, and then click Script configuration to file.

A Save As dialog box appears. Enter the folder where you want to save the file, and then click OK.

Click OK.

All jobs will be executed one time to initialize log shipping, and will report success or failure.

Enable and configure DFS Replication (DFSR) for the environment. For more information, see Replication (http://go.microsoft.com/fwlink/?LinkId=151670). For an example of configuring DFS Replication, see DFS Step-by-Step Guide for Windows Server 2008 (http://go.microsoft.com/fwlink/?LinkID=150765).

In the Object Explorer navigation pane, verify that all log-shipped content databases have Standby or Read-Only status.

Determine how long average log shipping jobs take to run on the secondary farm, by running the jobs and timing their duration. For more information, see Monitoring Log Shipping (http://go.microsoft.com/fwlink/?LinkId=151682).

Configure search in the secondary farm to meet the business objectives for the disaster recovery scenario. Initially, you may want to search the same databases, with the same crawl settings and crawl rules as the primary farm. If you determine that you cannot schedule crawls and log shipping so that they avoid overlapping, we recommend that you adjust the content that you include in crawls. For example, before failover, you might crawl only those databases that contain content of high business impact, and then crawl other content only after failover. For more information, see Limit or increase the quantity of content that is crawled (Office SharePoint Server).

Stop the SQL Server Agent job on the secondary farm to disable log shipping while you configure search.

Configure search on the secondary farm.

Determine how long search crawls take on the secondary farm. You can use data collected from the primary farm to estimate the time required on the secondary farm.

Determine how long average log shipping jobs take to run on the secondary farm, and when you want to schedule the jobs to run.

Determine how long incremental crawls take on the secondary farm, and when you want to schedule crawls. You may be able to use crawl data from the primary farm to determine how long incremental crawls take to complete. For more information about scheduling an incremental crawl, see Schedule an incremental crawl (Office SharePoint Server 2007).

If possible, schedule log shipping jobs and search crawls so that they do not overlap.

If you cannot schedule log shipping and incremental search crawls so that they avoid overlapping, choose from the following options:

Run both log shipping jobs and search crawls manually, suspending one set of jobs while running the other.

Allow search crawls to take precedence over processing logs, and create a script to automatically start log shipping when the crawler process is not running.

If the only active process in the database is the crawler process, configure log shipping to wait until the database is not in use, and then process the shipped logs.

If none of these options work, and if you cannot schedule the amount of data you are log shipping and the crawl times so that they do not overlap, consider actions to cut to make the system work; for example, if you cannot complete both log shipping and search in the times available, crawl only high-business-impact content databases before failover, and start crawling other content after failover).

Schedule the refresh script to run. If no new site collections have been added to the primary farm, you do not have to run the refresh script. When new site collections are added, the refresh script must be run periodically by using the Windows Task Scheduler. When the refresh script runs, it suspends both the crawler and the log shipping processes. For more information about scheduling tasks, see Scheduling tasks (http://go.microsoft.com/fwlink/?LinkId=151894).

If the refresh script is canceled while it is running, we recommend that you run the script manually to make sure that all the databases are reattached and that all the services are turned back on.

If possible, provide users with an updated hosts file that points to the Web applications on the secondary farm that you want to expose.

If you cannot distribute a hosts file, define a dedicated alternate access mapping for each Web application that you want to expose, such as http//readonly.contoso.com or http://secondary.contoso.com, and configure the map in the DNS.

Note:

If you have no more room to define alternate access mappings for a particular Web application, then this dedicated-mapping option is not possible.

Determine whether the primary farm is still available, and whether the network shared folder in which the backups are stored can be reached by both server farms. If neither of these conditions apply, go to the procedure Set content databases to read/write.

In Management Studio, in the Object Explorer navigation pane, right-click a content database, point to Tasks, and click Backup. The Back Up Database dialog box appears.

Click the Backup type drop-down list and select Transaction Log.

In the Select a page pane, click Options.

In the Transaction log section, select Back up the tail of the log and leave the database in the restoring state, and finally click OK.

This procedure is only useful if the primary farm is still available and if the network share on which the backups are stored can be reached by both server farms. If both of these conditions are not met, go to the procedure Set content databases to read/write.

In Management Studio on the secondary server, right-click the content database, point to Tasks, click Restore, and then click Transaction Log. The Restore Transaction Log dialog box appears.

On the General tab, select From File and Tape and enter the path to the backup file you created on the primary server.

In the Recovery state section, select Leave the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY), and then click OK.

When you are testing failover, be clear about the level of failover testing that the service level agreements require you to perform. The following are some common examples of failover testing.

Verification that the secondary site is live, and is being crawled For this type of failover test, you can provide the users with a hosts file or an alternate access mapping path to the secondary farm, so that they can verify that the farm is live and up to date. No additional steps are required.

Farm failover In this type of test, the primary farm is taken down for a short announced interval, but the secondary farm is not switched to read/write status. For this type of test, follow the procedures in the Failing over section, with the following differences:

Steps for failover test

Description

Perform

1. To start the failover test, on the secondary farm, stop the SQL Server Agent Job, so that no logs are being processed.

Do not perform

2. Disable all log shipping jobs in the primary farm

Perform

3. Stop accepting traffic to the primary farm

Perform

4. Back up the transaction logs on the primary server.

Perform

5. Restore the latest transaction logs to the secondary server.

Do not perform

6. Set content databases to read-write.

Do not perform

7. Optional. Restore the SSO encryption key

Perform

8. Direct traffic to the secondary farm

Do not perform

9. Finish configuring the secondary environment.

Planned data center failover with additional precautions In this type of test, the primary data center is taken down for an announced interval. The secondary farm is switched to read/write status. For this type of test, follow the procedures in the Failing over section, with the following differences:

Steps for failover test

Description

Perform

1. To start the failover test, on the secondary farm, stop the SQL Server Agent Job, so that no logs are being processed.

Perform

2. Disable all log shipping jobs in the primary farm

Perform

3. Stop accepting traffic to the primary farm

Perform

4. Back up the transaction logs on the primary server.

Perform

5. Restore the latest transaction logs to the secondary server.

Perform

6. Set content databases to read-write.

Perform

7. Optional. Restore the SSO encryption key

Perform

8. Direct traffic to the secondary farm

Do not perform

9. Finish configuring the secondary environment.

New step

10. Keep all of the log-shipped backups on the secondary farm so that you can use the database backup from the secondary farm to restart log shipping

Planned data center failover without additional precautions In this type of test, the primary data center is taken down for an announced interval to determine how long a true recovery will take. Some data loss is possible. The secondary farm is switched to read/write status. For this type of test, follow the procedures in the Failing over section.

Steps for failover test

Description

Perform

1. Before you begin, back up the log shipped databases on the primary farm, so that you have a current backup to use to restart log shipping.

When the secondary farm is functional, and the original primary database is accessible and the problem with that farm investigated and resolved, you can either make the former primary database into the new secondary database, or you can deliberately fail over from the secondary farm to the former primary farm, and then reconfigure log shipping as you initially had it structured.

Configure log shipping between the secondary farm and the primary farm. Establish a log shipping relationship between the SQL Server instance on the secondary farm and the corresponding instance on the primary farm. For details, see the section Configuring the log shipping environment.

On the primary farm, apply any unapplied transaction log backups to each database.

Use the DNS to stop accepting traffic on the secondary farm.

Fail over from the secondary farm to the original primary farm. For details, see the section Failing over, and then reconfigure log shipping.

Optional. Restore SSO by using the local copy of the encryption key on media.

Bring up the primary farm again, verify that all is working as expected and then change the DNS to direct incoming traffic to the primary farm.

The use of log shipping to provide a disaster recovery farm in a secondary data center is complex. Make sure that you establish clear service level agreements with users, and test the environment on a regular basis.