Will creating multiple Database connections allow me to configure multiple Tag Historian Pruning options?

I have created 2 database connections to my database SQLEXPRESS. By default this creates two Tag Historian connections also. I can configure one of the Historians to prune data after 3 months and the other historian to never prune data. At first glance this setup appears to only create one set of new tables in the database putting all data, whether configured in the first historian or the second into the same partition. When 3 months comes up will all the data be erased? How does Ignition know which tags to delete if not?

Pruning works by dropping partitions (tables), not by actually deleting rows from each table. Someone else with more experience will need to chime in on how exactly the pruning will behave with two connections. My guess is the historian with the 3 month cutoff will “win” and drop partitions, and essentially ignore your other historian’s never prune settings, but it could also be that Ignition checks to see the max prune setting and use that. Regardless of which one “wins”, I don’t think this will work the way you want since pruning works by dropping partitions, not deleting individual rows.

1 Like

You have two database connections in Ignition configured to point to the same schema in MSSQL? Why?

1 Like

Well mostly because I don’t understand databases very well. Is this a case where I should have one database for short term data and another for long term data?

When I think in terms of “efficiency” my first reaction is to put all of my applications data into a single database.

So, terminology gets a bit overloaded here, and Management Studio does no one any favors, since I think it refers to what every other DB calls ‘schemas’ as ‘databases’, but basically:
In terms of Ignition, there’s no value in creating multiple ‘database connections’ to the same schema in a database (a schema is, in broadest terms, a namespace for tables and other settings). You can have multiple schemas in one “database” (instance of your database software), and if you do then it makes perfect sense to have multiple connections to them in Ignition (if you actually need to connect to them, anyways). But if you’ve only got one schema, then you can just configure Ignition’s DB connection (I.E., configure connection pooling) if you have something to tweak.

Multiple Ignition connections to the same schema is an anti-pattern that we don’t check for or expect, which is precisely why stuff like tag history is going to break or do unexpected things in that configuration. To borrow a term, I’d call it ‘undefined behavior’. In practice, I’m pretty sure Will’s correct that the shorter prune setting would ‘win’ - but it’s better to never have to find out.

2 Likes

Reiterating what you said in my own terms…

I think in MySQL the developers refer to a database as a schema (they are one and the same).

In MSSQL (Microsoft) the developers allow the end-user to create schemas within databases which you can have 32k databases on a server.

Either way the story I’m getting here is I should not connect multiple Tag Historians to the same database whether or not I have multiple schemas in that database (applies to MSSQL only).

Only thing I will add is that I will sometimes add two connections to the same schema for security purposes. One connection using a DB username that only has Grants for a specific subset of tables in the schema, and another using a DB username with fewer restrictions. This way, I can use the connection with fewer restrictions for Gateway/Tag scripts, etc and the other for user facing tools. I’m sure I could try to accomplish this through security settings in Ignition, but I find it messy and prone to missing/forgetting something.

2 Likes

What I don’t understand is why the developers allow me to create multiple Alarm Journals connected to the same database/schema and specify table names accordingly but not for Tag Historians.

Tag historian has to have control over table names, because multiple gateways can talk to the same DB and store history independently. It’s just a different model for control. Alarm events/data are flexible enough that theoretically you could piggyback on an existing table, or you could decorate the autogenerated tables with your own metadata, or whatever. But the tag historian does not offer that level of flexibility - we expect to both manage all data storage and retrieval via Ignition, with the DB only offering ‘dumb’ storage.

1 Like

And I hate not being in control…

thanks for the clarification though.