IMD SolutionComputing LibraryShop main store

Explainer

What Ignition historian failover leaves behind in the second database

Edge computing and IIoT Published 7 min read

Short answer

When a database connection fails over, Ignition keeps collecting: it builds the historian tables in the secondary schema and commits samples there, and nothing merges back when the connection returns. Recovery is a manual reconciliation bounded by the failover window and guarded by a tag-mapping check. The supported design is database redundancy behind one logical endpoint, with History Mode matched to database availability.

On this page
  1. What the failover actually switches
  2. Why nothing merges back
  3. Settle gateway authority before touching data
  4. Reconciling the rows that stayed behind
  5. Store and forward stays enabled
  6. Worked example: bounding the recovery window for the Site A and Site B layout
  7. The design decision
  8. Questions asked

When the Historian1 connection fails over to Historian2, collection does not stop: the Tag Historian provider builds its tables in the secondary schema and commits samples there. When the connection returns to the primary, those rows live in the second database and nowhere else. Nothing merges back, and gateway redundancy will not fetch them, because its history recovery covers data cached by the backup gateway, not rows committed to an external schema. Getting the window back is a manual reconciliation. The supported design makes the problem impossible: one logical database endpoint, redundancy in the database platform, History Mode matched to availability.

What the failover actually switches #

Failover in this architecture lives on the database connection, not on the historian. Documented on the Ignition 8.1 redundancy pages for database considerations: any database connection can carry a failover datasource, and while the main connection is unavailable, queries executed on it pass through to that failover. Tag Historian requires an external database connection, and every historized tag in this layout is assigned to the Historian1 provider, whose connection points at the primary MySQL server. When that connection fails over to Historian2, the same logical provider keeps executing its inserts, now against the secondary server.

Note that the names do double duty here: Historian1 and Historian2 label both the database connections and the historian providers. The tags hold on to the provider identity; failover acts on the connection underneath it. That is why collection continues seamlessly while the physical destination changes.

An empty secondary schema is not inert. The provider creates the historian tables it needs on first write, the same mechanism explained in why Ignition tag historian tables are not created. Those tables form a valid, standalone historian schema. Their presence creates no replication relationship with the original schema; they are simply a second place where rows now exist.

Why nothing merges back #

Two different mechanisms get conflated here. Database-connection failover redirects queries to another server. Gateway redundancy history recovery, the setting at Config => System => Redundancy => Backup Node Settings => History Mode, governs what happens to history held by the backup gateway when the master resumes control. Documented for Ignition 7.9: in Full mode the backup node logs directly to the database; in Partial mode all historical data is cached until a connection is available again.

History Mode What it does Set it when
Full The backup node logs data directly to the database The database is external to both gateways, or is itself redundant behind a single URL
Partial All historical data is cached until a connection is available again The database is not expected to be reachable while the master is down

Neither mode is a database-to-database merge engine. Selecting Full does not migrate rows that a failed-over connection already committed to the second schema.

One more behavior to plan around: a failed-over connection has been reported to stay on the secondary until it is forced back. Do not assume the connection returned just because the primary server answers again. Read the connection's current target in the gateway and write one controlled sample before you touch anything else.

Settle gateway authority before touching data #

This layout puts the master gateway and the primary database at Site A, the backup gateway and the secondary database at Site B. Ignition redundancy depends on reliable master and backup failure detection, and a site-to-site partition is the case that breaks it: each gateway keeps its local resources while losing the peer, and each side then makes its own availability decision. Treat any interval where both databases hold samples for the same tags as a split-brain range.

Read the redundancy state on both gateways: one master, one backup, with the peer-detection path healthy. Then confirm that exactly one gateway is producing fresh samples before you start reconciling. A split-brain range cannot be repaired by copying rows, because the conflict begins at execution ownership, before any insert reaches SQL.

Reconciling the rows that stayed behind #

The rows in the secondary's sqlt_data table are real, committed history, and they are yours to move. Work in this order:

  1. Stop automatic switching for the duration: no new rows should alternate between databases while you calculate the range.
  2. Back up both databases before modifying any historian table. This is the one step that saves you if the merge goes wrong.
  3. Bound the window: query the first and last timestamps present in the secondary's sqlt_data, and exclude anything still pending in store and forward, because pending records are not database-resident yet.
  4. Compare the historian's supporting mappings across both schemas. Every tag identifier referenced by the selected rows must resolve to the same logical tag in the destination. Matching column layouts prove nothing: an independently created schema can assign identifiers differently, and this check is what makes the copy safe.
  5. Check the destination for overlapping tag-and-timestamp records inside the window and decide duplicate handling before inserting.
  6. Move the rows with their original timestamps, the same discipline as writing timestamped history back into a SCADA server, then verify through the Historian1 provider that a known tag returns values across the recovered range.

On the direct question: with no tags retired during the window, the copy is safe once step 4 passes for every identifier in the set. That condition is checkable, so check it rather than assuming it.

Store and forward stays enabled #

Do not disable store and forward because a failover database is configured. The two functions answer different failures: connection failover picks another reachable database; store and forward retains records when the active destination cannot accept them at all.

Read the store-and-forward status before changing the connection. A queue that is growing means connectivity or database acceptance is still broken, so fix that first: timestamps are still arriving from the backlog. An empty queue with current rows in the secondary means those rows are already committed, and store and forward will not fetch them back. After you force the connection to its final destination, watch the queue drain and confirm the newest sample appears only there.

