My dance with the User Profile Service

So I tried to deny it. There’s no way I’d ever have to rebuild the User Profile Service. Well . . . I was wrong.

The memberships tab in users’ My Sites wasn’t updating. While this really wasn’t a big deal, and the confusion around that particular tab abounds, it’s best to fix something regardless of your personal feelings about it. I opened a case with Microsoft and waited for their call.

At first the engineer figured it was a simple stsadm command of:

stsadm -o sync -deleteolddatabases 1

No dice.

We danced around and tried a few more things: clearing config cache, checking permissions, toggling the service on various boxes. All to no avail.

And then he dropped the bomb: “We’ll have to recreate the Profile Service.” I got a huge knot in my stomach. Everyone I ever talked to said that you should avoid this at all costs. Now I know why.

It’s a fairly straight forward process to delete the service app.  A Google search will give you lots of tips, blog articles, and technet articles on how to achieve this. Basically you stop the User Profile service on each box along with the Timer Service and Admin Service. Then delete the old service.

Here’s where things got really fun. </sarcasm> Little did I know that the Profile and Social DBs would be blown away in addition to the Sync DB. In retrospect there is a checkbox that asks if you would like to delete all associated DBs. I checked the box and in hindsight I shouldn’t have. That would have just deleted the Sync DB; probably the only problem in this case. I would recommend just deleting Sync DB at first. Worst case scenario you have to go through the process again and check that box the 2nd time. Better than having to recreate profile data like I did.

Recreating the Service is relatively simple as well. Click New > User Profile Service in the Manage Service Apps screen in Central Admin. Name your new app pool and databases. Make sure to use the same service account. Then recreate the My Site Host. Thankfully the Host sits in a different database so I didn’t have to recreate the Host. You just have to tell the Service App the URL to the existing My Site Host. Then click create. Create the Sync Connection with Active Directory (or whatever authority you use to sync up to). Then recreate any custom attributes.

You’ll have to be sure to go into your user facing web apps and add the new User Profile to the service connections. If you don’t, users won’t be able to see their name in the upper right hand corner. Then you’ll have to give the correct permissions to the service app in the Manage Service apps screen. Note: You MUST give the search account “Retrieve People Data for Search Crawlers.” Otherwise Search won’t see My Sites. And finally, go modify the User Permissions in the service app itself and remove the ability for NT Authority to have personal sites (My Content). I use a Windows Security group to manage that so I added that back at this point.

Now kick off a Full Profile Sync and then a Full Search Crawl. Baring any complications everything should be back to normal.

At this point I had a working sync, the memberships were working correctly, and all was right with the world…kinda. I contemplated how to recreate the profile information. Enter PowerShell. Side note: If you haven’t checked out http://get-spscripts.com/ you should. Shocked this guy isn’t an MVP yet. Most of the following PowerShell was found on his site.

First, to recreate the Profile photos. Thankfully the My Site Host contains all the photos. I opened up the User Photos library and exported all of the thumbnails to an external hard drive. 2100 photos was around 3MB so not too bad there. I then went through and grabbed all the Large thumbnails. Once complete I fired up this script to clear out the LThumb from the filename:

gci -rec | ? {$_.name.Contains(“LThumb”)} | % {rename-item $_.fullname -newname ($_ -replace “LThumb”,””) }

With the cleaned up filenames, I imported the files to my Central Admin box. From there you’ll run this script:

function Upload-PhotosToSP
{
    Param (
           [parameter(Mandatory=$true)][string]$LocalPath,
           [parameter(Mandatory=$true)][string]$MySiteUrl,
           [parameter(Mandatory=$false)][switch]$Overwrite
           )

    #Get site, web and profile manager objects
    $mySiteHostSite = Get-SPSite $MySiteUrl
    $mySiteHostWeb = $mySiteHostSite.OpenWeb()
    $context = Get-SPServiceContext $mySiteHostSite
    $profileManager = New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($context)
    try
    {
        #Get files from local folder
        $localPhotosFolder = Get-ChildItem $LocalPath
        #Get User Photos document library in the My Site host site
        $spPhotosFolder = $mySiteHostWeb.GetFolder("User Photos")

        #Upload each image file and configure user profiles
        $localPhotosFolder | ForEach-Object {

            #Generate file path for upload into SharePoint
            $spFullPath = $spPhotosFolder.Url + "/" + $_.Name

            #Check if the file exists and the overwrite option is selected before adding the file
            if ((!$mySiteHostWeb.GetFile($spFullPath).Exists) -or ($Overwrite)) {
                #Add file to the User Photos library
                write-host "Copying" $_.Name "to" $spFullPath.Replace("/" + $_.Name,"") "in" $mySiteHostWeb.Title "..." -foregroundcolor Green
                $spFile = $spPhotosFolder.Files.Add($spFullPath, $_.OpenRead(), $true)
                $spImagePath = $mySiteHostWeb.Url + "/" + $spFile.Url

                #Get the domain and user name from the image file name
                $domainName = $_.Name.Split("_")[0]
                $userName = $_.Name.Split("_")[1].Replace($_.Extension, "")
                $adAccount = $domainName + "" + $userName

                #Check to see if user profile exists
                if ($profileManager.UserExists($adAccount))
                {
                    #Get user profile and change the Picture URL value
                    $up = $profileManager.GetUserProfile($adAccount)
                    $up["PictureURL"].Value = $spImagePath
                    $up.Commit()
                }
                else
                {
                    write-host "Profile for user"$adAccount "cannot be found"
                }
            }
            else
            {
                write-host "`nFile"$_.Name "already exists in" $spFullPath.Replace("/" + $_.Name,"") "and shall not be uploaded" -foregroundcolor Red
            }
        }

        #Run the Update-SPProfilePhotoStore cmdlet to create image thumbnails and update user profiles
        write-host "Waiting to update profile photo store - Please wait..."
        Start-Sleep -s 60
        Update-SPProfilePhotoStore –MySiteHostLocation $MySiteUrl
        write-host "Profile photo store update run - please check thumbnails are present in Profile Pictures folder."
    }
    catch
    {
        write-host "The script has stopped because there has been an error: "$_
    }
    finally
    {
        #Dispose of site and web objects
        $mySiteHostWeb.Dispose()
        $mySiteHostSite.Dispose()
    }
}

That loads the function. Now execute the following command to upload the photos to each profile and create the thumbnails.

Upload-PhotosToSP -LocalPath "C:InstallPhotos" -MySiteUrl "http://portal/personal/MySite" -Overwrite

Photos recreated. Now on to the profile information. I had my DBA recreate the old Profile DB on my DEV environment. That allowed me to query it for the information I needed.

SELECT NTName,SecondaryVal FROM dbo.UserProfile_Full up
        INNER JOIN dbo.UserProfileValue upv ON up.RecordID = upv.RecordID
        WHERE PropertyID = 5005

The two keys to this SQL query are the SecondaryVal and the Property ID number. Changing the Property ID gives you different profile fields:

• 5005 – Ask Me About
• 5006 – Skills
• 5007 – Interests
• 5015 – Past Projects
• 5016 – School

Changing SecondaryVal to PropertyVal and using Property ID 16 gets you the “About Me.” Query for each and then create a CSV for each field in the profile. Be sure to include the headers too. Keep NTName and rename each property header as follows (this will make sense later when we execute the PowerShell):

• Ask Me About – rename column to “SPS-Responsibility”
• Skills – rename column to “SPS-Skills”
• Interests – rename column to “SPS-Interests”
• Past Projects – rename column to “SPS-PastProjects”
• School – rename column to “SPS-School”
• About Me – rename column to “AboutMe”

Feel free to go through the profile information and remove any NULL’s. It appears that any profiles that had information in a field at one time and then removed it puts a NULL in the DB. Weird.

Once you have the files where you want them, import them to your Central Admin server then use the following PowerShell to import the data back to the profiles:

#Set up default variables
$csvfile="c:[filename].csv"
$mySiteUrl = "http://profile"
#Attribute you're editing in the User Profile
$upAttribute = "SPS-Responsibility"
#Column in CSV you wish to import into Profile
$exceldata = "SPS-Responsibility"

#Get site objects and connect to User Profile Manager service
$site = Get-SPSite $mySiteUrl
$context = Get-SPServiceContext $site
$profileManager = New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($context)

#Create Lists from each item in CSV file
$csvData = Import-Csv $csvfile
foreach ($line in $csvData)
{
	#Check to see if user profile exists
	if ($profileManager.UserExists($line.NTName))
{
	#Get user profile and change the value
	$up = $profileManager.GetUserProfile($line.NTName)
	$up[$upAttribute].Add($line.$exceldata)
	$up.Commit()
}
else
{
	write-host "Profile for user"$line.NTName "cannot be found"
}
}

#Dispose of site object
$site.Dispose()

You’ll have to do this for each CSV. In my case this meant I had to do it 6 times to recreate each profile field. In the coming weeks I’ll probably go through and recreate the other profile fields but this is enough to get things back to 99% normal.

And that was my dance with the User Profile Service.