User Profile Synchronization issue

Introduction

User Profile keeps user information like account name, email address, first and last names, etc. of the users.

The same user-related information is stored in 3 locations – Active Directory, Shared Services Provider database and UserInfo tables inside content databases – and should be synchronized between all these locations. The synchronization is unidirectional regarding AD and bidirectional between SSP database and UserInfo tables.

Make notice, WSS installation doesn’t have Shared Services Provider on-board, only MOSS installation has it.

The primary source of user-related information is Active Directory (or any other compatible membership provider like ADAM, LDAP, etc.). Any changes in Active Directory will be propagated to User Profiles in Shared Services Provider database and UserInfo tables in content databases by means discussed later, but reverse synchronization never happens (from SharePoint t Active Directory).

Make notice, BDC can’t be the source of primary information, only as a provider for auxiliary information.

Initial acquiring

When the first time the user logs into a site, the information about this user is retrieved by SharePoint site directly from AD (or proper membership provider) and a new item is added to UserInfo table inside the content database where the site is stored. This way, the site learns all needed information (like account name, first/last names, email address, etc.) about the user.

The information stored in UserInfo table can be viewed/edited by end-users through OOTB’s My Settings pages (userdisp.aspx and useredit.aspx). The users can do it only with their own profiles, also SharePoint administrator could forbid even it.

The process of acquiring is not configurable in any way. Moreover, this process is started only ONCE for the pair {certain user, certain site}. On each consequential logins, the site looks into UserInfo table, successfully finds the information about the user and will NOT retrieve probably updated information from AD. So even if the user information is changed inside AD, the site will still use outdated data.

Another thing worse to mention is that SharePoint distinguishes “active” and “not active” users. If a user just logs into the site and navigates around it, then such user is treated by SharePoint as “not active”. On the other hand, if the user uploads documents or modifies list items or do any other form of contribution, then the user is treated as “active”. Returning back to the first visit: when a new record inside UserInfo table is added, this record refers the user as “inactive” user (there is “tp_IsActive” field in UserInfo tables responsible for that). Inactivity of the user prevents synchronization between User Profiles in SSP and UserInfo tables, but it will be discussed later. For now, just remember this piece of fact.

Once information about the user is stored in UserInfo table site, the information user membership (that the user participates in the site now) is pushed up to sync-related table in SSP databases. Also user membership information is pushed up to there each and every time when a user is added to some site. Later, when User Profile Synchronization time jobs will synchronize user-related information back from SSP database to UserInfo table, it will consider this membership and will sync with only sites which previously reported this user’s membership.

User Profile Import

User Profile service helps in the situation when user information is changed inside AD and has to be synced down to SharePoint. Once the information is changed, then it is synchronized down from AD to SSP database by User Profile Import timer job. This process can be configured in Central Administration -> Shared Services Provider -> User Profiles. The most important aspects of User Profile Import are:

• Import connections. They specify from where to import user-related information. For example, it is possible: a) to import from many sources, b) to import only from some sub OU of AD.
• Full Import and Incremental Import. It specifies whether import will pull information about all users or only about recently updated ones in AD.
• Import schedules. They specify on what time schedule User Profile Import runs. Usually Full Import runs every week, while Incremental Import runs every day.
• Mapping attributes. It allows having different names of certain user attribute in AD and SharePoint. For example, in AD account name is “distinguishable name”, while in SharePoint it is “account name”. Mapping is especially important in the case of multiple heterogeneous  sources.

Among other mapping attributes, there is “Replicable”. This particular flag is interesting in our case as it allows to disable pushing the attribute down from User Profile stored inside SSP database to UserInfo table inside content databases.

User Profile storage inside SSP could keep not only AD-based attribute properties, but also any custom properties not mapped to any AD-related attributes. Although it is possible to modify AD-based attributes manually, it is not recommended because the values will be overwritten on the next User Profile Import process.

Information stored in User Profiles can be modified:

• By SharePoint administrators, regarding all users, in Central Administration -> Shared Services Provider -> User Profiles.
• By end-users, regarding their own profiles, on special MySites pages. It is configurable what attributes can be edited by end-users. Also the users could be totally forbidden to modify their profiles.

User Profile Synchronization

Now it is time to synchronize user-related information (previously imported from AD to User Profiles in SSP database) with user-related information stored inside UserInfo tables of content databases.

This process is implemented as two timer jobs:

• Profile Synchronization,
• Quick Profile Synchronization.

Profile Synchronization timer job runs every hour (by default) and synchronizes all users, while Quick Profile timer job Synchronization runs every minute (by default) and synchronizes only users which have been changed since the previous run.

