Migrate Files To SQL
Exploring
This program lets you migrate the following RTF and CMS data to the SQL database when running SYSPRO 8 on-premise (instead of retaining them on the file system) or vice verse to move the data from the SQL database back to the file system:
-
RTF notepad documents attached to entities
-
CMS Activity body RTF documents
-
CMS Activity attachments
-
CMS Organization lists
New on-premise environments will store the RTF and CMS data on the file system by default.
Therefore, if you configure a new SYSPRO environment that requires the data to reside in Microsoft SQL Server, you must run the Migrate Files To SQL program to perform this migration.
The advantages of moving this data from the file system to the database include:
-
Centralized access to all related data
-
Reduction of file system usage
-
Simplified portability of data and backup strategy
-
Improved performance
-
Provision for enhanced scalability in future releases
This program can be accessed from various locations in SYSPRO:
-
Program list:
Program List > Administration > General Setup
-
Setup Options:
Setup Options > System Setup > System-Wide Personalization
From the Migrate RTF files to SQL from file system or Migrate RTF files from SQL to file system hyperlink against the RTF and CMS files in SQL option.
The name of this hyperlink changes depending on the current state of your file storage.
Starting
-
To ensure that the process completes correctly, the Migrate Files To SQL program must be run without any users being connected or processing related documents.
Solving
The following indicates the location of CMS data on the file system versus the SQL database:
-
CMS Activity body RTF documents
File system location: \Work\crm_{compid}\activity\body
SQL Database: {companydb} - SQL table: CrmActBodyData
-
CMS Activity attachments
File system location: \Work\crm_{compid}\activity\attach
SQL Database: {companydb} - SQL table: CrmActAttachData
-
CMS Organization lists
File system location: \Work\crm_{compid}\list
SQL Database: {companydb} - SQL table: CrmOrgListDefn
The following indicates the location of RTF data on the file system versus the SQL database:
-
RTF notepad documents attached to entities
File system location: \Work\Notes
SQL Database: {systemdb} - SQL table: AdmNotepadData
If you later decide that holding the RTF and CMS data in Microsoft SQL Server is not suitable for your environment (e.g. third party applications require access to the files) then you can migrate this data back to the file system by running the Migrate Files To SQL program again.
Using
When you run the Migrate Files To SQL program to migrate the data from your database to Microsoft SQL Server, you are prompted to confirm that you want to the start the conversion and, once confirmed, the process proceeds as follows:
-
RTF Notes are migrated into the database:
-
A backup folder named Backup_ccyy_mm_dd is created within the \Work\Notes folder on the file system.
-
The system retrieves each entry of the AdmNotepad table that’s flagged as not residing in SQL, records the file name defined and inserts the contents of this file into the AdmNotepadData table of the system-wide database.
-
-
CMS files are migrated into the database:
This process is repeated for each company with the Contact Management System module installed.
-
The system verifies the following:
-
The Contact Management System module is installed for the company.
-
The data files have not yet been migrated to Microsoft SQL Server.
This check allows for a restart if the process previously failed.
-
-
Backup folders named Backup_ccyy_mm_dd are created within the \Work\crm_{compid}\activity and \Work\crm_{compid}\list folders on the file system.
-
The migration of the CMS activity bodies and attachments proceeds as follows:
-
The system retrieves each entry of the CrmActivity table that’s flagged as not yet residing in SQL and writes information regarding the body and any attachments linked to the activity to a temporary file.
-
The contents of each file are uploaded to the CrmActBodyData and CrmActAttachData tables once all entries have been written to the temporary file.
If an activity has multiple attendees, then a copy of the body and attachment rows is written for each attendee. Therefore, the files are only moved to the backup folders once everything has been uploaded.
-
-
The migration of the CMS organization lists proceeds as follows:
-
The system opens each of the organization lists found in the \Work\crm_{compid}\List folder and extracts the information (including the operator, description, etc.).
-
The files are uploaded, and the extracted information is then written to the relevant row in Microsoft SQL Server.
-
-
-
The relevant rows in the AdmNotepad and CrmActivity tables are updated to indicate that the data now resides in Microsoft SQL Server.
-
The files are moved to the respective backup folders once they have successfully uploaded.
-
Upon exiting the program, an entry is written to the AdmSystemAuditLog table indicating if the migration was successful or not.
Copyright © 2022 SYSPRO PTY Ltd.