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

In this article, I will explore the importance of maintaining the integrity of data, especially referential integrity as the data moves from one database to another. As the system is migrated we will want to be able to ensure 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.

The previous article considered the physical differences between a Sage CRM Cloud Edition database and a Sage CRM on-premise database. The work needed to take into account the differences in data structures and objects will be one of the factors that decide the scope of the migration project.

We will have to decide whether the migration project includes both metadata and the application data. If the cloud system has new fields in screens and lists, new reports and custom entities then we must consider migrating the metadata for those structures.

Data Integrity in Application Data

One of the great strengths of Sage CRM is its openness. Its data model is documented and published and available to members of the Developer Program.

In some applications relationships between the different entities may be fixed or defined within the database. This is not the case within Sage CRM for the application tables.

This was a conscious decision made by the original designers of Sage CRM. The application is not a static application system but is a very flexible and customizable engine to create any kind of business process automation where entities and fields can be added at run-time in an ad-hoc manner from within the application.

Most records within Sage CRM exist in complexes. For example, a company will have child records as you can see from the section of the data model below.

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.

You will 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, therefore, need to think about the mapping of the data from the cloud database system to the new Sage CRM on-premise database. For example when thinking about addresses, phone or email's then the link tables will need to be populated.

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

In a default on-premise system the following base relationships will exist

A company record is directly referenced in the following 'Child' tables

  • Account (acc_companyid) - only certain Sage CRM integrated systems
  • Address_Link (adli_companyid)
  • Case (case_primarycompanyid)
  • Comm_link (cmli_comm_companyid)
  • Email (emai_companyid)
  • Lead (lead_primarycompanyid)
  • Library (libr_companyid)
  • Marketing (mrkt_companyid)
  • Opportunity (oppo_primarycompanyid)
  • Person_Link (peli_companyid)
  • Person (pers_companyid)
  • Phone (phon_companyid)

The link tables in turn link to other tables

  • Address_Link to the Address table
  • Comm_link to the Communication table which in turn may link to recurrences
  • Person_Link to the Person table

The case and opportunity tables exist in workflows

  • Cases link to CaseProgress
  • Cases link to SolutionLink which in turn link to Solutions
  • Opportunity links to OpportunityHistory
  • Opportunity links to OpportunityProgress

Depending on the features implemented

  • Opportunity links to Quotes which in turn link to QuoteItems
  • Opportunity links to Orders which in turn link to OrderItems

It is good news that Sage CRM uses standard SQL Server Identity which means when it comes to importing or migrating data we will have a much easier time of it.

But however, you decide to migrate the data from the cloud database, data cleansing is the big first job that needs to be done.

  • Is the existing data is in good shape?
  • Is there any duplicate data and have you made sure that any fields that will be mandatory within Sage CRM are complete?

Of course, records in Sage CRM can exist in a workflow process. It may not just be enough to bring across the opportunity data and link it to the correct person and company, you may also want to mark it with the correct state within a workflow that has also been imported. This brings up the question of how metadata can be migrated and the need to maintain its referential integrity.

Data Integrity in Application Data

There is strong referential integrity within some of the tables responsible for holding metadata.

Sage CRM needs to map from the classes used within the application layer to the underlying Sage CRM metadata tables.

Sage CRM uses Hibernate as its object-relational mapping tool to maintain the CRM metadata in memory so that it is database independent and fully managed.

Hibernate maps Java data types to SQL data types and provides data query and retrieval facilities used in the Interactive Dashboard and SData provider.

To allow the persistence of the Meta Data Model within Hibernate strict referential integrity needed to be enforced within Sage CRM metadata tables.

The following tables are used by Hibernate

  • Custom_ContainerItems
  • Custom_Edits
  • Custom_Tables
  • Custom_Lists
  • Custom_Pages
  • Custom_Relationships
  • Custom_Screens
  • Custom_ScreenObjects
  • Custom_Scripts
  • Custom_SysParams
  • Custom_Tabs
  • Custom_Views
  • AdvancedFinds
  • AdvancedFindFields
  • Custom_Reports
  • Custom_ReportBands
  • Custom_ReportCharts
  • Custom_ReportFavourites
  • Custom_ReportFields
  • Custom_ReportGroups
  • Custom_ReportSearches
  • ActionSecurity
  • Channel
  • FieldSecurity
  • TerritoryProfiles
  • Territories
  • TerritoryPermissions
  • Users
  • UserSettings
  • LPCategory
  • LPGadget
  • LPLayout
  • LPUserLayout
  • LPTemplateLayout
  • LPTemplateChannelLink
  • LPTemplateUserLink

This has created new MetaData dependencies.

  • Custom_Edits
    • (Custom_Tables)
  • Custom_Views
    • (Custom_Tables)
  • Custom_ScreenObjects
    • (Custom_Tables, Custom_Views[optional])
  • Custom_Lists
    • (Custom_ScreenObjects, Custom_Edits)
  • Custom_ContainerItems
    • (Custom_ScreenObjects x2)
  • Custom_Tabs
    • (Custom_ScreenObjects)
  • Custom_Screens
    • (Custom_ScreenObjects, Custom_Edits)
  • FieldSecurity
    • (Custom_Edits)
  • UserSettings
    • (Users)
  • TerritoryPermissions
    • (Custom_Tables ,Users, TerritoryProfiles, Territories)
  • Channel_Link
    • (Users, Channel)
  • Users
    • (Channel, TerritoryProfiles, Territories)

For example, any insert on TerritoryPermissions will require its parent records to exist first.

In this article, we have looked at the need to maintain integrity for both application data and metadata. We have seen that this is a complex and critical factor in the migration project. The choices we make about the data to be migrated and the need to maintain the relationships within the business data and any business rules will affect the scope of the project.

The next article will look at the practicalities of bringing new fields and custom entities to the destination system.

Migrating a database to an instance of Sage CRM 2018 R3

Links to all the articles in the series