Brian Alderman

Implementing data redundancy is one of the most effective ways to avoid data loss in any application. Although this article discusses configuring redundancy for SharePoint content stored in SQL Server by configuring SQL Server log shipping, redundancy should not be limited to only data redundancy. Any possible single point of failure, including hard drives, cables, and DNS or IIS entries should be taken into consideration to ensure you are able to quickly recover from what could be a disaster.

There are several methods to choose from when determining the type of data redundancy you need to ensure business continuity. A few of these methods include; SQL Server clustering, database mirroring, AlwaysOn, and SQL Server log shipping all of which are managed by your SQL Server DBA’s who are responsible for the SQL Server environment.

Log shipping provides both server-level redundancy as well as data redundancy, because you have an entire server (usually referred to as the secondary server) dedicated to hosting a copy of your SharePoint Web app content databases, service application databases, and your SharePoint configuration settings. This secondary server is very helpful if you need to quickly failover it in the event your primary SQL Server server fails. The secondary server can also be used to perform DBCC (Database Console Commands) to verify the integrity of your SharePoint databases instead of letting the secondary server just sit their idle waiting for the primary server to fail.

To configure SQL Server log shipping you must first stand-up a second server which is the secondary server that will mirror the configuration of your primary SQL Server server. After the primary server is made available, you can then create automatic shipments of the transaction logs from the primary server to the secondary server. You can use the upcoming steps to configure SQL Server log shipping for your SharePoint content databases from the primary server to the secondary server.

Note: In the following configuration, SQLPrimary is the primary SQL Server server and SQLBackup is the secondary SQL Server server. We will begin by creating a SQL Server alias called SPSQL_Instance which can be used to failover to the SQLBackup secondary server.

  1. Create a SQL Server alias called SPSQL_Instance by opeing the SQL Server Configuration Manager
  2. Expand SQL Server Native Client Configuration, then right-click Aliases, and click New Alias.
  3. Type in SPSQL_Instance in the Alias Name box, then type SQLPrimary in the Server box then click OK. (Alternatively, you can enter the IP address instead of the server name.)
  4. Login to the secondary SQL Server, named SQLBackup, and create a folder called LogShipping and share the folder with a network share name of LogShipping
  5. On the primary SQL Server, named SQLPrimary, open SQL Server Management Studio (SSMS), and add the SharePoint farm administrator domain account to the security logins and also map the SharePoint farm administrator domain account to the dbo role of each SharePoint content database.
  6. If the SQL Server Agent is not started on both SQL Server servers, start it, and also make sure the SQL Server Agent is configured to automatically start on both of these SQL Server servers.
  7. On SQLPrimary SQL Server locate the database that you want to configure log shipping for and right-click on the SharePoint content database and click Properties.
  8. Select Transaction Log Shipping, and then select Enable this as a Primary database in a log shipping configuration.
  9. Click Backup Settings and enter \\SQLPrimary\LogShippng
  10. Click the Schedule button and change Daily Frequency Occurs Every: to 5 minutes, and then click OK.
  11. In the Secondary Databases section, click Add and then click the Connect button to connect to the SQLBackup SQL Server server. Verify your SharePoint content database name is selected as the Secondary database for log shipping configuration.
  12. Click the option: Yes, generate full backup of the primary database and restore it into the secondary database (and create the secondary database if it doesn’t exist)
  13. Click Restore Options and type the location of the data file and the log file on the secondary SQL Server server. (Preferably, you would enter different drives; one for the data file and one for the log file)
  14. On the Copy Files tab type in \\sqlBackup\LogShipping, (or the share name you created on the secondary SQL Server server.
  15. Click the Schedule button and change the Daily frequency Occurs every: to 5 minutes
  16. On the Restore Transaction Log tab click the Standby Mode radio button and click the check box next to Disconnect users in the database when restoring backups. Otherwise the transaction logs will not be applied until later. Click OK.
  17. Optionally, on the Database Properties Select the Script Configuration button and choose Script Configuration to Clipboard, open Notepad and paste the log shipping configuration information and then save it to a location in the event you want to use it again later.
  18. Click OK, and then click Close after completion.
  19. Go to the SQLBackup SQL Server server and refresh the databases node to see that your SharePoint content database is set to standby / read only mode.

In the event the SQLPrimary SQL Server server fails you simply modify the SPSQL_Instance alias to point to the SQLBackup SQL Server so that server now responds to all SQL Server server requests.