Please Note This forum exists for community support for the Mango product family and the Radix IoT Platform. Although Radix IoT employees participate in this forum from time to time, there is no guarantee of a response to anything posted here, nor can Radix IoT, LLC guarantee the accuracy of any information expressed or conveyed. Specific project questions from customers with active support contracts are asked to send requests to support@radixiot.com.
Kepware OPC / MS-SQL Data source scalability
-
Hi,
I'm writing up a proposal for a client... they have a Kepware OPC server that has a MS-SQL database back end that is fed data from their Siemens control system...
The client has said that he would like us to look at querying the MS-SQL database directly for all the values for the system... His intention is to replicate the SQL DB from their secure network to a DMZ for us to talk to - providing better isolation given this system will be exposed to the internet.
- Is there any limitations or scalability issues with query for thousands of values via a database connection?
- Does Mango keep a connection alive per data source and issue all the defined queries across that link, or does it connect, authenticate, query, disconnect?
Is there a better way to query an OPC server for this kind of data?
Cheers!
-Shaun -
Hi Shaun,
The SQL Data Source in Mango works very well for this exact type of application. You can set your connection parameters and a Query and then bring all the data from the query into data points. I'm not sure if the connection is kept open or now so we'll have to get back to you on that but if you have more than one Data Source each will make it's own connection and run it's own query.
The main question will be how often do you need to run the queries and how long do the queries take to run. Mango shouldn't have any scalability issues with this but large SQL queries can take time so that will probably be your limiting factor.
Doe the result of your query just return a single value or do you need to bring across multiple values?
-
Hi Joel,
That sounds good... I can create a data source per logical 'group' to query values - this will probably speed up templating the whole thing, and spreading the queries across multiple connections will help things scale too I think..
The number of results can vary depending on the query... we can change the query to return a single data point "current value" or a list of variables/values relevent to the device we are querying data for.
The queries would probably run once per minute, and there could be up to 1000 data points, so that'd be ~17 data points updated per second as a ballpark guess.
This would grow over time and in its final state (after a few years of growth) could be about 4x this.
-
I think that would be fine but there will be some limit of how many connections the database will support at the same time so I would try to put as many data points into one data source / query as possible which I think be better than having lots and lots of data sources. You might have to play with it and just test different configurations but I'm confident it can work.
-
Thanks Joel,
I'll keep that in mind!
Cheers!
