IMD SolutionComputing LibraryShop main store

Selection guide

GeoSCADA Data Tables or an external SQL database

Edge computing and IIoT Published 7 min read

Short answer

Keep the records in native Data Tables while each retained table stays within the documented 100 MB maximum and writes are infrequent, exporting and trimming what you keep. Move to an external SQL database with ODBC linked tables when growth, business data ownership, or several SCADA systems needing one source of truth dominates, and call that database from client scripts or a separate service, never from logic.

On this page
  1. Which should I choose: Data Tables or an external SQL database?
  2. When should I keep the data in GeoSCADA Data Tables?
  3. When does an external SQL database win?
  4. How do I size a Data Table before committing?
  5. How does data move in and out of each option?
  6. Why is a linked ODBC table in logic a bad idea?
  7. What should the final design be?
  8. Questions asked

Keep the records in native Data Tables while each retained table stays inside the 100 MB maximum the GeoSCADA configuration guide sets for an individual Data Table and writes arrive as single records, not bursts — then export and trim on a schedule. Move to an external SQL database reached through ODBC linked tables when projected growth, business data ownership, or two SCADA systems needing one source of truth dominates. Whichever store you pick, call the external database from client scripts or a separate service, never from logic: a stalled query blocks the execution thread every logic program shares. The sections below give the sizing arithmetic, the trade-offs and the decision for each case.

Which should I choose: Data Tables or an external SQL database? #

Choose native Data Tables for a bounded dataset you can hold under the 100 MB per-table maximum with infrequent writes, and choose the external SQL database the moment growth, another owner, or several systems sharing one dataset enters the picture. Compare the complete data path, not the query syntax: native storage keeps records inside the synchronized SCADA database and leaves external access to design, while external storage centralizes access for other applications and leaves the GeoSCADA path to design through linked tables.

Criterion Native Data Tables External SQL database
Retained size Bounded datasets; 100 MB documented maximum per table Grows with the database edition and its infrastructure
Write pattern Infrequent single-record writes Substantial data-management load runs outside SCADA
Access from GeoSCADA Direct; records live in the synchronized database ODBC linked tables called from client-side script
Access from other applications SQL Export objects or a defined integration path Direct database interfaces under controlled permissions
Availability Carried by the SCADA server pair and partner synchronization Separate failover, backup and recovery plan
Data ownership SCADA team owns the records and their permissions The business function that owns the data

Neither choice removes integration work; it only decides which side of the wall the plumbing sits on.

When should I keep the data in GeoSCADA Data Tables? #

Keep them there when the dataset has a hard retention boundary, writes arrive as single records rather than bursts, and the SCADA team is the natural owner of the records and their permissions. The values a Data Table holds are stored in a data file inside the SCADA database, and the table's structure travels in the stream files that synchronize between servers. A bulky data file adds to the work partner servers do to stay in step, which is why operating practice keeps tables well under the maximum rather than riding it. The configuration guide draws no specific limit on the number of Data Tables per database; the setting to review as they multiply is the Data File Cache Size.

Continuous process history stays out of this decision: it belongs to the historian, whose performance guidelines cap storage at four records per minute per point — a 15-second minimum interval. Data Tables are for structured records such as batches, runs, operator entries and lookup sets.

When does an external SQL database win? #

Move out when projected retention passes what a 100 MB table can hold, when another business function owns the data, or when two or more GeoSCADA systems need the same records as a single source of truth. One authoritative database beats two systems reconciling copies that drift, and if quality or maintenance owns the dataset, it should live in a database they own, with permissions they grant. On capacity, SQL Server Express carries a documented 10 GB cap per database — a hundred times the per-table Data Table maximum — before an edition change is forced.

The plant-side pattern already exists when logging PLC values from an OPC server into SQL Server: a plant or edge host writes, other applications read. Before committing, list every GeoSCADA server and external application that reads or writes the data, define connection behavior during server failover and database unavailability, and assign an owner for schema changes, permissions, backups and recovery.

How do I size a Data Table before committing? #

Size it from measured net growth multiplied by retention, working in bytes rather than rows, because record width, variable-length content and indexes decide the stored footprint, not row count. Measure one representative record with its overhead, then extrapolate.

Worked example. A packing line writes one batch record every 15 minutes — 96 records a day — and a representative row measures 2 kB stored:

  • Growth: 96 records/day × 2 kB = 192 kB/day
  • One year retained: 192 kB/day × 365 days = 70,080 kB ≈ 70 MB
  • Time to the maximum: 100,000 kB ÷ 192 kB/day ≈ 520 days ≈ 17 months
  • Five years retained: 192 kB/day × 1,825 days = 350,400 kB ≈ 350 MB

Twelve months of retention lands at 70 MB with 30 MB of headroom under the limit: keep it native and export-and-trim on a schedule. A five-year queryable history lands at 350 MB, three and a half times the table maximum: that dataset belongs in the external database. Where updates and deletes shape the table, measure net growth over a representative cycle instead of extrapolating inserts — trimming changes the retained footprint.

