I have a Ignition setup, which uses only one database connection.
Historical data tables per month have more than 200 billion rows and size more than 50GB each one.
How to correctly transfer historical data tables to another database without lost data?
I would like to know the correct sequence of actions. Please help.
The simplest solution is to upgrade your database, or restore from it into a new one while Ignition is completely shutdown, where the new one takes the same connection settings and authentication. So it just wakes up with a new, bigger, database where the old one used to be.
Otherwise, it is complicated. Too complicated and situation-specific to even have a single correct sequence. You should study the documentation for the tables and examine your install until you understand the complex interactions between the various primary keys and the columns in other tables that reference them.
Once you understand the task, you can pick one of these approaches, based on your needs and options for downtime:
-
Point Ignition at the new database and old database simultaneously. Tag by tag, migrate data into the new database, switching the tag's history provider as soon as the new DB is finished backfilling. Almost no downtime, but may need manual intervention (missed row copies) for each tag when flipping over.
-
Point Ignition at the new database and old database simulateously. Create a tag history splitter to store to both databases. Bulk reconfigure tags to point at the splitter. After all tags are storing to both, you can then start backfilling. When backfilling is complete, reconfigure the splitter to query against the new DB. Then you can bulk reconfigure tags to point only to the new DB.
-
Point Ignition at the new database and old database simultaneously, but with the new database taking the old DB's connection name. Short downtime while the old DB is renamed but the new DB isn't. Once running, backfill from old to new.
3 Likes
Thanks for the detailed answer.
Would you need to completly shut Ignition down, or would disabling the database connection while the new database is being resotred be sufficient?
Thank You,
Dalton
It depends on how much data will pile up in S&F during the conversion. So, maybe. It's complicated.
We utalize spokes as well so the data should collect there. We are expecting about 21 hours to restore the DB. If calculated properly this should be a little over 1gb of data in the S&F for our system.
Consider approach 2 or 3 in my list.
I have a similar request.
I am moving from server 2016 to server 2022 and was wondering If/When I restore my GW backup on the new server - how will I keep the tag-id's correct when reestablishing connection with the server?
Should I make the new server name the EXACT same as the other - and IP address as well? Then shut down the old server once GW Restore is complete?
Is there a migration Guide?
What the database thinks its name is, or what IP address it uses, doesn't matter. Just that the gateway's own name is the same (inside Ignition, not the computer name/host name), and the data source name on the Ignition side is the same, and that data source points at the same data.
Do make sure neither gateway can talk to either database while you make the settings changes in the new gateway. So the new gateway "sees" what it expects within the Ignition name of the database, once it starts communicating, no matter what actual database is serving it up.
By default, an Ignition gateway constructs its name from the host name of the PC it is installed on. But that is not required, and a gateway backup will carry the original. It is a good practice to rename a new Ignition gateway to something more meaningful than the default, so that server upgrades cannot break things or introduce naming conflicts.
Thank you for the reply pturmel!
Is there a guide to upgrading the SERVER OS with Ignition on it?
OR
Can I just upgrade my server OS and Ignition stay in tact - no harm no foul?
Not that I'm aware of, and no, I doubt you can upgrade the OS without disturbing Ignition. Your comments also suggest you have your database on the same system as Ignition itself--very much not recommended in production.
My database is located on a different server - so no worries there.
I just remember a while back I had to move a gateway from one site to another - And I lost all the "Tag-ID" history after the move. I'm guessing cuz the Gateway name changed.
Just trying to cross all my i's and dot all my T's. 
Thanks!
1 Like