Ignition 8.1.18, SQL Server Express 14.0, Tag Historian 4.1.18
We currently have a single database containing all the historical data for our facility. The data is in the ignition standard structure with a table for each calendar month and a lookup table. I would like to change the structure so that each manufacturing cell has a separate database of historical data.
What is the best method for doing this whilst retaining data prior to the changeover and not having any duplicated data? I have considered creating multiple copies of the database and then filtering out the unwanted data from each instance. This requires a lot of time and some SQL code.
This change in structure is, in part, driven by the 10Gb limit on a database imposed by SQL Server Express. We are looking at upgrading to full SQL server, but I believe that the proposed structure is the right way to go regardless.
Disclaimer: I'm no expert in the historian area, but I've messed enough with it to know that it can get hairier than you'd first expect.
When directing your tags to another database, the references in sqlth_te will more than likely be different than what they currently are, so you'd need to change the ids in your new sqlt_data_X to match those new references.
But you'll also need to match the new references to the old ones, in order to know what rows need to be modified in the data tables.
All this quickly adds up in term of time, and while the operations are not very hard to figure out, it takes quite a long time for anything but a small amount of history data.
Maybe some people know of simpler ways to do this, but I can't see how you could avoid the basic steps:
change the tags historian configuration to point to a new database
take a tag in the new database's sqlth_te table
find this tag in the old one
get all the entries in sqlt_data_X tables that match the old tag id
copy that data to the new database and change the tagid to match the new one
You can make separate history providers pointing at difference database connections. You will have to edit all of your tags to point them at the new providers. This will break history. You will need as much SQL to transport history into the new providers as you would to filter in duplicate providers.
I recommend expediting your upgrade to a non-toy database. If $$ is the problem, I recommend PostgreSQL, a free non-toy database for which commercial support is available.