SQL Server 2008 Change Data Capture - Do you really want to log all the data activities such as audit trails?
In continuing from the series of Change-Data-Capture tags on this site I have been testing through this CDC feature on my test environment, which has proved fruitful results (to some extent). But the in the real world scenario do you really need to log all such activites?
As you may be aware that logging the data related activites is a 'must' for financial based enterprises such as Banks & Insurance in order to safeguard their data and helping in security too. This often called as Audit Trial which comes handy when you need to analyze security trial for sensitive applications data. Until now generic method to use Triggers, which will prove 'costly' when you have resource intensive query operations! In terms of managing such audit trial log files will also prove costly when you use middle tier based application to maintain the log by themselves.
To reduce such an overhead SQL Server 2008 has got (current CTPs) new framework to capture audit trial and its maintenance. All the log activities consists of in-built change tracking, change log tables and associated table value functions to access the data of change log. Here is the summary which I would copy in from CTP books online and I recommend to go through the information in order test further within your environment.
The process of enabling the audit trial logging as:
- Make sure the SQL Server Agent is up and running, this is required all the times when you want to capture the trials (see this connect feedback).
- By default SYSADMIN privleged user must enabe the data capture for a database by using the new system stored procedure sys.sp_cdc_enable_db_change_data_capture.
- Once it is executed a new schema called 'CDC' will be appeared and system table objects such as cdc.<tablename>, this will also create a new user called CDC within the database.
- Do not capture all the tables activities and it is a best practice to capture a set of tables or 'required' tables in order to manage your environment efficiently. IN order to perform that you need to execute another system stored procedure sys.sp_cdc_enable_table_change_data_capture, that requires a parameter as table name.
- The above action will let you see a new table with CDC schema that will use the source table copy with additional column for you to understand what type of change has been performed. Reference to CTP BOL - it also creates the capture and cleanup jobs for the database if the source table is the first table in the database to be enabled for change data capture and no transactional publications exist for the database.
- This will have you a new setup of change tracking on desired tables and additionally you can run new system functions that are mentioned below.
- cdc.fn_cdc_get_all_changes_[Capture_instance] (All changes happened to the source table in the given interval. [instance name] will be replaced by the instance name specified during enabling of data capture.)
- Another Stored Prodcedure cdc.fn_cdc_get_net_changes_[Capture_instance] will enable the Net changes function will report row for each source row only that has been changed by speicifying the LSN range. Being a source row can have multiple changes associated and within that LSN range can give more information to the user.
Hope the above steps would get you better understanding about procedure you need to follow in CDC and I believe as of now this feature is only available within Enterprise & Developer editions of SQL Server 2008 (ctp) (unsure whether RTM will have any changes to this edition specific). Further to the reference of above system tables that are created as CDC schema it is recommended that you do not query the system tables directly. Instead, execute the sys.sp_cdc_get_ddl_history and sys.sp_cdc_get_captured_columns (Transact-SQL) stored procedures.
Bear in mind that when you enable CDC for the current database that will capture records insert, update, and delete activity applied to SQL Server tables, make sure you only capture desired tables and not all of them otherwise it will be hard in making the details of the changes available in an easily consumed relational format. Further reference to CDC are Channel9MSDN, MSEvents1 on Technet Virtual Lab and MSEvents1 on ISV Virtual Lab.