IMD SolutionComputing LibraryShop main store

Comparison

PLC to SQL Server: REST API or direct database access

Edge computing and IIoT Published 8 min read

Short answer

Choose a REST API in front of SQL Server when the data carries business rules, and an external collector that reads the controller when the database is only a repository. Keep SQL out of the PLC. The deciding test: classify each exchange as event, lookup or stream, and pick the path whose owner commits the transaction and acknowledges back to the controller.

On this page
  1. The decision rule: transaction ownership
  2. Five paths from the PLC to SQL Server
  3. What a REST API buys, and what it costs
  4. When the database is only a repository
  5. TLS, certificates and the S7-1200
  6. Where the middleware runs
  7. Worked example: two exchanges against a 15-second takt
  8. The decision, case by case
  9. Questions asked

If the database carries business rules — serial-number genealogy, part lookups, batch relationships — put a REST API in front of SQL Server and run it on an industrial PC beside the controller. If the database is only a repository for history and measurements, let an external collector read the controller and write the records. Keep SQL out of the PLC program: it couples every schema change to a controller download, spends program memory on parsing and query handling, and still leaves credentials and transport security as PLC problems. The deciding test is transaction ownership: classify each exchange as an event, a lookup or a continuous stream, then pick the architecture whose owner commits the transaction and acknowledges completion back to the controller.

The decision rule: transaction ownership #

Classify before you choose a protocol:

  • Event — a part finished, a serial was assigned, a batch closed. It needs a durable record and an acknowledgment the PLC can act on.
  • Lookup — the controller asks a question: the last part for this batch, the next valid serial. It needs a bounded response time and a defined miss behavior.
  • Continuous stream — measurements destined for history. No per-value acknowledgment; rate, buffering and backfill are what matter.

For each exchange, write down the initiator, the completion condition, and who tells the controller. An architecture passes when one component owns every step from PLC capture to committed database row, including the acknowledgment; it fails when a record can vanish inside a silent retry. All five paths below can pass that test — they differ in who carries the burden of making it pass.

Five paths from the PLC to SQL Server #

The choice is older than REST: direct, HMI and middleware strategies for PLC-to-database connectivity all reduce to where the transaction lives. The five realistic paths:

Path Transaction owner Controller load Main commissioning burden
PLC HTTP client to REST API PLC initiates; API executes and acknowledges HTTP and TLS stack plus response parsing in program memory API contract, TLS, timeouts and retries in the PLC
External reader over the S7 protocol Application captures and writes Minimal: mapped data blocks Coherent snapshots, event detection, acknowledgment write-back
Direct SQL library in the PLC (LSQL) PLC owns query and transaction Library plus query handling in memory Credentials, schema coupling, per-state error handling
OPC UA server on the controller Client application reads and writes OPC UA server plus certificates Data modeling, certificates, transaction coordination
HTTP trigger with OPC UA payload API executes; HTTP carries the handshake Small HTTP call; data moves over OPC UA Correlating trigger, payload, result and acknowledgment

PLC HTTP client to REST API. Endpoints such as /add/serial and /get/last_part name production operations instead of SQL statements; the controller calls an operation and reads a result. The work moves to request encoding, TLS, retry handling and a written API contract.

External reader over the S7 protocol. An application polls data blocks and writes rows. You now own coherent snapshots (all values of one part read atomically), event detection (the edge on part complete), and a write-back flag — without the flag the path is fire-and-forget.

Direct SQL in the PLC. LSQL library blocks exist for direct SQL communication from the controller. Reported experience puts the cost in program memory rather than cycle time: heavy on the S7-1200 class, workable on an S7-1500. Add credentials living in the controller project and every schema change becoming a PLC change.

OPC UA. The controller exposes typed, readable data; the client application owns reads, writes and the database. The S7-1500 OPC UA server uses application certificates even with the security policy set to None, so certificates are part of commissioning either way.

