Speaking at the Project Conference

imageI am pleased to confirm that I will be speaking at the forthcoming Project Conference in Anaheim in February 2014. For those of you that haven’t been to a Project Conference, I absolutely recommend you do your best to get to one, the quality of speakers, presentations, networking and all round Project and SharePoint knowledge under one roof is second to none.

Once again I will be representing Nintex, a silver sponsor of the conference and presenting a session entitled:

Implementing your organisational process in SharePoint, Project Server and Office 365 with Nintex

With SharePoint and Project Server 2013, organisations have never before had such a rich platform to manage projects, ranging from lightweight project sites in SharePoint through to Project Server 2013 and even Office 365. Ensuring this platform can be customised to meet your exact process requirements is essential to get the most out of your investment. In this session you will learn how Nintex products can be leveraged to quickly, simply and effectively enhance and augment SharePoint & Project Server 2013 to meet your organisational needs.

I am hard at work building some awesome demos and content to knock your socks off. So please make sure you get along to see how Nintex can help.

It’s not too late to register, so head on over to www.msprojectconference.com  and I hope to see you in Anaheim.


Migrating PS 2010 to PS 2013 Walkthrough

CogsIn this post I am going to take you through the process of migrating Project Server 2010 into Project Server 2013.

Usually there are two ways of doing a migration, either an in place upgrade, where if you were particularly brave, you would take your production system and then run the installer for the new binaries on it to upgrade. I say ‘Brave’ because frequently this method of upgrade would be fraught with danger, not giving you sufficient options for dry runs or rolling back.

The other option was a database attach method, where you would build up your target environment on the new version, then migrate the databases from the old version and ‘do the upgrade’. With Project Server 2013 and SharePoint 2013, Microsoft have finally stopped supporting the in place upgrade, which in my opinion was one of the best moves ever. So for this post, we will be walking through the DB attach migration method, where we will take the four Project Server databases and the associated SharePoint content database and migrating that into Project Server 2013.

Now for the purposes of this walkthrough, I am going to assume the following:

  • You have already built a SharePoint 2013 and Project Server 2013 On Premises solution and performed the base configuration (accounts, binaries, install accounts and run the config wizard)
  • You have created a Project Server 2013 Service App in Central Administration
  • You have started the Project Service on the various servers.
  • We are only migrating the Project Server data and associated content (PWA site and project workspaces), we will not be migrating any additional SharePoint content you have in your source farm
  • You are not changing domains as you migrate the data.

Oh and most importantly, this walkthrough is for instructional purposes only, I accept no responsibility if this doesn’t work and corrupts your data. Backups and snapshots are your friends but no replacement for testing this time and time again before doing it for real.

Backup the Project Server 2010 source environment

To start with we need to back up your PS 2010 environment, ready to migrate it over to the target 2013 environment.

In SQL Server, choose to backup the four Project Server databases and the content database that holds the PWA site collection and Project workspaces. To do this, right click on the database name, choose Tasks and then Backup.

Backup Databases

Now in a real production migration scenario there are a number of other options you may need to consider, including outage notices, restricting users from accessing the system during the migration etc etc. I am not going to cover those here. This is just to give you a broad walk through of the process.

Restore the databases to the Project Server 2013 target environment

Restore the five databases backed up previously to the Project Server 2013 database server using the following method:

Right Click on the Database node and click on Restore Database…

Restore Database

Choose Device (1), select the location where the database backup files are located (2), add the backup device (3), click ok (4) and ok (5) again to commence the restore.

Restore Database - Devices

Repeat the above for each of the databases to be restored.

Once the files have been restored, ensure the databases have the correct permissions to allow the upgrade process to be performed, in my case this was granting my setup account access db_owner access.

Set Database Permissions

On the target farm, open up a SharePoint Management Shell in Administrator mode (ensure you have logged onto an account with sufficient privileges to perform the upgrade).

Test and Mount the SharePoint content database

The first step in the migration process is to mount the SharePoint content which includes the PWA site content, the Project Detail Pages, and Project Workspaces that were created under the PWA site and any custom lists or libraries you may have added.

Before doing the actual migration, 2013 provides a couple of handy PowerShell commands to ‘test’ the databases against the target environment before performing the migration. In this case, the test command will analyse the SharePoint content database and highlight any items referenced in the database that may not be present in the target environment, like missing web parts, features etc.