The buffering model is the same one at work in MQTT store and forward backfill after a connection outage: hold records at the edge, then refill the historian in order once the path is live. If your data path also publishes from a field device, configuring an edge device's MQTT store-and-forward for data backfill follows the same rules.

Worked example: bounding the recovery window for the Site A and Site B layout #

Inputs, with the source of each:

  • Architecture, from the case at hand: master gateway and primary MySQL server at Site A; backup gateway and secondary MySQL server at Site B; Historian1 is the provider on every historized tag; Historian2 is the failover datasource on the Historian1 connection.
  • Window bounds, read from the secondary's sqlt_data: first timestamp 06:42, last timestamp 09:15 on the day of the event. These are example values; substitute your own from the query.
  • Store-and-forward queue at the time of the cutover: empty, read from the gateway's store-and-forward status.

Rule in words: the recovery window is the time between the first and last timestamps that exist only in the secondary database, and it is valid only once nothing is still pending in store and forward.

Arithmetic: 09:15 is 555 minutes past midnight; 06:42 is 402 minutes past midnight. 555 minus 402 equals 153 minutes, which is 2 hours 33 minutes. Pending records: zero, so nothing is subtracted from the range.

Result: 2 h 33 min of tag history for the Historian1 tag set exists only in the Historian2 schema.

Decision: a zero window closes the incident with nothing to move. Any nonzero window, this one included at 2 h 33 min, goes to the mapping check in the reconciliation steps before a single row is copied.

The design decision #

Commit to one of two supported shapes and stop using Historian2-as-failover as the redundancy mechanism:

  • External database redundancy. Let the database platform's native replication, clustering or mirroring present one logical endpoint, a floating address works, and point the Historian1 connection at that single URL. Ignition neither knows nor cares which instance serves it. Set History Mode to Full.
  • Backup caching. When the backup cannot reach the database while the master is down, set History Mode to Partial and let the redundancy system recover the backup's cache when the master resumes. That is the supported merge: gateway cache into the database, never database into database.

For the two-site layout described here, build the first shape. Both gateways write through one logical endpoint, no second historian schema can accumulate, and the reconciliation problem this article opened with cannot occur in the first place.

Questions asked

Does Ignition ever merge the failover database back into the primary automatically?

No. Connection failover only redirects queries, so history committed to the failover schema stays there until you move it. The redundancy system's history recovery covers data cached by the backup gateway, not rows in an external database. Read the connection's current target before you start: a failed-over connection has been reported to stay on the secondary until it is forced back, so the primary being reachable again proves nothing.

Is copying the failover sqlt_data rows back safe if no tags were retired?

Only after a mapping check. Every tag identifier in the rows you copy must resolve to the same tag path in the destination schema: an independently created schema can assign identifiers differently, and matching column layouts prove nothing about identity. With no tags retired inside the window the comparison is bounded. Resolve each identifier in both schemas, confirm the destination has no overlapping tag-and-timestamp rows, back up both databases, then move the rows.

Should store and forward be disabled when a failover database is configured?

No. The two mechanisms answer different failures: connection failover picks another reachable database, while store and forward retains records when no destination accepts them. Disabling it trades a buffered gap for lost data. Watch one interaction: a reachable failover target lets queued records drain into the secondary, which turns pending records into database-resident history that only manual reconciliation will move.

What do Full and Partial History Mode actually recover?

Backup-gateway history, not database contents. Both modes are described in the body of this article: the backup either logs directly or caches until a connection returns, and the redundancy system recovers only that cached material when the master resumes. Neither mode migrates rows between external schemas. Choose Full when the database is external to both gateways or redundant behind one URL; choose Partial when the backup cannot reach it while the master is down.

Does the built-in historian in Ignition 8.3 change any of this?

It changes where new history can live, not the failover semantics. Ignition 8.3 introduces a redesigned gateway with a built-in core historian based on QuestDB, and a Remote Historian that reads and writes to another gateway over the gateway network. For two MySQL schemas already populated under connection failover, recovery is still the manual reconciliation above; those features are design choices for new deployments, not a retrofit merge tool.

Keep reading

  • Troubleshooting

    Edge trend shows no historian data: fix the doubled SELECT

    The trend stays blank because a complete SELECT statement was pasted into the Table field, so the gateway wraps it in its own SELECT ... FROM and SQL Server rejects the result as incorrect syntax near the keyword select. Put only Runtime.dbo.History in Table, carry DateTime, TagName and Value in the column controls, and move TagName = 'SysTimeSec' into the filter control.

    Edge computing and IIoT

  • Troubleshooting

    Fix historian silent data gaps with a registry value

    The data is stored; the retrieval view of it is stale. On the Historian server, create the DWORD32 value UseFileCacheForRetrieval set to 0 under HKLM\SOFTWARE\ArchestrA\Historian\Programs\aahStorage, then refresh the trend, with no reboot required. Confirm the fix by loading a previously blank window before running any manual query.

    Edge computing and IIoT

  • Explainer

    OLAP versus plain historian storage in SCADA databases

    A SCADA historian that stores timestamped values and serves trend queries is not doing OLAP, however many rows it holds. Only a deployment where a separate engine or scheduled job builds analytical structures, such as cubes or data marts, carries the OLAP licensing question. Classify by processing path, then decide whether the review is needed.

    Edge computing and IIoT