Project Server DR Failover SQL Cube Issue

Fortunately I do not often have to enact my customer’s disaster recovery plans, but when I do I am glad for things like SQL mirroring and SharePoint database fail-over.

No plan is perfect though, in this particular case Project Server OLAP cubes would refuse to build while failed over to the mirror server, giving only the following errors;

[8/01/2013 8:35 AM] Failed to build the OLAP cubes. Error: Error Adding DataSourceView ‘Project Reporting data source’ to database ‘FullCube1_Srv2’. Error: Error Adding Table ‘MSP_TimeByDay_OlapView’ to database. Error: Login timeout expired

A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

This problem is clearly caused by the SQL connection string, which in the configured cube looks like this:

Provider=SQLNCLI10;Data Source=primarysqlserver;Initial Catalog=ProjectServer_Reporting;Integrated Security=SSPI;Persist Security Info=False

As you can see the ‘Failover Partner’ parameter is missing, at first I thought that this was a classic case of not RTFM, if you neglect the steps detailed in the TechNet article (http://technet.microsoft.com/en-us/library/ff872145.aspx) on configuring SQL server mirroring for Project Server but instead only do the SharePoint configurations (which does get things online) then some Project Server specific connection strings are not updated.

The fix would seem to be pretty simple:

Set-SPProjectWebInstance …[insert all required parameters – ie all of them]… -PrimaryDBMirrorServer mirrorsqlserver -ReportingDBMirrorServer mirrorsqlserver

(Reference: http://technet.microsoft.com/en-us/library/ff607532(v=office.14).aspx)

HOWEVER, this does not appear to fix the cubes, which will continue to fail with the above error on build! The above steps do update the Sample report data sources in the BI site, but even if you create a new cube it still builds without a ‘Failover Partner’ configured:

To get to the above open SQL Management Studio and connect to your Analysis Server, then from the Cube –> Data Sources properties you can edit the data source used.

The good thing is that there is a temporary workaround while in a failed-over scenario, just manually update that connection string with the correct failover partner. The problem with this is that each time PWA builds the cube this change will be overwritten, but you could automate that if need be (or disable PWA build and schedule the build in SSAS directly).

Hurrah! Now I can get back to my day job.