Migration of ThingWorx Project from PostgreSQL to MSSQL Persistence Provider

Hello,

The success depends on whether you use any Database things and SQL Services in your project or you stick to standard ThingWorx data model. If it's the former -- you'd need to thoroughly check your SQL for Postgres-specific syntax. If it's the latter -- you can just export your entities on one system and import them on another.

Assuming you only use ThingWorx data model and didn't create any Database things that access ThingWorx DB:

• Compatibility issues between PostgreSQL and MSSQL
  - None as far as I know. Some apps like DPM only work on MS SQL, but I guess you don't use it.
  
  
• Areas that require special attention to ensure a smooth migration
  - When you export your entities, make sure you choose "Universal export" for things with password configs. Or just remember to (re)configure the credentials after the import manually. When you import entities, make sure you check "Overwrite property values".
  - Use "Include Subsystems".
  - General upgrade topics not related to DB, e.g. deprecated mashup widgets.
  - Import extensions first. Sometimes you need to import them in the right order.
  - Don't forget to also migrate your platform-settings.json, log4j custom config, if any, and the content of your file repositories, if any.
  - After you migrated your entities, migrate data (DataTables, Streams, etc.), too. You can use standard ThingWorx Export feature.
  
  
• Best practices to avoid common pitfalls
  - Export entities in binary format, all at once. In this case you won't need to figure out in what order to import your collections.
  - Perform multiple dry runs
  - Automate export and import
  - Double-check that your collection-level permissions were applied after the import
  - I would perform an upgrade and DB migration as two separate mini-projects, if I were you. For example, first upgrade and run it for some time, then change the DB. This will help you troubleshoot issues and will make it easier to explain to PTC Support when you encounter anything you can't solve yourself.

Docs for reference: https://support.ptc.com/help/thingworx/platform/r9/en/index.html#page/ThingWorx/Help/Getting_Started/ImportingandExportinginThingWorx/ImportingandExportingDataEntitiesandExtensions.html

/ Constantine

Helllo @JV_11087733,

It looks like you have some responses from some community experts. If any of these replies helped in answering your question please mark the appropriate reply as the Accepted Solution. 

Of course, if you have more to share on your issue, please let the Community know so other community members can continue to help you.

Thanks,
Vivek N.
Community Moderation Team.