0

I want to ask the DBA community about best practices for auditing the MSSQL database. I set up CDC MSSQL and experience some difficulties with it. It's working all fine until I change the structure of the table, after that MSSQL deletes CDC table.

How it should be done?

Thanks for the advice.

Val

2 Answers 2

2

nope & nope

CDC doesn't handle changes to table schema like adding or dropping columns, or to changing column data types well.

If you want to do that, you'll need to:

  • Save the current data off
  • Get the current Start LSN
  • Disable CDC
  • Make your changes
  • Enable CDC
  • Insert the old data back
  • Update the CDC capture instance with the Start LSN from before

Here's an example script using a table called Posts. You'll need to adapt it to suit your specific needs. I won't be doing that here.

--Optional: put it all in a transaction:
--BEGIN TRAN

--Back up current data
SELECT 
    dpc.*
INTO #original_cdc_data
FROM cdc.dbo_Posts_CT AS dpc;

--Get the current start LSN
DECLARE 
    @start_lsn binary(10);

SELECT 
    @start_lsn = ct.start_lsn
FROM cdc.change_tables AS ct
WHERE ct.capture_instance = 'dbo_Posts';

--Disable CDC
EXEC sys.sp_cdc_disable_table
    @source_schema = 'dbo',
    @source_name = 'Posts',
    @capture_instance = 'dbo_Posts';

--Make your changes here, whatever they may be.

--Re-enable CDC
EXEC sys.sp_cdc_enable_table
    @source_schema = 'dbo',
    @source_name = 'Posts';

--Insert back up data into the new table. If you added or dropped columns here, you'll need to account for that in the select list. If you just changed data types, make sure that the change is compatible here.
INSERT 
    cdc.dbo_Posts_CT WITH(TABLOCK)
SELECT 
    ocd.*
FROM #original_cdc_data AS ocd;

--Set the starting LSN to the value from above
UPDATE ct
   SET ct.start_lsn = @start_lsn
FROM cdc.change_tables AS ct
WHERE ct.capture_instance = 'dbo_Posts';

--Optional: complete transaction
--ROLLBACK;
--COMMIT;
1

best practices for auditing the MSSQL database

CDC is for data replication. At a minimum you would need to consume the CDC changes and write them someplace, as CDC is not intended to be a permanent repository for the change data.

So the best practice is to use SQL Server Audit if that aligns to what you mean by "auditing".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.