Getting PLC data into a database without wrecking the controller

The question is usually asked as a protocol question and is really an architecture question. Where the boundary sits determines whether a database problem becomes a production problem.

The rule

The controller should not know that a database exists.

A PLC scan is a hard real time loop measured in milliseconds. A database insert is a request over a network to a system that might be doing a backup, and it can take an unpredictable amount of time.

Joining those two directly means the machine's scan time now depends on a database server. That is a dependency nobody would design deliberately, and it is what a direct connection creates.

Four architectures, in ascending order of sense

Direct from the controller. Some controllers offer SQL instructions. It works on a good day and it fails badly: blocked scans when the server is slow, lost data when the network drops, and database credentials sitting in a PLC program that anybody with the software can read.

Polled by a gateway. Software reads the controller over OPC UA, Modbus or a native driver and writes to the database. The controller knows nothing. Simple, well understood, and the standard answer.

Published by the controller. The controller writes changes to an MQTT broker or an OPC UA server; a subscriber persists them. Efficient, since only changes move, and it scales to many consumers.

A historian. Purpose built time series storage with compression tuned for process data. Right answer for large tag counts and long retention, and a licence cost that has to be justified.

Timestamps

Where the data quality is won or lost.

A reading timestamped when the gateway wrote it is timestamped after an unknown polling delay. On slow process data nobody cares. On anything used for a sequence of events analysis, it makes the record useless: two events half a second apart can be recorded in the wrong order.

Where the ordering matters, timestamp in the controller and carry the timestamp through as data. Every layer after that preserves it rather than replacing it.

And keep everything in UTC until the moment of display. A plant that stores local time has two hours a year that either do not exist or happen twice, and the query that spans them silently returns the wrong thing.

Store and forward

The feature that separates a logging system from a logging system that works.

When the database is unreachable, the gateway buffers to local disk. When it returns, it sends the backlog with the original timestamps.

Without it, every network problem is a permanent hole in the record. With it, an outage is a delay. The buffer needs a bounded size and a policy for what to drop when full, and the policy should be oldest first, so the recent past survives.

Change of state, with a heartbeat

Most process values do not change most of the time. Polling every second and storing every reading fills a database with the same number.

Report by exception, with a deadband so noise does not count as a change. Then add a periodic write every few minutes regardless, so a flat line can be distinguished from a dead connection. Without the heartbeat, no data and unchanged data look identical.

What to store

The tags somebody will ask about, which is a smaller list than the tag table and a longer one than the current requirement.

Under-logging is discovered at exactly the wrong time, when somebody asks what the temperature did during the batch that failed and the answer is that it was not logged. Over-logging is a storage cost, and storage is cheap.

Err towards logging, but decide deliberately rather than dumping every tag, because a database of forty thousand columns nobody can name is its own kind of unusable.