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

Auditing of SQL Server changes on both server and database levels is not only recommended but also an obligatory task in many companies. In addition to many existing compliance standards which ensure the transparency of data and changes on the information for many different spheres, most companies also impose internal auditing specifications to track changes on the SQL Server databases to ensure that changes on important database tables are fully transparent for the purposes of general auditing, troubleshooting, investigation, disaster recovery etc.

In this article, we are going to look into data change auditing using the SQL Servers own ‘Change Data Capture’ and ApexSQL Audit – a powerful auditing tool for SQL Server database auditing which in addition to the change-auditing audits almost 200 SQL Server events and consequentially enables users to achieve compliance with many different standards such as GDPR, HIPAA, SOX, PCI…

Supported SQL Servers:

• Change Data Capture supports SQL Servers from 2008 version onwards. Additionally, Enterprise edition of SQL Server is a requirement and all other editions are not supported, except for SQL Server 2016.
• ApexSQL Audit supports all SQL Server versions from 2005 onwards and additionally fully supports ‘lower’ editions of SQL Server (Express, Standard, Developer).

Auditing mechanisms:

• Change Data Capture performs asynchronous auditing. This means that the auditing is not in sync with the actual change, and does not occur immediately as the changes on the database tables are processed in the SQL Server. Instead, the method used is periodical querying of the databases online transaction log. Furthermore, Change Data Capture requires that a SQL Server Agent is running on the audited SQL Server instance
• ApexSQL Audit, on the other hand, utilizes both SQL triggers for the purpose of before-after changes auditing as well as the SQL traces in order to capture almost 200 SQL Server events, both on the server and database levels. Database triggers used for before after auditing are automatically created on the tables that are configured for before-after auditing. The triggers are created on the tables that are being audited, and these triggers are fired immediately once the data change occurs which makes the auditing with ApexSQL Audit synchronous.

Data repository:

• Change Data Capture uses a decentralized system to store the data – this means that all the audited data is being stored in specific tables inside each audited database separately – this means that extracting audited data and creating reports is also decentralized and that these jobs must be run for each database separately, which can take quite some time, especially when audited databases are scattered across multiple SQL Server instances.
• ApexSQL Audit has a centralized system to store the audited data – all the audited data is stored inside one central repository database kept on a single SQL Server instance. This means that pulling complex reports for many databases scattered across different servers is a quick and painless task.

Audited data integrity protection:

• Change Data Capture doesn’t provide protection mechanisms that could be used to ensure the safety or integrity of the audited data – although, users can setup SQL Server encryption as per their own accord, but this requires intermediate SQL Server knowledge.
• ApexSQL Audit, on the other hand, protects the central repository database with a 256-bit row history dependent hash encryption which makes sure that any changes made on the audited data are completely transparent and that the user will be alerted on any integrity breach.

Auditing setup:

• Change Data Capture can be enabled only using system stored procedures – this means that the user needs to have at least intermediate SQL Server knowledge to be able to setup this auditing. Furthermore, while Change Data Capture can be enabled using code, SQL Server Management Studio does not provide any feature that would enable or help with the setup. This means that Change Data Capture must be enabled for each table individually which can require some amount of time to configure.
• ApexSQL Audit’s GUI provides quick mechanisms which enable users to setup auditing on many databases over different SQL Server instances all at once. Additionally, various filters (date-time, user, application, host…) provide great granularity and precision to fine tune the auditing configuration.

  

Audited data:

• Change Data Capture audits DML changes, and provides information on the operation type (insert, update, delete), shows historical data, and information on the values. However, the who, when and how information is not shown. While CDC does not track, who made the change, the process can be manually enhanced by adding a new field when storing user details and update these after every change. The same can be done for the time of the change and the application used to perform it.
• ApexSQL Audit, on the other hand, audits almost 200 SQL Server events out of the box including both DML and DDL changes as well as other events like configuration and security changes. While the “who”, “when”, “how”, and many other information is available in full, ApexSQL Audit also audits any access to the data and allows auditing of select, select into and read text queries that can provide vital additional information to accompany data changes.

  

Reporting

• Change Data Capture does not have out-of-the-box mechanisms for the purpose of reporting. In order to extract the data, user will have to run cdc.fn_cdc_get_all_changes and cdc.fn_cdc_get_net_changes functions, since the querying of the repository tables is not generally recommended in the community (e.g. MSDN).

  

• ApexSQL Audit provides full solution for the reporting on the audited data. Users can opt to run one of many out-of-the-box reports that are integrated in the application, and use a variety of available filters to create highly-specialized reports. In addition, ApexSQL Audit allows full customization of reports by utilizing logical conditioning wrapped inside the user-friendly GUI, which means that there is almost no limit on how granular or precise the custom report can be – there is no limit to number of the conditions that can be used. Reports can be saved for any future usage.

  

Archiving mechanisms

• Change Data Capture repository tables cannot be automatically archived and this will require additional efforts once the tables overgrow their environment and need to be moved to another SQL Server or some storage network drive. Furthermore, since the repository tables are decentralized, every table must be individually administered. Another limitation of this decentralized system is the access to the stored data – no sorting or easy-to-use mechanism are available, and accessing multiple archived table repositories can be a high time-investing job.
• ApexSQL Audit has full archiving mechanisms available, and the central repository database archive is created with a single click on the archiving option – fresh database will store all newly audited data, and archived repository can be taken offline and stored where ever the user’s needs dictate. Furthermore, archive can be directly added as the source for the reports via GUI which makes it easy to work with numerous archives.

Alerting

• Change Data Capture does not have out-of-the-box reporting mechanisms that will alert on a specific data change event. While SQL Agent can be used to alert on a specific event (e.g. delete), advanced alerting is not available (and this requires some SQL Agent knowledge).
• ApexSQL Audit has fully customizable alerting mechanisms on a data change event – in addition to receiving alert notification on a specific event (e.g DML change), users can configure an alert to be triggered when a specific before-change value is changed or after-change value is specified.