通過 MSSQL 的變更數據擷取 (CDC) 進行審計

我想向 DBA 社區詢問有關審核 MSSQL 數據庫的最佳實踐。我設置了 CDC MSSQL 並遇到了一些困難。在我更改表的結構之前一切正常，之後 MSSQL 刪除了 CDC 表。

應該怎麼做？

感謝您的建議。

瓦爾

不不不

CDC 不能很好地處理對錶架構的更改，例如添加或刪除列，或者更改列數據類型。

如果你想這樣做，你需要：

• 保存目前數據關閉
• 獲取目前的 Start LSN
• 禁用 CDC
• 進行更改
• 啟用 CDC
• 重新插入舊數據
• 使用之前的 Start LSN 更新 CDC 擷取實例

這是一個使用名為 Posts 的表的範例腳本。您需要對其進行調整以適應您的特定需求。我不會在這裡這樣做。

--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;

審核 MSSQL 數據庫的最佳實踐

CDC 用於數據複製。至少您需要使用 CDC 更改並將它們寫入某個地方，因為 CDC 並非旨在成為更改數據的永久儲存庫。

所以最好的做法是使用SQL Server 審計，如果這與你所說的“審計”一致。

引用自：https://dba.stackexchange.com/questions/305152