Migrating Data Into Dynamics CRM 4

Roshan Mehta, 17 June 2010

Data Migration is a massive step of implementing Microsoft Dynamics CRM. As a business, rather than throw away all the data that you have stored in your old system, you'd want to keep all that data and move it from the old system into CRM. This is a process that requires a lot of time and effort, and you need to be sure that your data is moved both quickly and accurately.

I have just completed a data migration from an old Access database into Microsoft Dynamics CRM. The Access database contains over 800,000 records, and contains 15 different tables. There are many steps to writing a data migration tool, and many different problems may arise.

Steps to create a data migration tool

1. Analysing the old database - It is important to have a good look at the structure of the old database, and how the data is stored. This stage is important so you know how each database column will map to a CRM attribute, and what data types you are working with.
2. Data cleaning - Bad data can affect the migration process. For example, when converting the mdb file to an SQL Server 05 file format, the conversion wizard complained about bad data, and the conversion for some tables was not complete. The bad data in this case was date values not being entered correctly, and also negative money values.
3. Writing the tool - The migration tool needs to be fast and accurate. Minimizing the number of CRM retrievals can greatly improve the performance of the migration tool. Also be sure to accurately map the database fields to the correct CRM fields. Make sure you include code to update a record if it already exists. This will allow you to re-run the migration tool if needed, without having it create duplicate records in CRM.
4. Testing - After the tool is written, test the tool on a development server to see if it is creating records correctly and not crashing anywhere.
5. Monitoring - If an error occurs during the migration, it is useful to know on which database table row the error occurred. I chose to print out the table name and row number, so that when the migration crashes, I will be able to check the raw data in the database and find out why the error has occurred. When restarting the migration tool, you don't want to import all of the records again that have been migrated successfully. It is a good idea to tell the migration tool to start at a specific row of a database table, and resume where the error occurred to save time.

I ran into a few problems when creating the data migration tool. One of the problems was the incompatibility issues between SQL Server 2005 and SQL Server 2008. I had converted the mdb file to an SQL Server 2008 database, and then tried to deploy this to the clients CRM server which uses SQL Server 2005. This was a major issue that took some time to solve.

Another problem was underestimating the time required for the migration to take place. Before the final migration tool, I had created a migration tool with a subset of the final data which I ran on a UAT environment. The records were migrated quickly into the CRM development environment. I needed to take into account that the final database size would be much larger than the subset, and would require a lot more time to migrate than the subset.

So to sum up my experience of creating and running a migration tool, it is important to have a plan before actually creating the tool. This will help you decide which order you want to migrate the tables in, and also which database fields need to map to CRM. Also, make sure you run a test of the migration tool using a subset of the data, and record the time taken to complete. This will allow you to estimate how long the final migration will take.