HTTP trigger with OPC UA payload. A small HTTP call starts the operation, the payload moves over OPC UA, and the API reports success only after the OPC UA exchange and its validations complete. The controller parses no JSON and still gets a definitive result.

What a REST API buys, and what it costs #

It buys one place for serial-format checks, relational checks and business calculations, with parameterized database operations — never SQL assembled from text the API received. PLC programmers call operations, not queries. The counter-proposal to read data blocks and write SQL hands the same middleware problem to a different team, minus the operation boundary.

An endpoint name is not a contract. Write down, per endpoint: the HTTP method, request fields, response fields, validation rules, and success defined as the transaction committed — not the request received. Return distinguishable outcomes for invalid input, missing record, duplicate submission, unavailable dependency and internal failure; a 200 response proves transport, not the production operation.

JSON is not mandatory. A plain-text command with a CSV response, exchanged over LHTTP to a Python service, parses cheaper in the controller; you still specify delimiters, escaping, field order and maximum lengths, and you still test each endpoint with valid, invalid, missing and duplicate data before the controller is connected.

The cost is a maintained component: hosting, patching, and an owner after the integrator leaves. The objection that custom middleware ties the plant to its author is answered with documentation, a container image and a boring, patched host — not with the claim that it is standard.

When the database is only a repository #

History tables, batch measurement dumps, genealogy written after the fact: the collector belongs outside the PLC. The established pattern is logging PLC values from an OPC server into SQL Server from a plant-side application, and the same shape works for other sources, as in Modbus TCP data logging to a database from a plant PC. The controller exposes data; the application owns capture, batching and writes; nobody waits mid-cycle.

MQTT changes the transport, not the decision: there is a broker to run and deliveries to manage, and still a component that validates and writes SQL. Weigh PLC MQTT direct publishing versus an IoT gateway when the traffic is mostly streams rather than lookups.

Keep LSQL-class direct access for the narrow case: a plain repository, a stable schema, and a team that accepts PLC downloads when the schema moves.

TLS, certificates and the S7-1200 #

Scope the security claims to the installed CPU and firmware. The S7-1200 and S7-1500 families use TLS for certificate-based applications including the web server over HTTPS, but older S7-1200 firmware versions do not support TLS 1.2 — confirm the firmware supports the TLS version your API requires before designing around HTTPS. Field reports describe LHTTP exchanges over HTTPS taking 6 to 10 seconds on an S7-1200; that is a report, not a specification, and it is the number to budget until you measure your own. On the OPC UA side, enable and test security early: the S7-1500 server needs application certificates even at policy None, and S7-1200 OPC UA servers are described in the field as solid until security is switched on — verify that on your firmware, not at commissioning.

Production needs both transport encryption and per-caller authentication; a demonstration on an isolated network establishes neither. Keep every exchange asynchronous to the machine sequence, advancing across controller scans and waiting for a result only where the sequence truly needs it.

Where the middleware runs #

Host the API on a fanless mini PC mounted in the control cabinet, running Windows IoT or LTSC, or Linux, on a documented patch schedule. The container image packages the software; the operating system lifecycle is the real commitment, and it needs a named owner. A demonstration box or hobby board with no patching path is where these deployments die in production.

Worked example: two exchanges against a 15-second takt #

Example inputs: a cell takt of 15 s per part, and two controller-initiated exchanges per part — register the serial, then fetch the last part record. The limiting figure is the reported S7-1200 HTTPS exchange time of 6 s to 10 s.

  • Worst case, both sequential: 10 s + 10 s = 20 s. That exceeds the 15 s takt by 5 s. Fails.
  • Best case, both sequential: 6 s + 6 s = 12 s, leaving 15 s − 12 s = 3 s of margin. One best-case retry adds 6 s: 18 s > 15 s. Fails.
  • One exchange per part, worst case: 10 s, margin 5 s. One worst-case retry: 20 s > 15 s. Still fails.

