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 are 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).
- 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.
- 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 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 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.
- 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.
- 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 are 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.
- 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.
- Change Data Capture repository tables cannot be automatically archived and this will require addition 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 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.
- 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.
|Change Data Capture||ApexSQL Audit|
|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|
|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 is performed||No requirements|
|Type of auditing||Asynchronous – latency between the change and auditing||Synchronous – auditing immediately follows the change process|
|Auditing mechanisms||Transaction log auditing||Trigger-based auditing (for before after) and SQL Trace (for auditing of other SQL Server events)|
|Data repository||Decentralized – separate database tables for each audited database||Centralized – regardless of the number of databases or different SQL Server instances, all audited data is stored in a single repository database|
|Auditing setup||Query-based. Each table must be manually configured for auditing||GUI based. Configuration of multiple tables in different database over many SQL Server instances at once|
|SQL Server Agent required||Yes – must be running so that Change Data Capture can function||No|
|Audited data details||The change type, value and historical data information are provided||The change type (insert, update, delete), value and historical data information, who made the change, when and how are provided as well as the DDL and configuration changes|
|Audited data types||DML only||Almost 200 SQL Server events, server and database levels, including DDL, DML, Security, Query, Execute, Backup/Restore, Warning and Error events|
|Reporting||Only via SQL functions (direct querying is not recommended)||Highly specialized GUI enables use of out-of-the-box reports as well as the creation of fully customized reports|
|T-SQL Knowledge required||Intermediate level||Beginner level|
|Archiving mechanisms||None||Full archiving as well as configuration mechanisms when working with archives|
|Alerting||No||Fully customizable before-after change alerting|
December 1, 2016