OPC Data Archiving to SQL and CSV Without a Historian

Archiving OPC data to SQL databases and CSV files gives you a reliable, queryable record of process values without purchasing, licensing, and maintaining a full enterprise historian. For most control engineers, the combination of event-based capture, structured storage, and standard file formats delivers everything a historian provides for routine reporting and troubleshooting — at a fraction of the operational overhead.

Key Terms

OPC DA (Data Access)

The OPC Classic specification for reading and writing real-time process values from PLCs, DCS controllers, and SCADA servers.

OPC UA (Unified Architecture)

The platform-independent successor to OPC Classic. OPC UA supports secure, structured data exchange over standard TCP/IP networks.

OPC HDA (Historical Data Access)

An OPC Classic specification for reading time-stamped historical data stored in a process historian.

OPC UA HA (Historical Access)

The equivalent of HDA within the OPC UA specification.

Event-based capture

A data archiving strategy that records a value only when it changes by a configurable deadband or crosses a defined threshold, rather than writing every scan cycle to storage.

Store-and-forward

A fault-tolerance mechanism that buffers data locally when a database or network connection is unavailable, then replays buffered records once the connection is restored.

Why Traditional Historians Are Often the Wrong Tool

Enterprise process historians — the kind purpose-built for SCADA and DCS environments — are powerful systems. They handle high-frequency data at scale, provide built-in redundancy, and integrate with process analytics platforms. They are also expensive to license, time-consuming to deploy, and heavily dependent on IT infrastructure and database administration skills.

For many sites, the actual requirement is more modest: capture key process variables at a meaningful rate, make them queryable for shift reports and maintenance analysis, and retain them long enough to support root-cause investigations. That requirement does not need a six-figure historian deployment. It needs reliable, well-structured archiving to a format that engineers already know how to query — SQL or CSV.

There is a second problem with traditional historians in brownfield environments: they typically require dedicated server hardware, specific Windows Server versions, and database back-ends that must be licensed and maintained independently. On sites with aging infrastructure or limited IT support, that dependency chain introduces more failure points than it eliminates.

How OPC Data Archiving to SQL and CSV Works

The core mechanism is straightforward. An archiving tool connects to an OPC server — OPC DA, OPC UA, OPC HDA, OPC UA HA, or MQTT — subscribes to the tags you want to capture, and writes timestamped records to a target storage location on a configurable basis.

SQL archiving inserts records into a relational database table. Each record typically contains a timestamp, tag name or item ID, value, and quality flag. The resulting table is immediately queryable with standard SQL, integrates with Excel pivot tables, Power BI, and custom reporting tools, and can be backed up and replicated using ordinary database utilities. Supported back-ends include Microsoft SQL Server, MySQL, and SQLite — the last of which requires no separate database server and is well-suited to standalone machines or edge deployments.

CSV archiving writes comma-separated records to flat files, typically one file per day or per shift. CSV output requires no database infrastructure, can be opened directly in Excel, and can be processed by virtually any analytics or reporting tool. The trade-off is that flat files are harder to query efficiently at scale, and merging files across long date ranges adds manual effort.

Cloud archiving extends both models to hosted storage. Microsoft Azure, Amazon AWS, and MongoDB are common targets, which is relevant for multi-site deployments where centralizing data from multiple facilities is a requirement.

Event-Based Capture vs. Time-Based Capture

The choice between event-based and time-based capture has a direct impact on storage size, database performance, and data utility.

Time-based capture writes a record on every scan cycle, regardless of whether the value has changed. On a tag that updates every second, this generates 86,400 records per day per tag. Multiply across hundreds of tags and the storage and indexing load grows quickly — most of it redundant data carrying no new information.

Event-based capture (also called exception reporting or on-change logging) writes a record only when the value changes by more than a configured deadband or when a defined condition is met. For a stable process variable that holds within a tight band for hours at a time, event-based capture can reduce record volume by 90% or more while preserving every meaningful change in the data.

For most OPC archiving use cases, event-based capture is the correct default. Time-based capture is appropriate when you need a guaranteed fixed-interval record — for regulatory compliance reporting, for example, or for feeding a downstream system that expects evenly spaced data.

Store-and-Forward: Protecting Data Integrity During Outages

Network outages and database server unavailability are normal operational events in industrial environments. Without protection, a gap in connectivity means a gap in your archive — and those gaps appear precisely when you most need complete data, during incidents and fault investigations.

Store-and-forward addresses this by buffering tag records locally when the connection to the target SQL database or CSV file path is unavailable. Once the connection is restored, the buffered records are written to the archive in chronological order, filling the gap without duplication or loss. The result is a continuous, complete archive even across network interruptions or planned maintenance windows.

This mechanism is especially important for remote sites or edge nodes communicating with a central database over WAN links with variable reliability.

Archiving OPC Alarms and Events

