Comparison
Logging PLC Data to SQL: Use a Gateway, Not the HMI
Short answer
Neither the S7-1200 nor a Comfort panel can be the SQL client: enabling OPC UA or loading MQTT blocks moves data, it does not commit transactions. Serve the logging tags from the S7-1200 over OPC UA and let a gateway beside the customer's database write the rows, keep the panel's CSV logging as the staged first move, and bench-test any direct-SQL library against the full 30-column record before purchase.
On this page
- Why neither the panel nor the CPU is the SQL client
- Five routes from the PLC to the database
- The decision for an S7-1200 with a Comfort panel
- Stage 1: turn the existing CSV logging into a controlled import
- Stage 2: serve the logging tags from the S7-1200
- MQTT from an S7-1200: settle the domain-name question on the bench
- Torn records: one machine cycle in one row
- Worked example: one 30-column record through the handshake test
- Direct-SQL blocks: settle three points before purchase
- Questions asked
Neither device on this machine can be the SQL client, and no scripting change fixes that. A Comfort panel runs a closed runtime: its VBScript writes files, and installing a database engine or third-party SQL software on it is not a supported path. An S7-1200 with the OPC UA server option enabled publishes tags, and publishing is not inserting. Some host has to convert tag values into committed transactions. The architecture that works keeps every box in its job: the S7-1200 serves the logging tags over OPC UA, a gateway beside the customer's database subscribes and writes the rows, and the Comfort panel keeps visualization plus its existing CSV logging until the SQL path passes acceptance.
Why neither the panel nor the CPU is the SQL client #
SQL logging is three functions: acquiring values, transporting records, and committing transactions. The panel already acquires, because its script writes CSV files today. The OPC UA server option on the S7-1200 is data access — it exposes tags in an address space. The MQTT client library is transport — it publishes messages to a broker. Neither one executes an insert statement, and enabling either does not turn the CPU into a database client.
The transaction therefore lives somewhere by design. The customer's SQL server is itself a computer at some location, and the first question to settle with them is where it sits, because the writer can run next to it. Refusing a computer on the plant floor sets the writer's location; it does not remove the writer.
Five routes from the PLC to the database #
| Route | How it reaches SQL | Main constraint | Fits when |
|---|---|---|---|
| CSV import | The panel keeps writing files; a scheduled importer loads completed files into a staging table | Batch latency, and the importer needs supported access to the files | The lowest-change first stage, with a file-based audit trail |
| OPC UA gateway | The S7-1200 serves tags; a client subscribes and writes parameterized inserts | Needs a host for the client, reachable from the CPU and the database | The end state for a structured tag set |
| MQTT to a broker | The CPU publishes payloads; a consumer subscribes and writes rows | Broker, security, payload schema, buffering and the consumer are all yours to build | Broker-based or cloud delivery already exists in the customer's architecture |
| Direct-SQL function blocks | CPU blocks send database operations with no intermediate computer | License locked to the CPU serial number; statement limits unverified against a wide record; a reachable SQL server is still required | A fixed schema, after a passed full-width bench test |
| OPC DA server plus client | PC-based server software exposes PLC data; a client logs it to SQL | Windows PC software and a legacy stack | Installations already standardized on OPC DA |
For new work on this hardware, OPC UA is the end state, the CSV import is the bridge, MQTT earns its extra engineering only where a broker already exists, and OPC DA stays where it already runs.
The decision for an S7-1200 with a Comfort panel #
- Change nothing on the floor today: keep the panel's CSV logging and add a scheduled importer. This separates acquisition risk from database-integration risk.
- Make the S7-1200 an OPC UA server and let a gateway write the SQL. That is the end state.
- Take MQTT only if the customer already operates a broker or a cloud endpoint. It is not automatically the cheaper route: message identity, acknowledgements, authentication, certificates, buffering, schema evolution and the database consumer remain your engineering.
- Consider direct-SQL function blocks only after they pass the full-width bench test in the worked example below.
- Ask where the database lives. On the customer's own host, the gateway runs there and nothing new appears on the floor. If the database must sit in the cabinet, the writer host sits there too, and industrial mini PCs are the hardware class that runs a gateway in a cabinet.
Stage 1: turn the existing CSV logging into a controlled import #
Freeze the file format before touching SQL: fixed column order, header names, timestamp format, decimal separator, null representation, and the file-rotation rule. Import by column name where the tool allows it, so a reordered column never lands in the wrong field.
Duplicate prevention starts upstream of the loader: each sample must already carry a unique key of source identifier plus record timestamp plus sequence number. Load completed files into a staging table, validate, then move accepted records into the production table inside one transaction. Mark or move the source file only after that transaction commits, and reprocessing a file then produces rejections, not duplicates.
The importer needs supported access to the panel's files, and remote file access to an operator panel has its own failure modes — see why HMI SFTP cannot see the SD card data log files before assuming the files are reachable. Acceptance is a count: inserted rows equal accepted records, with identical timestamps and values across the full record width.
Stage 2: serve the logging tags from the S7-1200 #
The OPC UA server is enabled in the CPU's device configuration in TIA Portal, with general and subscription settings in the server options. Working configurations are documented from TIA Portal V16 projects through a firmware 4.7 CPU under TIA Portal V21, so a project from before this generation means a tool and firmware update as part of the job.
Expose only the tags the log needs, and give the gateway an account with read-only access to them, so the logging path can never write a setpoint. The gateway owns everything the CPU cannot do: parameterized inserts, detection of rejected writes, retries of transient failures, store-and-forward buffering while the database is unreachable, connection-state monitoring, and an accounting of any sample lost between failure and recovery.
The direction is the same distinction laid out for panels in HMI OPC UA server vs client: which direction works — the floor device serves, the database side consumes.
MQTT from an S7-1200: settle the domain-name question on the bench #
The library documentation states that S7-1200 CPUs reach an MQTT broker by qualified domain name from firmware 4.4 with the TIA Portal V16 library block. The library's download page carries a note restricting qualified domain names to the S7-1500. The library document governs the blocks, but the two disagree, so settle it with one bench test: a DNS-resolved connection from your actual CPU to a broker, before the design depends on it.
MQTT solves transport only. The broker, its security, the payload schema, and the consumer that turns messages into rows are separate work, and that consumer is still a program beside the database.
Torn records: one machine cycle in one row #
Reading a wide record as independent live tags can straddle a machine cycle: the first values belong to one cycle, the last to the next, and the row is fiction. The interface that prevents it has five parts: the payload, the record timestamp as a normal column, a monotonically changing record identifier, a ready indication, and an acknowledgement written back by the gateway. The PLC freezes the payload when it raises ready. The gateway reads the identifier before and after the payload; matching reads prove the record never changed during acquisition. The gateway acknowledges only after the SQL transaction commits, and the PLC then builds the next record.
Worked example: one 30-column record through the handshake test #
Inputs, each with its source:
- 30 columns required in every SQL row — the record width the existing CSV log already carries.
- The record timestamp is one of those columns, so the handshake adds three items that never become SQL columns: record identifier, ready indication, acknowledgement — the interface above.
- One frozen test record, generated once at the PLC.
Rule in words: the server namespace must expose the payload plus the handshake; a row is valid only when the identifier read before the payload equals the identifier read after it, every field lands populated, and delivering the same record twice inserts exactly one row.
Arithmetic:
- Namespace items: 30 payload columns + 3 handshake items = 33 items to browse.
- Replay: 2 delivery attempts - 1 duplicate rejected by the unique key = 1 production row.
- Field check: 30 fields expected = 30 fields populated in that row.
Result: browsing the namespace returns 33 items, the frozen record commits as one row, and replaying the identical record changes nothing.
Decision: a mismatch between the before and after identifier is a torn record — discard it and read again, never insert the mixed values. Fewer than 30 populated fields, or an insert rejected at full width, fails the transport for this application. Applied to a purchased direct-SQL library, that failure on the bench is the answer: do not buy it for this record.
Direct-SQL blocks: settle three points before purchase #
First, the license binds to one CPU serial number, so the commitment is per-CPU. Second, the library still needs a reachable SQL server that you host somewhere, so a remote database is the only configuration that keeps new computers off the floor. Third, the width: the user guide reads as if it limits the columns a statement can write, and this record needs every column it has — do not interpret the guide, run the full-width insert from the worked example and count the fields that arrive.
Questions asked
Can I install SQL Server Compact on the Comfort panel and insert rows from VBScript?
No. Comfort panels run a closed runtime, and installing third-party software or a database engine on them is not a supported path. SQL Server Compact 4.0 has also been deprecated for roughly seven years, so it would be a dead end even on open hardware. Keep the panel scripting on file output and put the inserts on a gateway that can run a real database client.
Does enabling the OPC UA server on the S7-1200 write to the database by itself?
No. The server option publishes tags into an address space; an OPC UA client still has to subscribe or read and run the inserts. You can prove the server half before buying any gateway product: browse the CPU with any standard OPC UA client and read your logging tags. If the values arrive, the remaining work is only the client side.
The customer refuses a PC on the plant floor. Where does the SQL writer run?
Next to the database. If the customer already runs the SQL server on their own host, the gateway runs on or beside that host and the plant floor keeps only the S7-1200 and the panel. A writer host appears on the floor only when the database itself sits in the cabinet, and then it is compact industrial computer hardware sized for a cabinet, not a desktop PC.
How do I stop a repeated CSV import from creating duplicate rows?
Give every sample a unique key before it reaches the database: source identifier plus record timestamp plus sequence number. Load into a staging table, validate, then insert into the production table inside one transaction, and move or mark the source file only after that commit. The acceptance test is blunt: import the same completed file twice and expect zero new production rows.
What must a direct-SQL function block library prove before purchase?
One complete insert at your real record width. Write a single 30-column record on the bench and count the populated fields in the resulting row; anything short of 30 means the library's statement limit drops data on this application. The license is locked to the CPU serial number, so a library that fails on your CPU is not a free swap to another controller.