Data Forwarders

The Data Forwarders configuration page defines forwarders that replicate historian data, alarm records, and event records from the local EnvisionSCADA database to an external SQL database. The target database is a Database Connection (Configuration > Database Connections); the forwarder selects it by name, so the connection string is stored once and shared with every Data Grid object on the same database.

This is used for long-term data archiving, reporting, or integration with external systems that require access to historical process data.

Each data forwarder tracks the last forwarded record for data, alarms, and events separately. For alarms and events, the forwarder can also use a separate update query to apply later changes such as return and acknowledge information to rows that were already inserted in the target database.

Alarm and event update queries are triggered by the local historian update operations that modify existing HistoricAlarms and HistoricEvents rows. The data forwarder does not search the historian for changed rows on its own.

Data Forwarder Settings

Each Data Forwarders row represents one forwarding configuration.

Create a new data forwarder with the Add button in the toolbar. The Add dialog organizes all data forwarder settings in the sections General, Database, Data, Alarms, and Events, with default values already filled in. Enter at least the Name and press Create.

The data forwarder row shows Enable, Name, Connection, and Description directly and these values can be edited in place. Press the Settings icon on a data forwarder row to open the Data Forwarder Settings dialog, which contains the same sections and all settings. Press Save to apply the changes. Use the template loader icon next to the query fields to load a pre-filled example for the database type of the selected Connection and the selected Mode.

Press the Reset icon on a data forwarder row to open the Reset dialog. The dialog shows the current Last Forwarded Data ID, Last Forwarded Alarm ID, and Last Forwarded Event ID of the forwarder. Enter a lower value to resend records from that point, or a higher value to skip records, and press Save to apply. The forwarder continues from the new positions on the next forwarding cycle.

Notes

In Direct Insert mode every query field holds a complete SQL statement. In Stored Procedure mode a query field holds either a stored procedure name, such as sp_InsertData or dbo.sp_InsertData, which is called with the placeholders passed as named parameters, or a complete call statement, such as EXEC sp_InsertData @TimeStamp, @Variable, @Value, @Raw, @Status on SQL Server or CALL sp_InsertData(@TimeStamp, @Variable, @Value, @Raw, @Status) on MySQL and PostgreSQL, which is executed as written. Use the template loader button next to each field to load a pre-filled example for the database type of the selected Connection.

Each data forwarder automatically tracks the ID of the last successfully forwarded data record, alarm record, and event record, and continues from that point after a restart. When records need to be resent or skipped, use the Reset dialog on the data forwarder row to adjust Last Forwarded Data ID, Last Forwarded Alarm ID, and Last Forwarded Event ID.

Enable Verbose only during commissioning or diagnostics. It increases log volume significantly.

Alarm Query Update should use AlarmId together with OnTime as the target-side key. Event Query Update should use OnTime as the target-side key.

Alarm Query Update is typically used for OffTime, AckTime, AckUser, and AckComment changes. Event Query Update is typically used for AckTime, AckUser, and AckComment changes.

SQLite has no stored procedures. Use Direct Insert for SQLite targets.

On SQL Server, Identity is a reserved word. An Event Query that names the Identity column writes it as [Identity], as the template does.

On PostgreSQL, each placeholder value takes the type of the column or procedure argument it is written to, so @Value can be stored in a text or a numeric column and @TimeStamp in a timestamp with time zone column without a cast. A value that cannot be converted, for example the text of a string variable in a numeric column, stops that stream at the record until the column type or the query is changed; the error is shown in the System Log.

Historian times are UTC. In a timestamp with time zone column they keep their exact moment in time; in a timestamp column they are stored as UTC time. The forwarder sets the time zone of its PostgreSQL session to UTC unless the connection string sets Timezone.

PostgreSQL stores unquoted table and column names in lower case, which matches tables created with unquoted names. A stored procedure called by name alone receives the placeholders by argument name, so its argument names must equal the placeholder names including case, for example "TimeStamp". The CALL statement from the template loader passes the values by position and has no such requirement.

