Crm 2013 – Upgrading from an ex-Crm 1.2 organisation

This post should only affect a small fraction of Crm 2013 users, but if you do have a CRM organisation that was first created in Crm 1.2, and upgraded through the versions to Crm 2013, you may get an “unexpected error” message when opening account contact or lead records that had been created in Crm 1.2 (I told you this wouldn’t affect many people, but we do still have, and interact with, customers from Crm 1.2 days).

The cause of this is the ‘merged’ attribute. Record merging (for accounts, contacts and leads) was introduced in Crm 3.0, and a ‘merged’ attribute was created to track if a record had been merged. For all records created in Crm 3.0 and higher, this attribute was set to false, but for records created in Crm 1.2, the attribute was null.

This causes a problem in the RTM build of Crm 2013. If you enable tracing, you will see an error like the following:

Crm Exception: Message: An unexpected error occurred., ErrorCode: -2147220970, InnerException: System.NullReferenceException: Object reference not set to an instance of an object.
   at Microsoft.Crm.BusinessEntities.RecordDisabledMergedNotificationGenerator.BusinessLogic(IBusinessEntity entity, IOrganizationContext context, NotificationAdder notificationAdder)

So, that’s the problem. There are three ways to fix it:

• If you’ve already upgraded, then the quick, but unsupported, fix is via direct SQL statements that set the merged attribute to false (see below)
• If you have not yet upgraded, you can merge each affected record in turn with a dummy record, which will set the merged attribute.
• You can automate the merge process programmatically by submitting a merge request for each record, and passing appropriate parameters. I’m not sure if this will work after the upgrade, or only before, as I’ve not tried it

Unfortunately (but unsurprisingly), the merged attribute is not ValidForUpdate, so you can’t use a simple, supported update request to set the attribute

The SQL statements for an unsupported fix:

update contact set merged = 0 where merged is null

update account set merged = 0 where merged is null
update lead set merged = 0 where merged is null

Crm 2013 – No more ExtensionBase tables

So, Dynamics Crm 2013 is here, and there’s lots to say about the new UI, and the new features. But, many others are talking about these, so I thought I’d start with what may seem to be an obscure technical change, but it’s one that I welcome, and which is a significant contribution to the stability and performance of Crm 2013.

With Crm 3.0, Microsoft changed the underlying table structure so that any customisable entity was split across 2 tables; a base table that contained all system attributes, and an extensionbase table for custom attributes. For example, there was an accountbase and an accountextensionbase table. Each table used the entity’s key as the primary key, and the extensionbase table also had a foreign key constraint from the primary key field to the primary key in the base table. Each entity has a SQL view that joined the data from these table to make it appear as one table to the platform. As I understand it, the main reason for this design was to allow for more custom attributes, as SQL Server had a row-size limit of 8060 bytes, and some of the system attributes were already using ~6000 bytes.

The same table design was retained in Crm 4.0 and Crm 2011. However, Crm 2011 introduced a significant change to the plugin execution pipeline, which allowed custom plugins to execute within the original SQL transaction. This was a very welcome change that provided greater extensibility. However it did mean that the duration of SQL transactions could be extended, which means that SQL locks may be held for longer, which means potentially more locking contention between transactions. In very occasional circumstances, a combination of certain plugin patterns, the design of the base and extensionbase tables, and heavy concurrent use, could give rise to deadlocks (see below for an example).

Given this, I’m very glad that the product team retained the facility to have plugins execute within the original transaction (then again, it would be hard to remove this facility from us). It wouldn’t be realistic to ask customers to reduce concurrent usage of CRM, so the only way to reduce the potential deadlock issue was to address the design of the base and extensionbase tables. From my investigations (sorry, but I actually quite like investigating SQL locking behaviour), a substantial improvement could have been made by retaining the table design, but modifying the SQL view, but a greater improvement comes from combining the tables into one. An added advantage of this change is that the performance of most data update operations are also improved.

Deadlock example

Here are two SQL statements generated by CRM:

