Dynamics CRM 2011 Optimize built-in maintenance jobs

I've noticed the maintenance job "Cleanup Workflows" was taking considerably more time to execute as my AsyncOperationBase table grows after some investigation I've noticed CRM needed a tweak to optimize this maintenance job.

The "Cleanup Workflows" maintenance job like the other CRM maintenance jobs are based on SQL storage procedures that are executed by the Asynchronous Maintenance service on a scheduled basis. This particular maintenance job was slowing down on this particular storage procedure: p_CleanupInactiveWorkflowAssemblies

which looks like this:


There is a select with a where clause on the AyncOperationBase table

This select takes around 3min o execute on my live environment (this was a lot longer on my uat and dev systems) with 3.5million records which I think is not that many records, this is because the column OwningExtensionId and OperationType are not indexed, out-of-the-box and up to RU14 it does not create the necessary indexes to make this query run faster you have to create it yourself.

I've created the following index:

and the query takes now 0seconds to be executed.

Hope this was helpful.

Dynamics CRM 2011 Outlook Client Optimization

With the introduction of CRM rollups e.g. RU12 with cross browser support I've noticed the CRM Outlook client is struggling and crashing more often possibly due to all the new changes in the new rollups a number of tweaks and optimizations are available to improve the user experience when using the CRM outlook client.

Making CRM 2011 Outlook client stable
I've noticed Outlook clients started to crash randomly after the deployment of RU11 analysing Outlook.exe crash dumps revealed the issue was on SQL CE 3.0. A new version of SQL compact edition is available version 4.0 which as documented, handles memory better eliminating (or near) the out-of-memory crashes in Outlook while working with the CRM Outlook client.

The following Microsoft KB article documents how to upgrade SQL CE 4.0:
http://support.microsoft.com/kb/2616319

The process as per KB article:

1. Install SQL CE 4.0
2. Delete the CRM 2011 client current configuration and delete any cashed databases
1. C:\Users\[user]\AppData\Local\Microsoft\MSCRM
1. delete all *.sdf files
3. EDIT the configuration wizard config file to load the SQL CE 4
4. EDIT the  CRM client startup config file to load new assemblies.
5. Open Outlook and confirm the new SQL CE is loaded as per below screenshot

However I found that on random machines the configuration wizard failed to connect to CRM, the reason behind this was related with a configuration line on the configuration wizard config file:

<system.net>
    <defaultProxy useDefaultCredentials="true" />
  </system.net>

Removing the above code allowed the connection to be made. If you don't want to remove the above code from every user the following hotfix will resolve the issue:

http://support.microsoft.com/kb/2580188

CRM 2011 client x64
If you have the opportunity to instead of 32bits use the 64bits version of Office, then it would be strongly recommended to use the x64 it adds extra stability to Outlook and CRM client, as 64 bits has more room for memory allocation.

  Registry Keys
 As per the following KB http://support.microsoft.com/kb/2585157 the below registry keys will improve performance:

