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:

    From the Migrate Files to SQL option of the Functions menu.

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.

You can restrict operator access to programs by assigning them to groups and applying access control against the group (configured using the Operator Groups program).

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:

  1. RTF Notes are migrated into the database:

    1. A backup folder named Backup_ccyy_mm_dd is created within the \Work\Notes folder on the file system.

    2. 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.

  2. CMS files are migrated into the database:

    This process is repeated for each company with the Contact Management System module installed.

    1. The system verifies the following:

      1. The Contact Management System module is installed for the company.

      2. The data files have not yet been migrated to Microsoft SQL Server.

        This check allows for a restart if the process previously failed.

    2. 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.

    3. The migration of the CMS activity bodies and attachments proceeds as follows:

      1. 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.

      2. 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.

    4. The migration of the CMS organization lists proceeds as follows:

      1. 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.).

      2. The files are uploaded, and the extracted information is then written to the relevant row in Microsoft SQL Server.

  3. The relevant rows in the AdmNotepad and CrmActivity tables are updated to indicate that the data now resides in Microsoft SQL Server.

  4. The files are moved to the respective backup folders once they have successfully uploaded.

  5. Upon exiting the program, an entry is written to the AdmSystemAuditLog table indicating if the migration was successful or not.