SQL 2016 - Change Data Capture considerations?

Question

Hello, researched around and not quite fully clear on if going down this method for Table row auditing for our Application Development team...

• Does the DB require to be in a particular 'Recovery model'...like would Simple be good enough or does it need to be in Full or something else?
• Believe there will be some type of duplicate audit Table(s) created to match the targeted User created Table(s)...will this then increase the size of the User DB within and it's growth?
• Will the related DB Transaction Log grow more significant also if there are many User Tables to be used within CDC and performance impacts?
• What are the least privileged permissions for the end User to view the CDC audit changes and would viewing these CDC audit changes be a simple SELECT statement on each audit Table(s) or need some type of additional SQL system function with elevated permissions? ? (hopefully not Server level sysadmin or anything elevated)
• Believe it also creates additional SQL Agent jobs when setting CDC up related to it's target DB...does the end User need to access/further permissions granted to these jobs?...am just brainstorming what from my DBA perspective maintenance/security-permissions to think-plan about.

Sorry for wordy questions but trying to get a better overall picture of this CDC method vs SQL Audit method which am more familiar with.

Thanks in advance.

Answer 1

I don't know all answers on the top of my head, so I would have to read the documentation. But then again, so can you...

The main reason I post is that you seem to be looking into using CDC as an auditing solution. That is not the prime purpose of CDC. In fact, as far as I know, CDC does not track which user that did the change. But if the table has an auditing column - and you can trust that that column is updated in case of an UPDATE, you have the auditing information. Given that it is possible to update a row without changing an auditing column, my personal feeling is that this is not a satisfactory solution.

When it comes to your questions: Yes, CDC works with simple recovery as I recall, but it can lead to log growth, since data cannot be cleared until CDC as processed it. A for the SELECT, I guess that plain SELECT permissions should do. But it was quite a while since I worked with CDC.

Answer 2

Hi @techresearch7777777,

--Production databases should be in the full recovery model. The transaction log should be backed up frequently to help ensure recoverability with minimum data loss. As said here.

--Simply put, CDC SQL Server helps you integrate data faster and use fewer system resources. This is because log-based CDC is a highly efficient approach for limiting impact on the source extract when loading new data. You no longer need to deal with batch windows and bulk load updating. Instead, you can enable incremental loading or real-time streaming of data changes into your target.

--Requires the CONTROL SERVER permission.

Microsoft recommends viewing the audit log by using the Log File Viewer. However, if you're creating an automated monitoring system, the information in the audit file can be read directly by using the sys.fn_get_audit_file function.View a SQL Server Audit LogRegards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.