Configuring SQL Server Log Shipping on SharePoint Content Databases

Introduction to Log Shipping

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:

1. Backs up the transaction log at the primary server instance
2. Copies the transaction log file to the secondary server instance
3. Restores the log backup on the secondary server instance

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 theSQLBackup 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. ClickOK.
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 theSQLBackup SQL Server so that server now responds to all SQL Server server requests.

Troubleshooting the Sharepoint 2010 User Profile Service Application

Sharepoint 2010 User Profile Service (UPS) application allows the Sharepoint administrator great flexibility and is a "must have" feature if you are taking your Sharepoint to the next level.

Unfortunately UPS has a dark side that too many administrators have to face at one time or another, more often than not it is related to the "Forefront Identity Management Service" and the "Forefront Identity Manager Synchronization Service" service.

Some Tips 

• Upgrade to Sharepoint 2010 SP1 and the August 2011 CU at before attempting to resolve problems. Both of these updates resolve a number of issues that might impact the User Profile Service application.
• Don't ever try to change the "Forefront Identity Manager Service" or "Forefront Identity Manager Synchronization Service" settings manually from the Services MMC snap-in. It simply doesn't work as SP needs to do a great deal of configuration.
• Sharepoint can be super slow, sometimes you need to wait 10-15 minutes for things to happen, so when you click start and nothing happens, wait 15 minutes then check again.
• If you are re-creating the User Profile Service application, it is a good idea to use different names for the databases, the service application itself and the application pool. This will ensure there are no conflicts with any old settings that may be floating around in your Sharepoint configuration or registry.
• Be IISRESET "happy". It is a good idea to perform IISRESET's after major parts of the setup process. I normally follow a pattern such as: Start/Create the service, wait 10 minutes, IISRESET, next step. This will ensure all of Sharepoint is "on the same page" before moving forward to the next step of the process.

Possible Issues and Resolutions 

Problem:
After starting the "User Profile Synchronization Service" from Central Administration only the "Forefront Identity Manager Synchronization Service" starts, or both services fail to start.

Resolution: 
If you don't have too much already setup in the UPS or it is your first time setting it up, it can be much easier to delete the UPS, stop the Synchronization services and then recreate it, than mess around. See my rebuild process below.


Problem:
"Forefront Identity Manager" source logs an Event ID 3 in event logs.

.Net SqlClient Data Provider: System.Data.SqlClient.SqlException: HostId is not registered

Resolution:
Most of the time simply restarting the "Forefront Identity Manage Service" and "Forefront Identity Manager Synchronization Service" from the services MMC snap-in will resolve this issue. If either of them is in a Disabled state or the problem persists after a service restart, then I recommend rebuilding the UPS from scratch as per my instructions below.


Problem:
"ILM Web Service Configuration" source logs an Event ID 234 in event logs.

ILM Certificate could not be created: Cert step 2 could not be created: C:\Program Files\Microsoft Office Servers\14.0\Tools\MakeCert.exe -pe -sr LocalMachine -ss My -a sha1 -n CN="ForefrontIdentityManager" -sky exchange -pe -in "ForefrontIdentityManager" -ir localmachine -is root

Resolution:
If you have tried to provision the "User Profile Synchronization Service" a number of times you might see this error. It occurs because there are multiple "ForefrontIdentityManager" certificates stored in the Certificate store.

Firstly you need to stop the "User Profile Synchronization Service" under Sharepoint Central Administration > System Settings > Manage Services on Server.

Then open an MMC console, add a Certificates snap-in, select Computer Account. Check the Personal, Trusted Root Certification Authorities and Trusted People stores for duplicate "ForefrontIdentityManager" certificates and delete ALL the FIM certificates.

Next under Sharepoint Central Administration > System Settings > Manage Services on Server, Start the "User Profile Synchronization Service" again, you will be prompted for the password of the Sharepoint service account it is using. It should successfully restart and create a new certificate without conflicts.


Problem:
"Microsoft Resource Management Service" source logs a Event ID 0 in event logs.

Service cannot be started. System.InvalidOperationException: Cannot find the X.509 certificate using the following search criteria: StoreName 'My', StoreLocation 'LocalMachine'

Resolution:
The "User Profile Synchronization Service" can't find a certificate when it is starting. You need to stop and then start this service. Firstly you need to stop the "User Profile Synchronization Service" under Sharepoint Central Administration > System Settings > Manage Services on Server. After waiting 5 minutes, perform an IISRESET and then press Start to restart the service. You will be prompted for the password of the Sharepoint service account it using.


Problem: 
"Microsoft.ResourceManagement.ServiceHealthSource" source logs an Event ID 2 in event logs.

The Forefront Identity Manager Service could not bind to its endpoints.  This failure prevents clients from communicating with the Web services.

