How to track data changes using database auditing techniques

Tracking data changes helps us to understand moving parts in a database, learn about changing trends, easily recover from unwanted data changes or data loss, and adds more context to the auditing documentation. In this article, we will introduce ways of tracking database changes by exploring some out of many database auditing mechanisms in the SQL Server ecosystem.

Obtaining such value-added information from the live production databases without substantial data archiving and impacting the database performance is a challenging aspiration, and later in this course, we will touch base on some of the viable options.

Temporal tables

Temporal tables as a database feature are introduced in MS SQL Server 2016 and are generally useful in scenarios when a historical database change log is required and is a great performance-intensive tool while creating the logs.

The temporal tables are system-versioned tables having SQL Server control version history and store historical database changes in a dedicated table. In other words, whenever a DML transaction affects the table, the data is saved with time-stamped versioning. For instance, if UPDATE or DELETE statements make an effect on a table, data in use before the change is stored.

Enable system-versioning on a table

Before we start building on database auditing example using temporal tables, let us understand some of the implications while creating it:

• A new table dedicated to storing historical data changes is created
• The history table has to be schema-aligned with the candidate table in terms of column names, number of columns, ordering, and types
• The candidate table must have a primary key defined, and have only one PERIOD FOR SYS_TIME structured via two datetime2 columns
• The candidate table gets some extra GUI decoration

When enabling system-versioned tables, the candidate table is required to support version time stamping via “period” columns that will indicate on the data effective times. In other words, we will employ start row and end row columns to tell SQL Server when the row started and when it stopped being active. Let us start building the database auditing implementation on the example from scratch. To start, create the new candidate table or choose any existing one, below example demonstrates how to alter newly created table to enable system-versioning:

CREATE TABLE Employee
(
    [ID] INT NOT NULL PRIMARY KEY CLUSTERED
    ,[FirstName] NVARCHAR(100) NOT NULL
    ,[LastName] NVARCHAR(100) NOT NULL
    ,[Department] VARCHAR(100) NOT NULL
    ,[Role] VARCHAR(100) NOT NULL
    ,[Team] NVARCHAR(100) NOT NULL
    ,[Address] NVARCHAR(1024) NOT NULL
    ,[Salary] DECIMAL(10, 2) NOT NULL
    ,[Bonustarget] DECIMAL(10, 2) NOT NULL;

Now that we have a candidate table, let us follow the requirements and adjust it to support this feature by adding two columns that will indicate the period of data validity:

ALTER TABLE Employee
ADD
  ValidFrom datetime2(2) GENERATED ALWAYS AS ROW START HIDDEN constraint
  DF_ValidFrom DEFAULT GETUTCDATE()
  ,ValidTo datetime2(2) GENERATED ALWAYS AS ROW END HIDDEN constraint DF_ValidTo
  DEFAULT '9999.12.31 23:59:59.99'
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

With these two columns being added, we can move forward and enable system-versioning. Below T-SQL code will enable this feature and instantiate a new history table named “Employee_History”:

ALTER TABLE Employee
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

Historical data trail will be stored inside the history table whenever a DML operation drive changes. In the next chapter, we will explain how to query and get in touch with the database auditing trail from this read-only table.

Querying data from system-versioned tables

Temporal tables allow time-based data analysis via specialized sub-clauses to retrieve data from the candidate and history tables. This additional query power does not affect the conventual querying for the current data, and for that reason, in the example above we have used the HIDDEN attribute on the period columns to avoid displaying it in a result set unless they are specifically called out in a SELECT statement.

Retrieving the database auditing data from the system-versioned table is driven via sub-clauses defining the aspired time frame, here are five options that can be used:

• ALL – Shows the complete history of changes and the current data
• AS OF <date_time> – Displays what data looked like at a specific time
• FROM <start_date_time> TO <end_date_time> – Gives an overview of the data in specific period including start and end dates
• BETWEEN <start_date_time> AND <end_date_time> – Provides the data overview in specific time range between start and end dates
• CONTAINED IN (<start_date_time> , <end_date_time>) – Return rows that existed in a given time range

The example below provides a query that retrieves historical data changes made in this Employee table against specific rows with IDs 7 and 8 for the given period from 17^th of November 2020 to 21^st of November 2020:

SELECT *
FROM dbo.Employee
FOR SYSTEM_TIME FROM '2020-11-17' TO '2020-11-21'
WHERE ID IN (7,8)
ORDER BY ValidFrom

The result-set have shown the data changes for each row including the ValidFrom and ValidTo columns, and it may be used as a tool to track down what changes did affect the row, and in this example, we updated Role, Team, and Bonus Target values:

Temporal tables play a significant role in the SQL DBA toolset, as it provides in-time data analytics, reconstructing data state and database auditing trail for data changes, calculating trends and recognize anomaly behavior, recover from the unwanted data changes.

At the glance, it seems this technology is an all-in-one solution for regular DBAs, however, system-versioned tables can’t provide much of the metadata in the auditing trail. Row versions store and provide the actual data state and time being effective, leaving no trail on who did the change, what application was used, from what particular host, and what is the exact SQL operation that made changes. To overcome this challenge, it is required to employ additional auditing power to streamline this information, and in this article, we will introduce how to get data changes using 3^rd party database auditing tool.

Track data changes using a third-party database auditing tool

ApexSQL Audit is a compliance and auditing tool for SQL Server that track database activities and collect auditing information for almost 200 SQL operations, including DML activities. Each auditing event includes information on time, login, application, SQL query text, client host, and schema to describe an activity in generated reports.

Database changes per DML transactions can be reviewed by leveraging on a specific feature referred to as Before-After, and it is a SQL Trigger-based mechanism that collects before and after data changes values with the ability to distinctively tell what row in a table took effect by the change.

Before-after is a comprehensive database auditing mechanism that is easily configurable via application GUI and significantly decreases effort investment while configuring it against multiple tables in a database.

Let us quickly walk-trough the configuration process to track database changes on multiple tables at one edition set, here are the steps:

1. In the ApexSQL Audit GUI head to the Before-After tab

   

2. Opt to add the database and continue by specifying what tables you want to audit, we used AdventureWorks2017 for our example below

   

3. The configuration can be granularly defined and combine operations with the table columns, allowing the user to achieve specific auditing needs

   

Via this 3-step configuration using the application interface, we have complete configuring database auditing to track changes for each table and column per specific needs. After the auditing commences, the history of changes will be reconstructed and easily overviewed in auditing reports with rich metadata information:

Report feature provides the ability to generate and automate the reporting tasks while also including the ability to specify filters while obtaining this data from the auditing event logs. As the extra feature, ApexSQL Audit also provides a comprehensive data alert mechanism to raise an alert on any specific data change criteria and raise awareness of the activity in no time:

Configuring auditing specification, reporting task, and alerting are the three core pillars to utilize with ApexSQL Audit, in this article we’ve covered quick and easy configuration and output details, however, to grasp more about how to specify reporting and alerting to reach specific goals, feel free to visit this How to configure and use before-after auditing in ApexSQL Audit article.

Summary

In this article, we have covered two out of many SQL auditing solutions to track database changes, the native Temporal tables feature is a true powerhouse that helps in multiple scenarios while creating database auditing trail, especially with analytics and recovery. But, we’ve also acknowledged that system-version history changelogs do not provide rich metadata information that is a valuable piece for the audit review processes. As a potential solution to this challenge, ApexSQL Audit demonstrates a strong capability to track and document database changes on specific auditing policies, run and create the reporting documentation and provide real-time alerting that is as significant due to regulatory requirements that apply nowadays.