Archive OPC data in SQL

Archive data with OPC Expert to Microsoft SQL (MS SQL) databases. OPC Expert captures data using OPC DA, OPC A&E, OPC .NET (OPC Xi) or OPC UA, and sends it to any of several permanent storage destinations; in this way, OPC Expert can be considered the “Swiss army knife” of OPC clients. There is no need to buy, install and maintain multiple OPC clients when OPC Expert has nearly everything you would ever need to manage your OPC network.

OPC Data in SQL

OPC Expert sends all data to SQL server and provides three main tables for snapshot (real-time) data and history.  Integrators can use these table to receive real-time data updates in SQL or to access history.  Integrators can optionally turn these tables on or off

  • Realtime
    • Table containing item properties in each column
    • Table containing item updates with SQL data types (stores values with their corresponding SQL data types)
  • History: Table containing all historical data

Store-and-Forward

When OPC Expert loses SQL connectivity, it stores all data locally.  When connections return, OPC Expert forwards all data to SQL.  This prevents data loss.  When reconnecting, OPC Expert enables users to throttle SQL calls to prevent connection congestion.

Bulk configuration

OPC Expert’s drag-and-drop interface makes it easy to add a few items at a time.  But when users must configure OPC Expert to capture thousands of items, OPC Experts enables them to import CSV files for bulk configuration.  This enables integrators to combine the power of Excel’s efficient editing features to create CSV files.  OPC Expert reads these files and automatically sets up communication.

SQL Server Management Studio

OPC Expert enables users to access their data using SQL application as well as SQL Server Management Studio (SSMS).  Once OPC Expert’s Archive Server is running and storing data, use SSMS to:

  • Run queries
  • Monitor SQL server
  • Update real-time values
  • Interact with archived data
  • Video shows you how to install, connect, and navigate SSMS alongside OPC Expert to view your data

Run as a Windows Service

Run OPC Expert Archive Server as a Windows service.  Users can set OPC Expert to run as a Windows service.  OPC Expert supports operation as any user account including System.  Doing this enables OPC Expert Archive Server to

  • Start automatically with Windows
  • Run without requiring a user logon
  • Separate front-end GUI from back-end archiving
  • Enable Windows to manage runtime functions like start, stop, restart, etc.

Video shows how to setup OPC Expert Archive Server as a Windows Service

Archive Batch Data

OPC Expert can archive data based on user-defined events. Users may need to archive batch data as may be the case in injection molding machines, testing apparatuses, pharmaceutical ingredient production, etc. The unique aspect of this activity is OPC Expert should only archive data when a “batch complete” event occurs. Click Archive batch data for details.

Excel batch report

Change Item Update Rate

Users can change update rates for individual items.  This enables users to manage the amount of hard disk space archive files use and amount of data source server load.  This enables OPC Expert to:

  • Reduce file size when data does not change often
  • Improve accuracy of data capture (more updates per second)
  • Users can change default option to store data regardless of change, or even store data based on events (related video)
Archive OPC Data
Archive OPC Data
Archive OPC Data
Archive OPC Data

  • MQTT
    • SparkplugB
    • Quality of Service
    • Local and remote connections
    • Publisher and Subscriber modes
    • Birth Certificates and Death Certificates
    • Authentication including UN/PW and TLS
  • Capture data from
    • OPC UA and OPC Classic
    • 32-bit and 64-bit servers
    • MQTT (including SparkplugB)
    • Local and remote connections

Capture alarms and events using OPC A&E, and store in any database. Benefit from a permanent record of process and equipment alarms, operator actions and any plant disturbances. Using a database (such as MS SQL) enables the creation of custom reports to eliminate nuisance alarms, find bad actors and focus maintenance efforts on areas in need.

Capture real-time data and store in a database or flat file (CSV) to avoid expensive plant data historian solutions. OPC Expert is not a replacement for a full plant data historian such as OSIsoft PI, AspenTech InfoPlus.21, Honeywell PHD, Wonderware InSQL, Yokogawa ExaQuantum, etc., but instead offers added capabilities by enabling automation personnel to archive the data they require at a suitable frequency without disturbing their plant historian operation, or involving database administrators. Automation personnel can get their work done without requiring lengthy approvals and contentious work orders; they simply receive their data in the exact format they need so they can later import it into their own data analysis tools (such as Excel, LabView, Matlab, etc).

Event-based data capture enables OPC Expert to store data only when it changes, keeping file sizes small and avoiding the loss of any information.

Customized data start/stop events enable OPC Expert to be of particular use for batch operations. OPC Expert can start logging data only when a batch begins and stop data storage when the batch completes. This avoids storing bulky and useless data during transition periods between batches.

Easy data selection enables users to quickly select the data they want to archive, and immediately store it with a right-click. There is no need for users to receive expensive and inconvenient training.

OPC Expert does not require installation, does not change Windows Registry and is free to download making it safe to use on any computer. Download OPC Expert and begin capturing OPC data and archiving immediately in MS SQL, MQTT, or even CSV files!

Do you need additional options or features specific to your production environment? Do you have any suggestions regarding additional features you would like added? Let us know, and we’ll happily consider implementing them in a future OPC Expert release.