Migrate Thingworx database to external MySQL

[dharitac]

I have thousands of data table each with at least 100000 records. Till now I was using Thingworx Internal database and to access data I wrote all services by using built-in snippets given by twx platform for data tables. Now I have to change the framework and the new framework is supposed to use MySQL database. So there any way to migrate all of the Thingworx data to MySQL? 

[supandey]

Hi @dharitac but MySQL is not supported as persistence provider. Were you using 2 persistence provider previously? What were those? Since DataTable is stored in the persistence provider, to migrate the persistence provider you'll have to run through the Upgrade guide for ThingWorx, it includes the topic on migration as well between different persistence provider.

[dharitac]

Hi @supandey, best of my knowledge, no persistence provider is used. 

[supandey]

There has to be one, you can check that from the ThingWorx Composer > Data Storage > Persistence Proviers

You may have to enable the visibility on system objects to be able to see the Persistence Provider by clicking on the Filter and check the Show System Objects to view the entity

[jkaczynski]

Hi @dharitac and @supandey,

There is no need to use persistence provider. There can be a separate, standalone instance of MySQL and ThingWorx can connect to this DB using a standard JDBC connector. However, all the queries / commands needs to be created manually and the only way to migrate this data is to write a logic to do so.

So in a service you need to query a part of the data that is stored in ThingWorx's data storage, process it to fit the external MySQL database and push using the insert command.

For more information about connecting JDBC you can refer to:

http://support.ptc.com/help/thingworx_hc/thingworx_8_hc/en/#page/ThingWorx%2FHelp%2FExtensibility%2FJDBCExtension%2FJDBCExtension.html

You also found my old answer about this topic on this community.

Hope this clarifies a bit.

BR,

JK.

[supandey]

@jkaczynski I know. Albeit, DataTables by default are not stored in external data store, and as noted in the question user is working with internal DB which is a persistence provider. 

Thus a migration to external RDBMS system would required custom services esp. SQL services to export out the data from DataTables stored in the Persistence Provider to tables in MySQL.

[j_kaczynski]

Hi @supandey,

I believe owner of this topic asks if and how migrate the data from internal DB to MySQL. Not clarifying, that it should be an "internal" MySQL ;-)

And yes, I mentioned about the additional effort that is necessary here, unfortunately no possibility to migrate automatically.

BR,

JK.

[slangley]

Hi @dharitac.

If one of the previous responses provided a solution to your question, please mark it as the Accepted Solution for the benefit of others who may have the same question.

Regards.

--Sharon