Configuring SQL Server 2005 Log Shipping for SharePoint 2007 Disaster Recovery Farm

As part of the architecture design to provide 99.5% availability for our SharePoint infrastructure, which is equivalent to 3.5 hours downtime per month, we plan for the redundancy and failover between two server farms.  The primary farm is located in a main data centre and the secondary farm is located in a Disaster Recovery centre in the same city.  Both farms have been setup separately with their own configuration database and Central Administration content database.  All customizations and patches have been deployed on both farms as well.  

To support the disaster recovery scenario, the primary farm's content databases are configured with log shipping to the failover farm in the disaster recovery centre.  Log shipping essentially involves copying the content database backup and subsequent transaction log backups from the primary farm's database server and restoring the content database and transaction log backups on the secondary farm's database server. The content database and transaction logs are restored on the secondary farm in standby or no-recovery mode, which allows subsequent transaction logs to be backed up on the primary and shipped or copied to the secondary server and applied there.

The steps to configure log shipping for a particular content database are as follows:
1. Ensure that the content database is on Full or Bulk_logged recovery model since log shipping requires those models to function.  To change the recovery model for the database, right click on the database, select properties and in the Options tab, click on the Recovery Model drop down list box and select Full or Bulk_logged recovery model.
2. Right click the content database in the primary farm's database server, and then click Properties.
3. Under Select a page, click Transaction Log Shipping.
4. Select the Enable this as a primary database in a log shipping configuration check box.
5. Under Transaction log backups, click Backup Settings.
6. In the Network path to the backup folder box, type the network path to the share created for the transaction log backup folder.  Before configuring log shipping, we must create a share to make the transaction log backups available to the secondary server. This is a share of the directory where the transaction log backups will be generated. For example, if we back up our transaction logs to the directory d:\data\tlogs\, we could create the \\primaryserver\tlogs share off that directory.
7. If the backup folder is located on the primary server, type the local path to the backup folder in the If the backup folder is located on the primary server, type a local path to the folder box. (If the backup folder is not on the primary server, you can leave this box empty.)  If the SQL Server service account on primary server runs under the local system account, we must create backup folder on the primary server and specify a local path to that folder.
Notes:
If we are backing up to a network path, there will be some increase in the transaction log backup times and it will decrease the space requirements on primary server . The network path should go to the location where we want to store the transaction log backups on the secondary server. Most DBAs use a network share to back up the files to because they want the transaction log backup files on the secondary server to protect them in the event that the primary server goes down.  The local path option will back up the transaction logs to a path on primary server. Make sure the transaction log backups are not backed up to the same physical drive where your database data files or log files exist. Should they be backed up to the same physical drive, it would cause I/O contention and overall SQL Server performance degradation.
8. Configure the Delete files older than and Alert if no backup occurs within parameters.
9. Note the backup schedule listed in the Schedule box under Backup job. If we want to customize the schedule, then click Schedule and adjust the SQL Server Agent schedule as needed.
10. Click OK.
11. Under Secondary server instances and databases, click Add.
12. Click Connect and connect to the instance of SQL Server that we want to use as our secondary server.
13. In the Secondary Database box, choose a database from the list or type the name of the database we want to create.
14. On the Initialize Secondary database tab, choose the option that we want to use to initialize the secondary database.
Notes:
If we choose to have Management Studio initialize the secondary database from a database backup, the data files created on the secondary server will have the same names as those on the primary server, and will be created in an identical directory structure, including the drive letter.
15. On the Copy Files tab, in the Destination folder for copied files box, type the path of the folder into which the transaction logs backups should be copied. This folder is often located on the secondary server.
16. Note the copy schedule listed in the Schedule box under Copy job. If we want to customize the schedule for our installation, click Schedule and then adjust the SQL Server Agent schedule as needed. This schedule should approximate the backup schedule.
17.On the Restore tab, under Database state when restoring backups, choose the No recovery mode or Standby mode option. If we chose the Standby mode option, choose if we want to disconnect users from the secondary database while the restore operation is underway.
Notes:
No Recovery Mode: This is the default option. In this option, the destination database will be inaccessible.
Standby Mode: In this option, the destination database will be read only until the next transaction log backup is applied. After the transaction log is applied, the database will be returned to read-only mode again. This allows read-only access and users will not be able to make any changes to the database and they will be disconnected when the next database backup is applied.
18. If we want to delay the restore process on the secondary server, choose a delay time under Delay restoring backups at least.
19. Choose an alert threshold under Alert if no restore occurs within.
20. Note the restore schedule listed in the Schedule box under Restore job. If we want to customize the schedule for our installation, click Schedule and then adjust the SQL Server Agent schedule as needed. This schedule should approximate the backup schedule.
21. Click OK.

Steps 22 to 26 below are optional:
22. Under Monitor server instance, select the Use a monitor server instance check box, and then click Settings.  To monitor this log shipping configuration, we must add the monitor server now. To add the monitor server later, we would need to remove this log shipping configuration and then replace it with a new configuration that includes a monitor server.
23. Click Connect and connect to the instance of SQL Server that we want to use as our monitor server.
24. Under Monitor connections, choose the connection method to be used by the backup, copy, and restore jobs to connect to the monitor server.
25. Under History retention, choose the length of time you want to retain a record of your log shipping history.
26. Click OK.

27. On the Database Properties dialog box, click OK to begin the configuration process.