A customer had the requirement to merge two Sage CRM databases. They had just acquired another company who used the same version of Sage CRM integrated with the same version of a Sage BMS (Accounting System).

They wanted to know whether this could be done and whether there were tools out there to do such a thing.

Below are my thoughts on this.

The first thing to do is to make sure that you've read everything that is in the Help Centre. (http://help.sagecrm.com).

There you will find information in the System Administration Guide and in the Data Upload Workbook.

Merging data or carrying out a migration is a "can of worms". Nearly every instance of merging two Sage CRM databases is different depending on where the data is coming. Basically, have just 4 options to importing data:

1. SOAP Web Services (both flexible and very restricted) - from a REST based possibility see towards the end of this article.
2. COM Based Mechanisms (ASP application extensions or External COM applications)
3. .NET API (designed for the creation of application extensions)
4. Direct Data Interaction (just want to bring data from one database table to another database table)

The good news is that working with Sage CRM installs that use standard SQL Server Identity means when it comes to importing or migrating data we can have a much easier time of it.

But whatever approach is eventually decided upon, data cleansing is the big first job that needs to be done.

• Has the customer made sure that their existing data is in good shape?
• Have they eliminated any duplicate data and made sure that any fields that will be mandatory within Sage CRM are complete?

You need to make sure that the data demonstrates integrity - that the existing primary keys are in place and unique. You need to make sure that the referential integrity is clear. That is both that child records where necessary have valid foreign key information and that parental records have whatever necessary child records associated with them. You do not want to import addresses that are not associated with companies and you do not want to import companies without addresses.

You also need to make sure that field integrity is good. So don't go importing data that breaches the maximum or minimum values of a business rule. Don't go importing Person records that lack a surname field etc.

You then need to think about the mapping of the data from the external system to Sage CRM. For example when thinking about addresses, phone or email's then the link tables will need to be populated.

You will also have to decide the way in which the existing systems relationships will be maintained. So this may require you creating new fields in Sage CRM to hold the old primary key and old foreign information so that after importing into Sage CRM relationships can then be re-established by running update SQL statements on the CRM foreign keys based on the 'old' foreign values.

And it gets more complex. I mentioned populating the address and email tables when importing company or person data. There are intermediate link tables (EmailLink, Address_Link) that need to be populated. And what about setting workflow states? If you want to import Communication data and have e-mails, appointments and tasks all show up in the proper screens then hidden comm_link records will need to be created.

We have all the ingredients to help with data migration and merging of databases. We have the data model. We have documentation around workflow.

And we have documentation and articles around how Communications are structured - this is all related to the Exchange integration and other features. We even have blog articles on general approaches that can be taken.

What we don't have is a single document that brings this all together. But there are resources and tools that development partners have created to simplify imports of existing data into a CRM system.

BUT the correct import or migration of relational data will be key. This is the stuff that binds everything together. You can have an initial load of companies, contacts, leads and address, and even 'notes' through the basic data import mechanism but once we start thinking about activities like opportunities, cases, communications, custom entities etc and then the relationships that bind everything together become the priority.

I described 4 basic approaches to data migration at the top of the article. The API options (1 to 3) make the most sense to me because the data goes through the application layer. There would be fewer imports and less post-import processing to worry about than putting the data directly into SQL.

Where you do not want to face reinventing the wheel, you may wish to consider using a tool created by a partner.

The tools that I know that have been used for data migration projects are

Starfish ETL http://www.starfishetl.com/crm-migration/migrating-to-sage-crm

and

Inaport https://www.inaplex.com/Solutions/CRM-Migration-Solutions

Other partners such as Greytrix offer data migration consultancy. They are very experienced in migrating data from ACT, Goldmine, Salesforce.com, SalesLogix, Maximizer and custom DB's to Sage CRM.

There may be other companies and solutions that customers have used, and would recommend, but these are the ones that came to mind first. Please do share your experiences in the comments below.