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.取值

非常简单，又好用。