User profile properties mapped to a BCS connection requires two syncs for new users

Question

I have the User Profile Service Application up and running in a SharePoint 2010 farm. It is configured with two synchronization connections; one to Active Directory and a second to a backend database through BCS. About 10 user profile properties are mapped to the BCS connection. The synchronization of user profiles runs smoothly without errors - all users and their properties are imported correctly.

But there is one issue that I have so far not been able to get around. That is, when adding a new user simultaneously in Active Directory and the backend database then the user is first imported from AD without values for the properties mapped to columns in the BCS connection. These values are not imported before the second sync.

The BDC model used in the connection was created with SharePoint Designer and works without issues. The sync connection is created as a 1:1 mapping with items identified by a custom user profile property. I have also tried to identify items by AccountName.

The scenario is easy to reproduce:

1. Provision the User Profile Service Application (UPSA)
2. Add a synchronization connection to an OU in Active Directory
3. Create a simple SQL database with the same users as in AD. The DB should at least have a column with the AccountName and a column with some added information
4. Create an External Content Type with SharePoint Designer 2010 for the SQL database. Use whatever supported authentication scheme you prefer. I created and used a new Application in the Secure Store to have the sync service account (farm account) authenticate against the SQL database.
5. Add a synchronization connection to the SQL database using the BDC model created with SharePoint Designer. Select 1:1 mapping and identify users by the AccountName property.
6. Add a custom user profile property in the UPSA
7. Map the new property to a column in the SQL database.
8. Start a full synchronization of user profiles and wait for it to complete.
9. Verify that the custom user profile property is missing its value from the SQL database on all imported user profiles. THIS IS THE ISSUE.
10. Start a second full sync and wait for it to complete.
11. The user profiles now have the correct value on the mapped profile property.

I have checked the ULS logs for errors and also used the FIM client to monitor the entire synchronization process. But have not identified any related errors so far. My conclusion for now is that the issue is by design in User Profile Synchronization service.

My question is: Is this by design or is there a solution/work-around for this issue?

Answer 1

you might try monitoring the process in the Forefront Identity Manager client (C:\Program Files\Microsoft Office Servers\14.0\Synchronization Service\UIShell\miisclient.exe). If you havent used that tool before, don't make ANY config changes with it or you could break the whole thing. However, it is a great tool to monitor the sync process as it is the actual tool doing the sync.

In the environments I have dealt with that had multiple sync connections, the behavior I have observed is that the data from each independent connection is compared and gathered, but not committed to the SharePoint data stores until the end of the sync process. When the independent connections had data that overlapped, they weren't compared until the user already existed in the SharePoint data store. Meaning new users weren't added until the end of the sync process and data from additional sources was not matched unless the user existed prior to the beginning of the sync. So, based on those observations, it looks like your scenario is by design. Not necessarily a good design, but still by design.

One thing to note is that the User Profile Sync and such was nearly completely redone between 2010 RTM and 2010 SP1. If you have not applied SP1, you might apply it and see if the sync behaves any differently. I suspect it won't change much, but worth a shot if you haven't applied SP1. The Forefront Identity Manager client is in the same location in RTM and in SP1.