IMD SolutionComputing LibraryShop main store

Troubleshooting

Reconnect an edge PC to SQL Server after connection loss

Edge computing and IIoT Published 6 min read

Short answer

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.

On this page
  1. Why the reopen code does not reconnect the loop
  2. First, prove which layer failed
  3. Checklist: make the replacement reference reach the next query
  4. The recovery state machine
  5. Error handling that does not block recovery
  6. Where the retries run
  7. Clear the pool when the client keeps dead sessions
  8. Sizing the retry wait for a plant LAN logging PC
  9. Questions asked

Reconnection fails for two reasons, and reopening the reference fixes neither. The query loop still holds the old connection reference in its retained state, so the new session never reaches the next query — and the error that triggered recovery can sit on the open call's error in input and make the open skip execution entirely. Recover in a state machine, or a dedicated database worker, with connect, query, cleanup and retry-wait states: replace the stored reference on a successful open, clear the error chain between cleanup and connect, and retry every 5 s to a server on the plant LAN, 30 s across a VPN.

These loops are usually the logging side of an edge box. The write path itself is covered in logging PLC values from an OPC server into SQL Server and, for a panel PC station, log production data from a panel PC to SQL Server.

Why the reopen code does not reconnect the loop #

A connection reference names one session managed by the database access layer. Once that session is lost, keeping the reference restores nothing. A reopen creates a replacement session, and every later query must receive that replacement.

In LabVIEW, dataflow decides which value reaches a node. A new reference opened on one wire does not change the reference carried on another wire or held in the loop's retained state. The added code runs, the open succeeds, and the next query still reads the dead reference — or a default value supplied where the reference should be on the path that executes.

The second failure is quieter. Many LabVIEW functions skip their normal operation when an error is already present on the error in input. Check the documented behavior of the database open and close VIs you call: if the original query error is wired straight into the replacement open, the open never executes, and the overwrite you rely on never happens.

First, prove which layer failed #

An unchanged value on the screen identifies nothing by itself. Classify the failure before touching recovery logic:

  • Connection fault: the database operation reports a communication-class error. This is the case recovery is for.
  • Query fault: syntax, permission and data-conversion errors return on a working connection and do not call for a new session. Classify with the provider's diagnostic information.
  • Display fault: the query succeeds and the value never reaches the screen. Compare the query output with the value delivered to the display.
  • Stale display: the screen keeps showing the last good value while the loop has stopped acquiring. Show the last successful query time beside it so the number reads as old, not current.

Checklist: make the replacement reference reach the next query #

  1. Locate the reference input of the first query that fails after a recovery attempt.
  2. Trace that input back to the loop's retained connection state.
  3. Confirm the successful open's output replaces that retained state on the recovery path, in every case, not only the branch you tested.
  4. Check each other path for the old reference passed through unchanged, or a default value supplied where the reference should be.
  5. Block query execution while the retained state is invalid or being replaced.
  6. Give one execution context ownership of open, use and close. If several loops share the reference, one can close a session another is still using; pass database requests to a single owner instead of sharing the reference.

The recovery state machine #

Make the ordering explicit instead of embedding it in an error case:

State Operation Next decision
Connect Open the session and retain the returned reference Run queries only after a successful open; otherwise wait
Run queries Execute work through the retained reference Continue on success; classify errors before choosing recovery
Disconnect Release the old reference and invalidate the retained state Go to connect after a recoverable outage; exit at shutdown
Retry wait Delay the next attempt while accepting stop requests Return to connect when the delay expires

Startup enters connect, normal running enters the query state, and a connection failure enters disconnect before any new attempt. Shutdown also enters disconnect, and must never transition back into reconnect.

Error handling that does not block recovery #

  • Capture the first database error before cleanup starts. Closing an already broken connection raises errors of its own that bury the diagnostic that triggered recovery; keep the initial failure and its text independently of the error that controls the next operation.
  • Handle the error in input deliberately. Where the open VI skips execution on an incoming error, drive the recovery path with an independently cleared error and preserve the original diagnostic separately.
  • Record a close failure without letting it suppress the next connection attempt. Invalidate the retained reference even when releasing the broken session reports an error.
  • Verify the transitions with execution tracing: disconnect completes or reports its cleanup error, connect actually executes, and only a successful connect re-enables queries.

Where the retries run #

