Terminologies of History Infrastructure

Only stores data for the set maximum retention span or until the scheduled delete.

Long Term Storage stores data in monthly or yearly data base units (slices). (See Long Term Storage in Additional HDB Toolbar Control Procedures)

Once the configured number of slices has been reached, they are moved to the archive. An additional slice is created, if the slice’s storage capacity is exceeded during the defined period.

You can set the number of slices for each storage. At the end of the set period, a new slice is created and the oldest one is saved as an archive file. In a database query, all active slices are searched.

The Default Archive Groups assigns system and project data to a default storage group. There is no additional engineering required.

Using Custom Archive Groups you can engineer system and project data to be assigned to storage files with a custom retention span or to a custom Long Term Storage. (See Archiving with Custom Storage Periods in Configure History Infrastructure).

Using the Filter Groups you can engineer system and project data to be filtered based on COVs before being logged into the HDB.

Once the maximum number of active slices has been reached, the oldest slice is save das an archive file and can no longer be accessed directly.

The archive file can be re-attached to the History Database at a later point in time using mounting. (See Mount an Archived Long Term Storage Slice in Additional HDB Toolbar Control Procedures)

The Emergency Delete function is a default component that irretrievably deletes data from Short Term Storage.

Emergency Delete is performed automatically when 90 percent of the database capacity has been reached.

The maximum retention span defines the interval for the deletion of data in the Short Term Storage.

The minimum retention span sets the data that are retained in the Short Term Storage. These mechanisms allow you to meet legal requirements for data storage.

The infrastructure may extend to multiple SQL servers in a distributed system. Data from the different systems can be compiled to provide a joint report.

Data from multiple systems

Red line: The data query from system 1 takes place in the short term storage for system 1 if data is required from multiple systems. The data required from system 2 is provided by a query from SQL server 1 to SQL server 2.

Data from another system

Green line: Data is exchanged directly at the system level to SQL server 2 if a data query from system 1 occurs for system 2 only.

The History Database guarantees the storage of user activities in the system, alarms and their treatment, faults that occurred and are handled as batch messaging, type of values logged in trend.

• User and System Activities (Activity Log): User events are actions triggered manually by any user. A user event is logged when a user:
• Starts a program
• Exits a program
• Saves data
• Establishes a connection
• Disconnects
• Logs off
• Logs on
• Changes a property
• Takes manual control of the system (user action)
• Checks the system (user action)
• Changes an object (or one of its properties)
• Changes the state of a particular object

An event is also logged when:

• The hard drive is full
• A password is wrong
• Alarms (Event Log): All alarm state changes controlled by the Desigo CC alarm system are logged as alarm events. An alarm is logged when:
• A digital alarm is triggered
• An alarm is acknowledged
• An alarm is reset
• An alarm is disabled
• An alarm is triggered when a high limit value is exceeded
• An alarm is triggered when a low limit value is exceeded
• An alarm without feedback is triggered
• Trend Logging (Value Log): All Trend data is logged when:
• The value changes
• Data is uploaded from a Trend Log object

Two types of recovery are possible to back up the History Database:

• Simple History Database backup
• Full History Database backup

Long term storage databases are always in recovery model Full.

The following table shows a summary of the advantages and disadvantages of both recovery models. The backup mode you choose depends on how important historical data is for traceability purposes.

The simple system topology shown below (for example, for cost considerations, simple IT installation, small project) can easily be implemented. However, it is not optimal for historical data backup that requires a high level of data security because server failure means that all data up to the last data backup (backed up on DVD or external storage media) is lost.

NOTE:
The saved *.BAK files have the same data content for simple and full history backup.

Concept of Simple History Data Backup

• If the SQL Server fails , only historical data up to the last back up can be restored. A failure or unavailability of the SQL Server is recognized if the GMS writer loses the connection to the SQL Server.
• The incoming data (yellow) is temporarily saved to a local storage medium if the SQL Server is down. The temporary data (yellow) is transferred to the SQL database as soon as the SQL Server is available again (see HDB Exclusive Locked).
• Historical data (red), saved to the History Database between the last backup and SQL Server failure cannot be restored.
• During backup , all historical data is saved except the historical data between the backup and SQL Server failure .

