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:

Dynamics CRM 2011 recover deleted Active Directory user accounts

Some times user accounts in CRM get out-of-synch with Active Directory and this could be related with:

• AD account deleted and re-created
• AD restores
• Or anything else that would change the objectGUID in AD

The most common is deleted accounts in Active directory. E.g. people returning to the same company, which previously used CRM and the account has been disabled, AD admins deleted the account and now create a new account with the same details in AD; if you try to enable the user account in CRM, you will get an error because CRM can't find the same AD GUID:

External Error - No such Object on Server

If you haven't disabled the account, you don't have to follow the next instructions, simply change the username field to a random test account and save, and change it back again to the user account you want to configure and save, this will update the user GUID and the AD SID in CRM. However if you have disabled the account you need to do the following steps.

Building the LDAP Query
The process is simple and it involves finding out the new objectGUID and objectSID in AD with an LDAP query and update the CRM database manually.

You can use any LDAP tool, for simplicity I find LDP tool simple to use and also available with windows 2008 R2 servers.

Simply search for ldp on the windows search box, or type on the command prompt c:\ldp and press enter

1. With the tool open go to the menu Connection and select connect, type the LDAP/DC server

2. When connected, you need to bind the session to a user account, to perform the search under that user context. Go to the Menu Connection > Bind

3. I've used the currently logged on user, but you can specify any account you want.

4. When you bind the session to an account you should get a successful message like the below message:

0 = ldap_set_option(ld, LDAP_OPT_ENCRYPT, 1)
res = ldap_bind_s(ld, NULL, &NtAuthIdentity, NEGOTIATE (1158)); // v.3
{NtAuthIdentity: User='NULL'; Pwd=; domain = 'NULL'}
Authenticated as: 'DOMAIN\your_account'.

5. Now that you connected, go to Browse > Search

6. On the Search window, you type the Base DN (Distinguish Name) which is the location of the account in AD, select the scope: Base, and the most important select the attributes you want to view, in this case you only need:

objectGUID
objectSID

7. Click Run and the below screenshot shows the account new GUID and SID.

Compare the results with CRM
Compare these values with the ones in the CRM database:

select DISTINCT sub.fullname, sub.ActiveDirectoryGuid, sua.AuthInfo, sua.UserId

from SystemUserbase sub

inner join MSCRM_CONFIG.dbo.SystemUserOrganizations suo ON

        suo.CrmUserId = sub.SystemUserID

inner join MSCRM_CONFIG.dbo.SystemUserAuthentication sua ON

        sua.UserId = suo.UserId

where sub.DomainName = 'domain\username'

Update CRM database
From the screenshots above, we can see the information match with what it's in CRM and in AD. if the AD object changes in AD e.g. account deleted and re-created; the GUID and SID would be different in the LDAP results; with the results provided from the LDAP query, update the CRM database manually:





Conclusion
The above process applies to most scenarios when an account had the objectGUID and objectSID updated in AD and CRM becomes out-of-sync with AD, however if you didn't disable the account you are in a better position to simply change the domain username to a dummy account and revert it back, but when you disable accounts you can't enable it back again, you can then use the above process to recover the CRM accounts.

Hope this helps and please leave your feedback.

Dynamics CRM 2011 Multiple organizations Asychronous Service Optimization

On a multi tenant environment where organisations are used as a way of isolating data e.g. sales team vs IT staff; the Asynchronous services will not balance workload across organizations evenly; if you have one organisation generating more workflows than others, it's likely to have an impact on the other organisations Asynchronous jobs; e.g. taking longer to execute workflows, or data imports.

The below diagram illustrates the Asynchronous service workload on this scenario:

On the above diagram the bottleneck it's on the Sales Organization taking 59% of the Asynchronous processing time delaying the execution of the IT staff Organization workflows.

To avoid these type of bottlenecks we can limit the number of system jobs the Async service picks up per organization by setting the AsyncSelectMaxItems with the following PowerShell commands:

add-pssnapin Microsoft.Crm.Powershell
$itemSetting = new-object 'System.Collections.Generic.KeyValuePair[String,Object]'("AsyncSelectMaxItems",100)
$configEntity = New-Object "Microsoft.Xrm.Sdk.Deployment.ConfigurationEntity"
$configEntity.LogicalName="Deployment"
$configEntity.Attributes = New-Object "Microsoft.Xrm.Sdk.Deployment.AttributeCollection"
$configEntity.Attributes.Add($itemSetting)
Set-CrmAdvancedSetting -Entity $configEntity

The following diagram illustrates how the asynchronous service would balance the system jobs per organization:

Dynamics CRM 2011 Outlook client how to track items on multiple mailboxes stores

I came across this a few times and I don't think it's widely documented, if you want someone to track emails, contacts or other items on other users mailbox, this is possible with CRM client, you can make this possible with two steps and no permissions in AD are needed:

You would normally receive the below error when trying to track an item on other users mailbox:



To make tracking items on other users mailbox possible, you need to access the users items/mailbox, you do this by asking the user to:

1. First delegate control over the mailbox via the delegate permissions
2. Secondly by giving you folder permissions so you can expand the items in your Outlook.
3. Add the user's mailbox to Outlook and you ready to track other users emails, contacts etc..

 Note: The created record owner will be the original users mailbox but the 'Created By' will match the actual person that tracked the record.

Delegate Access
In Outlook go to File > Account Settings > Delegate Access

 





















Search the user in the Address Book > Click Add > and Ok:

Assign the Delegate Permissions:

Mailbox Folder Permissions
From the folders view, Right-click the Mailbox and select Folder permissions:

 










On the Folder permissions screen you don't have to give Owner permissions you can customize it, what is important is that the user gets permissions to read:

Now Add the user's Mailbox to your Outlook
On the Folder Permission screenshot above, on the General Tab > click Advanced and you see the below Screenshot.

Use the Add button to Add the Users mailbox by typing the User's Name and click OK, and the mailbox is added to your navigation folders.


Conclusion
And this should be enough to let you track items other users mailboxes. Hope this was useful and happy tracking. :)

Dynamics CRM 2011 Outlook client gotchas

I have recently come across a few interesting issues with dynamics CRM Outlook client which I would like to share, I don't think they are also widely documented.

Dynamics CRM 2011 Outlook client 'Sign-In' and 'Trying to Connect'
The user couldn't open CRM within Outlook despite all working perfectly, I mean, Configuration wizard completes successful, fiddler shows all connections made successful.

Another interesting behaviour was that every time I removed the configuration from the configuration wizard leaving it with no organization configured and returned to Outlook it would start configuring automatically.

Clicking on the CRM tab, was all greyed out and clicking on the CRM 2011 Button would show a page saying sign-in when you click sign-in would say trying to connect and would do nothing.

This was obviously something related with the user Outlook profile, so the resolution was to start outlook with the following swtich:

Outlook.exe /resetfolders



Dynamics CRM 2011 Outlook client out-of-memory errors
It seems it's no longer servers that are moving away from 32bits architecture; I have found that Outlook and even IE 32bits are struggling to keep the pace, below are a few screenshots from both IE and Outlook throwing errors after unable to allocate more memory to work with Dynamics CRM. The solution for this is to use 64bits version of Outlook and Internet Explorer.

Dynamics CRM 2011 Outlook client configuration wizard does not open
Upgrading the Outlook plugin from CRM 4 to CRM 2011 some times causes an odd behaviour where the configuration wizard runs on the background (only visible under processes on task manager) and then closes itself.

I found that the previous registry key from CRM 4 is not deleted from the registry and is the cause of the problem, delete the MSCRMClient folder under:

HKEY_CURRENT_USER\Software\Microsoft\MSCRMClient

and this should resolve the problem.

Rollup Installation Cannot load Counter Name data because an invalid index '' was read from the registry

This is related with a performance counter corrupted on the user machine. To fix the problem run the following commands on the command prompt:

C:\lodctr /r
C:\lodctr /e:CRM Client



Please leave feedback and if you have any gotchas like this please let me know, hope this helps someone.

Happy Holidays!

I wish everyone a merry Christmas and a great new year! Thanks for reading my blog, I'll see you in 2013 with more interesting articles. It was a very busy year and I hope you enjoyed to stop by and found something useful and productive.

Thanks and see you next year.

Dynamics CRM Database Optimization with DMVs

On this article I will be covering mainly optimization around indexes and how DMV's can help to monitor and optimize indexes.