HKEY_CURRENT_USER\Software\Microsoft\MSCRMClient NotificationPollInterval set to 3600000 (Decimal) StateManagerPollInterval set to 10 (Decimal) ActiveCachesUpdatingPeriodMilliseconds set to 3000000 (Decimal) IncrementalDataCachesInclusionUpdatingPeriodMilliseconds set to 6000000 (Decimal) IncrementalDataCachesExclusionUpdatingPeriodMilliseconds set to 6000000 (Decimal HKEY_CURRENT_USER\Software\Microsoft\MSCRMClient\{ORGGUID} TagPollingPeriod set to 600000 (Decimal) TagMaxAggressiveCycles 0

A couple of registry keys to consider as well:

HKEY_CURRENT_USER\Software\Microsoft\MSCRMClient

DisableMapiCaching	1
AddressBookMaterializedViewEnabled	1

CRM Settings

From CRM settings > administration there is a few settings that you should also consider to improve user experience:

System Settings > Outlook Tab you want to increasing the default values to reduce overhead on the server and on the user side:

Privacy Settings > Error Reporting here you can configure centrally all users settings on how to deal with Microsoft errors, the advantage here is that you reduce the noise on the user side.

Conclusion
I've covered a few known optimizations, the most impact is SQL compact edition 4 so I strongly recommend you upgrading your outlook client deployment. I hope this was helpful please leave any questions or feedback.

Dynamics CRM 2011 using SSIS to monitor pending emails

On this article I'll walk through how to build a simple and fully supported SSIS package to track pending emails in CRM. The idea is to receive an alert email when we reach a threshold suggesting email router is stuck, slow performance or unusual mail activity in CRM.

On this tutorial I use the SSIS CozyRoc component to connect to Dynamics CRM, you can download it here:
http://www.cozyroc.com/products

The package process:

1. Connect to a CRM organisation using the API
2. Run FetchXML to retrieve all pending emails
3. Count how many emails pending and storing the total number in a variable
4. Use an IF THEN ELSE statement to set the flow of the package to successful or failure based on the total number of emails pending
5. send an alert email if successful or failure.

In visual studio go to: New > Project > Integration Services Project

The first step is to create a variable that will hold the total number of emails within the Control Flow tab select view and variables, this will display the variables screen on the left, click new variable and name it: RowCount, the scope should be set to 'Package' this means our variable is a global variable so it can be used either in the 'Data Flow' or 'Control Flow' area.

The next step is to create a connection to CRM to retrieve the data we want.

On the connection Managers diaglog box, right click and select New Connection. From the list choose DYNAMICS-CRM CozyRoc

The following screen should appear, configure it with your CRM information:

I renamed my connection as SandBox Connection as per below screenshot:

Next Step is to build your Data Flow tasks, click on the Data Flow tab and drag the Dynamics CRM source task on to the Data Flow area. 

Note: the Dynamics CRM Source should be available on the bottom of your Toolbox, if you have installed the CozyRoc Component and you don't see it there is because you didn't complete the post-install steps, you need to add those two tasks to the General section of the Toolbox

Also drag the Row counter task and the Data Flow area will look like this:

Note: we haven't linked the Dynamics CRM source with the row counter task we will do this soon.

Double click the Dynamics CRM source task and the following window appears:

On the above screenshot the first tab, you need to select the Connection Manager, on this case we select our SandBox Connection

Click on the Second Tab:

On the above screenshot we want to make use of the FetchXML feature, so at the bottom under 'Custom Properties' on the 'InputType' change entity to FetchXML and under 'FetchXML' paste the following XML:

Click OK all done on the 'Dynamics CRM Source' task. The next step is the 'Row Count' task link the 'Dynamics CRM source' to the 'Row Count' task and double click the 'Row Count' task the following screen appears:

Here we simply set the 'VariableName' to use our global variable, on the variable selection window you should see User::RowCount tick the box and click okay. 

At this stage we run the package it will:

1. Connect to CRM Organisation SandBox
2. Run the FetchXML
3. Pass the data to the Row Count task and store the total on the User::RowCount variable

Great so we the Data Flow process in place, now we need to Send an email if we have more than 100 Emails pending we want our Control Flow area to look like this:

At the above screenshot, we added a 'Script Task' task to process the data coming from the Data Flow, we then send an email either if the package runs successful or fails.

Double Click the Script Task and on the main screen ReadOnlyVariables select our variable User::RowCount

You should see this:

On the Script Task what we need to do now is to insert code to check if the variable is bigger than X if yes set to Successful completion or to Failure. On the same window click on The button at the bottom Edit Script and insert the following code under the Main() function:

The last step is to configure the 'Send Email' task to send an email in case of Successful or Failure. In this example I've added both routes (successful and Failure, however in my production environment I only have one route for when the email is bigger than X, if you understood the above code you should be able to decide how you want to apply your logic the two routes were added to this article to understand better the process flow of this package and for testing purposes you may want to receive an email saying Yes or No to confirm the package is running okay.

I hope this was useful, please leave your feedback.

Dynamics CRM 2011 scalling multi-tenants across multiple SQL servers

Recently I've seen a few questions in the forums around placing new tenants on different SQL servers.
This is possible if you using an enterprise license, the option is available when you create a new tenant. You are not necessarily stuck to the SQL server where the MSCRM_CONFIG database is deployed, you could specify a different SQL server. You will need the same permissions as per the deployment guide.

However distributing tenants across multiple servers increases management, complexity and SQL licensing costs. Keep it simple, it's easier and cheaper to upgrade the hardware of one SQL server rather than having those resources distributed across multiple servers.

Creating New Organization
Open deployment manager click New Organization when it gets to the SQL server step specify a different SQL server

Click Next and specify the ReportServer as well

Note: you need local admin permissions on the SQL server and database permissions as per deployment guide.

The below diagram illustrates 4 Organizations distributed across multiple SQL servers and 2 of those with their own SQL Reporting Server.

Conclusion
You can distribute tenants across multiple SQL servers including the SQL reporting services. A number of things can influence the decision to place tenants on different SQL servers:

• Legal Reasons
• Shared Resources
• Size of databases with impact of backups 
• Performance
• Development and Testing

Dynamics CRM 2011 printing IFRAME

In the past I wrote an article on how to make iframes printable using the CRM print function, I was asked how to apply the same approach to CRM 2011, I had a look and CRM 2011 wraps the text inside another set of HTML elements, so we just need to make sure we return only text document.getElementById.textContent

This change is not supported as it changes directly an ASPX file, the file you have to change is custformprint.aspx located:

C:\Program Files\Microsoft Dynamics CRM\CRMWeb\_forms\print

For example if you have a SRS report loading in an IFRAME you just need  to set SRC="" url of the FRAME_ID:

document.getElementById('IFRAME_GoogleMap').src= "http://srsserver/reportserver?etc.....";
document.getElementById('IFRAME_GoogleMap').style.height = "400px";
document.getElementById('IFRAME_GoogleMap').style.width = "900px";

An example for Google Maps would be:


//does an initial check if the ID for the google map is set. (assuming you've used GoogleMap for the iframe)
if (document.getElementById('IFRAME_GoogleMap')) {
var url = "";
if (document.getElementById('address1_country_d') != null)
  url += (url == "" ? "" : ", ") + document.getElementById('address1_city_d').textContent;
if (document.getElementById('address1_city_d') != null)
  url += (url == "" ? "" : ", ") + document.getElementById('address1_city_d').textContent;
if (document.getElementById('address1_postalcode_d') != null)
  url += (url == "" ? "" : ", ") + document.getElementById('address1_postalcode_d').textContent;
if (document.getElementById('address1_line1_d') != null)
  url += (url == "" ? "" : ", ") + document.getElementById('address1_line1_d').textContent;
if (document.getElementById('address1_line2_d') != null)
  url += (url == "" ? "" : ", ") + document.getElementById('address1_line2_d').textContent;
if (document.getElementById('address1_line3_d') != null)
  url += (url == "" ? "" : ", ") + document.getElementById('address1_line3_d').textContent;

if (url != "")
{
  document.getElementById('IFRAME_GoogleMap').src= "http://maps.google.com/?q=" + url;
  document.getElementById('IFRAME_GoogleMap').style.height = "400px";
  document.getElementById('IFRAME_GoogleMap').style.width = "900px";
} else {
// If no data to pass to url, defaults to text only "No Results"
document.getElementById('IFRAME_GoogleMap_d').innerHTML= "No results";
}
}

dynamics CRM 2011 Data driven security Part II - Teams

This is part II of my previous post on data driven security where I mainly focus on Security Roles and Business Units architecture, you can read it here:
http://quantusdynamics.blogspot.com/2012/03/dynamics-crm-data-driven-security.html

On part II I will focus on Teams and it's concept around data sharing based on the same pyramid model used on Part I. I'm also covering how can teams leverage User management. In general Teams are great for:

1. User Access Management and Security roles
2. Sharing Data across Business Units
3. Enhance collaboration

User Management Simplified with Teams

User management in CRM can become as complex as you want. The management side has been designed to be very basic, adding a user and assign a security role, this is what it takes to give a user access to CRM. However with multiple business units and multiple security roles and the lack of a built-in tool to check 'effective permissions' across the hierarchy of records and Business Units this could become very time consuming to manage and troubleshoot permission issues.

You could add users multiple security roles at the same time, however you can't remove them at the same time, if you assign 300 users the wrong security role, you will need to remove one by one the same security role or develop a tool to do this.

However we are lucky because we have teams which can be used like AD groups or sort of OU with GPOs. How would this work for User Management? When we create a Business Unit, CRM will automatically create a team with the same name as the Business Unit you just created and link it with the same Business Unit. On Part I, I've used a pyramid type graph to illustrate permissions, the below screenshot is the same graph but I'v added highlighted in yellow the corresponding teams. 

On the above graph, all the default teams in yellow belong to their own BU, also all users moved to that BU will also be automatically added to the BU default team, this is great!! However the default teams are not assigned a security role, even better! :)