In the reality, it doesn’t mean that all-all-all changes will be synced down from User Profiles in SSP database to UserInfo tables in content databases immediately. These two jobs make rather limited amount of work in one run, postponing remaining work to the next runs. So if there are a lot of users to be synchronized between SSP database and UserInfo tables, then you have to wait considerable amount of time (many runs of both timer jobs).

Also it worse to remember that there are some cases when Profile Synchronization timer jobs limit their actions in different aspects:

• Profile Synchronization doesn’t work with attributes not marked as “Replicable” – they are not pushed down from User Profiles in SSP database to UserInfo tables of content databases.
• Profile Synchronization considers only users which membership in certain sites has been previously reported. Such membership is reported to SSP when user is added into the site. So for certain user, Profile Synchronization jobs try to synchronize this user information stored is SSP database with UserInfo tables of only sites where the user has been reported to have membership.
• Profile Synchronization doesn’t take into account users which are marked as “not active” (there is “tp_IsActive” field in UserInfo tables – see details above).
• Profile Synchronization doesn’t operate over the sites in content databases which were previously marked as “Moving” in SSP database by “STSADM –O PREPARETOMOVE” operation (this field marks a content database as being prepared for movement, see more details later in this article).

Parameters of Profile Synchronization time jobs can be configured by “STSADM –O SYNC” operation:

• SYNCTIMING option – it defines time interval for Profile Synchronization time job,
• SWEEPTIMING option – it defines time interval for Quick Profile Synchronization time job.

“STSADM –O SYNC” operation has some other options that could help in troubleshooting the problems related to user profile synchronization:

• LISTOLDDATABASES option – it shows the list of content databases that don’t have correctly synchronized user profile information during some time,
• DELETEOLDDATABASES option – it deletes sync-related information from such content databases. Don’t be afraid, the last operation deletes neither the content database itself, nor user information in UserInfo tables of them. The operation just wipes sync-related information out of the content database, so on the next profile synchronization, SharePoint will make full re-synchronization of user-related information and all sync-related information will be recreated from the scratch.

Issue with content database migration

As you can see the process of synchronization between these sources (AD, User Profiles in SSP and UserInfo tables) is rather complex and fragile.

It can be easily destroyed by the process of incorrect content database migration. The problem happens in following scenarios:

• The content database was detached and then attached back to the same farm.
• The content database was backed up and then restored back to the same farm.
• And similar scenarios.

Before the content database is detached or backed up, it should be prepared. There is “STSADM –O PREPARETOMOVE” operation. Practically all what this operation does is that “Moving” field for this site inside SSP database is assigned as TRUE. This field is simply a marker. This marker is taken into account in following situations:

• User Profile Synchronization timer jobs do not synchronizes user-related information with the sites in content databases marked as “Moving”,
• When the content database is restored/attached back, SharePoint checks this field. If the field is not TRUE, then SharePoint treats this content database as completely new one – from different farm.

In the latest case, SharePoint regenerates GUID of the content database being attached. Among other consequences of regeneration of content database GUID, there are 2 things to be noticed:

• SharePoint Search has to make full re-crawl of the content inside this content database (remember, SharePoint Search crawls the content by retrieving it directly from database, not by scratching rendered HTML pages).
• Profile Synchronization timer jobs try to repopulate UserInfo tables of the content database with user information taken from SSP database.

So if the content database was not correctly prepared for movement by calling “STSADM –O PREPRETOMOVE” operation, then Profile Synchronization timer jobs will try to insert information about users into UserInfo table of this just re-attached content database. But such information about these users is already present there. Boom! Lots of paired exceptions appear in Windows’s Event Log. The example of that paired exceptions:

Source: Office SharePoint Server, Category: User Profiles, Type: Error, Event ID: 5553

Failure trying to synch site {SiteGUID} for ContentDB {ContentDatabaseGUID} WebApp {WebAppGUID}. Cannot insert duplicate key row in object ‘dbo.UserMemberships’ with unique index ‘CX_UserMemberships_RecordId_MemberGroupId_SID’.

Source: Office SharePoint Server, Category: Office Server General, Type: Error, Event ID: 7888