To run the test command, enter the following in the Management Shell prompt:

Test-SPContentDatabase -Name <databasename> –WebApplication <web application name>

(of course replacing the items in the ’s with your values). In my example I entered the following:

Test-SPContentDatabase -Name PWA_WSS_Content_80 –WebApplication http://demo2013

and received the following results:

Test-SPContentDatabase output

Here the test command has identified a number of issues in the content database including missing assemblies and in this case that the content database is a Classic mode (the default for 2010) and the target web application is in Claims mode (the new default in 2013) and provides some steps to rectify it.

Notice at the top you can also see whether the problem found would block the upgrade or is an error. The goal of this step is to test, identify and then rectify any conditions that may impact the upgrade process as you can see below, the test command can be pretty comprehensive in the info it provides.

Once you are happy that all the potential upgrade blockers have been addressed then the content database can be mounted for real with the following command:

Mount-SPContentDatabase <databasename> –WebApplication <web application name> -NoB2BSiteUpgrade

The final flag is quite important, it signifies that you are upgrading a SharePoint 2010 database to SharePoint 2013.

In my example I entered the following

Mount-SPContentDatabase PWA_WSS_Content_80 –WebApplication http://demo2013 –NoB2BSiteUpgrade

Mount-SPContentDatabase output

The mount process can take some time to complete depending on the amount of content in the database. Once its completed, all that is required is to make sure your account has access to the PWA site collection you’re upgrading using the following command:

Set–SPSite -Identity <SiteCollectionName> -SecondaryOwnerAlias <account>

You can check if this command was successful by viewing the Site Collection Administrators through Central Administration.

Site Collection Administrators

In this migration the test command identified that we were attaching the classic database to a claims based database, so it is also necessary to migrate the users in the content db to their claims equivalent. To do so, enter the following Powershell command:

(Get-SPWebApplication <web application url>).migrateUsers($true)

Migrate Classic to Claims Users

Test and Upgrade the PWA Site

Before we attach the Project databases, we need to perform the actual upgrade of the PWA site, this is not performed as part of the SharePoint content database mount above. Once again there is a handy test PowerShell command that can be leveraged:

Test-SPSite –Identity <url of the PWA Site to test>

In my case this would be:

Test-SPSite –Identity http://demo2013/pwa

which will return any errors or warning that may cause the upgrade to fail:

Test-SPSite output

Again, the goal here is to identify any errors or conditions that may cause your upgrade to fail. In this case, I only have two warnings, so I am happy to continue and upgrade the site using the following command:

Upgrade-SPSite –Identity <url of the PWA Site> –VersionUpgrade

So in my case this would be

Upgrade-SPSite -Identity http://demo2013/pwa –VersionUpgrade

Upgrade-SPSite output

The important piece here is the –VersionUpgrade flag, this ensures the PWA site will be upgraded from Project Server 2010 to Project Server 2013 and is ready for the Project databases to be attached.

Convert the Project Server Databases

With Project Server 2013, there were a number of massive changes in the infrastructure and plumbing designed improve performance and maintainability. One of these key changes was the consolidation of the number of Project Server databases, from four down to one, making it much easier to maintain multiple PWA instances and stopping the proliferation of databases that you would get with Project Server 2010.

As part of the migration, it is necessary to perform a consolidation of the Project Server 2010 databases, again using a new PowerShell command:

ConvertTo-SPProjectDatabase -WebApplication <WebApplicationName> -dbserver <DatabaseServerName> -ArchiveDBName <ArchiveDBName> -DraftDBName <DraftDBName> -PublishedDBName <PublishedDBName> -ReportingDBName <ReportingDBName> -ProjectServiceDBName <ProjectServiceDBName>

On pressing enter, you will be asked if you want to convert the databases, choose yes.

ConvertTo-SPProjectDatabase output

Once completed, checking the SQL Server will show the consolidated database, in this example the ProjectServiceDB has been created.

Under the covers, the concept of separating the various draft, published, reporting and archive data is still present, but instead of four separate databases, there is now just one database with four schemas (dbo for reporting, draft, published and ver).

ProjectServiceDB Schemas