At this stage what you need to do is assign a security role to the default teams, the appropriate security role with permissions only for those Business Units. 

Now that all your default teams have a security role, if you didn't have users in the system, here is how it would work:

1. Add one or multiple users to CRM and simply select the business unit you want to place the user on, don't assign security roles
2. The user will inherit the Teams security role and it's permissions
3. For those familiar with AD, this concept is similar to OU GPOs, every object you place on an OU is bound to the OU GPO's

You populated all users across all Business Units, now you have 50 Junior Engineers to move to the Global Engineers OU, this is how it would work:

1. Select The users you want to move, and click 'Change Business Unit'
2. Users will be automatically assigned the Global Engineers Security role because they are automatically added to the GlobalEngineers default team.
3. Also users will be automatically removed from the JuniorEngineers team so they will also loose the JuniorEngineers Security role
4. Conclusion: you didn't have to remove or add security roles

Assigning Users Business Unit

Sharing Data

On the pyramid model the idea is to isolate data from other departments and teams, only specific permissions would grant the Junior staff tier access data on the upper Business Units, the only permission setting that allows this to happen is the 'Organisation' level and this would open access to all BUs and not just the BU directly above, we don't want this to happen because it would break the pyramid model so we have to use teams to share data with groups of users or the entire BU.

Facts about teams:

• Teams can only include users
• Teams can include users of any Business Unit
• You can share records with Teams from any Business Unit

To use teams to share records you create custom teams on the various business units, to be used as:

1. Using Security roles to provide single BU access
2. Object Access (record only)

Using Security roles
This method is familiar to us, we covered the concept on simplifying user management above. The team is assigned a security role so any user added to this team will also inherit the same security role, this is a great approach to give specific users in the junior tier, access to an external BU in this case the global engineers Business Unit.

Object Access

Instead of providing external BU access you may just want to share 1 or a few records with a number of users and teams, to do this you share data directly at the object-level (opportunity, account, etc).

1. Create custom teams to group users
2. On the record itself select Share and select the team you want to share the record with.
3. This means any team from any BU could be added, and the record accessed by multiple users from multiple Business Units
4. This avoided giving BU-wide permissions just to share one or a few records

The above diagram illustrates the concept of sharing records with Teams, team1 On the Junior Tier has been given share permissions on:

• One Record on the Global Engineers BU
• One Record on the Global Exports BU

A team from the Global Engineers BU has been also given access to a record on the Global Sales BU. I haven't illustrated the BU single acces because this is covered on the above diagram.

All users members of these teams are now able to view the records and if edit permissions were given able to write and collaborate.

Conclusion

I hope this was useful, please leave your feedback or any questions you may have.

Dynamics CRM 2011 Default Organization Bug

On Multi tenant environments Dynamics CRM 2011 contains an annoying bug related to the default organization which breaks a number of things in CRM.

On a multiple tenant environment user accounts are managed under the MSCRM_CONFIG database, SystemUserOrganizations table. CRM keeps track of users configured on multiple tenants including the DefaultOrganization. The default organization it's the organization the user lands by default when typing the URL without specifying the organization he/she would like to access.

The issue with the default organization is that each time you configure a user on a new tenant, the DefaultOrganization field on the database gets updated with a new value, and this value is not necessarily the tenant that you just added the user on, it rotates, and this causes a number of things to break in CRM:

1. Help Server URL
2. Web resources with direct calls to iframes
3. Exporting Error Rows from an import job
4. The administrator yellow bar alerting to assign user roles

To fix the problem, set the user default organization to the correct one.

You can use the following SQL query to find users default organization: