Introduction
Welcome everyone to this live presentation on OPC archiving. My name is Randy and I’m the presenter today. We’re going to talk about the data custody transfer between automation and IT, how we capture real-time OPC DA and UA data, and how we record long-term historical data. We’ll do this on an event-based data capture — you can also do time-based, and I’ll show you where to choose. I’ll also show you how OPC Expert maintains data integrity by preventing data loss using store and forward. At the end, we’ll send you an email containing a link to this webinar recording, a link to download OPC Expert, some quick setup videos, and an invitation to the next webinar.
The Data Custody Transfer Challenge
To give you a better idea of where we’re going: we have a couple of control systems, and we want to send the data somewhere — to some destination. This might be SQL or a remote SQL server, or somewhere in the cloud like Azure or AWS. To do that, we put OPC Expert on a computer, collect data via OPC into a local database, do some analysis in Excel, and send that data to the destination.
The steps are: capture OPC data, collect the real-time data into a database, activate Excel to look at some data and confirm we’re receiving it, then send the data to the destination — in this case, a Microsoft SQL server running on a remote computer. I’ll also show you what happens during a disconnection and recovery, and how OPC Expert is able to recover without losing data.
This is the core challenge: the data custody transfer. IT typically stores data in files like CSVs or XML, or in SQL databases such as Microsoft SQL, MySQL, PostgreSQL, or Oracle. They might store data in the cloud via Amazon AWS or Microsoft Azure, or in a NoSQL database like MongoDB, or send it to MQTT. All of that is owned by IT.
Automation, on the other hand, gets data via OPC. We’re reading and writing data to controllers like PLCs or DCSs, and sending data to applications like HMIs, historians, or advanced control systems. We use OPC classic or OPC UA — OPC with DCOM, OPC DA, or OPC UA — to capture real-time data. Automation transfers data with OPC. IT wants to capture that data and put it in a database. That’s what we mean by data custody transfer.
We use OPC Expert as the middleware. In this case, we’re using OPC Expert to take real-time data, capture it, and put it into permanent, long-term storage.
Requirements
The first requirement is reducing file size. Even a very small plant can generate a huge amount of data — every minute, every second, every tenth of a second, or every millisecond. To reduce the volume, we make it event-based: we store data only when it changes. If it doesn’t change, there’s no point storing it over and over. You can make it time-based and store every second regardless of change, but event-based is the most efficient approach, and I’ll show you where to set that option.
We also need to handle reconnections. You might need to reconnect if the OPC server crashed, if a network connection was lost, if SQL was taken down for maintenance, or if a Windows update caused a reboot. We can’t lose data in any of these cases. That’s where store and forward comes in: store the data locally, then forward it when the connection is restored, preventing data loss and maintaining data integrity. OPC Expert also already includes redundancy modules if you want to add that as well.
Platform Support
For Excel, OPC Expert supports both 32-bit and 64-bit releases, from Excel 2003 all the way through Office 365. For Windows, it supports Windows XP through Windows 11 on workstations, and Windows Server 2003 through Windows Server 2025. OPC Expert typically runs as a Windows service, which means it starts automatically with Windows — no log-on required. If there’s ever a reboot or if the service is stopped, Windows will automatically restart it and OPC Expert will immediately continue archiving data.
Security
Security isn’t optional today. OPC Expert uses protocol-level security embedded in the protocol itself — MQTT security, OPC UA security — and it also uses Windows security on top of that, plus the IT security mechanisms you already have in place, such as firewalls. You can configure OPC Expert for any port you want, so it sits cleanly on top of your existing infrastructure.
To get data from an OPC server, OPC Expert can use OPC security or Windows security, or both. If you’re sending data to Microsoft SQL, you can use SQL security or Windows security — SQL authentication or Windows user accounts. OPC Expert supports all of it. All communication can be encrypted if that’s what you want, or left unencrypted — your choice. And with a firewall, only one port is needed, and you get to set that port. This also means OPC Expert comes with embedded tunneling already supported.
OPC Expert Architecture
Everything shown here is embedded inside OPC Expert as modules — nothing is separate. OPC Expert starts with an OPC DA client and an OPC UA client to ingest data into its intelligent engine. Data comes in via OPC DA or UA. The Excel RTD (Real-Time Data) module then makes it possible to get data into Excel very efficiently, with no DDE required. You can also send data back out via OPC DA or UA.
This is also how OPC Expert works as a built-in data converter. You can ingest OPC DA and send out OPC UA, or receive OPC UA and send out OPC DA — UA-to-DA, DA-to-UA, DA-to-DA, or UA-to-UA. It normalizes your communication, and all of that is built into the architecture.
Beyond that, there’s MQTT — a full pub/sub system with a broker, publisher, and subscriber, all embedded inside OPC Expert. There are also database connectors: you can send data to Microsoft SQL and other SQL databases, to AWS, to Azure, or store it in XML or CSV. Store and forward is available for all of these destinations. OPC Expert can also ingest data from OPC Classic HDA, OPC UA, OPC Alarms and Events, or OPC Alarms and Conditions, and send data out via REST API.
In this webinar, we’ll focus on five specific modules: OPC DA ingestion, OPC UA ingestion, Excel RTD, REST API, and Microsoft SQL archiving.
Store and Forward
If the connection to a destination is ever broken — even if you take a system down for updates — all data is stored locally. When the connection is restored, OPC Expert sends all the stored data. OPC Expert doesn’t have to care whether it’s connected to remote systems or not, because it’s always storing locally and forwarding whenever a connection is available. That’s the store and forward mechanism, and it ensures you never lose data.
Demo: Capturing and Archiving OPC Data
In this demo, we’re taking data from controllers and sending it to a remote Microsoft SQL server. We have two computers: one called NGS Station running OPC Expert, and one called SQL-DB running SQL Server. We’ll collect data via OPC DA and UA, store it locally, analyze it in Excel, and send it to SQL. Then we’ll simulate a disconnection and recovery to see store and forward in action.
Installing and Starting OPC Expert
When you download OPC Expert, all you get is one executable. When you run it, it extracts all the files it needs automatically. Once started, OPC Expert appears in the system tray. Setup is straightforward.
Connecting to OPC Servers
On the demo computer, we have four OPC servers available: a Kepware DA server, a Modbus DA server, a Kepware UA server, and an OPC Expert server. We first connect to OPC Classic and grab 15 items. The quality in OPC DA is 192. When we switch to OPC UA, the quality value is different — that’s expected. We then open several folders under OPC UA and subscribe to all items, totaling 60 items.
Looking at the item list, items with quality 192 are from OPC DA and items with quality 0 (which is “good” in OPC UA) are from OPC UA. You can also see the specific server each item comes from and its item ID, including UA items with a namespace. We’re now collecting data from both OPC DA and OPC UA.
Creating an Archive to SQL Server
With all 60 items selected, we right-click and choose to create a new archive. There are many destination options available. We select Microsoft SQL Server and click Next. The target computer is SQL-DB on port 1433, using SQL authentication with a username and password.
Here you can also choose whether to archive only on value change (event-based) or to archive every second regardless of change (time-based). For this demo, we’ll use the defaults. There’s a test button to verify the connection immediately, and it confirms a successful connection.
Before clicking OK, note that on the SQL side there is currently no database named “history.” When we click OK, OPC Expert sets everything up on its own — it creates the database automatically. The connection indicator turns green, confirming we’re now connected to the database and archiving from both OPC DA and OPC UA.
Running as a Windows Service
To install OPC Expert as a Windows service, go to Tools and select Install as a Windows Service, running under the local system account. In Task Manager, you’ll see both the OPC Expert system tray application and the OPC Expert Archive Server running as the system account. In Services, it appears as “OPC Expert Archive Server” — clearly labeled. The archive server is now a service and will restart automatically if stopped.
Trending with REST API
To trend data, select one UA item and one DA item, right-click, and choose Trend. OPC Expert creates a REST API server service automatically and opens the trend in a browser using the MS Edge WebView. The trend is interactive — you can move items around, view cursor values, and see markers where data was missed. In this example, a gap appears at the point when we set it up to run as a service and it had to stop and restart, which is expected behavior.
Services Running Without the GUI
To demonstrate that the services are independent of the GUI: if you close OPC Expert entirely, the archive and REST API services continue running. You can see in Task Manager that they’re running as the system account. Even if you force-stop them, Windows restarts them automatically because they’re registered services. The GUI is not required to archive — it’s just a management interface. When you do reopen the GUI, it reconnects to the running archive automatically. CPU usage for the background services is minimal, barely registering in Task Manager.
Viewing Data in Excel
To use Excel with OPC Expert, go to Options and enable Excel features. Once that option is checked, when you open Excel you’ll see an OPC tab in the ribbon. Clicking Status shows the connection to OPC Expert Server and confirms that RTD, historical data, and UDF are all functioning properly, with data updating in real time.
From there, you can subscribe to inputs and see real-time data directly in Excel. You can also retrieve historical data. The interface is drag-and-drop and straightforward to work with.
Verifying Data in SQL Server
Back in OPC Expert, everything shows green in the archive view, confirming data is flowing. In SQL Server Management Studio, refreshing shows that the “history” database now exists. Expanding it reveals two important tables: an items table (real-time values) and a historical data table.
Querying the items table shows 60 rows — one per item — with the latest value, data type, and other metadata. SQL Server Management Studio doesn’t refresh automatically, so running the query again shows the values changing in real time. The historical data table is growing fast: within a short time, it already contains nearly 40,000 rows, and new rows are constantly being added.
Timestamps are stored in UTC by default, though local time is also available. Quality 0 indicates data from OPC UA; quality 192 indicates OPC DA. OPC Expert also includes some useful pre-built queries — for example, filtering by a specific item such as “flow” makes it easy to see just the values for that tag over time.
Store and Forward in Action
To demonstrate store and forward, we disconnect the network cable. The cursor in SQL stops moving. In OPC Expert, the database indicator turns red — it can’t reach SQL — but it’s still acquiring data and storing it locally. Nothing is missed.
When we reconnect the network, OPC Expert immediately reconnects to SQL. Running the query again shows all the data that was captured during the disconnection is now in the database — nothing was lost. The store and forward system is fully embedded in OPC Expert, and it works automatically without any configuration beyond the initial setup.
Summary
To recap the five tasks we completed: we captured OPC data from both OPC DA and OPC UA, collected real-time data, viewed it in Excel with both real-time and historical queries, trended a local view using the REST API, and stored the data in a remote SQL server. We also confirmed that store and forward works correctly — no data loss even with a full network disconnection and recovery.
You can do your analysis locally or in SQL Server — wherever is more convenient. And because everything runs as Windows services, OPC Expert keeps running and keeps archiving regardless of whether the GUI is open.
Training and Next Steps
To get automated troubleshooting, you can download OPC Rescue or download OPC Expert. With OPC Expert, you get exactly what was demonstrated here — trending, bridging, tunneling, archiving, and more. All of these modules are included in a single product.
To learn more, you can attend one of our hands-on learning classes and earn your Certified OPC Professional designation. In these workshops, you configure OPC UA and DA on two computers — you install it, uninstall it, and deliberately break things so you can learn to fix them. It’s a genuinely useful class. The classes are remote, interactive, and led by a live instructor, so there’s no travel required. You can attend a public class alongside people from other companies, or arrange private training for your own team. Check dates at opcti.com.
We’ll send you a link to this webinar recording, a link to download OPC Expert, and a link with Excel and archive examples. You can also scan the QR code on screen to access those resources right now. You’ll also receive an invitation to the next webinar, which covers OPC to Web — taking live OPC data and sending it to the web so you can trend and view it on your phone or computer using REST API. No programming required.
Questions and Answers
That’s the end of the formal portion. We have a bit more time for questions, so go ahead and write them in. Thank you again for attending, and I hope to see you at the next webinar. Good luck with your projects.
