This is the seventh article within a short series that explores the options available to a partner when moving a customer from Sage CRM Cloud edition (sagecrm.com) to Sage CRM on premise.

The last article raised the importance of maintaining the integrity of data, especially referential integrity as the data moves from one database to another. This article will continue that discussion and look at the practicalities of bringing new fields and custom entities to the destination system and we as ensuring that all communications, opportunities, notes etc remain the children of the correct company and person entities and that no orphaned or widowed records are created.

A default install of Sage CRM premise has 203 tables whereas there are 204 tables in a default Cloud instance.

It is important to remember that within Sage CRM the term Entity, although it can mean just the physical table, actually means the larger scale business object that contains all information a user needs to understand that thing. To be useful the entity Company must include all the persons connected to that company, their names and addresses, phone and emails.

The data tables we may wish to migrate from a cloud database to an on-premise data represent real and conceptual entities. A Company is an example of a concrete Entity as are Persons; a conceptual entity would be an Opportunity or a Case. Sage CRM doesn't make any distinction in the features it provides for either conceptual or concrete Entities. For example, when processing the data of a conceptual Opportunity entity a user will have very similar features available as when that user is finally able to record a concrete Order against the Opportunity.

The image above shows just the tables used in the definition of the Company Entity.

If we determine that we are going to migrate the data from one entity then we need to set the scope of the data that should also be moved.

In the example above, if we want to move all the Companies then to be useful we need to also move the data from all these tables.

  • Company
  • Person
  • Person_Link
  • Address
  • Address_Link
  • Phone
  • PhoneLink
  • Email
  • EmailLink

I will focus on the Company data in this article as it represents a core contact information that the migration would wish to preserve.

The correct import or migration of relational data will be key. Each of these tables will be related to each other through the use of primary and foreign key values. This is the stuff that binds everything together.

Note: The Phone and Email data in Sage CRM add another layer of complexity into moving from one database to another. See the article "Understanding the CRMEmailPhoneData table" for more background information.

Let's assume that we have downloaded the Cloud Database back up and unpacked it.

In my case, the database is on the same server.

The cloud database and the on-premise both use SQL Identities to manage their primary keys.

Our focus needs only to be on copying the data and matching this to the correct tables and columns. We don't have to worry about copying indexes, triggers or constraints.

Note: I have made the assumption that my target system is empty with no demo data installed.

In a previous article, I discussed that we would need to take into account the physical differences between the databases. I suggested a set of questions and in particular, these ones are highly relevant as we prepare to migrate and map data from the Cloud database to the on-premise database.

  • What tables are present in the Cloud Edition which are absent in the default on-premise edition instance?
  • What views are present in the Cloud Edition which are absent in the default on-premise edition instance?
  • What columns are present in the Cloud Edition which are absent in the default on-premise edition instance?

Before the migration, we need to make sure that any application data columns missing from the Cloud database have been added to the target Sage CRM on-premise database. This is best done through the Sage CRM interface.

Using a Script to Migrate Data

In both Sage CRM Cloud Edition and Sage CRM on-premise, primary keys are generated as a result of SQL Identity fields.

We can move the data manually using a set of scripts. To preserve the primary key values we would need to enable the insert of identities.

SET IDENTITY_INSERT [ [ database_name . ] schema_name . ] table_name { ON | OFF }

So we could write our own script with this structure

SET IDENTITY_INSERT Notes ON
insert into CRM.dbo.Notes (Note_NoteId, Note_ForeignTableId, Note_ForeignId, Note_Note, Note_CreatedBy, Note_CreatedDate, Note_UpdatedBy, Note_UpdatedDate, Note_TimeStamp,
Note_Deleted, Note_ChannelID)
select Note_NoteId, Note_ForeignTableId, Note_ForeignId, Note_Note, Note_CreatedBy, Note_CreatedDate, Note_UpdatedBy, Note_UpdatedDate, Note_TimeStamp,
Note_Deleted, Note_ChannelID
from SageCRMCloudEdition.dbo.Notes
SET IDENTITY_INSERT Notes OFF

The explicit value for the identity column in the target table (e.g. 'CRM.dbo.Notes') can only be specified when we explicitly list the columns to be inserted. This allows us to map the columns as we write the script.

You can see from the screenshot below that the script results in the records in the source table being exactly inserted into the target system preserving the primary keys and therefore the referential integrity.

Once you have finished with one entity then back up the target database and continue with the next item to be migrated.

Use the Management Console to Migrate Data

An alternative method to migrate the data is to use the Export Data feature of the SQL Management console.

This allows us to move and map several tables at the same time.

In the image above you can see that I have right clicked my Source database in the Management Console. I then selected tasks and then Export data.

I need to select the Source database, in my system, this is called 'SageCRMCloudEdition'.

Then the Target database, which in my case is 'CRM'.

I want to copy the data rather than write my own query.

And then I can select all the tables that are associated with the entity I want to move. I have selected the 9 entities I listed towards the start of this article.

Note: One of the subsequent migrations will need to account for the users and the teams so that the integrity of assigned data is maintained.

I have to click on mappings for each field and then tick the checkbox 'Enable Identity Insert' to allow the identity information to be migrated.

Once that is done, I can run the package.

and the data will be migrated.

And as you can see below this moves the data from the source (Cloud) database to the (Target) on-premise database very neatly and preserves the identity values and the referential integrity.

I would then need to back up the database so far before proceeding with the next entity that I wanted to migrate.

In summary, a practical procedure to migrate data would be:

  1. Download the Cloud Database
  2. Install Sage CRM on-premise with an empty database - no application or demo data within the system.
  3. Determine the tables and columns missing from the Sage CRM on-premise database.
  4. Add the missing structures through the Sage CRM user interface.
  5. Define the Entities (sets of tables) to be migrated e.g. Companies, Opportunities, Cases, Communications etc.
  6. Backup the target database before starting the migration.
  7. Migrate each Entity and then check referential integrity. 
  8. Backup the target database and then migrate the next entity.

In the next article I will look at the deployment possibilities for Sage CRM on-premise edition; Private, Public, or Virtual Cloud.

Please feel free to add any comments below.

Migrating a SageCRM.com database to an instance of Sage CRM 2018 R3

Links to all the articles in the series