Data management views (DMV's)
Dynamic management views store SQL transactional information which can be used to monitor health, performance and diagnose problems. DMV's are extremely helpful in particular around performance-tuning. The information is stored until you restart SQL server, after a restart all is reset to 0.

There are a number of DMV's to retrieve a different number of metrics for different purposes; we will be looking in particular at the Index related and Execution DMV's:

• Index
• sys.dm_db_index_operational_stats
• sys.dm_db_index_physical_stats
• sys.dm_db_index_usage_stats
• sys.dm_db_missing_index_columns
• sys.dm_db_missing_index_details
• sys.dm_db_missing_index_group_stats
• sys.dm_db_missing_index_groups
• Execution
• sys.dm_exec_query_stats
• sys.dm_exec_requests

1. http://msdn.microsoft.com/en-us/library/ms187974(v=sql.105).aspx
2. http://msdn.microsoft.com/en-us/library/ms188068(v=sql.105).asp

Indexes Overview
Imagine walking to a library with 1 million books stored across 5 floors, and ask for a book. If the library does not keep a record where each book is stored, which floor, shelf, categories etc... how long to you think staff would take to find your book? a very long time. The same concept applies to databases, indexes are needed to improve response time when users request specific data.

We will be mainly looking at optimization with indexes so before we do that I want to explain very briefly what they are and how they work. There are two types of indexes, clustered index and non-clustered index.

• Non-Clustered IndexesThis type of index, indexes a particular column and hold a reference to where on the table this data can be found. The example below, shows if you want to find an order with an amount of 210 you would need to scan the entire table to find all orders with an amount of 210. If we create an index for the column 'Amount', the index it's ordered descendent and the query would stop at the last 210 and there would be no need to continue the execution.

• Clustered IndexesClustered indexes have the same concept but, they sort the column 'Amount' descendent on the table itself, when orders with the amount 210 are found SQL does not need to lookup the reference to find the details of that order, the data is accessible immediately. 

Both type of indexes have positive and negative areas, the clustered index has a higher cost when you need to re-index the column 'Amount' because all the rows need to shift into the correct place, however the data it's immediately available when data is found. The non-clustered indexes are quicker to re-index but require an extra lookup to gather the data on the table. Also note that the clustered indexes only one index can exist per table. The non-clustered indexes can be created for multiple columns on the same table.

Missing Indexes
DMV's store missing indexes when queries are optimized by the SQL query optimizer; if you have a customized CRM environment you should monitor these type of DMV's on a regular basis. Below a nice script you could use with references to missing indexes DMV's.


SELECT   migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,   'CREATE INDEX [myIndex_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)   + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'   + ' ON ' + mid.statement   + ' (' + ISNULL (mid.equality_columns,'')     + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END     + ISNULL (mid.inequality_columns, '')   + ')'   + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,   migs.*, mid.database_id, mid.[object_id] FROM sys.dm_db_missing_index_groups mig INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10 ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
Index Usage
It's important you check for Index usage because indexes consume memory and disk I/O resources when they need to be rebuild to keep the most up to date data.

To check for index usage run the following query:

 select OBJECT_NAME(a.object_id),b.name,a.user_seeks,a.user_scans,a.* from sys.dm_db_index_usage_stats a join ORG_MSCRM.sys.indexes b on a.object_id = b.object_id and a.index_id = b.index_id where a.database_id = DB_ID('ORG_MSCRM') and b.name like 'myIndex%' order by b.name asc The output of this query would be something like:

From the above screenshot is clear that _DMV_09 has not been used since it was created and therefore should be deleted.

Fragmentation
CRM ships with built-in maintenance jobs which are configured during installation and are designed to optimize the database and reduce fragmentation. It's good practice to re-configure these jobs to run out-of-hours.

To configure the built-in sql maintenance jobs download the following tool:
http://crmjobeditor.codeplex.com/

Place the tool under C:\Program Files\Microsoft Dynamics CRM\Tools\

The tool provides a flexible and easy way to configure the maintenance jobs; note that the job will run once every day( every 1440 minutes), you can configure it to run more times during the day.

Normally the CRM built-in maintenance jobs are designed to keep the system healthy and do a very good job, to understand if you need these jobs to run more often you can monitor fragmentation with the following queries:

SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level, avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count FROM sys.dm_db_index_physical_stats (DB_ID(N'ORG_MSCRM'), NULL, NULL, NULL , 'SAMPLED') ORDER BY avg_fragmentation_in_percent DESC
Or you can be more specific at the table level:

SELECT avg_fragmentation_in_percent,* FROM sys.dm_db_index_physical_stats ( db_id('ORG_MSCRM'), OBJECT_ID('PrincipalObjectAccess'), NULL, NULL, 'DETAILED')

You can check if the maintenance tasks have run successful by looking at the last time the indexes have been updated:
SELECT name AS index_name, STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID('PrincipalObjectAccess') Check for expensive queries
With a better understanding around indexes and DMV's we could start looking at top 20 most expensive queries in your CRM environment using sys.dm_exec_query_stats to give you a better picture what is going on under the hood and analyse whether tuning is needed for those queries.

SELECT DISTINCT TOP 20 t.TEXT QueryName,
s.execution_count AS ExecutionCount, s.max_elapsed_time AS MaxElapsedTime, ISNULL(s.total_elapsed_time / NULLIF(s.execution_count,0), 0) AS AvgElapsedTime, s.creation_time AS LogCreatedOn, ISNULL(s.execution_count / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()),0), 0) AS FrequencyPerSec FROM sys.dm_exec_query_stats s CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t ORDER BY s.max_elapsed_time DESC

Database Maintenance
However CRM maintenance jobs do a really nice job in keeping the system healthy, I would still recommend  creating SQL jobs to do regular out-of-hours database maintenance:

• Shrink the database
• DBCC SHRINKFILE (1, TRUNCATEONLY);
• DBCC SHRINKFILE (2, TRUNCATEONLY);
• Rebuild indexes
• EXEC sp_msforeachtable 'DBCC DBREINDEX("?"," ",80)'
• Update statistics
• EXEC sp_updatestats;

Summary
There are a number of scripts and documentation on the internet around DMV's, is good practice monitor these metrics and optimize the system accordingly. Note that with each SQL version DMV's may change and new features added etc...

We have covered basic SQL optimization around indexes and an overview on how indexes work and how they can help. I hope you enjoyed the article, please leave your feedback.

References

1. http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx
2. http://msdn.microsoft.com/en-us/library/ms188068(v=sql.105).aspx