select
'new_entity0'.new_entityId as 'new_entityid'
, 'new_entity0'.OwningBusinessUnit as 'owningbusinessunit'
, 'new_entity0'.OwnerId as 'ownerid'
, 'new_entity0'.OwnerIdType as 'owneridtype'
from new_entity as 'new_entity0'
where ('new_entity0'.new_entityId = @new_entityId0)  

And

update [new_entityExtensionBase]

set [new_attribute]=@attribute0
where ([new_entityId] = @new_entityId1)
 
These were deadlocked, with the SELECT statement being the deadlock victim. The locks that caused the deadlock were:

• The SELECT statement had a shared lock on the new_entityExtensionBase table, and was requesting a shared lock on new_entityBase table
• The UPDATE statement had an update lock on the new_entityBase table, and was requesting an update lock on new_entityExtensionBase table

The likely reason for this locking behaviour was that:

• Although the SELECT statement was requesting fields from the new_entityBase table, it had obtained a lock on the new_entityExtensionBase table to perform the join in the new_entity view
• The UPDATE statement that updates a custom attribute (new_attribute) on the new_entity entity would have been the second statement of 2 in the transaction. The first statement would modify system fields (e.g. modifiedon) in the new_entityBase table, and hence place an exclusive lock on a row in the new_entityBase table, and the second statement is the one above, which is attempting to update the new_entityExtensionBase table

Both operations needed to access both tables, and if you’re very unlucky, then the two operations, working on the same record, may overlap in time, and cause a deadlock.

The new design in Crm 2013 solves this in three ways:

1. With just the one entity table, the SELECT statement only needs one lock, and does not need to obtain one lock, then request another
2. Only one UPDATE statement is required in the transaction, so locks are only required on the one table and they can be requested together, as they would be part of just one statement
3. Both operations will complete more quickly, reducing the time for which the locks are held

Of these 3 improvements, either no. 1 or 2 would have been sufficient to prevent deadlocks in this example, but it is gratifying that both improvements have been made. The third improvement would not necessarily prevent deadlocks, but will reduce their probability by reducing overall lock contention, and will also provide a performance improvement.

SQL Setup error "Registry properties are not valid under this context"

When using new versions of software (in this case SQL Server 2012 service pack 1), there's always the chance of a new, random error. In this case it was "Registry properties are not valid under this context" when attempting to add a component (the Full-text service) to an existing installation.

It seems like the issue comes down to the sequence of installing updates, both to the existing installation, and to the setup program. The specific scenario was:

• The initial install of SQL Server had been done directly from the slipstreamed SQL Server 2012 service pack 1 setup. At this time, the server was not connected to the internet, so no additional updates were applied either to the installed components, or the setup program
• When attempting to add the Full-text service, the server was connected to the internet, and had the option set to install updates to other products via Microsoft Update. When I started the setup (which used exactly the same initial source), the setup downloaded an updated setup, and also found a 145 MB update rollup that would also be installed
• Part way through the setup steps, setup failed with the message "Registry properties are not valid under this context"

The problem seemed to be that the setup program was using a more recent update than the currently installed components. Even though the setup program had identified updates to apply to the current components, it had not yet applied them before crashing out with the error.

The solution was to go to Microsoft Update and install the SQL Update Rollup, then go back and run SQL Setup to add the extra component. Interestingly, SQL Setup still reported that it had found this 145 MB rollup to apply, even though it was already installed

Understanding error codes

Error messages frequently include error codes; sometimes they also include useful text that describes the code, but sometimes they don’t, leaving you to discover what the code means. Here's how I decipher CRM and Windows error codes.

CRM Error Codes

The CRM error codes are (reasonably) well documented here in the CRM SDK. If you’re searching for the code, one thing to watch for is that the code may be referenced with a prefix of 0x (which indicates the code is represented in hex) – e.g. 0x80040201. If you search for the code, it’s best to remove the 0x prefix.

It is also possible that you may receive the code as an integer (e.g. -2147220991). If you do, convert if to hex (I use the Calculator application), then search for it.

Windows Error Codes
There is more variation in how you may identify a Windows error code, but they are ultimately numerical values starting from 1, and (as far as I’m aware) are consistent across versions of Windows. Newer versions of Windows may include error codes that don’t exist in previous versions, but the same error code should have the same meaning across versions.

