Understanding and configuring the data source auditing mechanism
In the beqom application the term "data source" can refer to different types of objects. For instance, it can be a table/view populated with data used as a reference in the application, or it can be a table that is populated via the interactions of the users with the system. A good example of this is the forms attached to processes, that are used by managers to enter additional information about a payee.
A number of mechanisms are in place in the beqom application to ensure traceability of the data throughout the application. One such mechanism is the use of temporal tables, described in herein.
This is an note/information message.
For more information about the other auditing mechanism in the application (audit of kernel tables), refer to Understanding & configuring the beqom auditing system.
Introduction
In the beqom application, the data source auditing mechanism uses the features offered by SQL Server temporal tables. Temporal tables enable database users to obtain information about the data contained in the table at any period of time, within the retention period defined for the table. In the beqom application, historical data are retained in history tables created in parallel to the audited tables.
Using this mechanism, with each update or deletion performed on the table, a record containing the previous data is created in the history table; in other words, the database contains the current values in the table and the previous values in the history table at any time.
General Principle
Temporal tables, also called system-versioned temporal tables, are a SQL feature introduced in SQL Server in 2016 (for complete information about temporal table, please visit Microsoft's Knowledge Base, here.
For a database table to be used as a temporal table, it must contain two columns of datetime2 type, known as period columns. These columns are used by the system to determine the period of time during which a given record is valid.
In Microsoft SQL Server Management Studio, temporal tables are represented by a specific icon in the database tree (a table icon with a clock). When expanding the node for a temporal table, the historical table is then displayed.
Retention Policy
In order to be able to use temporal tables, the parameter TEMPORAL_HISTORY_RETENTION parameter needs to be configured at database level. It is enabled by default on the database, but it is possible to change the setting using the ALTER DATABASE statement. Therefore, database administrators must check that the parameter is enabled using the following statement:
SELECT is_temporal_history_retention_enabled, name
FROM sys.databases
If it's not enabled, it must then be activated using the following statement:
ALTERDATABASE<myDB>
SET TEMPORAL_HISTORY_RETENTION ON
For more information about the retention policy, please visit Microsoft's Knowledge Base, here.
Retention Period
The retention period corresponds to the time after which the data contained in the history table will be removed. The removal is performed automatically by the SQL Server engine.
History Table
The history table is created next to the data source table and is "attached" to this table. It contains exactly the same columns as the original table.
By convention, it has the same name as the original table, with the suffix _xhisto. For instance, for a table called datasource_8a872803_d394_4a28_a499_73e777acf20b, the history table would be called datasource_8a872803_d394_4a28_a499_73e777acf20b_xhisto.
Period Columns
When the data source table is created as a temporal table, two additional columns are automatically created: sysStartDate and sysEndDate. These columns are not accessible using any API method, therefore, they cannot be renamed.
Period columns are managed by SQL Server. Consequently, these columns should never be changed manually. By default, these columns are set as HIDDEN, meaning that they will not be returned by statements containing an asterisk, such as SELECT * FROM datasource_8a872803_d394_4a28_a499_73e777acf20b.
Changing the Retention Period of a Temporal Table
It is possible to change the retention period of a temporal table after its creation, using a simple ALTER statement, such as the following:
ALTERTABLE datasource_8a872803_d394_4a28_a499_73e777acf20b
SET (SYSTEM_VERSIONING =ON (HISTORY_RETENTION_PERIOD =9 MONTHS));Disabling the Auditing on a Temporal Table
It is possible to disable the auditing on a temporal table using an ALTER statement such as the following:
ALTERTABLE datasource_8a872803_d394_4a28_a499_73e777acf20b
SET (SYSTEM_VERSIONING =OFF)
Once the auditing is disabled on a temporal table, it is also possible to remove the history table using a DROP TABLE statement, such as the following:
DROPTABLE datasource_8a872803_d394_4a28_a499_73e777acf20b_xhisto Extended Configuration
The data sources for which the auditing is enabled present additional entries in extended_configuration, in ds.bqm_datasource:
history_table_name: name of the history table (table whose name contains the _xhisto suffix)
row_start_column_name: name of the column defined as ROW START
row_end_column_name: name of the column defined as ROW END
Querying a Temporal Table
Traditional queries can be used to retrieve data from the data sources in the beqom database, such as:
SELECT*FROM datasource_8a872803_d394_4a28_a499_73e777acf20b
However, if you use this type of statement, the period columns will also be returned, if they are not in HIDDEN state. To avoid returning them in the result, you need to specify the columns that you want to query, as illustrated in the following example:
SELECT [id_3fe6db4a_598e_496c_9eee_4e3ea7a8c637]
,[column_854cd1ae_6a87_4983_b71e_849d6e5fecc4]
,[column_062bce20_1b1c_4489_a1f3_fbb091de4531]
,[uidUserModify]
,[idStep]
FROM [dbo].[datasource_8a872803_d394_4a28_a499_73e777acf20b]
Similarly, it is possible to use traditional SQL queries to retrieve data from history tables, as illustrated in the following example:
SELECT*FROM datasource_8a872803_d394_4a28_a499_73e777acf20b_xhistoTime-Based Analysis
The FOR SYSTEM_TIME clause can be used to perform an analysis based on the current data and the data in the history table. The following operators can be used with FOR SYSTEM TIME:
AS OF <date_time>
FROM <start_date_time> TO <end_date_time>
BETWEEN <start_date_time> AND <end_date_time>
CONTAINED IN (<start_date_time> , <end_date_time>)
ALL
For instance, the AS OF operator offers a snapshot of the state of the audited table at the specified point in the past. For instance:
SELECT*FROM datasource_8a872803_d394_4a28_a499_73e777acf20b
FOR SYSTEM_TIME ASOF'2020-11-06 12:03:08' ;
For more information about tome-based analysis of audited data, with examples and complete descriptions, refer to the temporal table documentation from Microsoft's Knowledge Base, here.