An attempt to use the new Optimized Item Level Recovery (ILR) feature of System Center 2012 Data Protection Manager (DPM) for SharePoint recovery with a clustered SQL server as the instance specified as the location to "temporarily stage content database prior to recovery" in the DPM Recovery Wizard fails with the following error:
The recovery jobs for SQL\SharePoint_Config that started at StartDateTime, with the destination of sp2010.domain.com, have completed. Most or all jobs failed to recover the requested data. (ID: 33330)
DPM was unable to export the item
Documents/test.doc from the content database SQL\WSS_Content. Exception Message = Shared%20Documents/test.doc.
Additionally the following information will be logged:
The job to recover DatasourceTypeDatasourceName to TargetServerName, that started at StartDateTime, failed using optimized ILR. Another job using unoptimized ILR has been triggered.
1. If the instance of SQL Server used for temporary staging is running as a Local Service.
2. If there is major version change in the SQL Server used during backed up and SQL Server used for temporary staging.
If SQL Server used for temporary staging is clustered.
The inability to use a clustered SQL server is a limitation with SQL Server 2008 and SQL Server 2008 R2. This problem does not exist when using a clustered Microsoft SLQ Server 2012 server so as a workaround we can use a non-clustered SQL Server 2008 or clustered SQL Server 2012 instance as the temporarily staging location.
Since DPM utilizes a non-clustered SQL instance that meets our requirements already, we can use it as the temporary staging location. However, we will need to change the account running the SQL Server (MSDPM 2012) service from the Local Service account to a domain account that is a member of the local administrators group. We will also need to allow for incoming communication from the WFE to the SQL services on the DPM server.
As mentioned in #1 from the cause section, the instance of SQL Server used for temporary staging cannot be running as a Local Service. It also cannot be running under the MICROSOFT$DPM$Acct local computer account created during the DPM setup. Change the account used to "Log On" to the SQL Server (MSDPM2012) instance to a domain user account that is also a member of the local administrators group on the DPM server.
We’re not done yet though. After making these changes you will still not be able to perform Optimized ILR. When you run through the wizard to perform Optimized ILR, now you will probably receive the following error:
Type: SharePoint export and import task
Description: DPM was unable to query the unattached content database SQL\WSS_Content. Exception Message = A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified). (ID 32018 Details: Unknown error (0x80131904) (0x80131904))
A network trace taken during the restore process will show failures to establish a connection from the WFE to the DPM server using UDP port 1434 (SQL Browser service) and TCP port 1433 or an ephemeral port if SQL is configured for Dynamic Port allocation.
So our last step will be to create a firewall rule that allows UDP port 1434 (for the SQL Browser service) and TCP port 1433 or an ephemeral port if SQL is configured for Dynamic Port allocation (for the SQL Server services) from the WFE to the DPM server. Once this is done, Optimized ILR should work as expected.
| Senior support Escalation Engineer | Management and Security Division