# Large MSSQL Tag History and Replication **URL:** \ **Category:** Ignition\ **Tags:** vision, ignition81\ **Created:** [February 10, 2025, 3:46pm UTC](https://forum.inductiveautomation.com/t/large-mssql-tag-history-and-replication/102108 "2025-02-10T15:46:16Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![hmiCanuck](https://avatars.discourse-cdn.com/v4/letter/h/d6d6ee/32.png) [@hmiCanuck](https://forum.inductiveautomation.com/u/hmiCanuck)\ **Post date:** [February 10, 2025, 3:46pm UTC](https://forum.inductiveautomation.com/t/large-mssql-tag-history-and-replication/102108/1 "2025-02-10T15:46:16Z")
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? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [February 10, 2025, 4:55pm UTC](https://forum.inductiveautomation.com/t/large-mssql-tag-history-and-replication/102108/2 "2025-02-10T16:55:34Z")
> [@hmiCanuck](#): > > on the same machine 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. > [@hmiCanuck](#): > > I can't use history table partitioning because of the replication - it won't automatically pick up the new monthly tables. 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. ---
**Author:** ![hmiCanuck](https://avatars.discourse-cdn.com/v4/letter/h/d6d6ee/32.png) [@hmiCanuck](https://forum.inductiveautomation.com/u/hmiCanuck)\ **Post date:** [February 10, 2025, 5:30pm UTC](https://forum.inductiveautomation.com/t/large-mssql-tag-history-and-replication/102108/3 "2025-02-10T17:30:02Z")
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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [February 10, 2025, 6:02pm UTC](https://forum.inductiveautomation.com/t/large-mssql-tag-history-and-replication/102108/4 "2025-02-10T18:02:57Z")
> [@hmiCanuck](#): > > set the project back about 6 months. 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. ---
**Author:** ![hunterdg](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/hunterdg/32/67774_2.png) [@hunterdg](https://forum.inductiveautomation.com/u/hunterdg)\ **Post date:** [February 11, 2025, 7:13am UTC](https://forum.inductiveautomation.com/t/large-mssql-tag-history-and-replication/102108/6 "2025-02-11T07:13:39Z")
> [@hmiCanuck](#): > > I can't use history table partitioning because of the replication - it won't automatically pick up the new monthly tables. 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 ---
**Author:** ![hunterdg](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/hunterdg/32/67774_2.png) [@hunterdg](https://forum.inductiveautomation.com/u/hunterdg)\ **Post date:** [May 19, 2026, 2:20pm UTC](https://forum.inductiveautomation.com/t/large-mssql-tag-history-and-replication/102108/7 "2026-05-19T14:20:45Z")
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) ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [May 19, 2026, 2:22pm UTC](https://forum.inductiveautomation.com/t/large-mssql-tag-history-and-replication/102108/8 "2026-05-19T14:22:30Z")
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.)