Forward Signatures requires Forward Audit Trail. An audit trail record carries the identifier of the signature that authorized the action, so a forwarded signature is only meaningful together with the audit record that references it. Saving a data forwarder with Forward Signatures enabled and Forward Audit Trail disabled is refused.

Signature records are always forwarded ahead of the audit trail records that reference them. When an audit trail record refers to a signature that has not been forwarded yet, that record and the ones behind it wait for the next cycle instead of being written with an identifier the target database cannot resolve. Audit trail records therefore always arrive in order.

Together with Forward Data, Forward Alarms, and Forward Events, the audit trail and signature streams place a complete copy of the historian records in a database that the site already backs up and retains, which is how record protection and retrieval are handled for 21 CFR Part 11 deployments.

Rotation in the Historian section on the Parameters page removes records on age alone and does not check the forwarding position. Size Rotation Days and Audit Retention Days longer than the longest period the target database can be unavailable, otherwise records can age out locally before a forwarder that is offline has sent them.

A Database Connection that is selected by a data forwarder cannot be deleted; point the forwarder at another connection first. Renaming a connection renames the reference on every data forwarder that uses it.

Projects from a version where each data forwarder held its own Database Type and Connection String are converted on upgrade and on import: the pair becomes a Database Connection named after the server and database or the SQLite file, and the forwarder is linked to it. Forwarders that pointed at the same database share one connection. The System Log lists the result under Migration or Import.

InfluxDB

A data forwarder writes to InfluxDB 2.x or InfluxDB 3 when its Connection is an InfluxDB Database Connection. Both versions take the same templates; for InfluxDB 3 the database must exist, see the Database Connections article. Each query field then holds an InfluxDB line protocol template instead of a SQL statement, with the same placeholders as the SQL templates. The template loader fills in an InfluxDB template for each field. Mode does not apply to InfluxDB.

A template has up to three parts separated by spaces: the measurement with its tags, the fields, and the timestamp. Each historian record produces one line, and the forwarder sends the lines of one batch in a single request of at most 5000 lines. The forwarding position moves only after InfluxDB has accepted the request, so a failed request is sent again in the next cycle.

historian,variable=@Variable value=@Value,status=@Status @TimeStamp

InfluxDB stores a point with the same measurement, tags and timestamp only once. An update template therefore writes the same tags and timestamp as the insert template, with the changed fields: the acknowledge and off time fields are added to the point the insert wrote. A record sent a second time after a failed request replaces itself and is not stored twice.

InfluxDB fixes the type of a field when it is first written. The data template writes a numeric @Value as a number, so a project that historizes text variables keeps its numbers and its texts in separate fields.

alarms,alarm_id=@AlarmId,asset=@Asset severity=@Severity,message="@Message",args="@Args",off_time="@OffTime",ack_time="@AckTime",ack_user="@AckUser",ack_comment="@AckComment" @OnTime

alarms,alarm_id=@AlarmId,asset=@Asset off_time="@OffTime",ack_time="@AckTime",ack_user="@AckUser",ack_comment="@AckComment" @OnTime

TimescaleDB

TimescaleDB is used through a PostgreSQL Database Connection and the PostgreSQL templates. To keep the forwarded history in a hypertable with compression and a retention period, create the target table and run the statements below once in the target database. Adjust the intervals to the requirements of the site.

CREATE TABLE HistoricData (TimeStamp timestamptz NOT NULL, Variable text NOT NULL, Value text, Raw text, Status integer);
SELECT create_hypertable('historicdata', by_range('timestamp'));
ALTER TABLE historicdata SET (timescaledb.compress, timescaledb.compress_segmentby = 'variable', timescaledb.compress_orderby = 'timestamp DESC');
SELECT add_compression_policy('historicdata', INTERVAL '7 days');
SELECT add_retention_policy('historicdata', INTERVAL '365 days');