Database Migration: Sybase to MS SQL

Synopsis:
Read about how we helped The Institute for Functional Medicine migrate from a Sybase CRM platform to Microsoft Dynamics/MS SQL.

Challenge:
In order to support their organization's continued growth the Institute for Functional Medicine needed to upgrade their existing CRM software. After extensive research they decided that Microsoft Dynamics would best meet their unique needs. In addition to the normal challenges of configuring and customizing a new CRM platform however there was another complication. They had thousands of customer records in their existing Sage Fundraising software and there was no clear way to import this data into the new system. Each system used its own unique database schema and the two systems were on completely different database platforms - The Sage system utilizing a Sybase backend and the new Dynamics platform requiring a MS SQL environment. Because of the complicated nature of migrating this data when they contacted Big Step Consulting IFM was beginning to consider manually entering all their client records as this appeared to be the only viable solution.

Solution:
From a technical standpoint there were two main challenges in this project. The first was to get access to the existing data and figure out how to import it into a new MS SQL database. The second challenge would be to make sure the data was imported in a way that was consistent with the Microsoft Dynamics database schema. In the absence of a traditional database import/export connector Big Step wrote custom SQL scripts to facilitate migrating the data out of Sybase. These scripts essentially were a query whose results generated the insert statement we would run to create the record in our new MS SQL database. This is a technique we often rely on in situations where we have limited control or access to the source database. It allows us to easily migrate data from any database system we can run a simple query on.

Having tackled the data export process we then utilized Microsoft CRM Data Migration Manager to facilitate transferring the existing customer data into the new Dynamics system. Part of the Migration Manager system is a well documented temporary database that Microsoft provides. By mapping our exported data to the appropriate fields in this temporary database we were then able to run the Migration wizard and import all the critical client data into Microsoft Dynamics.

Although this entire process sounds complicated the development portion was completed over a weekend and resulted in no downtime for IFM. They left the office on Friday and when they returned Monday their new CRM system was configured and contained all the contact records and invoices from the previous Sage system.

Technologies Used:
Sybase
MS SQL
TSQL
Microsoft Dynamics/Microsoft CRM