ApexSQL Log vs. SQL Server Change Data Capture (CDC)

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 3^rd 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).

Auditing mechanisms

• 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

Data repository:

• 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.

Auditing setup:

• 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.

Audited data:

• 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

Auditing filters:

• 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

Drill-down mechanisms:

• 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.

Reporting

• 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.

Recovery mechanisms

• 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).

Transaction shipping

• 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.

Log shipping

• 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