How-to
Log production data from a panel PC to SQL Server
Short answer
Run Node-RED on the panel PC, read the production values straight from the controller, and insert one row per event into SQL Server with the mssql-plus node. Keep the panel's CSV-plus-FTP file relay only as a fallback, block duplicates with a last-record check and a unique key, and expect about 86,400 rows, roughly 8.6 MB, per day at one record per second.
On this page
- Can a panel PC put production data into SQL Server without a historian?
- Should I read the controller directly or relay the panel's CSV file?
- What does the controller need before Node-RED can read it directly?
- How do I insert production rows into SQL Server from Node-RED?
- How do I keep duplicate rows out of the table?
- How much data does per-second logging put in SQL Server?
- How do I prove the whole path works before go-live?
- Questions asked
You can move production data from the machine into SQL Server without buying a historian or building middleware: run Node-RED on the panel PC, read the production values straight from the controller, and insert one row per event. The panel keeps running its own screens untouched while the database becomes the system of record for reports, OEE calculations and shift exports. This article walks the direct-read setup, the CSV file relay worth keeping only as a fallback, the duplicate protection that keeps replays out of the table, and the sizing arithmetic that tells you whether the table, and SQL Server Express, can hold the traffic you plan to send it.
Can a panel PC put production data into SQL Server without a historian? #
Yes: Node-RED running on the panel PC or on a small edge box reads the production values straight from the controller and inserts one row per event into SQL Server, so you need no historian license, no ODBC driver on the panel, and no middleware server.
The stack is three pieces. The panel PC, or a small edge PC beside it, runs Node-RED as a service that starts on boot. A driver node polls the controller for the tags the production screen already shows — cycle count, good parts, machine state — and a SQL Server node inserts one row per event into the logging database. Nothing on the panel's own application changes: the screen keeps its job, and the database becomes the record every report reads.
Commit to one timestamp source before the first row lands. Take timestamps from the controller where the protocol delivers them, or stamp them on the Node-RED host at arrival, and never mix the two in one table. A report that blends controller time with host time produces shifts that overlap by seconds or lose them, and nobody trusts a data log that argues with the clock.
Should I read the controller directly or relay the panel's CSV file? #
Read the controller directly: every file-relay step is one more thing that can stall, duplicate, or drop records, and a direct read puts one timestamped row per event into SQL Server with no CSV parsing and no FTP schedule to babysit.
| Route | Data path | Choose it when | Watch |
|---|---|---|---|
| Direct read | Controller → Node-RED → SQL Server | A driver node exists for the controller protocol and its firmware meets the driver's documented floor | Poll cadence, read-only access |
| CSV relay | Panel logs CSV → FTP → Node-RED → SQL Server | The panel is a closed unit that logs tags to file but cannot run programs | Duplicate files, reprocessed rows, FTP schedule |
The CSV relay is the fallback, not the answer. A closed panel that cannot run programs but can log tags to a CSV file, plus an FTP batch that moves the file to a network share on schedule, still gets the data across: Node-RED reads the file, parses the rows and inserts them. That pattern is written up end to end, for one specific panel, in the EA9-T12CL data logging to SQL Server via Node-RED guide. Every transfer step, though, is one more schedule to babysit and one more way to create duplicate rows, so take it only when no driver exists for the controller.
If the closed panel is the only device at the line and there is no PC anywhere near it, the direct read needs a host: mount a fanless mini PC beside the cabinet and run the logging flow there. A third variant — the same direct read delivering to a message broker instead of a database — is worked through in Bridging OPC UA PLC data to MQTT with an edge PC.
What does the controller need before Node-RED can read it directly? #
A driver node that speaks the controller's protocol and a controller that meets that driver's documented firmware floor: the community PCCC driver, for example, requires Ser A FRN 5 or later, or Ser C FRN 3 or later on the controller family it targets, and earlier versions will not work.
That example is the point: driver requirements are per protocol, per driver and per controller family, and the driver's own documentation states them. Before committing to the direct route, confirm three things. First, a driver node exists for the controller's protocol. Second, the controller's series and firmware revision meet that driver's documented floor. Third, the driver can operate read-only. If any of the three fails, take the CSV relay, because a panel that already displays the values can already log them.
One safety note, and it is a hard rule: the logging connection is read-only. Nothing on the data-collection host ever writes a value to the controller — a data log that can move the machine is not a historian, it is a hazard.
How do I insert production rows into SQL Server from Node-RED? #
Install Node-RED, add the SQL Server nodes, and write one INSERT per message: the node-red-contrib-mssql-plus node takes a query on msg.topic and executes it for each message that arrives, so a function node mapping your controller tags to columns is all the code you write.
- Install Node-RED on the host and register it as a service so logging survives a reboot and a power cut. On Windows install Node.js, then Node-RED, then a service wrapper; on a Linux box the whole procedure is written up in Install Node-RED Natively on a Linux Edge Computer.
- Add the SQL Server node from the palette. The community node for current Node-RED is node-red-contrib-mssql-plus. Configure its connection with the server address and instance, the database name, and a dedicated SQL login holding INSERT rights and nothing wider.
- Add the driver node for the controller. For controllers speaking PCCC over Ethernet the community node is node-red-contrib-pccc. Poll the production tags at the cadence your reports need; the sizing arithmetic below tells you what that cadence costs.
- Map values to columns in a function node. Declare every field — station_id (number), ts (number, milliseconds), good_count (number), machine_state (string) — and default what can be missing. Never insert a row whose required fields came through undefined.
- Build the INSERT and put it on msg.topic. Bind the values as parameters instead of concatenating them into the query text; the node's help panel documents the parameter syntax for your installed version.
- Deploy and watch the debug pane. Each event must produce exactly one insert with an affected-rows result of 1.
How do I keep duplicate rows out of the table? #
Track the last processed record in Node-RED context and refuse anything older: store the newest timestamp or record number after each insert, compare every incoming value against it, and add a unique key on the table so a replayed insert fails instead of duplicating.
Two events create duplicates in practice: the flow reprocessing the same CSV file after an FTP retry, and a driver reconnect that retransmits records the first attempt already inserted. The gate below handles both; put it in a function node ahead of the SQL insert.
// ts: number, milliseconds since epoch, taken from the record itself.
// lastTs: number held in Node-RED context, initialises to 0 on first run.
const ts = Number(msg.payload.ts);
const lastTs = context.get('lastTs') || 0;
if (ts <= lastTs) {
return null; // retransmit or reprocessed row: drop it
}
context.set('lastTs', ts);
return msg;
The test that proves it: insert a record carrying ts = 1000, so context holds 1000. Replay the identical record — the gate returns null. Send ts = 1005 — it passes and context becomes 1005. SELECT COUNT(*) on the table then returns 2, not 3. For file relays, also keep a context set of processed file names and delete a file only once its inserted row count equals its data-line count.
Make the database the authority, not the flow. Define the table with PRIMARY KEY (station_id, ts), so any duplicated insert fails with a constraint violation the SQL node reports and a catch node can log. Context protects one Node-RED instance; the key protects the data even if the flow is restored onto a second machine or the context resets.
How much data does per-second logging put in SQL Server? #
At one record per second a production table grows by 86,400 rows and about 8.6 MB a day at 100 bytes per stored row, which is roughly 3.2 GB a year — light work for a server, and about three years to SQL Server Express's 10 GB per-database cap.
Worked example, one line logging one event record per second around the clock:
- Records per day: 1/s × 60 × 60 × 24 = 86,400 records each day.
- Bytes per day: 86,400 records × 100 B/record = 8,640,000 B ≈ 8.6 MB each day. The 100 B row width is an illustration covering timestamp, station id, counters and index overhead — measure your own row before trusting the extrapolation.
- Records each year: 86,400 × 365 = 31,536,000 records; at 100 B that is 3,153,600,000 B ≈ 3.2 GB a year.
- Days to fill the Express cap, documented at 10 GB of data per database with log files outside it: 10,000,000,000 B ÷ 8,640,000 B each day ≈ 1,157 days ≈ 3.2 years.
The decision falls out of the same arithmetic. Event logging at one record per second is light work: any modest server holds years of it, and even Express runs about three years before a purge or archive is needed. Sampling raw values at 100 ms is a different workload: 10 records/s is 864,000 records and 86.4 MB each day, reaching the same cap in 10,000,000,000 ÷ 86,400,000 ≈ 116 days. At that rate, log events into SQL Server and send high-rate raw samples to a store built for them; the capacity math for a self-hosted Node-RED, InfluxDB and Grafana stack is worked through in Sizing a self-hosted Node-RED InfluxDB Grafana historian.
How do I prove the whole path works before go-live? #
Push one known event through and count it at the far end: set a production counter to a chosen value, watch the row arrive with the right timestamp and station, replay the same event, and confirm the table still holds exactly one row.
Run three checks in order and go live on the numbers, not on a glance at the debug pane.
One known record. Set the production counter on the controller to 42, trigger one cycle, and query the far end:
SELECT station_id, ts, good_count
FROM dbo.production_log
WHERE ts = '2025-01-15T08:30:00'; -- illustrative values
It must return one row with good_count = 42 and the timestamp source you committed to earlier. Replay the same event from the flow: the dedupe gate drops it, and COUNT(*) for that timestamp stays at 1.
One outage. Stop the SQL service or pull the network cable for a minute while cycles are running, restore it, and confirm buffered records replay oldest-first with no gaps — or that records were dropped by design if you built no buffer. What is unacceptable is silence: rows missing with nothing recording the miss.
One file, if you relay CSV. A shift file holding 480 data lines must produce exactly 480 rows on the first pass and add exactly 0 rows when the same file is processed again. Rows inserted minus lines in the file is your duplicate count, and it must be zero both times.
Questions asked
Can Node-RED run on the same panel PC as the HMI or SCADA software?
Usually yes: a logging flow of a few dozen nodes is light on CPU and memory, and Node-RED runs as a service on Windows or Linux beside a light HMI application. Keep it off a host your automation vendor requires as a dedicated, locked-down image, keep the logging flows in their own project directory, and size the machine for the data workload, not just the screen.
Should inserts run on every value change or on a timer?
Both, split by tag type. Cycle counts, batch numbers and machine states belong in the table the moment they change. Analog values get one row per scan interval your reports actually use, for example every 60 seconds, gated by a deadband so a signal hovering between two states does not write thousands of near-identical rows. Set the deadband from the smallest change anyone actually reports on.
What SQL Server login should the Node-RED connection use?
A dedicated SQL login used by nothing else, granted INSERT and SELECT on the logging database only. The connection never needs sysadmin rights, and a narrow login means a misconfigured flow cannot read other databases or alter the logging table. Store the password in the Node-RED credential store, rotate it on the plant's normal credential schedule, and give that account to no human.
What happens to records while SQL Server is offline?
By default the SQL node reports an error and that message is gone, so add a catch node that writes failed records to a local file or queue and replays them oldest-first when the connection recovers. Cap the buffer by record count so a long outage cannot fill the disk, and prove the behaviour during commissioning by stopping the SQL service under load.