AssetWorX Cache

AssetWorX can optionally cache the last known real time values of Equipment Properties and store them in a Microsoft SQL database table. This document describes how to configure this feature and how it works in detail.

Cache Data Store

The AssetWorX cache needs to be stored in a Microsoft SQL database table. Edit the setting under “Product Configuration->Other settings” to specify which database that table should reside in. By default, this setting is empty and the AssetWorX cache disabled.

Initializing and Upgrading the Cache Data Store

When the Cache Data Store connection string is filled in, as well as every time the AssetWorX Point Manager starts up or reconnects to the database after a failure, it checks whether the cache table is present and creates a new one, if it is not. If it finds a cache table with an older structure (from an older GENESIS64 version), it automatically upgrades it.

Cache Data Store Table Structure

The Cache Data Store consists of a single table named ASC_DataCache and has the following structure:

Column Name /Type	Function
ConfigurationId uniqueidentifier	A GUID that identifies the AssetWorX Configuration database. Theoretically, different AssetWorX installations can store their cache into the same ASC_DataCache table, and they will use this column to distinguish rows that they own. The value matches the column CacheGuid from the ASC_RootConfiguration table, which is not editable from WorkBench.
ItemId int	The Equipment Property ID. This matches the ID column from the ASC_EquipmentProperties table.
PropertyId int	0 for the value of the Equipment Property. Equipment Properties with a Source type set to Dynamic tag cache not only the last value that came from the input, but also various metadata elements, like Data Type, Access Rights, Description and so on. Values other than 0 in this column are meant for those other metadata types. If the Source type is set to Static, Polled dynamic tag or Expression, only the value 0 is used here.
RowTimestamp timestamp	This column gets automatically updated by SQL server whenever the row is updated. This is used by the AssetWorX Point Manager when determining changes made to the table by other parties.
Value varbinary(MAX)	All portions of the data update (that is the actual value, status code and time stamp) encoded in a proprietary format. AssetWorX relies on this column when the value can’t be converted into a text value, for example if it is an array of values or a custom object.
StatusCode int	The status code of the data update.
Timestamp datetime2(7)	The timestamp of the data update.
ValueType int	The data type of the value.
TextValue nvarchar(MAX)	The value of the data update converted to text. The text conversion uses the .NET invariant culture, regardless of system and database culture settings.

The ValueType column can contain one of these values:

Value	Meaning
-1	Custom object, or the value can’t be converted to text. In this case, AssetWorX will read only the Value blob and will ignore the StatusCode, Timestamp and TextValue columns when reading the row. The TextValue will be empty when AssetWorX writes the row.
0	Empty value
1	Char
2	Int16
3	Int32
4	Int64
5	Byte
6	UInt16
7	UInt32
8	UInt64
9	Single precision float
10	Double precision float
11	Boolean (true/false)
12	String
13	TypeInfo. All columns including TextValue will be written by AsstWorX, but only the Value blob will be read back.
14	StatusCode
15	DateTime. The TextValue is ISO 8601 compliant. More information can be found here: https://docs.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings#Roundtrip
16	GUID
17	SByte
18	Decimal
19	reserved
20	TimeSpan
21	DateTimeOffset. The TextValue format is the same as of DateTime.

Synchronizing with the Cache Data Store

The AssetWorX Point Manager does not read or write the ASC_DataCache on demand, but instead keeps a copy in memory all the time and periodically synchronizes it with the database. This period can be specified under “Product configuration->Other settings”.

Writing to the Cache

When synchronization is due, AssetWorX first writes all change that it accumulated since the last successful write. When writing fails, AssetWorX will keep trying to reconnect and then write the changes on the next synchronization period.

Reading from the Cache

Immediately after writing changes, AssetWorX will read any changes done to the cache table by other actors. This feature uses SQL Change Tracking (https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-tracking-sql-server) which AssetWorX automatically sets up when initializing the cache table. If AssetWorX is for some reason unable to set up SQL Change Tracing, it will be reported as an error into TraceWorX and AssetWorX will continue operation while only ever reading the cache table once and then not tracking any changes done by other actors.

Synchronization and SQL Database Restore

