I am having a discussion and looking for talking points of any view concerning use of DB / SQL query in ignition.
Basics of this discussion is design the system such that nothing except what is required (Tag/alarm history) comes from SQL so that if the SQL connection is not working nothing is impacted.
System config for reference redundant Physical servers running VMs Private network only. 3 Backend servers, 2 frontend servers. 2 MSSQL cluster servers. Only SCADA related VMs on the physical servers.
Millions of tags, but lots of replications (10,000 instances of a single UDT)
Use visibility / multiple layers fixed text / individually called templates etc
or
Use Template repeaters with datasets built by SQL query to generate on page only the data needed.
My personal view is that trying to not use a DB and SQL query with ignition is akin to digging a ditch with a spoon when you have a shovel.
One of the thoughts is what if the DB is down. My response is what if Ignition is down. In my mind Ignition and the DB are one thing but you use redundancy with DB to minimize that instance.
I can't imagine making anything other than a very simple project without a database and from your description it doesn't sound like that.
I also don't understand why they would go through all the that trouble - 3 backend servers, 2 MSSQL clusters etc and then tell you you can't use a DB.
If they're worried the DB might go down setup a redundancy? Seems like they already have the seup for it.
One system I work one has that. The ignition aspect has two servers, a main and a redundant setup via Ignition. Ignition connects to a MSSQL listener port which behind the scenes is actually two MSSQL cluster nodes setup for redundancy.
There are just certain things (outside of Tag/alarm history) that really need persistent data storage. If you try not to use a database then invariably you'll end up trying to store large datasets as tags, which will kill the Internal DB and cause issues for Ignition.
If you have redundancy set up on the DB you'll be set. I've been running a single non-redundant MSSQL instance along side our SCADA system for over 10 years and can count on one hand the number of times that server has gone down. And of those times, only 1 that I remember actually resulted in downtime.
I would say there is a stronger chance that not using a DB leads to problems with Ignition than using one.
Same. I have multiple lines that just have a single non-redundant db that Ignition is connected. I'm been in this field 5 years now so not too long, but never once for me has the db being down been the reason for downtime.
You make a good point. Even if they do choose to not use a DB and only use Ignition - they're still using actually using a DB just one that is not suited to high I/O usage (SQLite).
To be clear still going to use the MSSQL cluster DB for tag and alarm history. The question is more of.. For instance on a display I can
On a parametrized popup that opens for the 10,000 UDTs mentioned before
There is a type # that is one of the tags in that UDT and based on that I have 16 different lines of text I need to display. Any tag could be one of 8 different sets of text
do the OLD method where you have 8 layers of 16 items with text with each text visibility based on the Type#
Or use SQL with the list in a DB table and generate a dataset for display based on the type. 1layer of 16 items or template repeater with SQL generated dataset.
Is it simpler to go with the OLD method because that is what traditional SCADA people know or simpler to have a SQL call so there are a lot less objects.
The issue raised though is with OLD method if the DB is down it still works, with SQL method it requires DB (Unless I add dataset tags but that loads more in ignition memory and could be an issue depending on how many I have as someone else mentioned)
This is a reasonable use case for a dataset memory tag to be coupled with the lookup() expression function. Possibly initialized from a gateway startup event.
I prefer these to any other method for overhead reasons.
Yeah that is one of the ideas I am looking at implementing / testing out. Little more complex IMO because of the added step but doesn't require DB function all the time. Some of the other query/datasets I have are a lot more variable (1000,s) of different datasets so not sure that will work with that many dataset tags. My big concern with that is I am currently at 15-20 gb of memory usage on backend servers and not sure how much I want to push that higher.
You may find memory consumption goes down with the dataset memory tag/lookup() combination, as you won't be continually making new little datasets for every query binding.
CPU should go down, too.
Note that these dataset memory tags should be in the same gateway as Perspective (and Vision), not in the tag hierarchy of a drivers-only backend.