Design Question: GeoSCADA Data Table vs External SQL Server DB

[MikeSI]

Hi all,

I'm working on a project which involves storing, gathering and querying data from a few sources (both within GeoSCADA and also external).

I'm at the tinkering stage and tossing up whether to use Data Tables, or to use an external SQL database (such as SQL Server Express) for the data storage.

Has anyone been down this road before? Any lessons learnt?

Is there a 'best practice' solution?

Seems to me, if I go with Data Tables the issue will be accessing and storing data from outside GeoSCADA.

If I go with an external DB, the issue will be accessing/storing data from inside GeoSCADA. The only way I understand to do this is using ODBC linked tables, and I'm not sure how reliable this is.

Any advice much appreciated.

[tfranklin]

There's a lot of "it depends" when you start to design stuff like this.  Some questions you'll need to consider:

1. What generates the data?  Is it client driven via script?  Is it logic? Is it both?
2. How much data is being stored?
3. What is the frequency of which data is updated/inserted/deleted into the table?
4. If I store it externally, how can we ensure the data is redundant/fault tolerant and available across all GS servers?

In general, my recommendations are always that if you're going to keep it in a data table in Geo SCADA you want to ensure the table never exceeds 100MB.  Going beyond that could cause issues with server synchronization.  Typically we store as much in the GS data tables as we can until we see that growth might become and issue.  We also have some utilities that are explicitly written to store data outside of GS when we know that size will become an issue. 

• If you're storing data inside of a data table:
  • Avoid frequent writes
  • Don't exceed 100MB as a rule of thumb (a little more is probably okay)
  • Consider pre-loading the data (checkbox on the properties) if the table is large
  • Maybe consider a background process for purging cache on it

• If you choose to store data in SQL
  • Ensure you have a redundancy plan.
  • Utilize Linked ODBC tables to perform CRUD operations from within Geo SCADA
  • Avoid using Linked ODBC tables in logic.  It's a death-wish. 
  • You can store far more data but will need to consider the load of how a client will hit it.
  • You can utilize SQL views to do a lot of complex stuff for pre-processing

I'm sure somebody will disagree with something above, but in general the answer is always that it depends.  I'd consider the size and rate of which you write to the tables to make a decision.  If it's infrequent and < 100MB of data you're probably fine leaving it in a data table within GS.