Thursday, January 22, 2009

Moving SharePoint My Links between SSPs

Background

Well I have had this ongoing annoying issue with a Shared Service Provider (SSP) that I described in this old posting. The issue was an AD account was removed, it had permission set to in the BDC, we were getting an error trying to remove it because if we did not the BDC index would fail. Anyways, the solution was either to buy DeliverPoint to delete this one dead account of the SSP or do something else. Our solution was to recreate the SSP which I discussed in this post. Well to make a long story, longer, our solution did not entail backing up and restoring the SSP because the error would still be there. So our solution was to rebuild the SSP and them move over it. This is how I got to here.

My Links Issue

Normally My Links in SharePoint was not a high priority item. However we found out after we had moved over to the new SSP that users had thousands of My Links set up. In this post, I discussed creating a separate web application for the SSP site and we moved over to a new SSP. One unexpected result was all of the My Links were now missing.

This was particularly confusing because when managing My Links, the user is redirected to My Sites URL for the SSP. We are using My Sites, I had thought this data was stored as part of the My Sites content database. Wrong – not the case at all. All of the My Links are stored in the SSP database. So the issue was how do we get the URLs out of the old SSP database and into the new one?

Resolution

We considered many options, but the best option that was going to move the data between the databases. So we restored the old SSP and found that the URL were stored in UserLinks table. In the UserLinks table, there is a column called RecordID. The RecordID column refers to the user account that the URL is for. Going to the UserProfileValue table, we say that this table stored the information that is retrieved from the Profile Search Service that is in the SSP.

Now the interesting thing is you cannot just insert all of the records in UserLinks table from one SSP database to another. The reason why is because the RecordID will be different on in each SSP database. This is because the Profile Search Service on the old SSP DB indexed different accounts over a year ago versus the new SSP with the fresh brand new index of user profiles we had in.

The following query will show you a report of how to reconcile the RecordIDs across the database.

SELECT UPV1.RecordID, UPV1.PropertyVal, UPV2.RecordID, UPV2.PropertyVal
FROM SharedServices1_DB.dbo.UserProfileValue as UPV1, SharedServices2_DB.dbo.UserProfileValue as UPV2
WHERE UPV1.PropertyID = 3 AND UPV1.PropertyVal = UPV2.PropertyVal

To resolve the issue, we ran this SQL Script to move the data over from one SSP database to another. One ramification is that if an account was removed between the creation of the old and new SSPs, the URLs from that removed account will not be copied over to the new SSP because the profile service search will not find the account.

The following SQL statement shows how to move the data over.

INSERT INTO SharedServices2_DB.dbo.UserLinks(RecordID, Title, GroupType, GroupTitle, URL, ContentClass, PolicyID, ItemSecurity)
SELECT UPV2.RecordID, UL1.Title, UL1.GroupType, UL1.GroupTitle, UL1.URL, UL1.ContentClass, UL1.PolicyID, UL1.ItemSecurity
FROM SharedServices1_DB.dbo.UserProfileValue as UPV1, SharedServices1_DB.dbo.UserLinks as UL1, SharedServices2_DB.dbo.UserProfileValue as UPV2
WHERE UPV1.PropertyID = 3 AND UPV1.PropertyVal = UPV2.PropertyVal AND UPV1.RecordID = UL1.RecordID

NOTE – Microsoft does not recommend making changes to data in the database. In this case, since it was a straight insert into an empty table with URLS, we were ok with moving forward…

4 comments:

David M said...

Rather than go through all that effort of moving databases (also unsupported), why not use the STSADM -o RestoreSSP for the old SSP, set all the new permissions on the restore and move the My Sites back. There is nothing wrong with running two SSP's and you don't lose your links, you also will fix all the issues the deleted account caused.

David McMillan, MCT

Jason Apergis said...

The reason why, if you look at the referenced posting, is that the SSP had to be completely rebuilt. We could not consider that option...

AccelSys said...

This works but if you have done a profile import before running this script on the new SSP then the RecordID's will be out of sync and you will place links to the wrong users.

If this happens you will have to come up with another SQL script to sync the recordID in both databases based on the new profile import.

Funky McNugget said...

While I did not run into the sync problem mentioned by AccelSys (the query is already intended to sync the recordIDs, so I don't see what the problem would be there), however I did run into a relatively minor issue with the query above that is easily correctable.

In the second query on the page (the one doing the INSERT), there's a WHERE clause stating "UPV1.PropertyID = 3".

That should be ANDED with a "UPV2.PropertyID = 3".

Basically, in that query we weren't saying what kind of data to look for in the UPV2 source, so it was picking up extra data - in my case, the "Manager" field (also formatted like an account name), so my manger's links ended up in all of his subordinate's lists!

ANDING a UPV2.PropertyID = 3 ensures you're only looking up the actual identity of the person with that RecordID - and not other fields such as Manager which will confuse the statement, and add additional links that don't belong.

This only affects users that actually have data in the Manager field, so is probably why most people won't run into (or notice) this.

Great article and SQL. Much appreciated!