This is the third 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 consider the different options for extracting the data from an instance of Sage CRM cloud edition. There basically three broad options. The first is using the standard features of Sage CRM to export selected Contact information, the second option is to use Web Services (both RESTful and SOAP interfaces) to extract data from the live Cloud instance and the second is downloading the database and additionally the document library.
Each option has its advantages and disadvantages.
Export using standard features
Exporting results from a Find or and Advanced Find
Sage CRM Cloud edition offers simple ways of exporting information in CSV or text format.
Any users, if they have been given the correct rights, can quickly create a list report in CSV or Text file format from any search list, saved search, or group based on a primary entity.
- Entity (Result List Name)
- Company (CompanyGrid)
- Person (PersonGrid)
- Case (CaseGrid)
- Opportunity (OpportunityGrid)
- Lead (LeadGrid)
- Communication (CommunicationFindGrid)
- Order (OrderGrid)
- Quote (QuoteGrid)
If a group is exported to a CSV file, it is saved in Shared Documents on the Main menu.
And in addition to the system entities listed above you can also create exports for any custom entities that have been created. The lists used to display the results of a search all follow the naming convention [EntityName]Grid.
The data that is included in the export depends on the definition of the grids used to display the search results. If a column is included in the list then it will be exported to the CVS or text file. This means that exporting data in this way is only going to provide a basic set of information. Furthermore, it will not include suppressed or hidden system information - and importantly this includes the primary key values.
One of the challenges of moving data from one system to another is going to be the maintenance of data integrity. We have to have an easy way of maintaining relationships and the primary key values are crucial for our ability to do this.
A group is a defined set of records that all belong to the same type of entity. You can create a group based on opportunities or a group based on companies or a group of cases. Each of the main entities including new custom entities can allow groups to be added and the starting point for groups is the definition of a view.
The views can join multiple tables together looking at for example a list of contacts, the details of their company, their default address, phone and email details. But the views that are used to create groups need to be designed so that each row returns a unique person, company or lead id.
This means that you can export all of the core contact details for a company, a person or lead and aggregate all sorts of information. But groups will only include a single instance of the company, person or lead.
You can define which columns appear in the group results when you define the group but like search results, these will not include hidden system fields such as the primary key values. In addition, the export will only export the columns in the list, for the group.
Export to CSV from Reports
Reports offer the system administrator the ability to use a view that has been defined that draws information together in the way that is needed. Like for groups, the views used in Reporting can join multiple tables together but unlike Groups and the Search screens, we are not limited to primary entities. Reports can be written based on views that are based on any table within the system. Reports also differ from the Groups because they are quite happy to work with duplicated person, company and lead information.
We are able to include any column from the underlying view within the result set of the report with the exception that like Groups and exports from Find screens, we can not include hidden system fields. We can not, by default, report on the primary keys of the records included in the report.
The export using standard features is quick and easy but the major issue that is associated with it is that whether we use the export from search, groups or reports we are basically taking slices of the data and importantly we do not have access to the primary key values. This means that once we have the data, for each entity, company, opportunity, communication etc, we do not have the key values that are needed for referential integrity. Exporting via the standard features is good for a simple export of, for example, contact information we would then be hard pressed to be able to associate the data subsequent exports of opportunity, case or communication data.
Another limitation of exporting using the standard features is that it only references the business data stored in the database. It does not include documents or files that have been uploaded to the system. Neither does it include any information about workflow or business rules.
Using Web Services to extract information
I will consider 3rd party tools and services in a later article. There are products and services provided by several Sage partners that offer the ability to extract, transform and then load data into another Sage CRM instance and these interactive tools will connect and use either the RESTful web service or the SOAP equivalent.
Let's look then at the practicalities of using these APIs directly within a data migration project.
Firstly whether we are thinking about the RESTful SDATA API or the classic SOAP Web Service it is important to note a few things that they have in common, namely that they share with the main user interface but that is all. They will return the primary key and foreign key information for business data that is queried and will enable referential integrity to be maintained as the data is imported into a new system.
There is no GUI provided by the web service interfaces and the interfaces do not extend to cover business rules, whether these are defined in the screens or workflow. You will also need to create the mechanism to handle the use of the API whether this is a Web page or an executable program, in order to provide users with the required functionality. And this programme must control error handling and session management.
Do you have custom entities?
Probably the main factor in choosing whether to use the RESTful SData interface over the SOAP web service is the presence of custom entities within the cloud system
New Entities within a Sage CRM Cloud edition instance will have created using the Sage CRM Builder. This is a utility similar to the Advanced Customization Wizard that will create the data structures and base metadata needed by a new custom entity. But the way in which Builder creates the entities is different from the way in which new entities are added using the Advanced Customization Wizard.
The new Entities created by the Sage CRM Builder use link entities and new data types, unlike Sage CRM on-premise. The new structures (the Link Tables and Data Types) created are not exposed fully by the SOAP WSDL
You have other issues too, the SOAP WSDL is strongly typed with only limited schema discoverability and with challenges around exposing metadata and other services.
There is a range of factors that will affect the performance of any web service such as web server response time and availability, client application execution time; database views and indexes that are implicitly referenced in CRM. Nevertheless, the Web Service SOAP messages move much more data than the average HTTP GET or POST call, which may adversely impacting network performance. Repeated SOAP-client calls to access the server could start to degrade the performance.
The SOAP Web Services suffer then the disadvantage that they do extend over any custom entities and much great consideration needs to be given to the design of the web service client application and the SOAP methods used to retrieve data.
Because of SData's common way across Sage CRM of describing new objects and discovering them the RESTful SDATA interface is likely to be a better choice when retrieving data. BUT the critical issue is that only application data is covered by the web services - whether SOAP or SDATA. This means that you can not access metadata, the aspect of the system that defines the interface and business rules that controls how the user experiences the system. Neither can the web services select documents or files that have been uploaded to the system.
Exporting the Database
Although the Sage CRM Cloud edition and Sage CRM On-Premise had the same origin, the Cloud edition has diverged from the On-Premise product substantially. The database of a cloud edition cannot just be swapped over to work with an On-Premise edition. BUT working with a full database of source system with complete access to the target system means that is it so much easier to carry out data migration from one system to another.
This can be done by downloading the database from the instance of Sage CRM. You can schedule a download from
<My Profile> | Administration | Data Management | Database Download.
Importantly this is also the place where you can download the document library.
You will be notified once the database is ready to download. The database will need to be unzipped and can be created as a new database under a local install of Microsoft SQL Server. Note: This can be Microsoft SQL Server Express.
I've taken quite a lot of words to reach this point but basically for any migration that is going to be carried out that includes documents, metadata (including business rules and workflow), custom entities and business data then you are best approaching this by downloading the entire database.
Migrating a SageCRM.com database to an instance of Sage CRM 2018 R3
Links to all the articles in the series