Post June 2011 CU Move-SPProfileManagedMetadataProperty BUG - and the workaround

  • Article

If you're upgrading from SharePoint 2010 and following the post upgrade steps from Perform post-upgrade steps for a database attach upgrade (SharePoint Server 2010) , you'll need to upgrade your multi string values from your old 2007 SSP to 2010 MMS. 

 

Let's have a little background on why we have this. Here's a screenshot of the old values in the Responsibilities Field:

 

 

In previous 2007, I'd have to free text search for "SharePoint 2007" and again for "Migration" if I wanted to find this user.  If I wanted to search for both, our results could get skewed as users could have those terms in different order.  Is it possible?  Yes.  Is it easy?  No.  New with 2010 is the Managed Metadata service which eliminates this problem.  With the individual fields set to metadata, we enable the search refiners in SharePoint 2010 and the order is no longer an issue.  Is it simpler?  Absolutely.

 

While upgrading, there is a Powershell script to migrate the flat text field of the SSP 2007 to MMS in your new SharePoint 2010 environment called Move-SPProfileManagedMetadataProperty.  Unfortunately, if you are post June CU, there's a bug where you'll get this error message:

Move-SPProfileManagedMetadataProperty -ProfileServiceApplicationProxy <GUID> -Identity <Name of Field> -AvailableForTagging -TermSetName Responsibility
Move-SPProfileManagedMetadataProperty : UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
At line:1 char:38
+ Move-SPProfileManagedMetadataProperty <<<< -ProfileServiceApplicationProxy 9
1740c45-b8c2-4ade-85bb-344f2a4eca47 -Identity SPS-Responsibility -AvailableForT
agging -TermSetName Responsibility
    + CategoryInfo : InvalidData: (Microsoft.Offic...ProfileProperty:
   SPCmdletMigrate...ProfileProperty) [Move-SPProfileManagedMetadataProperty]
  , SqlException
    + FullyQualifiedErrorId : Microsoft.Office.Server.UserProfiles.PowerShell.
   SPCmdletMigrateDataForOneTaxonomicProfileProperty

 

If you BING search, you'll find that the Quoted Identifier is used to isolate quotes (") and apostrophes (') and this flag helps protect against SQL Injection attacks in SharePoint. You'll also find (as stated here https://social.technet.microsoft.com/Forums/en-US/sharepoint2010setup/thread/6fdb0ca3-5ac5-45ab-8663-af536e6dae24) that modifying the database works. THIS PUTS YOU IN AN UNSUPPORTED STATE AND SHOULD NOT BE DONE WITHOUT MICROSOFT SUPPORT APPROVING THE CHANGE.

 

"Ok Ryan, so what's the fix?" In short: we get around the Move-SPProfileManagedMetadataProperty bug by not using it! Enter the User Profile Replication Engine (from the SharePoint Administration Toolkit: https://www.microsoft.com/download/en/details.aspx?displaylang=en&id=20022). The User Profile Replication Engine (or UPRE) has a -DoUpgrade switch which migrates the 2007 strings to 2010 MMS. To make this work, you must have the old 2007 farm and new 2010 farm both online. My old 2007 MySite Host is https://vm-moss:10378/ and my new 2010 MySite Host is https://vm-sp2010:13965/

 

Here's the walkthrough (Note: my SharePoint 2010 environment is called VM-SP2010. My SharePoint 2007 environment is called VM-MOSS):

 

1) Install the SharePoint Administration Toolkit

2) Create an Managed Metadata Service (MMS) and User Profile Service (UPS) in the same proxy group (the same group is how the UPS knows which MMS to utilize)

3) Assign the proxy group to your 2010 MySite Host Web Application. You can see here that my Web App is "SharePoint 2007MySites - 13965" and I have the MMS and UPS assisgned

 

4) Make yourself an administrator and full control to the MMS and UPS at _admin/ServiceApplications.aspx

 5) Start the UPRE and type in this Powershell command:

get-spprofilepropertycollection -source <2007 MySite Host URL> | start-spprofileservicefullreplication -source <2007 MySite Host URL> -destination <2010 MySite Host URL> -MaxNumberOfThreads 3 -DoUpgrade

So, for my environment, I used:

get-spprofilepropertycollection -source https://vm-moss:10378/ | start-spprofileservicefullreplication -source https://vm-moss:10378/ -destination https://vm-sp2010:13965/ -MaxNumberOfThreads 3 -DoUpgrade

 

 And hit enter.  Wait for it to start enumerating profiles to insure you don't have any errors.  A full replication takes quite a while (1000 users on my environment took about 5 minutes).

 

6) Once complete, verify the multi-value property has been updated correctly by viewing a profile.  In this case, we'll use Administrator.

It works!

 

 

And there you have it.  If you have custom properties in 2007, you'll need to have the same custom properties in 2010.  The easiest way to do this is backup and restore your SSP database to 2010, then use it as the name of your Profile DB when you create your UPS.  That'll upgrade the DB to 2010 and bring your properties over.  From there,run a full import with UPRE following the steps above.

 

Problems with the UPRE need to be troubleshooted and then a recovery run.  Check out https://technet.microsoft.com/en-us/library/cc663011.aspx for steps to do that.

 

Hope this helps someone out there.  Any problems, let me know!