Decision: with these numbers the controller cannot own this transaction — any sequencing of two HTTPS exchanges, with or without a retry, breaks the takt. Move ownership out: the controller sets a record-ready flag, an external collector reads the record over OPC UA or the S7 protocol, writes SQL Server, and sets a stored bit that the controller checks in the following cycle. The controller never blocks on a network round trip. The takt is the example input; the 6 to 10 s figure is a field report that your firmware, TLS session reuse and local switch will move — measure before sign-off, and never design to the best case.

The decision, case by case #

  • Serial genealogy, part lookups, anything with business rules: REST API on an industrial PC beside the controller; the PLC calls operations, the API validates and commits.
  • History and measurement storage: external collector over OPC UA or the S7 protocol writing to SQL Server; the PLC only exposes data.
  • A controller-initiated event whose worst case plus one retry fits the available window: the PLC HTTP client is legitimate — keep it asynchronous to the machine sequence.
  • A lookup needed mid-cycle with a tight budget: no network lookup in the cycle; preload the data or hand ownership to the collector.
  • Direct SQL from the PLC: only a plain repository, a stable schema, and acceptance that schema changes are PLC changes.

Questions asked

Does the S7-1200 support HTTPS for REST calls?

TLS support is firmware-dependent. The S7-1200 family uses TLS for certificate-based applications such as HTTPS, but older firmware versions do not support TLS 1.2, so confirm the installed firmware supports the TLS version your API requires before designing around HTTPS. Field reports with the LHTTP library over HTTPS describe 6 to 10 second exchanges; budget the worst case and measure on your own controller before sign-off.

Where should the REST-to-SQL middleware run?

On an industrial PC in or next to the control cabinet: a fanless box PC running Windows IoT or LTSC, or Linux, on a documented patch schedule. The container image packages the software; it is not the support plan. The host needs an operating-system lifecycle owner, because middleware deployed on a demonstration machine or hobby board with no patching path is where these projects fail in production.

Why not let the PLC query SQL Server directly with LSQL?

It works while the database stays a plain repository with a stable schema. Every table change becomes a PLC change, credentials sit in the controller project, and the library plus query handling take controller program memory, reported as heavy on the S7-1200 class and workable on an S7-1500. The moment validation or business rules appear, they migrate into the controller program, which is exactly what an API in front of the database prevents.

Is OPC UA simpler than a REST API for this?

OPC UA gives you typed data, subscriptions and certificate-based security, and it removes JSON parsing from the controller, but the transaction still needs an owner: a client application must read the data, write SQL and acknowledge back. A documented combination uses an HTTP call for the handshake with the payload carried over OPC UA, so the controller receives a simple success or failure instead of a JSON document to parse.

Does MQTT replace this decision?

No. MQTT is a transport: it adds a broker to run and deliveries to manage, and it does not remove the component that validates data and writes to SQL Server. It suits continuous streams and fan-out distribution better than request-response lookups, where you need an answer tied to a specific question within a bounded time.

Keep reading

  • How-to

    Log production data from a panel PC to SQL Server

    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.

    Edge computing and IIoT

  • Troubleshooting

    Reconnect an edge PC to SQL Server after connection loss

    Your reopen code fails because the loop still queries through the old connection reference and an incoming error can skip the open call entirely. Recover in a state machine with connect, query, cleanup and retry-wait states that replaces the stored reference: retry every 5 s to a server on the plant LAN, every 30 s across a VPN.

    Edge computing and IIoT

  • How-to

    Deploying Edge Data Store in Docker on a controller PC

    Run Edge Data Store as a Docker container on the controller-class edge PC and let its OPC UA adapter ingest the local server over localhost. Define egress targets, buffering and forwarding schedules in the JSON configuration, size the buffer from tag count, scan rate and outage target—50 tags at 1 event/s across an 8-hour outage is about 288 MB—and sign off only after a live pulled-cable test.

    Edge computing and IIoT