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 http://host.domain.com/Shared 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.
So what causes this? If you look at the content at http://technet.microsoft.com/en-us/library/hh858899.aspx titled "Error ID: 33330" it lists the following possible reasons this error is logged as the following:
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. 3. 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 Status: Failed 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.
Michael Vargo | Senior support Escalation Engineer | Management and Security Division
Get the latest System Center news on Facebook and Twitter:
App-V Team blog: http://blogs.technet.com/appv/ ConfigMgr Support Team blog: http://blogs.technet.com/configurationmgr/ DPM Team blog: http://blogs.technet.com/dpm/ MED-V Team blog: http://blogs.technet.com/medv/ Orchestrator Support Team blog: http://blogs.technet.com/b/orchestrator/ Operations Manager Team blog: http://blogs.technet.com/momteam/ SCVMM Team blog: http://blogs.technet.com/scvmm Server App-V Team blog: http://blogs.technet.com/b/serverappv Service Manager Team blog: http://blogs.technet.com/b/servicemanager System Center Essentials Team blog: http://blogs.technet.com/b/systemcenteressentials WSUS Support Team blog: http://blogs.technet.com/sus/
The Forefront Server Protection blog: http://blogs.technet.com/b/fss/ The Forefront Endpoint Security blog : http://blogs.technet.com/b/clientsecurity/ The Forefront Identity Manager blog : http://blogs.msdn.com/b/ms-identity-support/ The Forefront TMG blog: http://blogs.technet.com/b/isablog/ The Forefront UAG blog: http://blogs.technet.com/b/edgeaccessblog/
as to the "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" is there any official KB or document regarding this? I need to convince someone to implemenet our sharepoint on SQL2012 and not 2008R2
Don't forget to mention that after changing the identity of the SQL Server and Agent services, you must change two registry entries, as per social.technet.microsoft.com/.../dpm-2010-scheduled-jobs-disappear-rather-than-run
else, scheduled jobs could not be fired and the dpm console will not notice!!.