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.
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:
In the ApexSQL Audit GUI head to the Before-After tab
Opt to add the database and continue by specifying what tables you want to audit, we used AdventureWorks2017 for our example below
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.
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.
© ALL RIGHTS RESERVED. Feedback Conditions d’utilisation Confidentialité Cookie Preference Center