crminthefield: CRM E-mail Router w/ 5000+ users

[Blog bot]

Источник: http://blogs.msdn.com/b/crminthefiel...il-router.aspx
==============

As Dynamics CRM moves more and more into the Enterprise customer space, the PFE team often encounters scenarios that may not be documented, found in the Implementation Guide, etc., and this is a scenario I recently ran into while working with a customer. Basically, the customer had a large number of users configured to use the E-Mail router as their E-mail access type:



Figure 1: E-mail Access Configuration

However, not all of the users seemed to be processing email as intended. Investigating the configuration, we noticed that when we would attempt to load the user information into the E-mail Configuration via the "Load Data" function in the E-mail Router Configuration Manager (Figure 2), only 5,000 users were loaded into the configuration. For example, if the customer had 6,000 users with an E-mail Access Configuration type of "E-mail Router" there were basically 1,000 users that were not loaded into the configuration so the E-mail router had no knowledge of these users and mail was not getting processed for them.


Figure 2: "Load Data" in the E-mail Router Configuration Manager

A SQL Server Profiler trace uncovered the following query that was executed when the "Load Data" button was clicked:

exec sp_executesql N'select

top 5001 "systemuser0".SystemUserId as "systemuserid", "systemuser0".FullName as"fullname", "systemuser0".InternalEMailAddress as "internalemailaddress", "systemuser0".OutgoingEmailDeliveryMethod as "outgoingemaildeliverymethod", "systemuser0".IncomingEmailDeliveryMethod as "incomingemaildeliverymethod"

from

SystemUser as "systemuser0"

where

(((((("systemuser0".InternalEMailAddress is not null and "systemuser0".IsDisabled = @IsDisabled0)) and (((("systemuser0".OutgoingEmailDeliveryMethod = @OutgoingEmailDeliveryMethod0)) or (("systemuser0".IncomingEmailDeliveryMethod = @IncomingEmailDeliveryMethod0 or "systemuser0".IncomingEmailDeliveryMethod = @IncomingEmailDeliveryMethod1)))) and (("systemuser0".InviteStatusCode = @InviteStatusCode0)) and (("systemuser0".EmailRouterAccessApproval = @EmailRouterAccessApproval0)))))) order by

"systemuser0".SystemUserId asc',N'@IsDisabled0 bit,@OutgoingEmailDeliveryMethod0 int,@IncomingEmailDeliveryMethod0 int,@IncomingEmailDeliveryMethod1 int,@InviteStatusCode0 int,@EmailRouterAccessApproval0 int',@IsDisabled0=0,@OutgoingEmailDeliveryMethod0=2,@IncomingEmailDeliveryMethod0=2,@IncomingEmailDeliveryMethod1=3,@InviteStatusCode0=4,@EmailRouterAccessApproval0=1

The above is basically checking to ensure the user in the SystemUser table is enabled, has an email address and has either the Incoming or Outgoing email delivery method (Figure 1 above) and is selecting the top 5000 users that match the criteria to load into the E-mail Router configuration. This helped explain why we were "missing" some of our users in the E-mail Router configuration - the query to retrieve users for the configuration is returning a max of 5000.

But what if your customer needs to have more than 5000 users configured for the E-mail Router? There are a couple of work-arounds that can be implemented - they are both Registry Settings on the Dynamics CRM Application Server(s).

NOTE: There is a fix coming for this "limit" in a future Update Rollup for Dynamics CRM 2011

IMPORTANT: As with any registry change, please be sure to backup the registry prior to making any changes.

The options are as follow:

OPTION 1: TurnOffFetchThrottling

1. Click Start, click Run, type regedit in the Open box, and then click OK.

2. Locate and then select the following registry subkey:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSCRM

3. On the Edit menu, point to New, and then click DWORD Value.

4. Type TurnOffFetchThrottling, and then press ENTER.

5. Right-click TurnOffFetchThrottling, and then click Modify.

6. Type a number other than 0 in the Value data box, and then click OK.



7. On the File menu, click Exit.

8. As this is a registry change, you do not need to restart the server / service for the change to take effect.

9. The "Load Data" SQL Query now looks like this following this change:

Notice: There is no “top” argument in this select statement – this will return all rows that match the query criteria.

exec sp_executesql N'select

"systemuser0".SystemUserId as "systemuserid", "systemuser0".FullName as "fullname", "systemuser0".InternalEMailAddress as "internalemailaddress", "systemuser0".OutgoingEmailDeliveryMethod as "outgoingemaildeliverymethod", "systemuser0".IncomingEmailDeliveryMethod as "incomingemaildeliverymethod"