There is a quick and easy way to get the message associated with a given code – go to a command prompt and enter NET HELPMSG - e.g.

NET HELPMSG 5

And you’ll get the result

“Access is denied”

This is the message for error code 5 (which is probably the most common code I encounter, though I don’t keep stats on this…)

So, that’s fine if you’ve been given the error code as an integer value (I don’t know what the highest valued error code is – it’s probably either in the high thousands, or maybe 5 digits), but it’s not always that easy.

The code may be in hex(aka hexadecimal). If it contains one of the characters a-f, then it’s in Hex and you’ll need to convert it to decimal. I use the Calculator application to do this. Also, the value may be provided in hex but comes out just as digits. So, if I have an error code, and the message seems entirely irrelevant, I normally convert the code as if it were in hex to decimal, then pass it to NET HELPMSG.

The code may be in hex, but supplied as a 32-bit (or maybe 64-bit) integer with some higher bit flags set, for example:
80070005
0x80000002
&H80040035
The prefixes 0x and &H are some ways to indicate the value is in hex, and these prefixes can be discarded. You can also discard all but the last 4 characters (in these examples 0005, 0002 and 0035) and convert them from hex to decimal (in these examples giving 5, 2 and 53 respectively).

Finally, you may get a 32-bit integer with some higher bit flags set, receive the value in decimal, rather than hex. These almost always have the highest bit of a 32-bit value set, which means that in decimal they come out around 2 147 000 000 (or more commonly as a negative number, as they are typically signed integers). So, if I got an error code of -2147024891, I would:

1. Convert it to hex, giving 80070005
2. Discard all but the last 4 characters, giving 0005
3. Convert it back to decimal, giving 5
4. Run NET HELPMSG 5, and find that I’ve got another ‘Access is denied’ message

The given key was not present in the dictionary - what it means, and how to avoid it

A common error posted on the CRM Development forum is ‘the given key was not present in the dictionary’. This is a relatively easy error to diagnose and fix, provided you know what it means. It will also help to identify the line in the code at which the error occurs, which is most easily determined by debugging.

The error refers to a ‘dictionary’, and a ‘key’. The ‘dictionary’ is a type of collection object (i.e. it can contain many values), and the ‘key’ is the means by which you specify which value you want. The following two lines of code both show an example:
Entity e = context.InputParameters["Target"];
string name = e.Attributes["name"];  // Note that this is equivalent to: string name = e["name"];
In the first line, InputParameters is the dictionary, and "Target" is the key. In the second line, Attributes is the dictionary, and "name" is the key. The error ‘The given key is not present in the dictionary’ simply means that the dictionary does not have a value that corresponds to the key. So, this error would occur in the first line if InputParameters does not contain a key called "Target", or in the second line if there were no "name" in Attributes.

The way to avoid these errors is simple; test if the key exists before trying to use it. Different collection classes can provide different ways to perform this test, but the collection classes in the CRM SDK assemblies all inherit from an abstract DataCollection class that exposes a Contains method, so you can use a consistent approach across these collection classes.
if (context.InputParameters.Contains("Target"))
{
 Entity e = context.InputParameters["Target"];
 if (e.Attributes.Contains("name"))
 {
  string name = e.Attributes["name"];
 }
}

There are a few common reasons of the use of CRM collection classes where a key might not be present when you expect it:

• Within a plugin, the values in context.InputParameters and context.OutputParameters depend on the message and the stage that you register the plugin on. For example, "Target" is present in InputParameters for the Create and Update messages, but not the SetState message. Also, OutputParameters only exist in a Post stage, and not in a Pre stage. There is no single source of documentation that provides the complete set of InputParameters and OutputParameters by message and stage, though this post provides a list of the most common ones for CRM 4, and most of these still apply in CRM 2011
• The Attributes collection of an Entity will only contain values for attributes that have a value. You may get the Entity from a Retrieve or RetrieveMultiple having specified a ColumnSet with the attribute you want, but this attribute will not be present in the Attributes collection if there were no data in that attribute for that record
• Within a plugin, the Attributes collection of an Entity that you obtain from the "Target" InputParameter will only contain attributes that were modified in the corresponding Create or Update method. Using the example above, if this were in a plugin registered on the Update message, the "name" attribute would only be present if the "name" attribute was changed as part of the Update; the "Target" InputParameter will not contain all the attributes for the entity

