• Adam Thurgar

Oncology App - CDC


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.


3 views

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t