If you want to skip the commentary -- jump to the bottom for the skinny.
I've been planning doing the planning for my company's CRM 4 upgrade for the last couple weeks. We've got a fair amount of ISV code that's also revving, but one thing has stuck in the back of my mind throughout the planning process. Outlook Synchronization. Ironically I've been panning for a (supported?) way to administratively disable synchronization of certain CRM entities with Outlook. Why? Well the problem's a bit deep rooted. Here's a shot at not making it completely boring.
Basically my company works under a B2C model so Contacts are a central entity. Exceptions exist, but largely we see customers as "Contacts". We have a contact ownership model that puts the ownership of several thousand contacts in the hands of no more than 100 people. For most users that own contacts -- there are easily hundreds of contacts for each user. There is do distinguishable benefit for these contacts to sync with outlook -- outside of the address book. In fact, most users would see the creating of all of their "customers" as Outlook Contacts as wasteful info pollution. They don't want it to happen. Even if they don't know it yet. So, the problem at hand it how to administratively disable the synchronization of contacts. Because even though there is an options panel that is available to disable this via a check box -- we'd really feel better if we took care of this for everyone.
Sprinkle in the fact that we are an RDP shop (Citrix actually) and that user sessions are actually taking place on about 8 servers in our data center. So, if long running sync processes soak up out processing capacity on the Citrix farm -- it's bad news in IT land. People aren't just freezing up their work laptop or desktop while they sync. They have potential to do collateral damage to their fellow worker who just happens to be working on the same Citrix box. We have a vested interest in "managing" this sync process across the enterprise of about 250 users. Preliminary testing shows that at least 10% of our user base is looking at potential multi hour syncs. Most of this is because the 4.0 client now syncs completed activities, which was not the case in the past.
So what to do? RegMon turns up no leads. There doesn't appear to be any files that get written to according to FileMon. But Fiddler and SQL Profiler reveal some hope. The settings are stored in the database. Perfect. Here's the details.
*** The Magic Starts Here ***
YMMV. I encourage you to backup your database often while fiddling. Remember that manipulating the CRM Database via SQL script is unsupported. Please, please, please, test, test, test in a non-production environment. Totally unsupported. Please don't call me or Microsoft if you screw it up.... If you're at all squeamish or risk adverse stop right now, close your browser and never return to this post. Its better for both of us if you do.
There, with that out of the way, apparently the sync settings are implemented using User Queries. User Queries are best known as Advanced Find definitions that the user has saved. I suspect a Users' custom list views fall in here too. Even better, the User Queries that power the sync process with Outlook have a "querytype" of 256. Doing some investigative work I watched what happens to these user query records as I changed and saved my sync settings in the UI. The only significant change is to a pair of fields (StatusCode and StateCode). Here's some more of the particulars.
The Database:
[YourOrganizationName]_MSCRM
The Table:
UserQueryBase
The Structure:
There's a bunch of stuff here, but the three fields of particular interest are:
- UserQueryId: Primary ID of the UserQuery
- OwningUser: FKey to SystemUserBase.SystemUserId... basically the user that this query is for
- StatusCode: Not totally sure... but the value of this is 2 when the corresponding checkbox is Unchecked, 1 when it is checked
- StateCode: Again, not really sure, but this value is 1 when the checkbox is unchecked and 0 when checked.
ReturnedTypeCode: The Entity for which the User Query is for. Using the SDK, you'd know that the TypeCode for the Contact entity is "2". - QueryType: Distinguishes between the different type of user query. I've found that the ones affecting sync have a value of 256.
- Description: If the User query was created by a user, this is the description field that is on the UI. An example is "Contacts Syncing to Outlook"
- Name: If the User Query was created by the user, this is the field that would be the given name to the query. An Example is "My Outlook Contacts"
Running the following query disables the checkmark for contacts for a given SystemUserID (a join to SystemUserBase can function as a more friendly limiter). Leaving that clause off runs it up for everyone.
update userquerybase
set statuscode = 2, statecode = 1
where owninguser = '[SystemUserID of the user you want to change settings for]'
and querytype = 256
and ReturnedTypeCode = 2
... and this one would check it.....
update userquerybase
set statuscode = 1, statecode = 0
where owninguser ='[SystemUserID of the user you want to change settings for]'
and querytype = 256
and ReturnedTypeCode = 2
Obviously, if you alter the query and drop some of the additional clauses (ReturnedTypeCode) you can apply this behavior more broadly. I have not yet done this. See disclaimer above.
Extending the Hack:
So, the whole thing started with the premise of just disabling Contacts. But as I uncovered the volume of syncable objects owned by our users it became clear that some throttling of the sync must take place. Hate to crush those Citrix servers and people's Monday productivity. Our approach for the weekend upgrade is to extend the hack to the other syncable entities to the point that everyones settings for synchronization will basically be all unchecked. Our plan is to treat Outlook synchronization as opt-in. So, we'll give users a resource -- probably a MOSS survey to fill out and opt in. We'll provide a link as a part of our communication plan for the weekend's events. This way as users log into the system come Monday, the impact of the Outlook client will be very slight and allow us to manage the sync across the enterprise. As people log in, we'll run SQL scripts to check the appropriate boxes in users' Sync options and watch performance on the Citrix Servers. If the boxes respond well, we can ramp up the sync to more users that have responded to the survey. Eventually, after the rush, we'd have followup communications to remind folks that they can get Sync turned on.
Of course an enterprising user *CAN* just enable the features on their own -- essentially pulling the pin on the grenade. With any luck we can detect these folks and rebalance other users away from them if need be. Most users in our enterprise are not going to be so adventuresome -- and for those that are we may get them to log in on Sunday to get their sync out of the way.
Additional thoughts:
Extra credit to those readers who noticed the FetchXml and LayoutXml as a part of the UserQuery record. An added benefit would be if you have the option of using a filter to limit the quantity of records synced to.... only "Open" or "Scheduled" ones. I'm continuing my fiddling to see if I can effectively use a carefully crafted saved Advanced Find and use the FetchXml and LayoutXml to limit the available records for sync. I'd implement this into our upgrade this weekend by having all user's sync settings set to only sync open or scheduled items -- perhaps only as of a certain date. Over time, as the sync processes calmed down, relax the query to a point where eventually there is no filter. If it works it would be an even more graceful means to cushion the processing blow, allowing all users to effectively sync right away in the morning and at least get something.
*** More to come on this ***