Mount, Test and Upgrade the Project Service Database

Next we need to mount and test the freshly created Project Service Database to ensure there are no issues which may impact the database being upgraded. To do so, enter the following command:

Mount-SPProjectDatabase –Name <ProjectServiceDBName> –WebApplication <webapplicationname>

in my case this will be:

Mount-SPProjectDatabase –Name ProjectServiceDB –WebApplication http://demo2013

Mount-SPProjectDatabase Output

Once mounted, the database can be tested as follows:

Test-SPProjectDatabase -Name <ProjectServiceDBName>

in my case this will be

Test-SPProjectDatabase –Name ProjectServiceDB

This will check various aspects of the newly consolidated database, including the schema version and things like the security roles.

Test-SPProjectDatabase Output

Once you are happy there are no UpgradeBlocking errors, you can then proceed to upgrade the Project Service Database to the new 2013 schema using the following command:

Upgrade-SPProjectDatabase -Name <ProjectServiceDBName> -WebApplication <webapplication to mount against>

in my example this would be:

Upgrade-SPProjectDatabase -Name ProjectServiceDB -WebApplication http://demo2013

Upgrade-SPProjectDatabase Output

Again, this PowerShell command will modify the database, so you need to answer the confirmation prompt in the PowerShell window.

Mount, Test and Upgrade the ProjectWebInstance

Now that the PWA site collection has been upgraded, the Project Server databases consolidated and had their schemas updated (if required), the next step is to mount, test and upgrade the ProjectWebInstance. This is effectively the wiring up of PWA site collection to the Project data.

As with the other steps in the upgrade, there are three components, mounting the database so Project Server and SharePoint know about it, Testing it for potential issues and then performing the upgrade of the Project data itself.

To mount the ProjectServiceDB, enter the following PowerShell command:

Mount-SPProjectWebInstance –DatabaseName <ProjectServiceDBName> -SiteCollection <url of the PWA site>

so in my example this will be:

Mount-SPProjectWebInstance –DatabaseName ProjectServiceDB -SiteCollection http://demo2013/pwa

Mount-SPProjectWebInstance Output

The mount process shouldn’t take very long at all to complete.

To test the ProjectWebInstance, use the following PowerShell command:

Test-SPProjectWebInstance –Identity <url of the PWA site>

In my example, this would be:

Test-SPProjectWebInstance –Identity http://demo2013/pwa

Like the other test commands, the output of this command will show if there are any potential issues that would stop the ProjectWebInstance being upgraded

Detailed Test-SPProjectWebInstance output

As you can see in the screenshot above, the default output is not so useful, but it does show if there is likely to be an issue. If you want to see a little more detail and stop the unhelpful ‘…’ truncation, modify the command a little to output to a text file where you can see all the info:

Test-SPProjectWebInstance –Identity http://demo2013/pwa | Format-Table -Wrap -AutoSize | Out-File -FilePath c:\output.txt

This will result in:


Once you are happy with the results of the test command, we can perform the actual upgrade using the following:

Upgrade-SPProjectWebInstance -Identity <Url of the PWA site>

in my example this would be:

Upgrade-SPProjectWebInstance -Identity http://demo2013/pwa

Upgrade-SPProjectWebInstance output

You will be prompted if you want to perform the upgrade, answer yes. When the prompt comes back you are nearly there.

