This is the fifth 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.

In this article, I want to look at the physical differences between a Sage CRM Cloud Edition database and a Sage CRM on-premise database.

The previous article considered the question of what a business may want to include in the migration. This is because a migration project needs to consider its scope and take into account the time and resources that are available.

We looked at the reality that managing a project involves understanding and managing three key elements -

•What do we have to do (Scope),
•How much time do we have to do it (Time),
•How much money is available to do it (Cost).

Let's assume that you have exported the database from Sage CRM Cloud edition and that you have successfully unzipped the backup and restored the database into a local instance of MS SQL Server.

You might be tempted to think that you could just run the database with an install of Sage CRM on-premise but there are a number of very important differences. Although the two editions or versions of Sage CRM (Cloud and On-Premise) had the same origin they have been developed as differing products. They have had separate code bases for many years. The Cloud edition has diverged from the On-Premise product substantially. The interface is different, the user navigation and screens are different. There is also a completely different approach to customization whether that is when new entry types are used for new fields or how new custom entities may be added to the system.

This means that you can not just expect to slap a Cloud edition database within an on-premise install and expect things to work. We need to look at the differences in data structures and objects in order to understand the work that needs to be carried out to all the migration to succeed.

Sage CRM's interface depends on the underlying physical data structures that model the entities. These are the data structures that hold the company, person, opportunity and communications plus any custom structures that have been created - this is the business or application data. We will need to understand how these differ between the source Cloud edition database and the target on-premise edition database. But above the application data is the metadata that contains the information about how the company, opportunity data is presented in the interface.

Below are just some of the investigations that need to be carried out

  1. Are there any system parameters added to on-premise that are absent in the Cloud edition?
  2. What default tables are present in the on-premise edition that are not in the Cloud edition?
  3. What are the columns for any new tables? (Name, Data Type, Length)
  4. What new default Views are present in the on-premise edition that are not in the Cloud edition?
  5. What are the columns for any new views? (Name, Data Type, Length)
  6. What new columns exist within default on-premise edition tables that are absent in the Cloud edition?
  7. What columns within default on-premise edition tables have a different Data Type in the Cloud edition?
  8. How does the use of database trigger differ between on-premise and Cloud edition?
  9. What tables are present in the Cloud Edition that are absent in the default on-premise edition instance?
  10. What views are present in the Cloud Edition that are absent in the default on-premise edition instance?
  11. What columns are present in the Cloud Edition that are absent in the default on-premise edition instance?
  12. What are differences in System Actions referenced in Menus between the Cloud Edition and On-Premise edition?
  13. How do Reports and Groups differ between the two systems?
  14. How do workflows differ between the two systems?

These questions may seem daunting at first but actually, we can make this quite a simple task by comparing a vanilla instance of the currently supported version of Sage CRM on-premise with the downloaded instance of the Cloud edition database.

There are a set of scripts available within the download area that are used for investigating new and changed metadata structures in an installation of Sage CRM.

These are found in the download gallery "EXAMPLE COMPONENTS & DEVELOPER RESOURCES (ALL VERSIONS)" but you will need to have the correct rights to access these.

https://community.sagecrm.com/partner_community/m/example_components__developer_resources/28038.aspx

The next article will consider how we will maintain data integrity during the migration of the data. This is especially referential integrity as we will want to ensure all communications, opportunities, notes etc remain the children of the correct company and person entities and that no orphaned or widowed records are created.

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

Links to all the articles in the series