How does data move in and out of each option? #

Native tables export through SQL Export objects fired by Schedule triggers; external tables are reached from GeoSCADA through ODBC linked tables that support create, read, update and delete from client-side script. For transfers that must run unattended, put them in a separately managed application or service rather than in an operator display, with explicit handling for disconnection, retries and duplicate submissions.

When the link drops mid-shift, the recovery sequence is the one you already use to reconnect an edge PC to SQL Server after connection loss. On the query side, selective queries and views shape results before clients retrieve them; a view centralizes calculations but does not make an expensive query fast, so measure execution time and returned volume under the intended concurrent load, display refreshes included.

Why is a linked ODBC table in logic a bad idea? #

Because the logic engine runs many programs in one largely linear execution thread built for sub-millisecond scans, and a database call that waits seconds holds that thread while every other program queues behind it. The engine is designed for short scans — read inputs, compute, write outputs — and its integrity comes from that discipline. A linked-table call leaves the SCADA database server, crosses the ODBC driver and the network, and waits on another database for as long as that database takes: seconds when it is busy, minutes when it is down. The waiting execution can hold database locks the whole time, and logic instantiated across templates multiplies the exposure — a query that behaves in commissioning can disrupt a site once fifty scheduled copies run.

Queries against memory-resident real-time data fit the short-scan model; disk-backed stores such as the historic archive and the event journal already add latency before the external hop. Keep control-critical programs — sequencing and interlocks — free of external calls entirely, so a database outage can never stall the logic the plant depends on.

What should the final design be? #

Data Tables with scheduled export and trim for the bounded case; an external SQL database reached through linked tables from client script, plus a separate service for unattended transfers, for everything else. Concretely:

  • Bounded and SCADA-owned: native Data Tables. Size with the growth arithmetic, schedule export then trim, and treat a large table's pre-load option as a memory-and-startup trade-off, not a size reduction. Cache maintenance and record deletion solve different problems — clearing a cache is not a retention policy.
  • Growing past the table maximum, shared between systems, or owned by another function: the external SQL database. Linked tables from client script for operator actions, a separate service for unattended transfers, an owner for schema and backups, and failover behavior defined for every connection.
  • Either way: no linked tables in logic.

Questions asked

Is the 100 MB Data Table limit per database or per table?

Per table. The configuration guide sets 100 MB as the maximum size of an individual Data Table, and it draws no specific limit on how many Data Tables a database may hold. The practical constraint then becomes the Data File Cache Size setting and the synchronization workload between partner servers as total stored content grows.

Can I create a new Data Table every month instead of trimming?

You can, but it rarely pays. Programmatically creating database objects, then repointing queries, displays and export logic at each new table, adds configuration churn that outlasts the problem it solves, and every table still counts toward database storage and cache. Scheduled export followed by trimming one stable table is the lower-maintenance design.

Does moving the data to SQL Server Express make it redundant?

No. Inside GeoSCADA, redundancy comes from the server pair and the synchronization between partner servers. An external database inherits none of that: you plan its failover behavior, the connection handling on every GeoSCADA server when it disappears, its backups and its recovery. Moving data out of SCADA never provides fault tolerance by itself.

How fast can a Data Table be written compared with the historian?

They answer different needs. The historian's performance guidelines allow up to four stored records per minute per point — a 15-second minimum interval — for continuous values. A Data Table holds structured records such as batches, shifts and operator entries; its value is a bounded, queryable record set, not high-rate capture. For high-rate signals, use the historian or an edge store built for that rate.

What happens to clients if the external database goes offline?

Calls made from client-side script fail or time out in that display alone; the SCADA server and its logic keep running, which is the reason client script is the right home for linked-table access. Handle the error in the script so the operator sees a clear message, and test the behavior with the database stopped mid-transfer before go-live.

Keep reading

  • Selection guide

    OPC UA and MQTT dashboard comparison for plant data

    Choose Node-RED when the dashboard only displays MQTT or OPC UA values; both protocols have maintained connector packages for it. Choose a tag-based SCADA platform when operators must write values, acknowledge alarms or open trends under accounts, and Grafana on InfluxDB when stored trends are the whole job. The decision hinges on interaction, not rendering.

    Edge computing and IIoT

  • Troubleshooting

    Edge trend shows no historian data: fix the doubled SELECT

    The trend stays blank because a complete SELECT statement was pasted into the Table field, so the gateway wraps it in its own SELECT ... FROM and SQL Server rejects the result as incorrect syntax near the keyword select. Put only Runtime.dbo.History in Table, carry DateTime, TagName and Value in the column controls, and move TagName = 'SysTimeSec' into the filter control.

    Edge computing and IIoT

  • Explainer

    OLAP versus plain historian storage in SCADA databases

    A SCADA historian that stores timestamped values and serves trend queries is not doing OLAP, however many rows it holds. Only a deployment where a separate engine or scheduled job builds analytical structures, such as cubes or data marts, carries the OLAP licensing question. Classify by processing path, then decide whether the review is needed.

    Edge computing and IIoT