CRM 4/2011 Data Migrations – Lessons Learnt

Gayan Perera, 07 February 2011

*CRM 4/2011 Data Migrations – Lessons Learnt

We’ve done quite a few data migrations over the years and I’d like to share with you some tips and tricks we’ve learnt along the way. Hopefully these will make your data migrations easier.

Note: This is aimed at migrating records upwards of 100,000.

Building the import tool

Before you build a custom import tool see if an existing product can migrate data from your existing system to CRM. If not, you’ll need to build the import tool yourself using the CRM SDK. Here are a few tips:

• Avoid round trips
Try to grab as much data as you can in a single hit, this’ll speed up your import considerably.

• Avoid creating multiple CrmService instances
Create the CrmService once when your import tool starts and then re-use it per record that you’re importing, this’ll remove unnecessary overhead. Especially with CRM 2011 Online if you re-authenticate per record you’ll notice a huge delay in importing record, this can sometimes be 5+ seconds per record.

• Ability to skip records
Add the ability to skip records or start from a specific position to your import tool. We’ve had issues when importing millions of records, the tool would crash due to a bad record and have to start again, not nice when the re-run takes 12 hours+. Another benefit of having this that you can migrate data using multiple machines by splitting records evenly.

• Ability to import a single record
Similar to skipping records add the ability to import a single record, this allows you to re-try bad records or check accuracy after a fix to the import tool without having to import all the records.

• Ability to partially migrate data
Your import tool should have the ability to import units of data, for example, import customers, check for accuracy then move onto customer activities, check for accuracy and move onto the next unit. This allows you to backup at certain points of the migration so that if a unit fails you can simply restore and resume.

• Ability to see progress / speed
It's nice to see how many records has been processed, how many left and how fast it's actually migrating the data.

Port exhaustion errors

Once you start hammering the crm service with multiple requests you'll soon hit a System.Net port exhaustion error, to get around this you can do couple of things.

1. Put a Thread.Sleep() between each record that's being imported. We've found that putting a sleep of 50-100ms works nicely.

2. Open up the registery editor and set the following keys then reboot the machine.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
TcpTimedWaitDelay = 30
MaxUserPort = 65534
MaxHashTableSize = 65536
MaxFreeTcbs = 16000

More gotchas

• DateTime - make sure that the date is greater than 1900-01-01

• Trim/Truncate string values - because crm has length limits make sure you trim/truncate values to the limits that are set in crm

• Negative numbers - by default money fields start from 0, if you want negative values make sure to change the attribute in crm first

Checking for accuracy

All the imports we’ve done so far have taken at least three re-runs to get the data to match up correctly. When we’ve done financial data migrations we wrote SQL queries to match each record in CRM and the legacy system to ensure numbers match. This also helps isolating bad records and refines your tool.

**CRM 4/2011 Data Migrations – Lessons Learnt

Backup & Restore

SQL backup and restore is your friend, for example, create your crm instance, install all the customizations and take a backup, this will be your base image. Now do a data import and check for accuracy, if the data is bad simply restore the base image and try again.

* Image 1 from http://www.istockphoto.com/file_thumbview_approve/5556602/2/istockphoto_5556602-transfer-data.jpg
** Image 2 from http://upload.wikimedia.org/wikipedia/en/5/51/Backup_center_icon.png

Category List

Filter by Author