Process data is only part of the picture. For root-cause analysis and compliance reporting, you also need a record of alarms and operator actions — when a high-pressure alarm fired, when an operator acknowledged it, when a setpoint was changed. OPC Classic exposes this information through the OPC A&E (Alarms and Events) specification; OPC UA exposes it through OPC UA A&C (Alarms and Conditions).

An archiving solution that captures A&E and A&C data alongside process values creates a unified record that is far more useful for incident investigation than process data alone. Without alarm history, you can see that a value spiked — but not what triggered the response, who acknowledged it, or what actions were taken.

Comparison: OPC Archiving Approaches

Troubleshooting Common OPC Archiving Issues

Records stop appearing in the database after a period of normal operation.
The most common causes are a lost OPC server connection or a database connection timeout. Verify the OPC server is online and that the archiving tool is actively subscribed to the target tags. Check the database connection string for expired credentials, and confirm the SQL server service is running.

CSV files are created but contain only a header row with no data.
This typically indicates that no tag values have changed since the file was created, and event-based capture is correctly producing no records. If you expect data, verify the tags are live on the OPC server and that the quality flag is Good. Bad or Uncertain quality values are typically not archived by default.

After a network outage, the SQL database contains a gap in the time series.
Store-and-forward was not enabled, or the buffer was exhausted before the connection was restored. Enable store-and-forward and confirm the local buffer path has sufficient disk space for the expected outage duration at the current record rate.

Timestamps in the archive do not match the expected time zone.
OPC DA timestamps are UTC by default. If your reporting tools display timestamps without applying a UTC offset, records will appear to be offset from local time. Confirm whether your archiving tool normalizes timestamps to local time or stores UTC, and configure your reporting layer accordingly.

SQL inserts are slow or the database is growing faster than expected.
Time-based capture at a high scan rate is almost always the cause. Switch to event-based capture with an appropriate deadband for each tag. For analog values, a deadband of 0.5–1% of engineering range typically retains all meaningful changes while eliminating the majority of redundant records.

How Archive OPC Data from OPC Expert Addresses These Requirements

Archive OPC Data is the archiving module within OPC Expert. It connects to OPC DA, OPC UA, OPC HDA, OPC UA HA, and MQTT sources and archives to CSV files, Microsoft SQL Server, MySQL, SQLite, Microsoft Azure, Amazon AWS, and MongoDB. Data capture is event-based by default, with configurable update rates per tag. Store-and-forward is built in. OPC A&E alarm and event logging is supported alongside process value archiving.

The tool runs as a Windows service, requires no installation, and makes no changes to the Windows Registry — which matters on audited systems where software installations require change control approval. Bulk tag configuration is handled through CSV import, allowing hundreds or thousands of tags to be configured in a spreadsheet and imported in a single operation. For an overview of the OPC specifications the module connects to, see the OPC Foundation’s technology overview.

For sites that do not have an existing historian and are not ready to invest in one, Archive OPC Data provides a practical, maintainable archiving layer that integrates directly with the SQL and file-based tools engineers already use.

Frequently Asked Questions

Can I archive OPC data to SQL without a dedicated database server?

Yes. SQLite is a file-based relational database that requires no separate server process. An archiving tool that supports SQLite can write structured, queryable SQL records to a local file on the same machine running the OPC client — no database administration required.

What is the difference between OPC HDA and archiving OPC DA data to SQL?

OPC HDA is a specification for reading historical data that is already stored in a process historian. Archiving OPC DA data to SQL is the process of creating that historical record from live real-time data. OPC HDA is for reading from an existing historian; SQL archiving is for building your own historical store from real-time OPC subscriptions.

How much disk space does OPC data archiving to CSV or SQL require?

It depends heavily on tag count, scan rate, and whether you use event-based or time-based capture. A rough benchmark for event-based archiving of process values at a typical update rate is 50–200 bytes per record. A site with 500 active tags at an average of 10 changes per hour generates roughly 24 MB per day — well within the capacity of any standard server.

Does archiving OPC data require stopping the OPC server or interrupting live operations?

No. OPC archiving works by subscribing to live data through the OPC client-server interface. It does not require exclusive access to the OPC server and has no impact on other clients reading the same data concurrently.

Summary

• Archiving OPC data to SQL and CSV provides a reliable, queryable historical record without the cost and infrastructure of an enterprise historian.
• Event-based capture reduces storage volume by recording only meaningful value changes rather than every scan cycle.
• Store-and-forward protects data integrity during network outages by buffering records locally and replaying them once the connection is restored.
• SQL archiving supports full relational queries and integrates with standard BI and reporting tools; CSV archiving requires no database infrastructure and opens directly in Excel.
• OPC A&E and OPC UA A&C alarm and event logging should be archived alongside process values to support complete incident investigation.
• For most sites, a lightweight archiving tool running as a Windows service — with no installation or Registry changes — is a more practical solution than a full enterprise historian deployment.