Print | posted on Sunday, April 15, 2012 7:31 PM
Since the release of SharePoint Server 2010, the maintenance of the User Profile Service Application (UPA) Sync DB has been extremely problematic. I’m not talking about “standard” database maintenance tasks here, you know those routine tasks you should be performing on your environment but that seldom are implemented by those running operational service :). This is all about the Sync DB retaining data that it shouldn’t. There is quite a lot of confusion out there surrounding this topic in general and it has certainly hit a lot of customers, hard.
The fundamental issue stems from the choice of the build of FIM bundled with SharePoint. Whilst that build has changed over time with updates, the necessary stored procedures to clean out the history of sync runs were missing. This in almost all cases leads to a unwieldy Sync DB, and that’s putting it politely. Even in small scale VM environments such as test labs and the like the Sync DB can become massive over time when running regular sync tasks. Imagine that in production for a real deployment for 250,000 users and the problem is a considerable challenge!
You may have found on the interwebz a dodgy SQL script which was provided to a customer and then unfortunately was posted to the tubes. This would approximate the necessary clean up. However that script is NOT supported, and it only actually works on three specific builds of the product anyway.
Therefore the official guidance has been for a very long time to throw away and recreate the UPA (whilst retaining the Profile and Social DBs) and re-provisioning the UPS service instance, which results in a nice clean Sync DB.
Far from an ideal solution, especially considering all of the Synchronization Connections, Filters and Mappings must be re-created in this scenario (and there is no way to automate connections and filters in a supported and reliable fashion). And of course a Full Sync needs to be run after the provisioning, not in large environments a trivial operation.
Thankfully one of the improvements in the February 2012 Cumulative Update is finally the inclusion of six new Stored Procedures that together deal with deleting unnecessary history during sync operations.
Now finally the Sync DB will remain at a sensible size, commensurate with the number of objects you are syncing. Assuming you of course are also performing the appropriate regular database maintenance tasks.
Since this release I’ve fielded a number of questions from those who are battle hardened with SharePoint 2010 CUs. They ask, can we add these stored procedures to our deployment without deploying the February 2012 CU? Of course the answer to this is “no”. Or more accurately, “hell, NO!”. Even if it was supported, which is most definitely is not, it wouldn't work.
There are two new “core” FIM sprocs that support the four ones responsible for clean up (DeleteObjectReferences, TruncateObjectsInternal, TruncateInstanceData and TruncateSystemObjects). However a total of 49 other sprocs and three tables must be also updated to make the overall solution work. Also, there are other schema updates in the Sync DB to support additional fixes in this CU. So just don’t go there. There’s no cheat, there’s no shortcut, if you want automatically managed Sync DB, you must deploy this CU.
Whilst I’m at it I will also highlight another important UPA related aspect of this CU. There are a number of scenarios where when implementing the NetBIOSDomainNamesEnabled property, during Full Sync some profiles may not be imported. The majority of these problems have now been resolved. Not 100% but pretty damn close, and we have a careful eye on this.
It’s also worth noting that the build of FIM in the February 2012 CU has been revved to 4.0.2450.47. There are other fixes in the CU, but they are extremely edge. What you can do is take it from me as red that the February 2012 CU is a critical element for the User Profile Synchronization service instance.
s.