NOTE 1:
Back up the History Database at regular intervals (recommended daily) and copy the backup data to an external medium.
NOTE 2:
Create a hard disk monitor in Desigo CC for each disk drive used.

Individual databases can be distributed across several servers or storage media to allow for restoration of all data following an SQL Server failure. This is the optimum system topology.

NOTE:
The saved *.BAK files have the same data content for simple and full history backup.

Concept of Full History Data Backup

• Individual databases must be distributed across several storage media to allow for restoration of all data following SQL Server failure.
• If the SQL Server fails , all historical data can be restored under certain circumstances. A failure or unavailability of the SQL Server is recognized if the GMS writer loses the connection to the SQL Server.
• With full History Database backup, the data backup files , or are created automatically based on the stored data volume. You can also manually back up or schedule a backup.
• After creating a backup file , all new data is saved to an additional transaction log file until the next backup procedure. The additional storage in the *.LDF file takes place until a new backup file *.BAK is created.
• Restore transaction log data. Convert the LDF file to a TRN file. See the Microsoft SQL Server documentation for the workflow to convert an LDF file to a TRN file.
• The backup file *.BAK must first be restored after a Server failure.
• After successful restoration of the *.BAK file, the transaction log backup file *.TRN needs to be restored.
• While the SQL Server is down, incoming data (yellow) is temporarily saved to local storage media (see HDB Exclusive Locked). Data as of this time can be restored through this backup mechanism.
• As soon as the SQL Server is available again, all data is backed up during the next backup .

NOTE 1:
In the event of an SQL Server failure, no additional activity log, alarm and online trend data can be saved. The data is saved temporarily to a local storage medium. Make sure the local storage medium has sufficient free space to allow for temporary storage.
NOTE 2:
If the last unfinished transaction prior to SQL Server failure was not saved, it is lost.
NOTE 3:
We recommend creating (similar to a simple backup) a backup *.BAK file of the History Database at regular intervals (recommended daily) and copying the backup data to an external medium.
NOTE 4:
To avoid running out of hard disk space, create a hard disk monitor in Desigo CC for each disk drive used.

NOTICE

Avoid Data Loss

An experienced SQL Server administrator is required to create a TRN file (backup of transaction log) from an LDF file (transaction log). Data may be lost if the TRN file is not created correctly.

Some Desigo CC functions require exclusive access. The database is locked exclusively in this case. No further data is saved to the History Database during this period. The database is exclusively locked for the following functions:

• Purge
• Increase database
• Upgrade
• Delete for more storage
• Emergency delete

In these cases, incoming history data is automatically saved to temporary storage on the Desigo CC server. Data stored to temporary storage is automatically added to the History Database as soon as it becomes available. At the same time, new data is saved temporarily until all data has been added to the History Database.

NOTE:
This mechanism ensures that no history data is lost during normal operation.

HD2 temporary files are created in the following situations:

• When no HDB is linked to the project and the project is started.
• When the SQL server is stopped.
• When the connection from the project to the SQL server is lost.
• When the HDB (STS) is full.
• When the HDB (LTS) is not available.

NOTE: If there are no HD2 files present, it indicates that all temporary data has been saved to the database.

Configure mdf and ldf file on separate disks for HDB database and for the LTS databases.

By this way write operations to the disk happen in parallel for each database transaction thereby improving performance.

Also each of the following can be configured on a separate disk (preferably SSD instead of HDD):

1. Operating system and Desigo CC
2. Desigo CC project
3. HDB data (mdf) file
4. HDB transaction log (ldf) file
5. LTS data (mdf) files
6. LTS transaction log (ldf) files
7. HDB and LTS backups, recovery logs and archives

Also refer Microsoft recommendations for best SQL Server performance.