from

SystemUser as "systemuser0"

where

(((((("systemuser0".InternalEMailAddress is not null and "systemuser0".IsDisabled = @IsDisabled0)) and (((("systemuser0".OutgoingEmailDeliveryMethod = @OutgoingEmailDeliveryMethod0)) or (("systemuser0".IncomingEmailDeliveryMethod = @IncomingEmailDeliveryMethod0 or "systemuser0".IncomingEmailDeliveryMethod = @IncomingEmailDeliveryMethod1)))) and (("systemuser0".InviteStatusCode = @InviteStatusCode0)) and (("systemuser0".EmailRouterAccessApproval = @EmailRouterAccessApproval0))))))',N'@IsDisabled0 bit,@OutgoingEmailDeliveryMethod0 int,@IncomingEmailDeliveryMethod0 int,@IncomingEmailDeliveryMethod1 int,@InviteStatusCode0 int,@EmailRouterAccessApproval0 int',@IsDisabled0=0,@OutgoingEmailDeliveryMethod0=2,@IncomingEmailDeliveryMethod0=2,@IncomingEmailDeliveryMethod1=3,@InviteStatusCode0=4,@EmailRouterAccessApproval0=1


OPTION 2: MaxRowsPerPage

1. Click Start, click Run, type regedit in the Open box, and then click OK.

2. Locate and then select the following registry subkey:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSCRM

3. On the Edit menu, point to New, and then click DWORD Value.

4. Type MaxRowsPerPage, and then press ENTER.

5. Right-click MaxRowsPerPage, and then click Modify.

6. Type a number that is more than or equal to the number of users setup for the email router (10,000 for example) in the Value data box, and then click OK.



7. On the File menu, click Exit.

8. As this is a registry change, you do not need to restart the server / service for the change to take effect.

9. The "Load Data" SQL Query now looks like this following this change:

Notice: The query is now selecting the top 10,000 records that match the criteria (instead of the default of 5,000).

exec sp_executesql N'select

top 10001 "systemuser0".SystemUserId as "systemuserid", "systemuser0".FullName as "fullname", "systemuser0".InternalEMailAddress as "internalemailaddress", "systemuser0".OutgoingEmailDeliveryMethod as "outgoingemaildeliverymethod", "systemuser0".IncomingEmailDeliveryMethod as "incomingemaildeliverymethod"

from

SystemUser as "systemuser0"

where

(((((("systemuser0".InternalEMailAddress is not null and "systemuser0".IsDisabled = @IsDisabled0)) and (((("systemuser0".OutgoingEmailDeliveryMethod = @OutgoingEmailDeliveryMethod0)) or (("systemuser0".IncomingEmailDeliveryMethod = @IncomingEmailDeliveryMethod0 or "systemuser0".IncomingEmailDeliveryMethod = @IncomingEmailDeliveryMethod1)))) and (("systemuser0".InviteStatusCode = @InviteStatusCode0)) and (("systemuser0".EmailRouterAccessApproval = @EmailRouterAccessApproval0)))))) order by

"systemuser0".SystemUserId asc',N'@IsDisabled0 bit,@OutgoingEmailDeliveryMethod0 int,@IncomingEmailDeliveryMethod0 int,@IncomingEmailDeliveryMethod1 int,@InviteStatusCode0 int,@EmailRouterAccessApproval0 int',@IsDisabled0=0,@OutgoingEmailDeliveryMethod0=2,@IncomingEmailDeliveryMethod0=2,@IncomingEmailDeliveryMethod1=3,@InviteStatusCode0=4,@EmailRouterAccessApproval0=1

Results in the E-Mail Configuration Manager following the change (notice, more than 5000 users have been loaded):



Figure 3: E-mail Router configuration manager with 5000+ users

Regardless of the Registry Key you use, be careful of possible downstream effects of the change across the application - such as if the customer uses Fetch queries in other areas where having the throttling off may negatively impact performance.

Also take into consideration that as you add more users to the E-mail router configuration, you may impact overall processing time so consider using multiple providers, for example, in your configuration.

As mentioned earlier in the article, there will be a fix included in an upcoming Update Rollup for Dynamics CRM 2011 that will allow you to load more than the current default level of 5000, but until then, you can use one of the above methods to ensure all the users in your organization are loaded into the E-mail router Configuration.

Brian







Источник: http://blogs.msdn.com/b/crminthefiel...il-router.aspx