Data archiving in D2000 system
A part of D2000 system is the efficient archiving system (D2000 Industrial SQL Archive), that provides the following functions:
- Archiving of object values in the system (primary archive).
- Calculation of archived values using statistical function (statistical archive).
- Calculation of archived values using defined expression (calculated archive).
- Script-filled archive (Value warehouse).
Each object of Historical value type has a configuration parameter specifying the archive history depth. Values older then the depth are automatically deleted. Checking the option Depository causes the value to be stored also in depository database. In some cases it may be important to know that the archive process was not running. For this purpose, the archive process writes special values with the START and STOP attributes - the option Write Start/Stop must be checked. The option Archive enables/disables writing into the archive database.
The archive subsystem takes care of processing the delayed data. It means, that all dependent values of statistical historical values are recalculated again (e.g. balance calculations) after the arrival of delayed or old data.
To ensure reliability and adequate performance, the archive is optimised for the use with Sybase Adaptive Server Anywhere, PostgreSQL and Oracle databases. Oracle database must be used on the OpenVMS platform (a Sybase port doesn't exist) and is tested for the use in large-scale systems (terrabyte archives). Using Sybase Adaptive Server Anywhere is recommended for archives under 10 GB when using SQL Anywhere 9.0 and under 50 GB when using SQL Anywhere 12.0.
Using PostgreSQL is recommended due to its price-performance ratio for all archives ranging from small to large (tested on 2 TB archive database and over 12 TB of depository databases).
Besides the databases mentioned above, D2000 system also supports the MS SQL server (see the topic Support of MS SQL 2000, MSDE 2000, SQL Server 2005 Express and MS SQL 2008 express edition).
For small application with low requirements for the archive subsystem performance, it is possible to use a freely available database MSDE (Microsoft Desktop Engine) - it is a version of the MS SQL server with limited performance. For more information on MS SQL and MSDE see the topic Support of MS SQL 2000, MSDE 2000, SQL Server 2005 Express and MS SQL 2008 express edition.
Note: SQLite and MySQL (and MySQL clones such as MariaDB and PerconaDB) are also supported experimentally. For more information, please contact Ipesoft.
Recommended settings for archiving using PostgreSQL database are described in document PostgreSQL settings for optimal Archive performance.
Using the standard SQL databases provides the possibility to analyse and process historical values by means of external tools, e.g. Microsoft Excel.
Several articles about archiving are available on our blog, currently in Slovak language only:
- Archiving in SCADA and MES systems
- Archiving in SCADA and MES systems, part 2
- Enterprise features of archiving in SCADA and MES systems
- Enterprise features of archiving in SCADA and MES systems, part 2
- Enterprise features of archiving in SCADA and MES systems, part 3
- Information from a workshop about archiving (SK)
- Archive and PostgreSql depository databases
- Migration of depository databases to PostgreSQL in practice (SK)
- Arcsynchro and PostgreSQL depository databases (SK)
- Continuous integration in an archive or how to do a sawtooth diagram