A state machine inside the existing while loop works when database calls and retry waits do not starve its other responsibilities. When the interface or the acquisition logic must keep responding while SQL Server is down, move the database work to a dedicated worker: a queued state machine or queued message handler that serializes requests and owns the connection.

Decide three more things at design time:

  • Whether queued requests wait, expire or fail while disconnected.
  • That shutdown outranks retries and queued database work.
  • The connection and command timeout settings in the database access configuration, because they bound how long the worker can sit inside one call before it can process another request.

A successful open is not a health guarantee: the connection can fail between a health check and the next query, so every database operation still needs its own error handling.

Clear the pool when the client keeps dead sessions #

Closing a connection through ADO.NET SqlClient returns it to a pool rather than destroying it, and the next open can be handed a session that died in the outage. SqlClient documents two methods for exactly this: ClearAllPools empties the connection pools for the provider, and ClearPool empties the pool for one connection. Call one after a connection-class failure, before the next open. The layer below your code can hold dead sessions too — a driver issue logged against a SQL Server client library describes the driver continuing to use the old connection after an internet outage — so after a network drop, treat an open as unproven until the first query returns.

Sizing the retry wait for a plant LAN logging PC #

Inputs:

  • Retry-wait bounds for this recovery pattern: 5 s and 30 s.
  • Time basis: 60 s per minute, 60 minutes per hour.
  • Outage window for the attempt count: one full hour, an illustrative input, not a measured outage.

Rule in words: attempts in an interval equal the interval divided by the retry wait, and the worst-case delay to resume after the server returns equals one retry wait.

Arithmetic:

  • 5 s wait: 60 s ÷ 5 s = 12 attempts per minute, and 12 × 60 = 720 attempts in the hour.
  • 30 s wait: 60 s ÷ 30 s = 2 attempts per minute, and 2 × 60 = 120 attempts in the hour.
  • Resume delay after recovery: one wait — 5 s against 30 s.

Decision: set the retry wait to 5 s when SQL Server sits on the plant LAN, a switch hop or two from the logging PC. Logging resumes within 5 s of the server or switch coming back, and 720 failed opens across a full hour of downtime is a negligible load for SQL Server. Set it to 30 s when the path crosses a VPN or WAN link: recovery there is measured in minutes, each failed open holds the worker until the connection timeout expires, and 120 attempts per hour leave the worker free for stop requests and status polls.

Questions asked

Why does opening a new connection not fix the loop?

Because the next query never receives it. A connection reference names one session, a reopen creates a second session, and in a dataflow language a new value on one wire does not change the reference the loop retained. Trace the failing query's reference input back to that retained state and make a successful open replace it on every path. The same signature appears when an error case closes the reference while a parallel path still queries it.

How often should the worker retry the connection?

Every 5 s when the server is on the plant LAN: 12 attempts a minute, 720 in a full hour of downtime, negligible for SQL Server, with logging resuming within 5 s of recovery. Every 30 s across a VPN or WAN link, where recovery takes minutes and 2 attempts a minute keep the worker responsive. Start at 5 s and lengthen only when the link, not the server, is what fails.

Do I need to clear the connection pool?

Yes, when the client uses ADO.NET SqlClient. The pool can hand the next open a session that died in the outage, so an open alone does not prove the connection works. ClearPool empties the pool for one connection and ClearAllPools empties the provider's pools; call one after a connection-class failure, before the next open. Clearing the pool does not replace a reference your loop still holds, so do both.

Does a failed query always mean the connection is lost?

No. Syntax, permission and data-conversion errors arrive on a working connection and need a fix in the query, not a reconnect. Classify the error with the provider's diagnostic information first, and recover the connection only for communication-class failures. A query that succeeds while the screen shows nothing is a display-binding fault, not a database one.

What should the operator screen show during an outage?

Separate connection state from the last good value, and show three conditions: connected, reconnecting and unavailable. Keep the last successful query time on screen beside the value, so a number the loop stopped refreshing reads as old data rather than a current measurement someone might act on.

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

  • Comparison

    Choosing edge storage for high-rate vibration data

    Keep the CSV-in-ZIP vibration archive that already works at 10 kHz, and treat Parquet as a candidate format, not a capacity fix. Move only after the Parquet writer proves exact sample fidelity, sustained throughput at the real acquisition rate, and recoverable completed archives after power loss. A format change is an engineering change with its own acceptance test, and compression in either format never repairs precision lost upstream.

    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