SQL Server log shipping offers path to SharePoint disaster recovery

Brien M. Posey describes how to enable disaster recovery for SharePoint 2010 using SQL Server log shipping. It’s one way to back up data to a secondary SharePoint farm to prepare for the worst.

SharePoint administrators need to be able to recover their systems if disaster strikes. Though there are many different methods for recovering SharePoint, one option is to use a disaster recovery farm that you’ve set up with data from your primary server farm. A disaster recovery farm can be created and updated through SQL Server log shipping or Database mirroring. This article discusses how to use the log shipping approach.

Log shipping is a function of SQL Server, not of SharePoint. The basic idea behind log shipping is this: It provides a mechanism for database changes to be replicated in the form of transaction log backups in one or more secondary SQL Server systems. That way, if something happens to the primary SQL Server machine that the SharePoint collaboration platform depends on, the data still exists on at least one backup server and can be restored in the production environment.

SQL Server log shipping is not a form of failover clustering. In fact, in spite of a few similarities, log shipping does not provide any sort of automatic failover capabilities for SharePoint. In addition, log shipping is not a real-time or synchronous operation; it must be scheduled. But even so, log shipping between systems can be set up to occur as frequently as once a minute.

Learn more about SQL Server log shipping and other database availability initiatives

Read about four tips to maintain RTO and RPO with SQL Server log shipping

Get some top tips for SQL Server database failover success

Learn about the options when deciding on a SQL Server disaster recovery solution

The exact process for enabling log shipping varies depending on the versions of SQL Server and SharePoint that an organization is running. The following is a set of generalized instructions for environments based on SQL Server 2008 and SharePoint 2010.

SharePoint managers can configure the backup systems in a secondary data center or create an alternate SharePoint farm within the primary data center. With that in mind, the first step is to install and configure the disaster recovery farm, which should use the same service accounts as the original one.

Once the new farm is in place, it’s time to install the Web applications, service applications and customizations that are used within the primary SharePoint farm. Next, detach the content database from the backup farm and delete it; log shipping from the primary farm will re-create the content database and then continue to update it. Keep in mind that the content database is the only database to which log shipping will be applied.

Preparing SQL Server for log shipping
The first step in preparing the backup SQL Server database is to create a file share on the secondary server to use to store the transaction logs that will be shipped to that system. Then open the SQL Server Management Studio application and connect to your primary SQL Server system. Once you’re connected, expand the Databases folder as shown below and locate the SharePoint content database.

Next, you will have to verify that the content database is configured to use the Full recovery model. Right-click on the database and choose the Properties command from the Shortcut menu. When the database properties sheet appears, select Options (in the Select a Page section) and set the recovery model to Full if it isn’t already, as shown below.

To enable log shipping, again right-click on the content database and choose Properties from the shortcut menu. This time, select the Transaction Log Shipping option under Select a Page. Then click on the “Enable This as a Primary Database in a Log Shipping Configuration” check box.

Now go to the Network Path and to the Backup Folder dialog box and enter the network path for the file share that you created earlier. Click the Add button, found under Secondary Server Instances and Databases. Connect to the SQL Server instance that will act as your secondary server. Next, locate the Secondary Database box and enter the name of the database you want to create. You must then initialize the database through the Initialize Secondary Database tab.

Finally, select the properties sheet’s Copy Files tab. You will have to specify the destination folder for files that are copied to the secondary server. Click OK to complete the configuration process, but remember that you will have to repeat it if you want to create additional secondary servers.

Because SQL Server log shipping does not provide for automatic failover capabilities, there are some manual processes that will need to be tackled if you ever need to use the database in the disaster recovery farm. But once the setup procedures are completed, you’ll be prepared to take those next steps if disaster does strike.

Brien M. Posey is a Microsoft MVP with two decades of IT experience. Before becoming a freelance technical writer, Posey was chief information officer for a national chain of hospitals and health care facilities and a network administrator for insurance companies and the Department of Defense.

Dig Deeper on Enterprise SharePoint strategy