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.