SQL Server CDC + SSIS CDC Package 完成FDA 合規

目前接到一個任務，公司的系統需要滿足FDA的合規。老闆又不想將這部分放在程式開發中完成，硬是將任務塞給了資料庫。

沒有辦法，只好接受。

分析市場上合規軟體，大都使用Trigger 或者在伺服器上設定Agent來完成資料變更的跟蹤。主要的商業軟體有如下表：

ID	Offering	Data Changes	Code Changes	Auto Data Collection	Reporting	Real Time Alerting	Comments
1	ApexSQL Audit	*		*	*		使用Triggers
2	Omni Audit	*	*	*	*	*	使用Triggers
3	Idera Compliance Manager	*	*	*	*	*	安裝Agent
4	Imperva	*	*	*	*	*
5	Lumigent Audit DB	*	*	*	*	*
6	Quest Software SQL Watch		*	*	*	*	使用Triggers
7	SQL Server Profiler	*	*	*
8	SQL Server Triggers	*	*	*

  我推薦使用SQL Server CDC + SSIS CDC Package就可以很好的完成相同的需求。

  關於CDC  的基本操作，我這就不再介紹了，網上的文章很多。

  以下是摘抄給老闆的報告，如果有什麼不明白的地方還請大家賜教。

  系統架構如下：

Change data capture (CDC) records insert, update, and delete activity that is applied to a SQL Server table. This makes the details of the changes available in an easily consumed relational format. CDC capture process retrieve changed data from SQL Server transaction log and write the changed rows to the tracked table’s associated change table. Create ETL Jobs to move overdue changed data from OLTP DB to Data Warehouse DB.

A good example of a data consumer that is targeted by this technology is an extraction, transformation, and loading (ETL) application. An ETL application incrementally loads change data from SQL Server source tables to a data warehouse or data mart. Although the representation of the source tables within the data warehouse must reflect changes in the source tables, an end-to-end technology that refreshes a replica of the source is not appropriate. Instead, you need a reliable stream of change data that is structured so that consumers can apply it to dissimilar target representations of the data. SQL Server change data capture provides this technology.

The illustration shows the principal data flow for change data capture.

 

需要另外建立DW 資料庫，用以存放Archive 資料。

建立步驟如下：

用CDC 處理DDL語句時，我們應該特別注意ADD 操作，由於CDC 不支援動態增加表欄位的更新，需要再次從建立立Capture Instance（微軟建議）。

DDL Operation: Add New Column

The capture process responsible for populating the change table will ignore any new columns that are not identified for capture when the source table was enabled for change data capture. We can rebuild capture for the table that reflects the new column structure. After rebuild capture for table, we should merge the old archive table to new archive table in DW database.

設定SSIS 2012 中新的package CDC control  來協助完成Archive ETL的設定。因為是新技術，這部分內容網上很少。

1.初始化

2.取值

非常簡單，又好用。