When dealing with sensitive data, one of the requirements is to know - who changed what and when.

This means tracking all inserts, updates (before and after) and deletes. The application itself would not allow any deletions, but somebody could use T-SQL to do a delete and this would need to be tracked.

One of the major keys here is that there should be no sharing of user accounts and every user had their own AD account and they were forced to logout and login.

It was decided to use Change Data Capture (CDC) to get this information.

https://blogs.technet.microsoft.com/sql_server_isv/2010/12/02/change-data-capture-what-is-it-and-how-do-i-use-it/

The original database design already had a lot of information in every table to help capture this information with additional columns for CreatedDate, CreatedBy, LastEdited, EditedBy and Timestamp.

CDC creates tables under its own schema (cdc.) The database needed to be enabled for CDC and then ALL tables needed to have CDC enabled as well. All of the CDC schema tables were placed on their own filegroup so they could be moved to different tiered storage if required.

CDC will increase the size of the database and its backups and it will take more disk space.

There will also be a performance impact on your storage.

We also needed to change the retention period for the CDC cleanup job (to 100 years). But you could also just disable this job or delete it all together.

Being in an Availability Group adds some complexity as you need to know that CDC is running on the primary and that if there is a failover then the CDC jobs on the primary need to be enabled and started and on any secondaries disabled and stopped. To capture AG failover we used the AG alerts mentioned in the blog of 17th July 2017.

The three standard alerts that we created are:

- 1480 for 'Role Change'

- 35264 for 'Data movement suspended'

- 35265 for 'Data movement resumed'

Finally for reporting of CDC tables you need to understand the _$operation codes that are referred to in the link above.