In Katmai we have the cdc(Change data Capture).Change data capture(CDC) is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred.
For more details go to books online and write "change data capture"....
.
Sara Tahir [MSFT] wrote:
We do plan to provide auditing support in SQL Server 2008. However, the feature is not available in SQL Server 2008 June CTP. Expect to see it in a future CTP.
I just tested cdc and it works fine in the june ctp.
|||Yes, CDC works just fine. There is couple of steps to make ti work...but it's cool feature.
Example:
--to activate cdc
EXEC sys.sp_cdc_enable_db_change_data_capture
--checking
SELECT is_cdc_enabled FROM sys.databases WHERE name = 'databaseName'
--enabling chanage data capture on specific table
USE test;
GO
EXECUTE sys.sp_cdc_enable_table_change_data_capture
@.source_schema = N'dbo'
, @.source_name = N'Imena'
, @.role_name = N'cdc_Admin';
GO
-- INSERT, DELETE, UPDATE some staff in table
-- look at change history
SELECT * FROM cdc.dbo_tableName_CT
Also you can trakc DDL statments in ddl_history table
It would be nice to have user name in cdc.dbo_tableName_CT
|||What is the performance impact of CDC?
Can you write the data to audit detail to another database?
-John
|||Hi all,
I have also worked a little on the CDC. You can find my notes at http://www.kodyaz.com/articles/change-data-capture.aspx
As far as I see, there is not any information about who did the change kept on the CDC tables, on mirror of the original tracked table. Or am I missing something since I did not check more on the underlying tables.
CDC is a common topic all of us dealing with. I guess you have followed the topic "Data Auditing vs CDC" at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1730222&SiteID=1 also topic "CDC" at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1752141&SiteID=1
In those forms, CDC is said to be designed for ETL and also data is extracted from transaction logs. Additional disk activity is the major topic that may affect the performance. But it should be handled in each databases own conditions.
I do not think it is possible to configure the CDC tables to be created on an other database.
|||
As far as I see, there is not any information about who did the change kept on the CDC tables, on mirror of the original tracked table. Or am I missing something since I did not check more on the underlying tables.
A lot of DBA and programmers will use CDC to kept record of changaed data, but withou who part, its a little bit poor feature.
|||Since I only have Katmai running on a MacBook Pro running Vista in Parallels Desktop, and on a lowly server in our data center, I have not had the luxury of doing extensive performance tests just yet. Simon Sabin raised an issue on connect about the expense of turning a bulk update into a set of 1000's of insert statements (to the tune of 2 * the number of rows affected), which I can understand would take over certain systems.
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=283761
As for writing the data to another database, you don't have any control over the destination CDC objects, but sure, you can create a trigger against them. I coded up a very basic example which (thouh it stored the data in the same DB, it doesn't have to) augmented the data collection with username and date/time, which CDC doesn't collect:
http://sqlblog.com/blogs/aaron_bertrand/archive/2007/06/21/playing-with-cdc-in-katmai.aspx
I asked for more options here:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=283707
Note that there are currently a couple of bugs, for example, modifying the base table in SSMS will eradicate the CDC objects on it without warning, and modifying the table using ALTER TABLE does not change the set of columns that are captured. If you drop a column, it stays in the capture table but all values become NULL; if you add a column, it does not appear in the capture table unloess you drop the CDC config and re-create it. I did not test what happens when you rename a column or change its datatype.
These are documented here:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=283765
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=283442
Hope this information is helpful.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
>>
What is the performance impact of CDC?
Can you write the data to audit detail to another database?
>>
These forums don't handle threading very well, do they?
|||CDC will have performance impact. a form of replication's logreader agent will scan the transaction log of the database ( can consume CPU as well as disk activity) and will write to the change tables. So for every ins/upd/del you want caputred will incur an additional write to disk. Since disk can be a bottleneck there are a couple ways to alleviate this - the capture scan job can be scheduled to run at off peak hours, and you can also place the capture tables on a different db filegroup, but it has to be the same filegroup as the db.
|||One more note - as mentioned above, CDC was not designed for auditing, so it most likely won't meet your auditing needs.
|||As Greg has already mentioned, CDC is not designed for auditing (as traditionally defined) puposes. If it meets your needs, then that is great. As I've already posted earlier in this thread, we do plan to provide auditing support in SQL Server 2008. However, the feature is not available in SQL Server 2008 July CTP. I'd highly recommend trying out the auditing functionality when it becomes available in a future CTP.
For more on CDC vs. Auditing, take a look at the following thread on this forum: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1730222&SiteID=1
No comments:
Post a Comment