Migrating GoldMine to Microsoft CRM 3.0:
History of the Project

The Challenge

What needed to be done was to migrate an estimated 18,000 contacts + details, cutomizations etc... from our -up till now- used Goldmine database to our newly aquired Microsoft CRM 3.0 system. The source system was Goldmine 6.0 using DBase, and our target system was Microsoft CRM 3.0 using SQL Server 2005. Getting these on the same page data-wise was not an easy task, mainly due to Goldmine being pickish as to its environment and licensing terms.

Accessing the Data

The first job consisted of beeing able to access the goldmine data. This proved to be more difficult than I at first had thought. I had no desire to work with the DBase system directlty, so the idea was to move the Goldmine data from DBase to SQL Server. This required a seperate Goldmine license (corporate enterprise if I remember correctly). So we bought a single user license for Goldmine 6.5 in order to be able to do this. The idea was that Goldmine would just upgrade and transfer the data. Unfortualtely Goldmine decided that the license type wouldn't match and refused to comply. This meant exporting the current Goldmine data into a transfer package and reimporting this into the new Goldmine 6.5 installation.
Goldmine 6.5 wouldn't work properly with either SQL Server 2005 or Windows Server 2003 and would not accept a non server environment to set up the database, so in the end I set up an isolated Windows Server 2000 system, and installed SQL Server 2000 from the included disks that came with Goldmine. I had to upgrade to the newest Windows Server Service Pack in order to have everything running, but after that it worked.

I then exported the Goldmine data from the old system and imported it into the new - a few hickups (and days - Goldmine is slow at this) later and the data was safely within the SQL Server 2000 database. I then proceeded to detach it, make a backup (don't forget this!), copy it onto the main SQL Server 2005 system and attach it there. It took me 2 whole weeks to make this process work, but when I was done the the result was that I now had both databases on the same server instance. I would now be able to reference both databases in an SQL script. This would allow me to use simple SQL queries to copy the data. No DTS (Data Tranformation Packages) required.

Aternate way: Using Microsoft Access

Later I discovered that I could have used Microsoft Access instead. In this way one creates a new Microsoft Access Database and imports the Goldmine tables as ODBC links. Then one uses the "Upsize Database Wizard" to get them into the SQL Server database. This worked for most of the tables, except for the CONTACT2 one.

First attempts using DMF

I had previously done a thorough search on the internet to see if anybody had done anything similar to what I was going to attempt. The only useful resource I came accross was Scurvy Jake's Pirate Blog - Migrating GoldMine to Microsoft CRM. This blog was a great help, although it only targets the older 1.2 version of Microsoft CRM. The core of the SQL statements used here are based on that blog. The blog targets the Microsoft Data Migration Framework that simplifies migration, but does not allow for custom fields and tables. As our Goldmine data was extensively customized with over 300 extra fields and also required migrating some of the data to a new custom table (schools), I eventually had to abandon using the Microsoft Data Migration Frameowork in favour of copying the data directly. Even so, the scripts I put together for the Microsoft Data Migration Frameowork served as a basis for the eventual solution.

Structural Overview

Generally the data that needed to be migrated consisted of (in Microsoft CRM Terms):

Filters and Reports were recreated manually, as they were not compatible between the two systems.

The Little Helper

The more SQL scripts I wrote, the more difficult it became to manage and run. Eventually it got to a stage where I got fed up and wrote a little program (SQLTree) that let me organize my SQL scripts into a tree structure that I could then execute against the data source with a single click. It took me 4 days to write that initial version but it saved me many more in the long run. I will be posting the resulting project file at the end of this guide (modified a bit so as not to give out sensitive information about the migrated data) in the format used by this tool. If you want to get at the SQL itself without using my SQLTree tool, the project file is in plain XML format, so you can always just extract the SQL from there (it's a bit messy to do though). One or two bits are not SQL (but C# scripts), so you will have to compensate for them some other way.
Oh, and yes, of course the little SQLTree tool is free and you can get it here: SQLTree: A Tree Structure SQL Script Editor

Copyright Jonathan Gill 2007