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

Dynamics CRM 2011 upgrading from trial license

I often see on the CRM forum questions how to upgrade CRM from a trial installation into a full licensed environment without the need to re-install CRM, so decided to create a quick post on how to do this and where to find the newly purchased CRM license key.

Registry key
If you purchased the full license, Microsoft will provide you with a Media download link, so you can download the installation DVD. The key is not provided by the sales assistant's because the registry key is now built-in in the media. However you can retrieve this key form the DVD on:

D:\Server\amd64\License.txt

Upgrade Trial License
Open deployment manager:

PowerShell

You can also set your new license key with PowerShell, below are the commands to do this:

To activate CRM PowerShell commands type the below command:
Add-PSSnapin Microsoft.Crm.PowerShell

To set the new CRM key run:
Set-CrmProductKey -ProductKey [string]

To get your current version:
Get-CrmLicenseProperty

Dynamics CRM troubleshooting Kerberos Authentication

This is Part 2 of my article on improving CRM performance with Kerberos http://quantusdynamics.blogspot.com/2011/12/extreme-performance-with-dynamics-crm.html

Kerberos is not the easiest protocol to troubleshoot due to it's complex architecture, I would like to share with you a few tips on how to tackle kerberos issues.

To troubleshoot kerberos I'm going to use the following tools:

• Wireshark (we could use Microsoft network monitor but for Kerberos I think Wireshark is simpler to use and illustrate)
• Fiddler (capture HTTP packets)
• ADSI Edit or Command prompt setspn
• Microsoft Kerbtray to list/purge tickets or command prompt klist.

If you read the first article on how to improve performance with kerberos, you understand that when you use  custom service accounts you will need unique SPN's configured to allow authentication to succeed. However you could use built-in accounts, the best practice and recommendations from Microsoft is to always use custom service accounts, and it's here where things get a bit more complicated. I'm assuming you have attempted to configure Kerberos based on my first article and you run Fiddler for the first time and let's assume you see this on the first attempt:

This ticket is an NTLM ticket, Kerberos failed somewhere and we will try to find out using wireshark.

Open Wireshark start capturing network traffic and on the search field type: Kerberos

Wireshark shows a packet with error: KRB5KDC_ERR_S_PRINCIPAL_UNKNOWN highlight this packet and on the below screen expand the Kerberos packet details and you find that the missing DNS is HTTP/crm.ha Kerberos is looking for this and can't find it. The next step is to register an SPN under the service account CRM website is running on.

Configuring SPN

a quick way to create visualise SPN's  is using the Microsoft command tool Setspn, open the command prompt and type:

setspn -L DOMAIN\SERVICE_ACCOUNT 

Replace DOMAIN and SERVICE_ACCOUNT with your own domain and service account. This will list any SPN's configured under that account.

If you can't find the SPN listed under that account create the SPN:

setspn -a HTTP/crm.ha SERVICE_ACCOUNT

Run again the first command and check if the SPN has been created.

Another way to create SPN's is using the ADSI Edit GUID, it's easier to manage multiple SPN's for multiple accounts etc..

To install ADSI Edit follow the following instructions:

On the Server manager under Features add new features and navigate to the following options:

When installed  go to: Start menu > Administrative Tools > ADSI Edit

Open ADSI Edit and right click and choose connect to...
On the below screen type the name of the object you want to use, and leave default naming context and type the SERVER_NAME click ok. replace SERVER_NAME with the name of Domain controller in your network.

Below Expand the folders until you see the service account you want to configure the SPN's

 When you find the account, right-click and properties. The below screen appears. Scroll down to servicePrincipalName and click Edit.

On the below screen you can manage the SPN's and add the needed SPN, you can type HTTP/crm.ha and click OK and Apply and it's done.

Looking for Kerberos tickets

We have configured the missing SPN, the next step is to run wireshark and look for the kerberos ticket.

Highlighting the first packet show us what request's are going through in the process.

I highlight the first HTTP packet and I already see the first negotiation using Kerberos tickets look for Authorization: Negotiate YIIUM this is a Kerberos ticket.

Below I highlight the next in line HTTP packet HTTP/1.1 found and you see a reply with a kerberos ticket again.

It looks like we are now using Kerberos. if we look back at Fiddler now, we should see the following:

Also another way to check your kerberos tickets it's using Microsoft Kerbtray or the command line prompt klist.

Usage: Klist.exe [-lh ] [-li ] tickets | tgt | purge

On the command line run:

klist [ENTER] This should show you all kerberos tickets issued to your machine and user account, you may see a number of them.

To purge the tickets do:

klist purge

You can also use a GUID tool called kerbtray you can download kerbtray from here:

• http://www.microsoft.com/en-us/download/details.aspx?id=17657
• http://www.microsoft.com/en-us/download/details.aspx?id=7887

Kerbtray will appear as a green icon on your tray area, right-click this icon and you can purge tickets or list the current tickets. This is the same functionality as the klist command.

Event Logs

You could also look for kerberos authentication issues by enabling Kerberos debug logging on the server, this would generate a number of detailed event logs on your server which can be quite handy:

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

Summary

I hope this was helpful, the article is designed to give an overview of the tools available for troubleshooting kerberos authentication issues and how to use this tools to look for the right information.

References
If you interested in learning more about Kerberos and it's architecture, I would recommend you read this article which gives a great overview on this. You may also find loads of high-level articles on this.

• http://cs.unc.edu/~fabian/course_papers/steiner_neuman.pdf