Resolution:
You can try restarting the "Forefront Identity Manage Service" and "Forefront Identity Manager Synchronization Service" from the services MMC snap-in. If this does not work then I recommend rebuilding the UPS from scratch as per my instructions below.


Problem:
"Forefront Identity Manager" source logs an Event ID 3 in event logs.

.Net SqlClient Data Provider: System.Data.SqlClient.SqlException: Cannot open database "DBNAME" requested by the login. The login failed.
Login failed for user 'DOMAIN\service-spsql'

Resolution:
This issue occurs if you have recreated the User Profile Service application and the database settings have not updated in the registry. It is normally a smart idea to stop the "User Profile Synchronization Service" under Sharepoint Central Administration > System Settings > Manage Services on Server, wait 5 minutes, then restart it.

If it is simply a database name wrong you can edit it in the registry under the following paths.

HKLM\system\currentcontrolset\services\FIMService

HKLM\system\currentcontrolset001\services\FIMService

HKLM\system\currentcontrolset002\services\FIMService

HKLM\system\currentcontrolset\services\FIMSynchronizationService

HKLM\system\currentcontrolset001\services\FIMSynchronizationService

HKLM\system\currentcontrolset002\services\FIMSynchronizationService

The main two values you will want to look at are "DatabaseName" and "DatabaseServer", ensure those two are correct then restart the "Forefront Identity Manage Service" and "Forefront Identity Manager Synchronization Service" from the services MMC snap-in.

If this doesn't work, then stopping the "User Profile Synchronization Service"and then restarting it from Central Administrator is your best solution.


Problem: 
 "User Profile Service" source logs an Event ID 1511 in event logs. The "event user" will be one of your Sharepoint service accounts.

Windows cannot find the local profile and is logging you on with a temporary profile. Changes you make to this profile will be lost when you log off.

Resolution:
This is a fairly common problem and one of the easier ones to fix. Firstly go into your IIS management console > Application Pools and search for any application pools that have the same username as the event log user. Stop all of those application pools and then run an IISRESET.

Then from the command line run the commands. The first command adds the problem user account to the local administrator group (to aide the profile creation) and the second command creates the user profile.

net localgroup administrators DOMAIN\AppPoolAccount /add
runas /u:DOMAIN\AppPoolAccount /profile cmd

When this process is complete remove the user from the local administrators group.

net localgroup administrators DOMAIN\AppPoolAccount /delete

Then you will need to restart all the IIS Application Pools you previously stopped.


Problem:
After adding a "Synchronization Connector" to the UPS you can no longer get into "Manage User Profiles". When clicking "Manage User Profiles" the web browser simply times out with no errors in the event log or ULS logs.

Resolution:
Unfortunately I am still struggling with this one and have no resolution. On the other hand it seems to make no difference, unless you want to map custom properties, which you can do manually through the Forefront Identity Manager console from the Sharepoint server desktop. While it is annoying to not be able to access, it doesn't seem to have any functional restrictions, in my environment at least.


Recreating the User Profile Service application

1. First we need to get rid of the broken instance of UPS.

a. Under Central Administration > System Settings > Manage services on server, stop the "User Profile Service" and "User Profile Synchronization Service"

b.  Under Central Administration > Application Management > Manage service application, delete the "User Profile Service application"

c.  On the Sharepoint server itself, open an MMC console, add a Certificates snap-in, select Computer Account. Check the Personal, Trusted Root Certification Authorities and Trusted People stores for "ForefrontIdentityManager" certificates and delete ALL the FIM certificates.

d.  Open a Sharepoint 2010 Management Shell, issue the command get-spserviceapplicationpool. Remove any service application pools that are associated with previous UPS applications with the command:

remove-spserviceapplicationpool "PoolName"

e.  Delete any pending timer jobs related to the UPS synchronization service provisioning. Under Central Administration >Monitoring > Check job status, check the Running section for any related jobs and delete them.

f.  Wait 15 minutes and then issue an IISRESET before proceeding to the next step.

2. Under Central Administration > System Settings > Manage services on server, start the "User Profile Service.

3.  Under Central Administration > Application Management > Manage service application, create a new UPS. Use a different name than you did for the previous UPS instance, different database names and a different application pool name. Wait 15 minutes then issue an IISRESET.

4. Under Central Administration > System Settings > Manage services on server, start the User Profile Synchronization service. Wait 15 minutes and if both FIM services are running from an MMC services snap-in as below, issue an IISRESET.

If you get this far and have no problems opening your UPS application from Central Administration > Application Management > Manage service application then congratulations, more than likely you have resolved your problems.

You can now proceed to adding Synchronization connectors and bringing those attributes in from Active Directory.