Reporting Services log files

This should be a very brief post. I find myself replying to many posts in the CRM forums about reporting problems, and most commonly direct people to the Reporting Services log file(s), which provides a lot of potentially useful information. The main information you can get is:

• More detail than CRM will provide on errors running a given report
• Whether or not a report is being run

In general, I find the information reported in the log files to be self-explanatory, but if I find examples of messages than could benefit from further explanation, then I'll post them here.

The log files are in the Reporting Services\LogFiles folder within the Reporting Services directory. On a default instance on SQL 2008 R2, this would be:

%Program Files%\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles

Using WSDL Proxies with CrmOnline - Recent changes

A recent change applied to Crm Online in EMEA affects the information needed to connect to Crm Online if you don't / can't use the .Net 4 CRM SDK assemblies. This article gave information that was valid at the time, but the AppliesTo parameter now needs to be urn:crmemea.dynamics.com, instead of urn:crm4.dynamics.com

I would expect similar changes would occur in other data centres, but unfortunately I don't know of a way to know when such changes will occur

Thanks to fellow MVP Wim Coorevits for discovering this change

CRM Email Router errors with ADFS

I've been trying hard to get too involved with the detail of ADFS and Claims authentication, but I've not been able to avoid it completely (though as an aside, maybe I should try and adhere to the MCT renewal perspective that believes you cannot be an expert in more than one of Dynamics, Development, or IT Professional).

One issue I got involved is that the Crm Email Router can end up continually crashing if it can't connect to ADFS. This is a significant server stability problem, as the Dr Waston errors can cause the server to be compute bound, taking resources from other services. The symptom is a lot of errors in the event log like 'The authentication endpoint Username was not found on the configured Secure Token Service'. One consideration is whether you have the endpoint configured in ADFS (see http://www.powerobjects.com/blog/2011/06/22/error-with-crm-2011-adfs-and-email-router/ for an example), but another consideration is service dependencies.

In most environments, ADFS would run on a different server from the Crm Email Router, but in smaller or test environments they may run on the same machine. This causes a problem, as the ADFS service is set by default for a delayed start, whereas the Crm Email Router service is not. Therefore the Crm Email Router starts before ADFS, it fails to connect, and all the errors start occurring.

The solution I applied was to set a service dependency so that the Crm Email Router is dependent on the ADFS service. This can be done via a registry value:

1. Use regedit to go to the HKLM\System\CurrentControlSet\Services\MSCRMEmail key
2. Add a Multi-string (REG_MULTI_SZ) value 'DependOnService'
3. Set the value to adfssrv (or add this value if there were already a dependency)

Using wsdlbasedproxies with Claims authentication

The CRM SDK has a little-known, but very useful set of projects called wsdlbasedproxies, which show how to connect to the CRM web services without using the .Net 4.0 assemblies.

When testing the project for claims, I found that it needs some code additions. The code as supplied (in SDK v 5.0.9) sets the credential.Windows property, but this fails with the error "The username is not provided. Specify username in ClientCredentials".

Fortunately, this can be easily fixed by setting the credentials.UserName property instead. To do this, I made the following code replacements:

Replace:
credentials.Windows.ClientCredential = new NetworkCredential(UserName, UserPassword, UserDomain);
with
credentials.UserName.UserName = UserName;
credentials.UserName.Password = UserPassword;

And replace:
client.ClientCredentials.Windows.ClientCredential = credentials.Windows.ClientCredential;
with
client.ClientCredentials.UserName.UserName = credentials.UserName.UserName;
client.ClientCredentials.UserName.Password = credentials.UserName.Password;

Data Migration Performance to Crm Online

I've recently been looking at the rate of data migration into Crm Online using SSIS, and how this can be optimised. I started with a baseline rate of 12 records per second, and have so far improved this to 430 records per second, all using one client machine.

