How to Migrate Social Comments to New URL

One of our clients had a requirement to move their intranet portal to a new URL. One of the things that went missing after the URL migration were “social comments” for which we used “Social comments component” to add commenting functionality to custom page layouts for news articles.

Comments Break Down

For social commenting, SharePoint uses Social database which is a part of the User Profile Service Application. The social database contains 2 tables that are of interest to us. The first one is the “URLs” table and the second one is “SocialComments” table.

The SocialComments table stores all comments that were added to any SharePoint site and just has a reference to the URLs table. The URLs table is a master table which stores all URLs used in any kind of social features. 

To get all comments’ URL we need to run query:

select * from Urls

This query will return all URLs used in social features. We can then copy URLs to a CSV file and write some PowerShell to migrate URLs to new ones. The script is as follows:

$csv=Import-Csv "C:\Scripts\Comments Fix\CommentsOldUrl.csv"

$upsProxy=Get-SPServiceApplicationProxy | ?{$_.typename -like "*user*"}

foreach($entry in $csv){
    $oldUrl=$entry.url
    $newUrl=$oldUrl.replace("http://dev.local","https://dev.com")
    Move-SPSocialComment -ProfileServiceApplicationProxy $upsProxy -OldUrl $oldUrl -NewUrl $newUrl


}

Hopefully this will help someone when changing URLs of your web applications and will make the process easier.