System.Data.SqlClient.SqlException: Cannot insert duplicate key row in object ‘dbo.UserMemberships’ with unique index ‘CX_UserMemberships_RecordId_MemberGroupId_SID’ at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.Office.Server.Data.SqlSession.ExecuteNonQuery(SqlCommand command) at Microsoft.Office.Server.UserProfiles.WSSSynchSqlSession.SynchExecuteNonQuery(SqlCommand cmd, Boolean throwOnFail) at Microsoft.Office.Server.UserProfiles.WSSSynchSqlSession.SynchExecuteNonQuery(SqlCommand cmd) at Microsoft.Office.Server.UserProfiles.SiteSynchronizer.WriteChangeLogConsumed() at Microsoft.Office.Server.UserProfiles.SiteSynchronizer.Synch() at Microsoft.Office.Server.Diagnostics.FirstChanceHandler.ExceptionFilter(Boolean fRethrowException, TryBlock tryBlock, FilterBlock filter, CatchBlock catchBlock, FinallyBlock finallyBlock)

So the correct way for movement is to call STSADM –O PREPARETOMOVE on the content database before. But there is also one detail to keep in mind: the semantic of “STSADM –O PREPARETOMOVE” operation was changed after Infrastructure Update in year 2009. To avoid full re-crawling of the content database being restored to the same farm back (good example – restoration of content database from the backup), SharePoint does NOT regenerate content database ID since that innovation. It helps to avoid re-crawling, but as now “Moving” field is not paid any attention, then it means that “Moving” flag remains as “TRUE” preventing User Synchronization timer jobs to operate over this site! For such ugly cases, STSADM –O PREPRETOMOVE operation has a special flag – UNDO. It just clears “Moving” flag.

In the case, when you are in the situation having in hands already moved content database which was not correctly prepared, then there is a workaround for you – just call “STSADM-O PREPARETOMOVE –SITE-OldContentDb” on this already attached content database. It will refresh sync-related information. As some incremental user profile synchronization could already have happened to this content database, then it is recommended to run “STSADM –O SYNC DELETEOLDDATABASES” in order to have sync-related information up-to-date.

So the complete sequence of steps for this situation is:

1. Determine where configuration database of SharePoint is located. For it, navigate to Central Administration ->Operations -> Servers in Farm. On the page, the server hosting SQL Server with configuration DB will be mentioned (unfortunately, it shows only server name without name of SQL Server instance) and the name of SharePoint configuration database.

2. Determine the name of content database by its GUID. For it, execute SQL command on that SQL Server Instance with configuration database:

SELECT ID, Name
FROM [ConfigDB].[dbo].[Objects]
WHERE ID = 'ContentDB'

where ‘ConfigDB’ is the name of SharePoint configuration database and ‘ContentDB’ is the GUID of content database from error 5553 in Event Log. Look at “Name” field of the result – it is the name of content database.

3. Determine on what SQL Server Instance this content database is stored. For it, navigate to Central Administration -> Application Management -> Content Databases. Find the content database by its name – the details of it will tell you the full name of SQL Server instance and the name of database.

4. Determine the ID of the root web of the site collection where your site is located. For it, navigate to the SQL Server instance storing the content database, and execute following SQL statement against it:

SELECT ID, RootWebId 
FROM [ContentDB].[dbo].[Sites]
WHERE ID = 'SiteID'

where ‘ContentDB’ is the name of content database and ‘SiteID’ is GUID of the site from error 5553 in Event Log. Look at “RootWebId” field of the result – it is GUID of the root web of desired site collection.

5. Determine FullUrl of that root web. For it, on the same SQL Server instance, execute following SQL statement:

SELECT [Id],[SiteId],[FullUrl]
FROM [ContentDB].[dbo].[Webs]
WHERE IN = 'RootWebID'

where ‘ContentDB’ is the name of content database and ‘RootWebID’ is GUID of root web of desired site collection. Look at “FullUrl” field of the result – it is relative part of the URL (despite of the naming!). So to have the real full URL of the site collection, you have to combine this filed with the “PortalUrl” field in the table.

6. By now, you have full URL of the site collection and GUID of the content database, so execute:

STSADM –O PREPARETOMOVE -Site 'FullURL' -OldContentDb 'ContentDB'

where ‘FullURL’ is the full URL of the found site collection and ‘ContentDB’ is the GUID of the content database from error 5553 in Event Log. Make notice – you have to use this full URL without slash at the end – so instead of using “http://portal/sites/SiteColl/”, you have to use “http://portal/sites/SiteColl”.

7. And the latest step is to rebuild from the scratch the sync-related information stored in the content database. For it, execute:

STSADM –O SYNC DELETEOLDDATABASES 0

It will remove sync-related info only – neither content database, not user-related information. And on the next synchronization, the sync-related info will be recreated (probably during several runs – depending on the amount user population).

That’s all, thanks for reading!