Large MSSQL Tag History and Replication

Ignition
,
1

We have a 8.1 Vision setup with MSSQL running on the same machine. Our historical data collection is ~200GB / month. Yes, I know that's ridiculous, and it's actually been reduced from its previous 2TB / month.

I've setup snapshot DB replication with the idea that the master could remain "lean" (maybe 1 year of data) and the subscriber would store all history. It's not pretty and I thought I'd ask this to see if others were having similar pain.

  • I can't use the Tag History Splitter instead because the backup SQL server isn't always connected. Ignition's Store & Forward won't support the potential quarantine size I'd need.
  • I can't use history table partitioning because of the replication - it won't automatically pick up the new monthly tables.
  • Pruning will become a large pain because if I completely delete the master's history DB, the tag IDs will likely change. And if I don't then "shrinking" a table with replication enabled is also ugly.
  • Long term storage becomes a large pain with a single table growing to say 20TB in size.

Anyone else trying to store AWS sized historical data?

2

Don't do this. Especially with such a large workload as yours. Ignition's CPU usage pattern and general database CPU usage patterns do not play well together. Don't put them on the same machine.

Don't use MS SQL Server. Other, more competent, brands (like PostgreSQL) will happily replicate new tables, too. So you can (and you should) use Ignition's partitioning. Or you can use a DB like Timescale (which is actually enhanced PostgreSQL) to handle the very large tables. (It'll compress them, too.)

Your problems revolve around using MS SQL Server. Don't. Really.

3

While I completely agree with you, unfortunately I don't have a choice with either.

I inherited the code from a previously developer. The code has already been "certified" for use in our environment and switching DBs or moving the DB server would literally set the project back about 6 months.

4

You are going to spend more time trying to solve this unsolvable problem. Tell your client/bosses that you need to rip the bandage off.

6

I’ve solved worked around this headache with a combination of snapshot replication for the keyless tables, transactional replication for the rest, and a scheduled job that adds unsynced (& drops synced) articles.

I’ve also implemented transactional replication on keyless tables by creating a view with a clustered index on the publisher and replicating that to/as a table on the subscriber

7

following up on this - (as opposed to posting a new question, but perhaps that would be better - please advise)

should old history partitions still be getting new rows? current partition is sqlt_data_1_20260517, but sqlt_data_1_20260330, sqlt_data_1_20260504, sqlt_data_1_20260511, etc are still getting new rows. most often they're just single rows per tagid (presumably new tags), but in at least one case I found several thousand rows for a couple tagids, though that might've been an artifact of partial table synchronization

latest end_time in partitions table for the older tables is correct (in the past)

8

That often means you have devices with bad clocks, which pass through when using OPC connections instead of IA native drivers. (Or they startup with a bad clock, and your infrastructure fixes it after some samples are delivered.)