Migration of ThingWorx Project from PostgreSQL to MSSQL Persistence Provider

[JV_11087733]

Hi,

I’ve developed a project in ThingWorx (V9.6) using PostgreSQL as the persistence provider. We are now planning to migrate this project to a new ThingWorx(V9.7.1) environment that uses Microsoft SQL Server (MSSQL) as its persistence provider.

I would like to know whether such a migration is feasible, and if so, what potential conflicts or challenges we might face during the process. Specifically, I’m looking for guidance on:

• Compatibility issues between PostgreSQL and MSSQL
• Areas that require special attention to ensure a smooth migration
• Best practices to avoid common pitfalls

Your insights and recommendations would be extremely helpful to ensure a hassle-free transition.

Thank you in advance for your support!

[Constantine]

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

[slangley · Accepted solution]

Hi @JV_11087733

Please be advised that PTC does not support a migration of this nature.  Please refer to this article for more information.

Regards.

--Sharon

[vnamboodheri]

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.