Resolving SharePoint User profile SYNC

 

The Issues:

Certain User profiles are out of sync , normally users notice that before admin ( as usual) .Reason being , the profile sync services don’t generate any errors or exceptions , or simply you don’t have logging enabled on your farm for any reason.

The most noticeable symptom is a profile image missing , or a change in the Profile Source (like last name or phone number ) that does not show up at the site level.

Another symptom: the profile information show up fine on the profile service (My Site , Shared Services etc.) but not on certain sites.

Note:

Please read the following carefully and clear you mind from all the Google , MSN , MSDN ,TechNet noise ,there are so many problems and many solutions ,yet if you understand how things are wired together , you will be able to fully clear the problem yourself, no code nor technical support needed.

Architecture

Profile Synchronization Timer Job

Quick Profile Synchronization Timer Job

Profile Sync Life cycle:

[1] The Profile Source is in general an AD entry, a SQL record etc.

[2] The Profile by default has some properties as in, First Name, Last Name, Name (that is more of the display Name) and AccountName . Also the Profile services allow you to extend the profile properties as needed, different companies have different custom properties as they need [as in Department, Manager, Skill, etc.]

[3] The unique ID on the profile database is the [AccountName] or account and all sync operations are based on this particular attribute.

[4] There are two SYNC operations that are responsible for getting the Profile information from the Profile Source to the site level (Site Collection to be more correct).

1. Profile Synchronization job : collect the profile from
2. Quick Profile Synchronization job : Push profile into User Information Lists for each site collection.

Each Site Collection has a unique hidden list (hidden for a reason so do not change it and let us keep it this way.) the list is named “User Information List” on the API you can access it by
RootWeb.SiteUserInfoList (just to explore and compare) .The List holds the information that we see on items like First Name and display name etc at the site collection level.

[5] Note that You can have a single SharePoint application with a single Content database or multiple Content databases, but at least one site collection per content databases, if the content database does not have any Site collection, you don’t need it. The Sync process iterate through each

We will go through the detection of the problem , reasons and the solutions.

First let us check the SQL configuration consistency.

Detecting using Stsadm:

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\BIN>

stsadm -o sync -listolddatabases 0

That will show the synched databases within 0 days. (Not the un-synched as it might imply) so be careful.

So what you see here is the list of successfully synced content databases.

So If you have a list of all the content databases these should match number and Id.

Again to get all the content databases bases from the Central Admin -> application management -> Content databases.

The SQL part of things:

Notes: Microsoft does not like using direct SQL queries against any of the SharePoint Databases for the following reason

Queries against Content Databases will create Locks and that will slow the site(s) down, also may cause other services like search and profile sync to lockup.

Microsoft will not support your SharePoint system if you use CUD (Create , Update , Delete) operations against that farm

But after working with this technology for so long , you get familiar with the sql configurations etc.

It is not bad nor harmful to query the configuration databases.

The Sync status can be found in the following tables

DB :SharePoint_Config , Table :SiteMap

DB: SiteSynch

USE SharedServicesIntranet_DB

SELECT Sync.LastSynch , Sync.ChangeToken , Sync.SchemaVersion ,Sync.LastChangeSynchSuccess , Sync.Moving , Sync.moving Deleted , Sync .Registered , AppID.Name AS db , CdbID . Name AS webapp , SiteMap.Path
FROM dbo.SiteSynch Sync

JOIN SharePoint_Config.dbo.SiteMap SiteMap ON Sync.SiteID = SiteMap.ID

JOIN SharePoint_Config.dbo.Objects AppID ON SiteMap.ApplicationId =AppID.Id

JOIN SharePoint_Config.dbo.Objects CdbID ON SiteMap.DatabaseId =CdbID.Id

The result of that Query should be something like this , should not have any Deleted or moved.

 

Causes:

[1] One of the content databases could be offline or detached at the SQL server level. (check the list of content DBs under Central admin – > application management -> content databases

[2] Duplicate Content database Ids (happens when you move content DBs around). (Query the contentdbs table on the config database.)

[3] The database was prepared to be moved but never moved, and hence became out of sync.

[4] The not enough trimming between the Profile Sync Job and the Quick Profile Sync Job.

Solutions matches the causes above :

[1] check the list of content DBs under Central admin – > application management -> content databases. Also check your SQL Server for all the list of content databases.

stsadm -o deleteolddatabases 0

[2] If you duplicate content database Ids , you will have to detach/delete and reattach [Operation feared by most , but the safest of all]

stsadm -o preparetomove –url http://Server/SiteCollectionUrl/ -undo

Read more here

http://technet.microsoft.com/en-us/library/ff628582.aspx

[3]You will have Un PrepareTomove …. Here is the command.

stsadm -o preparetomove –url http://Server/SiteCollectionUrl/ -undo

[4] The default timing is One hour for the Profile Sync Job and a one to few minutes for the Quick Profile Sync.

Those are controlled by the the following in order.

stsadm -o sync -SyncTiming <schedule(M/H/D:value)>

Sample :

stsadm -o sync –SyncTiming M:5 -SweepTiming M:1

I normally do that setting during debug

The Final solution :

This will fix any problem and all problems ( that I came across for all reasons )

The following sequence [for the databases that you want to sync]

stsadm -o preparetomove –url http://Server/SiteCollectionUrl/ -undo

stsadm -o deleteolddatabases 0

stsadm -o sync –SyncTiming M:5 -SweepTiming M:1 -IgnoreIsActive 1

wait for the next update ( and that should be under the Timer Job Status in Central Admin )

you should start see the fixed properties.

When done , fix your timing back to normal

stsadm -o sync –SyncTiming H:1 -SweepTiming M:5 -IgnoreIsActive 0