Common SQL Server database auditing goals include the ability to
- achieve compliance regulatory standards,
- perform data flow checkups, complete internal/external investigations of who changed what,
- perform post-disaster analysis when data is unintentionally changed and to discover why did it occur, when, etc.
- maintain history of data changes (before-after auditing)
There are many different ways of auditing a SQL Server database to accomplish these goals. In this article, we are going to look into auditing SQL Server databases change history (before-after) using the database transaction log files with SQL Server Change Data Capture (aka CDC) and with ApexSQL Log.
CDC is an out-of-the-box feature of SQL Server enterprise version (and now in all editions of SQL Server 2016 and up). ApexSQL Log is a 3rd party tool for SQL Server database auditing and data loss recovery which reads transaction log files and backups and enables users to examine them in a comprehensive grid and roll back or forward any logged changes.
The following are side by side comparisons of functionality, organized by category
Supported SQL Servers:
- Change Data Capture supports SQL Servers from 2008 or higher. In addition, the Enterprise edition of SQL Server is a requirement, except in SQL Server 2016 and higher.
- ApexSQL Log supports all SQL Server versions from version 2005 onwards and also fully supports all editions of SQL Server (Express, Standard, Developer, Enterprise).
- Change Data Capture audits only online transaction log file and these mechanisms require installation and configuration of the tool in order to audit data. This means that auditing of events that have occurred prior to the CDC installation cannot be performed.
- ApexSQL Log on the other hand is able to audit transaction log files as well as backups, which means that once installed, ApexSQL Log will audit any events that have occurred long before the application has been installed in the environment.
Transaction log and recovery model:
- Change Data Capture fully supports all recovery models (full, bulk-logged, simple). When a database is in the simple recovery model and SQL Server needs to truncate the online transaction log file, truncation will be postponed until CDC is successfully performed and audited information is extracted from the online transaction log file.
- ApexSQL Log offers two modes of auditing, Continuous and Ad hoc. With continuous auditing, ApexSQL Log is set up to poll the transaction log frequently and pump data to a repository, which ensures that the information inside the transaction log file is audited before the log file gets truncated. Using this method, ApexSQL Log can operate successfully regardless of the recovery model
In ad hoc mode, where ApexSQL Log is used only when needed, for example in the case of a forensic auditing situation where data changes are being investigated, ApexSQL Log generally requires the database to be in the full recovery model
- Change Data Capture uses repository tables to store the audited data. These tables are created for each audited database separately.
- ApexSQL Log
set up for continuous auditing, ApexSQL Log can create repository tables directly in the audited database, or any other database on any connectible SQL Server instance when set up for continuous auditing. The repository in this cases is centralized and stores information for all tables, allowing for easy querying and reporting.
Used on an ad hoc basis, ApexSQL Log works without a repository, by reading the transaction log and rendering results directly to a grid in the user interface where the auditing results can be inspected and analyzed before pumping them into repository tables.
- Change Data Capture must be configured via usage of system stored procedures, which means that in order to setup CDC some SQL Server knowledge is required. In addition, there are no specific mechanisms in SQL Server Management Studio that will enable user to quickly complete the process.
- ApexSQL Log’s provides both a GUI and CLI to setup auditing, enabling users to configure auditing for many databases relatively quickly. In addition, users can save their configurations in a form of project which can be than latter applied to additional SQL Server instances and databases.
- Change Data Capture audits DML changes only (vs DDL) and the information available is the operation type (insert, update or delete), basic historical data (history of change operations), and the data values before and after the change.
- ApexSQL Log audits both DML and DDL changes, but also provides additional important information, including who, when and complete historical data of each row affected by the data/structure change, as well as a variety of information regarding the operation and transaction that has occurred.
Audited data information comparison
|Schema||Schema name of table affected by change||✓||✓|
|Object||Object affected by change||✓||✓|
|User||User that made the change||✓|
|User ID||ID of the user that made the change||✓|
|Transaction state||State of the transaction||✓|
|Begin/end times||Time when the transaction begun and ended||✓||✓|
|Transaction name||Name of transaction||✓|
|Transaction ID||ID of transaction||✓|
|Transaction duration||Transaction length||✓|
|LSN||Unique LSN identifier||✓||✓|
|SPID||Server process ID number||✓|
|Parent object||Parent object of the affected object||✓|
|Parent schema||Parent schema of the affected object||✓|
- Change Data Capture requires users to audit all changes on the tables configured for auditing.
- ApexSQL Log provides variety of auditing filters to quickly isolate relevant changes or narrow the requirements for archiving auditing data in the repository.
Users can choose to filter by Date/time, operation e.g. Insert, table, user, SPID, transaction, field values, transaction description filters (based on the description content). Users can thus set up a specific audit plan with required information only
- Change Data Capture has no mechanism for drill down
- ApexSQL Log provides quick and easy mechanisms which allow users to locate specific transactions and to explore the full history of related events. This means that all changes on selected transaction will be shown for each audited operation, and that user will be able to see complete historical information at a glance.
- Change Data Capture doesn’t have any out of the box reporting, instead providing cdc.fn_cdc_get_all_changes and cdc.fn_cdc_get_net_changes functions to query the repository tables.
- ApexSQL Log offers a separately downloadable reporting module including stored procedures and SQL Server Reporting Services reports (.rdl files) that allows users to set up a reporting front end to query the repository and provide aggregate reports (see below). By accessing the repository tables directly, users can easily query and return results directly
- * Soon ApexSQL Log will offer an out-of-the-box reporting environment including
- A selection of pre-built graphical reports
- A selection of pre-built grid based reports
- A custom reporting interface with built-in filters, sorting and grouping support of results, and a selection of exports e.g. Excel in both Standard and Aggregate data formats
Data presentation and exporting
- Change Data Capture does not include a user interface
- ApexSQL Log enables users to perform auditing directly to a comprehensive grid, in ad-hoc mode, which allows users to audit old or current transaction log files where these can be inspected and analyzed before writing the information to the desired output format (repository, report). The ApexSQL Log grid allows users to use many filters and to drill down through the audited results and perform high-end analysis and investigations on who did what and from where.
ApexSQL Log can export results from the grid directly to file including XML, HTML, CSV or SQL Server bulk file.
- Change Data Capture does not provide any recovery mechanisms
- ApexSQL Log provides recovery mechanisms and enables users to roll-back (aka Undo) any changes that were audited to the pre-change state (e.g. recover deleted data).
- Change Data Capture N/A
- ApexSQL Log provides transaction play-back (aka Redo) capability which can be used to replicate changes in another environment, ship logs etc.
- Change Data Capture N/A
- ApexSQL Log allows for shipping log backup files from other servers, to a central server to be read such as the case of an ad hoc need for forensic auditing
|Change Data Capture||ApexSQL Log|
|Supported SQL Server version||SQL Server 2008+||SQL Server 2005+|
|Supported SQL Server edition||Enterprise only, except for SQL Server 2016 and higher||Express, Standard, Developer, Enterprise|
|Auditing mechanisms||Can audit only events which occur after CDC has been configured||Audits all events (including those before the tool has been installed) depending on recovery model|
|Database transaction log||All recovery models are supported, but truncation of the transaction log file in the simple recovery model can be halted until the CDC process is performed||All recovery models supported when performing regular continuous auditing. When database is in the simple recovery model, information won’t be available for ad hoc auditing|
|Data repository||Repository tables are created inside audited database only (separate for each database)||No restrictions on databases or SQL Servers for repository. A repository isn’t required for ad hoc auditing directly against the transaction log|
|Auditing setup||Each table must be manually configured for auditing with SQL||GUI based, with fully leveraged CLI. Auditing is configured per database with the ability to import configuration (project) to different databases.|
|SQL Server Agent required||Yes – this service has to be running so that CDC can operate successfully||No SQL Server Agent requirements|
|Audited data details||Only DML changes are audited with basic information (change type, values and basic historical data information)||Both DML and DDL changes are audited. Full information on who, when and from where made the change, complete historical data of each row affected by the data/structure change, variety of information regarding the operation and transaction is available|
|Auditing filters||No||Variety of filters are available to achieve great precision when auditing (Date/time based filters, operation, table, user, and other filters)|
|Drill-down mechanisms||No||Drill-down mechanisms that enable historical overview of transactions, operations, row history|
|Reporting||Only via SQL functions||Repository tables to directly query, existing stored procedures can be used create reports. Auditing to GUI for advanced analysis.|
|Data presentation and exporting||No||Auditing results to grid, as well as exporting auditing results to various file formats|
|Recovery mechanisms||No||Full rollback and roll-forward recovery mechanisms|
|Transaction shipping||No||Creates re-play scripts to replicate changes|
|Log shipping||No||Provides log shipping mechanisms by replaying all information within transaction log files|
|T-SQL Knowledge required||Intermediate level||None|
December 12, 2016