When restoring the SQL database to a previous state, the in-memory copy will become out of sync and will still contain the values from before the restore. Writing to the cache will continue normally though.

To solve this, either restart the AssetWorX point manager, or call this method from a client: ac:\AcCacheReinitialize.

Use with Redundancy

It is possible to have two AssetWorX instances with the same configuration share a Cache Data Store. Since both instances write and read the cache table periodically, a change written by one will eventually be reflected by the other. This can be used in some redundant scenarios.

Redundancy with a SQL Always On Availability Group

From a client point of view, a SQL Always On availability group behaves as a single SQL instance that stays operational if one of its nodes fails. This makes it ideal for a redundancy use case. More about this SQL feature can be found here: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server

Redundancy with a Simple SQL Installation

Because both AssetWorX instances keep the content of the cache table in their memory, a failure of the shared Cache Data Store has only a limited impact. The AssetWorX instance will serve the last known values to their clients, and any updates to the cache will be remembered and applied to the Cache Data Store once it becomes available again.

Of course, a change to the cache made on one AssetWorX instance will not propagate to the other one as long as the Cache Data Store is unavailable.

Timing and Data Propagation

AssetWorX ensures that eventually the data in the Cache Data Store and the data seen by AssetWorX clients will be the same. This section describes the timing and some properties of the data exchange.

The Timestamps on Data Updates

AssetWorX does not handle the timestamps on data values in any special way. It will accept a data update with an older time stamp after it already received a newer one and will send them out in the same manner. There is also no special handling for the timestamps in the data cache.

The main advantage of this approach is, that it can handle data with wrong timestamps. There are also disadvantages, which will be apparent further when describing some timing properties of the cache.

Worst Case Time Required for Data to Propagate from One AssetWorX Point Manager to Another through a Shared Cache Data Store

In this scenario, there are two AssetWorX point managers connected to the same store, and a client writes the value “a” to a static Equipment Property on Point Manager 1 (PM1). The horizontal line represents time, and the vertical lines represent events, when the AssetWorX Point Managers start synchronizing with the database. The dotted arrows represent events when the Point Manager would read from or write to the cache, if there were any data changes. Only a few of the dotted arrows are drawn to not clutter the diagrams too much.

In this scenario, the client wrote to PM1 shortly after it started updating the database table, so the value gets written on the next synchronization event.

On the next synchronization event, PM2 manages to execute the synchronization before PM1, so it just misses the update. Since both Point Managers execute the synchronization at the same time, it is random which one will be first and which one second.

On the third synchronization event, the updated value is finally read by PM2. This is the worst-case scenario, where it takes slightly over two synchronization periods for an update to make its way from one Point Manager to another.

Worst Case when a Value Written Later can be Overwritten by an Earlier One

In this scenario, value “b” is written later than “a”, but due to timing, value “a” gets propagated everywhere. A value can be overwritten by another one that was written up to one synchronization period earlier.

Local Change has Precedence Over Change Coming from Database

This is a modification of the diagram above. The value “b” is written just before an updated value from the cache is read. The local change has precedence and won’t be overwritten.

Performance Counters

The AssetWorX Cache exposes these performance counters to the system:

Counter	Description
Cache reads/sec	Number of rows read from the database cache per second. This counter is updated only once every "synchronization period" when all the rows are read.
Cache writes/sec	Number of rows written to the database cache per second. This counter is updated only once every "synchronization period" when all the rows are written.
Cache read load %	The percentage of time spent by the cache thread by reading from the cache. This counter is computed at the start of every "synchronization period" from the values of the last period. E.g. if the "synchronization period" is 10 seconds and the cache spent 2 seconds reading, then at the next 10 second period start, the value of this counter changes to 20.
Cache write load %	The percentage of time spent by the cache thread by writing into the cache. This counter is computed at the start of every "synchronization period" from the values of the last period. E.g. if the "synchronization period" is 10 seconds and the cache spent 2 seconds writing, then at the next 10 second period start, the value of this counter changes to 20.
Cache total load %	The percentage of time spent by the cache thread reading to and writing from the database. This counter is simply the sum of the read and write load counters.