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:

clip_image001[6]

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.

Update: Reporting from SharePoint lists with SQL2008R2

A long time ago (it seems) I blogged about reporting directly from the SharePoint workspace sites using SQL Reporting Services to get around the limitations with the reporting database refresh. Using XML provided a neat method for pulling data directly from all the out-of-the-box lists such as Risks and Issues, as well as other custom lists.

Unfortunately setup and maintenance was difficult requiring a little knowledge of web services as well as some XML syntax, but fortunately now with SQL Server 2008 R2 things have improved drastically!

What’s New: Now you can directly create ‘Microsoft SharePoint List’ data sources!

See the screenshot:

clip_image001

Better yet when you create a Data Set using the above data source the Query Designer is completely new:

clip_image002

Finally no more messing around with column names using XML and having to have specific views configured in your SharePoint sites.

That should make things much easier!

 

BTW. Lastly it’s definitely worth mentioning that the above also works with Report Builder 3.0!

How to Check the Version of SharePoint / Project Server Installed

It seems SharePoint 2010 gives a nice new way to say exactly what versions of the binaries are installed on any given server in your farm, this is a great enhancement from 2007 which effectively required a Windows Explorer properties check at times to be absolutely sure that all the servers had been updated!

Find it in Central Admin by;

  1. Open Central Admin go to Upgrade and Migration

clip_image001

  1. Select Check product and path installation status

You should see something like the following:

clip_image002

Specifically if you scroll down you will see the project server section under each server in the farm, and in my case you can see the RTM version number plus the KB2394322 (October 2010 CU).

clip_image003

Nice.

 

FYI that SQL method still can come in handy (when migrating for instance) so if you’re not familiar with it here it is:

USE ProjectServer_Reporting

SELECT * FROM dbo.Versions

Not exactly as much detail as the above method, but it does confirm what version server your databases should belong to.

SQL Reporting Services 2008 R2 Required for SharePoint 2010 Add-in

Found this one out the hard way this week; it seems that currently the only supported version of SSRS for the SharePoint 2010 SRS Add-in is SQL 2008 R2. At least one forum poster from MSFT has confirmed this.

Annoying as it can be surprisingly hard enough to convince customers to install Windows 2008 let alone upgrade their SQL servers! Ah, well at least SRS can be separated from the SQL DB servers making it a bit easier.

Update 12/07: This has now been addressed by Microsoft in the SQL 2008 SP1 CU8 release which adds support to reporting services 2008 to work with the R2 add-in.

Quickly Updating the Default BI Data Connections

As you’ll quickly find in Project Server 2010, if you want to use the default Business Intelligence Center data connections then in order to include your own custom fields, then you’ll need to update the connectors. Here’s a quick guide on doing that to get to that data for your first Project Status Report.

Before I get into it don’t forget that the OLAP connections are automatically created, so in many cases you can use those, but if you’re like me and prefer direct reporting from the Reporting Database then you’ll have to create your own connections.

Step 1: Start from an existing connection file

For this I am going to use "Project Server – Project and Task Data" from the default location "BC CenterData Connections – English (…)", find the existing file and save it somewhere locally using the Send to – Download a Copy option.

clip_image001

Now open that ODC file in Notepad and you’ll see the XML contents and hopefully the important part the section labelled <odc:CommandText> where the SQL "SELECT …" statement is, that’s what we need to change to include our custom fields.

Step 2: Add your Custom Field details using SQL Management Studio

Using SQL Management Studio is the easiest way to confirm that you have the right field names to add to the query, to do that find your ProjectServer_Reporting database and in particular what you will want is the View named dbo.MSP_EpmProject_UserView (or one of the others if you want task or resource fields). Select the view and using the ‘Select Top 1000 Rows’ option to generate a SELECT query returning all data.

 

clip_image002

You’ll end up with something like this:

/****** Script for SelectTopNRows command from SSMS ******/

SELECT TOP 1000 [ProjectUID]

,[ProjectName]

[ …. Snip lots of built in fields….]

,[ProjectBaseline10Duration]

,[Project Departments]

,[Sample Business Need]

,[Sample Areas Impacted]

,[Sample Proposal Cost]

,[Sample Compliance Proposal]

,[Sample Assumptions]

,[Sample Goals]

,[Sample Post Implementation Review Date]

,[Sample Post Implementation Review Notes]

,[Sample Proposed Start Date]

,[Sample Proposed Finish Date]

,[Sample Approved Start Date]

,[Sample Approved Finish Date]

FROM [PWA_ProjectServer_Reporting].[dbo].[MSP_EpmProject_UserView]

The nice thing is in the views all the custom fields are listed last, as you can see from above the out of the box ‘Sample’ fields are all there to see. So next you just have to copy those field names into the ODC file opened earlier and save your changes.

Step 3: Update the ODC file

The only catch with updating the ODC file as you probably noticed is the format includes the view or table name prefix, such as:

MSP_EpmProject_UserView.ProjectBaseline10FinishDate as [ProjectBaseline10FinishDate],
MSP_EpmProject_UserView.ProjectBaseline10Duration as [ProjectBaseline10Duration],

So all you need to know is that unless your changing the name you don’t need the bit after ‘as’, so if you search and replace to prefix each of your custom fields to add the "MSP_EpmProject_UserView." before the "," so you end up with something like:

,MSP_EpmProject_UserView.[Sample Business Need]
,MSP_EpmProject_UserView.[Sample Areas Impacted]
,MSP_EpmProject_UserView.[Sample Proposal Cost]

Now the last step is to paste those lines into the ODC file in Notepad, but note that the commas are at the start not the end, so you just need to make sure that when you paste the new lines that you have just one comma between each line except for the last line before the "FROM". You should end up with something like this:

MSP_EpmTask_UserView.TaskBaseline10Duration as [TaskBaseline10Duration],
MSP_EpmTask_UserView.TaskBaseline10DurationString as [TaskBaseline10DurationString],
MSP_EpmTask_UserView.[Health] as [Health]
,MSP_EpmProject_UserView.[Sample Business Need]
,MSP_EpmProject_UserView.[Sample Areas Impacted]
,MSP_EpmProject_UserView.[Sample Proposal Cost]
FROM
dbo.MSP_EpmProject_UserView
INNER JOIN dbo.MSP_EpmTask_UserView ON
MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID
ORDER BY
MSP_EpmProject_UserView.ProjectName,
MSP_EpmTask_UserView.TaskIndex,
MSP_EpmTask_UserView.TaskName
</odc:CommandText>

Step 4: Upload the new ODC file to your Data Connection library

Now your just about done, save the file and back in your BI Center upload the file to a new location, make sure that you don’t try to overwrite the default file as those may be replaced by future service packs, I upload them directly into the Data Connections library.

Once uploaded you can now use them in Excel as normal. You might want to update or copy the existing templates and edit the data connection properties to point to this new ODC file, or otherwise just add them in as you normally would and carry on..