Migration Project Server (2010 to 2019) Issue 1 - Upgrading from 2013 to 2016 - Error encountered while migrating project data in Content database. The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users

I was intending to write a set of posts on the topic of migrating SharePoint and Project Server from 2010 on premise through 2013, 2016 to 2019 Azure, using SQL Managed Instance as the backend SQL Service.  This set of posts are still getting drafted and updated as we move through this cycle, but I came across a huge blocker this week that I wanted to post on, whilst it was fresh.

Our Azure migration machines (2013 and 2016) are:
- dual core 2.3ghz
- 28GB Ram

2013 server is Windows Server 2012 R2 running SQL 2012 SP1
2016 server is Windows Server 2016 running SQL 2014 SP1

The Project Server dataset we are dealing with here is c.100GB (50% archive data).

The uplift from SP/PS 2010 to 2013 went without a hitch over a 2.5hr period which was a huge win.  However when attempting to upgrade to 2016 we hit a hitch.

Firstly some background:  When you perform the Migrate-SPProjectDatabase command, the following will happen (well it certainly did to us)

- the ProjectWebApp DB from 2013 will increase in size by approx 150-200% (across MDF and LDF) whilst the database is "upgraded"
- once this step completes, the data is then transferred to the target content database, so this will become approximately the same size as the source Project Web App Database.

This means you will need AT LEAST 250-300% of the original DB size SPARE SPACE on your Azure VM for this process to work.  Now factor in backups and your disk requirements are going to be significant in the short term.

So our first migration failed due to disk space.  Ok fine, lesson learnt.

Our second run encountered the following issue during the Archive (ver) schema:


Error encountered while migrating project data in Content database. The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users


This error occurred repeated times, always at 4.63% 

If you have landed on this page, you probably know that there are limited hits for this error.  Most items send you down the road of looking at other users with large lock counts, which wasn't helpful for me, as this is a dedicated machine for this action, so there aren't any other users.  

The SQL machine was set to use max available memory (default Memory settings) so this felt like a possible area of consideration.  Also, SQL 2014 SP1 was installed but no CU, and SharePoint 2016 / Project Server 2016 was RTM (my bad!).

So, I implemented
- SQL Min Memory 10GB
- SQL Max Memory 18GB (of 28GB)
- SQL 2014 SP1 CU13
- SP/PS Patch KB4454111

Next I restored the SharePoint Content Database and Project Server application database from original backup TO NEW NAME (see footnote as to logic)

Then re-running the migrate-SPProjectDatabase step completed successfully!  It did take over 8 hours bit it got there in the end!

So - patch up, set memory correctly and you should be okay!

FOOTNOTE:
after all of this I had to failed attempts at this upgrade (more, but some used the -overwrite switch so dont count).  Each of those failed attempts left a residual database footprint in the SharePoint 2016 Farm (visible via Central Admin | Upgrade and Migration | Review Database Status.  

As far as i can tell there are no xxx-SPProjectDatabase commandlets beside the migrate one used here in SharePoint 2016 Management shell (as there are no project databases in 2016 onwards).  So i am kinda stuck with these until further hacking is done to clear them down.  However i was concerned that these MAY be causing issues for the further tests of the migration, which is why i restored the SQL DBs the final time with new names, just to be sure.