How to safely migrate Tag History tables from one server to another?

Ignition
1

We want to move off one server (MySQL) to another (MSSQL), and have several years worth of historical data. I have a DB setup on the new server, and tested it with a couple of testing tags, seems like that's working well so far.

Is it as simple as exporting from one server into the other?
Is the tag ID only used in the partition tables and the te table?
I just realized there are three schemas in MySQL with separate historians, which means that for each one, the tag ID starts at one, so I would need to remap them if I bring them all into one DB on the new server, OR I could create two more separate Historian DB's on the new server and not worry about tag ID collisions. Thoughts on that?

Also, it looks like there could be an issue with the partition names, creating possible duplicates (assuming I combine them all into one DB). And I have a question on the naming convention for the partitions: What does the number after sqlt_data_ mean? I see most of the tables have a 1, and a few in the new historical DB have a 3.

Current historical:

image
image158×112 1.09 KB

And on the new server:

image
image183×129 1.57 KB

And one last question, do I need to do anything with the other tables?
image

Thank you!

2

If you shut down Ignition, then export from one to the other, and make the new DB take the old DB's name and IP address, there won't be any problem.

Or, you can disable the DB connection in Ignition before transferring, and point that DB connection at the new DB before re-enabling.

3

What if I change each tag's history storage provide to the new DB? And then import all the history from the old DB?

4

See this more comprehensive topic:

5

Phil, good post, good info. Thank you.

It turns out, the process is not as complicated or as worrisome as I thought. The structure of the Historical tables is easy to understand, given that one has a general understanding of relational databases and database systems as a whole. The process, so far, has been fairly seamless, no downtime, and maybe a couple of missed tag scans, (most of the tags moved over as of now scan once every hour). And it went like this:

  1. create the new historian DB
  2. ensure a proper user has proper permissions
  3. (At this point, I created a test tag which caused the Historian to create the tables for me.)
  4. Create the table structure
  5. Export the data from the current historical tables
  6. (I moved a couple of non-mission critical tags over to get a feel for what might happen, if anything.)
  7. Import the "main" table data first
  8. Import the historical data next - this was imported into new tables, then I ran INSERTS to move that data into the new historical tables.
  9. While I was importing the historical data, I moved over one folder's worth of tags, non-mission critical. I was able to read the history from the date ranges available from the import.
  10. This is where I am at now. The next steps should work without any problems, I hope.
  11. Move the rest of the tags over.
  12. Export the remaining historical data collected since I began this migration
  13. Import into new DB.
  14. Check for any missed tags, connections, missing history...
  15. Drop the old historical DB.

I found that using Microsoft's Migration Assistant for MySQL, works well for converting the MySQL schemas/tables, creating those objects, and facilitating the export/import process.

If anything outside of the expected happens during the last few steps, I'll post an update here.

EDIT: This is a slow process, as I estimate 600 to 700 million records are being imported into SQL Server, but it is faster than if I had to do this without the SSMA.

3 Likes