Finally, now that all of the various databases have been successfully upgraded and wired up, all that is required to do is to make sure the PWA features have been activated in the PWA site (things like . To do so, enter the following command:

Enable-SPFeature -Identity pwasite –URL <url of the PWA site>

in my example this would be:

Enable-SPFeature -Identity pwasite –URL http://demo2013/pwa

And that’s the primary content migration completed.

But we’re not there just yet….

Now that all of the various databases have been migrated, tested, upgraded and wired up, just like in Project Server 2010, there are a few post migration tasks you will need to perform.

In all the migrations I have done from 2010, the first task post migration is to ensure the administrator account of the PWA site we have just migrated is correct. To check this, in Central Administration go to the Manage Service Apps and choose your Project Server Service App. In my case I saw something like the following showing that the Provisioning had yet to be completed successfully, even though I had completed all the steps above.

Manage Project Web Apps - Warning

Open the context menu and choose edit, the properties for the PWA instance will be shown.

Edit Project Web App - Change Administrator

Here you can see the administrator account is incorrect and still showing the admin account of the source 2010 environment.  The fix is really simple, enter your target administrator account details and press Edit. After a little churning and ‘Waiting for Resources’ you should see the following and can now get into your PWA instance.

Manage Project Web Apps - Provisioned

The last thing you will need to do is to perform a bulk update of the Project Sites to reassociate with the target server name and to set up the various content types.

To access the Bulk Update Connected Project Sites feature (as it has been renamed in 2013), choose to Manage the Project Web App we just migrated in the Manage Project Web Apps screen above, this will bring up the instance settings for configuration.

Manage PWA Settings

Click on Bulk Update Connected SharePoint Sites and select the site paths and most importantly the ‘Update Content Types’ and ‘Synchronize site Permissions’ options and click on Update.

Bulk Update Connected SharePoint Sites

And that’s it…

All being well you will now have a fully working migrated Project Server 2013 instance, with all the data you had in 2010 successfully migrated over into 2013.

Completed PWA Migration

Of course, in the real world it won’t be this plain sailing, there will be missing features and web parts that you will either have to deactivate or remove, there may be multiple content db’s for your SharePoint content or you may run into errors because you missed or skipped a step. My advice, as always is to test your migration, test it several times and then test it again.

In the next few posts, I will look at some other common migration  scenarios, including porting your data between a Production and Dev/Test 2013 environment, migrating data between environments of different patch levels and taking a look at some common errors you may run into during all of these, and how you can fix them.

Microsoft PowerShell Command Builder

At the SharePoint Conference in Anaheim, Microsoft released a pretty nifty PowerShell Command Builder for SharePoint. Whilst it doesn’t look to support Project Server PowerShell commands just yet, it does support SharePoint Server 2010 which as Project Server practitioners we all need to roll our sleeves up and do something to now and again.

PowerShell Command Builder Design Surface

The tool itself uses a handy drag and drop interface to select what you wish to do and then prompts you to enter the relevant information for the command. When your finished you can simply copy it to your clipboard to paste into the SharePoint PowerShell window.

PowerShell Command Builder - Add Farm Solution

The SharePoint PowerShell Command Builder is built in Silverlight 4  and can be accessed at http://www.microsoft.com/resources/TechNet/en-us/Office/media/WindowsPowerShell/WindowsPowerShellCommandBuilder.html. There is also a handy getting started guide here.

Update: I just noticed that if you right click on the Command Builder, you get the option to install it locally.

Install on your computer

Project Demo Image–Excel Services problem after SP1 upgrade

Excel Services ErrorAnother Update: Thanks to SeaMonkey76 in the MSDN forums pointing out that this issue is fixed in the October 2011 SharePoint CU (http://support.microsoft.com/kb/2596582), so get patching :)

Update: A couple of readers have pointed out that this behaviour is is the norm where there isn’t a root site and it’s best practice to implement one. I totally agree and have run into situations where things like InfoPath have ceased to work due to this. The purpose of this post is more for those people that run into this error in the demo image, after an upgrade and can’t figure out why. It took me a little while to track it down, so I just through I would share.

I am posting this in case anyone else runs into a similar problem in Excel Services and to give some search engine index bait.

Following upgrading my IW demo image to SP1 + June CU, I noticed that Excel Services components in the Business Intelligence centre were no longer rendering and consistently showing the loading graphic.

Excel Services - Loading Graphic

On closer inspection, the browser was showing the following error:

Message: Syntax error
Line: 1
Char: 1
Code: 0
URI: http://project.contoso.com/_layouts/EwaStringsHandler.ashx/en-US?rev=V5cXKzVnvQRpcOxdnGD5cQ%3D%3D&flh=4sHCQGJUaMtQCBbbV6EmZw==

Checking the ULS logs showed these error messages:

An internal error occurred.    at Microsoft.Office.Excel.Server.MossHost.MossHost.Microsoft.Office.Excel.Server.Host. IEwaHost.IsSecureConnection()     at Microsoft.Office.Excel.WebUI.EwaCUIDataSource.EnsureDocument()     at Microsoft.Web.CommandUI.CUIDataSource.RunQuery(UIQuery query)     at Microsoft.Office.Excel.WebUI.EwaRibbon.QueryRibbonDataSource(CultureInfo uiCulture, String clientID, Boolean denormalizeImareUri)     at Microsoft.Office.Excel.WebUI.EwaStringsHandler.ProcessRequest(HttpContext context)


Watson bucket parameters: SharePoint Server 2010, ULSException14, 5f9be61a “excel services application”, 0e00178d “14.0.6029.0”, f5b5c9d6 “microsoft.office.excel.server.mosshost”, 0e001785 “14.0.6021.0”, 4d65e5e7 “wed feb 23 21:00:23 2011”, 000002d3 “000002d3”, MISSING, 4a6d3421 “nullreferenceexception”, 66326e39 “f2n9”

It seems that Excel Services now needs a site collection to be present in the root of the a web application or it will throw the above error. I am not sure when this behaviour changed, but given the IW Demo Image and Project Demo and Evaluation pack does not install a site at http://project.contoso.com it is necessary to create a blank site collection via Central Admin manually.

Once this is done, the Business Intelligence Centre should start to render the Excel Services components once again.

Native Project Server Support and the iPad. It works. Sort of.

With the introduction of Service Pack One, Microsoft have introduced Cross Browser support to the timesheet, task and risk and issue features of PWA (in addition to the existing support of the Project workspaces), for the following browsers:

  • Internet Explorer 9 (32-bit) on Windows 7, Windows Vista, and Windows Server 2008
  • Internet Explorer 8 (32-bit) on Windows 7, Windows Vista, and Windows Server 2008
  • Internet Explorer 7 (32-bit) on Windows Vista, Windows XP, and Windows Server 2003
  • Firefox 3.6.8+ on Mac OS X v10.6, Windows 7 (32-bit/64-bit), Windows Vista SP2, Windows XP SP3, Windows Server 2003 and UNIX/Linux
  • Google Chrome 6.0 on Windows 7
  • Apple Safari 5 on Mac OS X v10.6

What isn’t listed is Safari for iOS which the iPad uses which I was keen to see would work or not. Like many people, I purchased an iPad so I could sit on the sofa and catch up with twitter, RSS feeds, email and play Angry Birds without having to have a massive laptop burning a hole through my legs.  So the idea of being able to fill in my timesheets in Project Server using an iPad really appeals to me.

Anyway to cut a long story short, I thought I would wire up my SP1 + June CU VM to my home network and point my iPad over at PWA to see what would happen. To my surprise, instead of seeing the new cross browser error message (below)…

Project Server - Non Compatible Browser

I was granted with this Smile

PWA on iPad

Clicking through the cross browser pages (Tasks, Timesheets and Risks / Issues), the pages seem to render fine, both in portrait and landscape mode:

Timesheets on iPad

However the issues seem to start when you try and use the app.

Splitter Bar

To start with the splitter bar (as highlighted above) doesn’t seem to work via the touch interface, which if you have a lot of fields being displayed in the left hand pane, can make the screen unusable.  There is a potential workaround for this, by reducing the number of columns in the view, however these won’t be reflected until a new period is created.

More Timesheets on iPad

Scroll Bars

Like the splitter bar, the scroll bars don’t seem to operate as expected, but touching on the region outside of the actual scroll bar as marked in red below will work


On closer inspection the scroll bar itself is not a traditional scroll bar, instead it is one rendered in HTML as part of the JSGrid, which might explain why Safari on the iPad is having trouble interacting with it.

Keyboard sensitivity

Again, more of a limitation of the iPad, but when trying to enter text into a timesheet entry towards the bottom of the screen, the keyboard will fly out and obscure where the text entry is taking place.

Tasks and the iPad keyboard

One option is to run this in portrait mode, but that will have the side effect of hiding some of the columns of information.


Finally, the ribbon seems to be having a bit of a hard time when used on the iPad. When you pinch to zoom in, the ribbon tries to do it’s best and resizes as per the screen below. However when you zoom out the ribbon can take a while to realise. This isn’t a Project thing per se, and I would imagine it’s an issue with all SharePoint sites viewed via the iPad.

iPad Ribbon Confusion


As I said above, Project Web App on the iPad works, sort of. But it does have some limitations and annoyances which you will need to work around. As this isn’t an officially supported browser within SP1, if you use the iPad you will have to live with these, but kudos to the Project team for their excellent implementation of cross browser and not explicitly blocking the iPad.

Speaking at Tech.Ed Australia 2011


I am absolutely thrilled to announce I will be presenting alongside SharePoint MVP Brian Farnhill at Tech.Ed Australia 2011 a session entitled:

‘A SharePoint Developers Guide to Project Server 2010’

It’s just meant to be – Project Server and SharePoint server are better together, and this is the session that will show you why. Come and join us as we take you on a tour showing how the two products work together, and how you can deliver better solutions by integrating the two. Get to know how developers can leverage both of these great tools together and how you can deliver more value, what the features of the tools can offer and what you need to know as a developer to start solving real world business problems.

We are hard at work on some cool demo’s to show and it is shaping up to be an excellent session.

Tech.Ed Australia 2011 is on the Gold Coast between the 30th August and the 2nd September, registrations are now open. Find out more at http://australia.msteched.com/

Add the target attribute to Project Server quick links

Recently on the Project forums, a user asked if it was possible to add a target attribute to a menu item in the quick links of Project Server, i.e. the target=”_blank” to force the link to open in a new window. This capability can be pretty useful, especially when you want to provide links to non project server content that supports the use of the tool such as a PMO help site, or perhaps even your favourite EPM blog Smile

Unfortunately the Project Server quick link functionality doesn’t give you an option to set a target and no way to decorate a html anchor statement to insert one.

Project Server Quick Link


So how can we make this work?

Well my first thought was to look at using jQuery in a content editor web part on the page. The code required is relatively easy, in this case I have added a link to my blog on the PWA page via the quick links which I am going to target.

 <script type="text/javascript” src="SiteAssets/jquery-1.5.2.min.js"></script><script type=”text/javascript”>

$(document).ready(function () {
    $('a[href^="http://www.epmsource.com"]').attr({ target: "_blank", title: "Opens in a new window" });


The code above firstly provides a reference to the jQuery library, then targets the URL http://www.epmsource.com and adds the target=”_blank” and a title ‘Opens in a new window” to the link on the page.

On the surface, this looks like it’s addressed the need, but it only works on the page that the content editor web part was placed. To make sure the fix is on every page we either have to add a content editor web part to every page, which isn’t possible, or we need to add the code to every page using code, luckily this is pretty easy to implement using a SharePoint Sandboxed solution.  At it’s core, the module will deploy jQuery and the code in lines 3 to 5 into the PWA SiteAssets library, this is achieved by the module elements.xml file which looks like this:

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <Module Name="TargetBlankLinks" Url="SiteAssets">
      <File Path="TargetBlankLinks\jquery-1.5.2.min.js" Url="TargetBlankLinks/jquery-1.5.2.min.js" Type="GhostableInLibrary"/>
      <File Path="TargetBlankLinks\PopNewWindow.js" Url="TargetBlankLinks/PopNewWindow.js" Type="GhostableInLibrary" />

The important bits above is the Url statement which tells SharePoint where to put the file, and the Type statement that tells SharePoint it can drop the files into a document library, in this case the SiteAssets library. Once the code is deployed, all that is needed is to get the PWA page to reference the script files, this is done be a simple element file and the wonderful ScriptLink command.

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
		<CustomAction ScriptSrc="~Site/SiteAssets/TargetBlankLinks/PopNewWindow.js"

You’ll notice we reference the two files dropped into SharePoint via the module using the ScriptSrc statement, the ~Site bit, tell SharePoint to look in the current site path, so /PWA/SiteAssets, which is required as we are using a Sandboxed solution. Finally the Sequence statement allows us to define the order in which the scripts are loaded, making sure jQuery is available before our JavaScript code calls it. Finally all that is required is to tie all of the above in a feature and we can then build and deploy it to PWA.

Visual Studio 2010 Feature

Once deployed you should see the link specified has been targeted by jQuery and target=”_blank” and the ‘Open in a new window’ title added.  Of course if you need to add these target statements to any more links, simply add a new row to the PopNewWindow.js file.

I have uploaded a copy of the Visual Studio solution file to my SkyDrive account should you wish to have a bit of a closer look.