Interfacing with SQL Servers with zenon – Historian SQL Server Interface
The historian SQL Server interface can be thought of as an add-on to the zenon Supervisor, Historian Module. The historian SQL Server interface allows zenon Archive Data to be transparently evacuated to a centralized SQL server. This offers the benefit of data being stored in a central place in an open format where data can be queried with a simple SQL statement. Additionally, this data can still be read back into zenon for usage in the Extended Trend Module, Reports or the Archive Revision Screen seamlessly.
Additionally, this module allows Alarms and Events to be exported to a centralized SQL Server.
Use Case 1
Some companies prefer the integration of process data to other organizations in the corporate structure to be over an open, flexible, and well understood medium such as an SQL Server. Once process data is available in an SQL Server, it can easily be queried, reported, and analyzed by many people or 3rd party software components.
Use Case 2
Often, another common reason to send process data to a centralized SQL Server is for long term storage. Directly on the HMI server, there may not be an abundance of freely-available disk space or there may not be any standard backup strategies in place. However, if process data is incrementally evacuated to a high availability SQL Server with redundant hard disk configurations (RAID), long-term data storage may become more practical.
How it Works
The process of moving locally stored historian data to an SQL server is called “evacuation” in zenon, and is performed automatically once configured. This evacuation cycle can range from an immediate evacuation to evacuation e.g. at the end of a production cycle or after every month.
Note 1: after each change in the archive configuration, the tables need to be updated. This can be done conveniently from the zenon editor.
Note 2: when configuring the SQL OLEDB provider string through the dialog and using the SQL native client 10 or 11, with SQL server authentication, the password for the SQL user that was entered in the dialog, is not taken over in the provider string. The provider string must be enhanced manually with the entry “password=”
To evacuate Historical (archived) data to an SQL Server, you must configure a time period, after which, older records will be automatically evacuated to an SQL Database. During the configuration in zenon, you must create the database connection, credentials to be used, and point to the SQL Server and database which should be used. From zenon, the necessary tables and formats are created automatically.
Once properly configured, the historian in the runtime will automatically evacuate the archived data, for which the configured time period has expired, to the SQL Server. This evacuated data can be transparently read back into the zenon Runtime via pre-defined screen types for the Extended Trend, Report Generator, Report Viewer, and the Archive Revision screen. Additionally, if for some reason the SQL Server cannot be reached by the zenon server, the historical data will be cached locally on the runtime server. If the SQL server again becomes available, the data which should have been evacuated will be automatically flushed.
If you have configured redundant zenon servers, then while both primary & standby servers are online, the primary server will evacuate the historical data to the SQL Server. If the primary server fails, the standby server will also automatically take over the responsibility of evacuating the historical data to the SQL server. Only after zenon confirms a successful SQL evacuation will the original archive files on the server be deleted. In case the SQL server cannot be reached (e.g. when the network or the database is down) the data is stored locally and evacuated on the next try.
The zenon Alarm Logs and Event Logs can also be exported to an SQL Server. The Alarm & Event exports are one-directional, meaning that zenon will export the records to separate SQL Tables, but will not read the alarms or events from the SQL Server.
This module requires the full zenon Historian Module and a zenon Supervisor Runtime License. For redundant systems, it is recommended to license the historian SQL Server interface on both the Primary & Standby servers. For the configuration of this module, it is also required to be licensed in the zenon Editor.
When this module is purchased from COPA-DATA, 1 CAL (client access license) is provided for the Microsoft SQL Server. However, COPA-DATA does not provide the actual license or installation media for the Microsoft SQL Server. This must be obtained from the system integrator or end-user.