SQL Server CDC + SSIS CDC package meets FDA compliance

Currently, we have received a task in which the company's system must comply with FDA compliance. The boss does not want to put this part inProgramAfter the development is completed, the task is pushed to the database.

There is no way to accept it.

Most compliance software on the market uses trigger or agent on the server to track data changes. The main commercial software is as follows:

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	*	*	*	*	*	Install agent
4	Imperva	*	*	*	*	*
5	Lumigent audit DB	*	*	*	*	*
6	Quest software SQL watch		*	*	*	*	Triggers
7	SQL Server Profiler	*	*	*
8	SQL Server triggers	*	*	*

I recommend that you use the SQL Server CDC + SSIS CDC package to meet the same requirements.

I will not talk about the basic operations of CDC anymore.ArticleA lot.

The following is a report excerpted to the boss. If you have any questions, please kindly advise.

The system architecture is as follows:

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 authentication shows the principal data flow for Change Data Capture.

 

Another DW database needs to be created to store archive data.

The procedure is as follows:

When using CDC to process DDL statements, we should pay special attention to the add operation. Since CDC does not support dynamic addition of table column updates, we need to create a new capture instance (recommended by Microsoft ).

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 shoshould merge the old archive table to new archive table in DW database.

Set the new package CDC control in SSIs 2012 to help set archive ETL. Because it is a new technology, this part of content is rarely online.

1. Initialization

2. Values

It is very simple and easy to use.