The easiest way to migrate data into Crm Online is via a synchronous, single-threaded process that writes one record at a time, so this was the baseline. It soon became very clear that the performance bottleneck in this scenario is network latency - i.e. the round-trip time for the network packets to make a request to the Crm Online server, and to receive a response back.

So, the challenge was to improve on this, which can be done by addressing each aspect of the simple scenario - i.e.

1. Synchronous v. asynchronous calls


2. Single or multi-threaded - either within one process, or multiple concurrent processes


3. Sending more than one record at a time

So far, I've not tested asynchronous calls, primarily because SSIS is stream-based, and I can't see a way to write out synchronous error output if using an asynchronous pattern. It would be possible to write out asynchronous error information, but for now that would involve too much code rewrites. In general, though, I would expect use of an asynchronous pattern would give similar performance benefits to the multi-threaded approach, though it may be possible to multiply the benefits by combining the approaches.

Multiple threads
SSIS controls the threading behaviour of a package, so rather than try for a multi-threaded single process, I went for running several instances of the same package concurrently, which you can do with the dtexec tool. There are two main aspects to making this work:

1. You will need to be able to partition the source data, so that each package instance submits different records. For my tests, I had an integer identity column on the source data, and used the SQL modulo operator (%) to filter on the remainder from an integer division. For 10 concurrent packages, the where clause was 'WHERE id % 10 = ?' with the '?' replaced by a package variable.


2. The package will not be able to reference any files, either as data sources, destinations or log files, as SSIS will attempt to get exclusive access to the files. So, I used a SQL Server source, and wrote log information to SQL via the SSIS SQL Log Provider

I tested 10 concurrent packages, and this gave between a 7-fold and 9-fold performance improvement.

Submitting multiple records
The Crm API is primarily designed around modifying one record at a time, with a separate request per record. However, CRM 2011 introduced the facility to pass multiple records, using the RelatedEntities property of a 'parent' entity. This allows you to build (in memory) a RelatedEntityCollection of multiple records, then attach this to one record, and submit this as one request.

There are two limitations to this approach:

1. The entities have to be related via a relationship in CRM.


2. The same data operation has to apply to the parent record, and the records in the RelatedEntityCollection

Initially I'd hoped to use the systemuser entity as the parent entity, as there is necessarily a relationship between this entity and any user-owned entity. However, this wouldn't work with limitation 2, as I wanted to update the systemuser, but create the related entities, and this doesn't work.

Instead, I had to make schema changes. I created a new entity (e.g. exc_batchimport), and a 1-N relationship with each entity that I wanted to import. Each request would then create 1 exc_batchimport record, and a configurable number of related records.

I tried various batch sizes, and had success up to a batch size of 1000, but failures with a batch of 5000. My initial view is that the failures come from the number of records, and not the total data size, but I've not tested this extensively.

This approach also gave significant performance gains, though only when network latency was a main performance factor - i.e. it helped a lot when connecting to Crm Online, but gave no appreciable benefit (and in some case, worse performace) when connecting to an On Premise CRM server. Most of the benefit came with a batch size of 10 records, though performance did continue to improve slightly if increasing the batch sizes up to 1000 records.

Performance results
I did the tests running on Windows 2008 Server with moderate capacity:

• A virtual server running via Hyper-V


• One processor core allocated to the server, running at 2GHz


• 4 GB of memory


• Server was running in a hosted environment in England, connecting to the EMEA Crm Online Data Centre

The tests were done writing 100000 new records to a custom entity, writing 2 text fields, and integer field, and an option set, and allowing CRM to generate the primary key values.

Concurrent packages	Batch Size	Time	Records / sec
1	n/a	832	12
10	n/a	1094	91
10	10	276	362
10	100	267	374
10	1000	233	429

Conclusions
The main performance issue with modifying multiple records with Crm Online relates to network latency. I've successfully tested 2 complementary approaches which give a combined 35-fold speed improvement, and it may also be possible to gain further improvements with asynchronous calls.

The performance figures were for a custom entity. I'm intending to do further tests with the customer entities (account and contact), and the activity entities, as each of these require more SQL updates than a custom entity.