Entries:
Comments:
Posts:

Loading User Information from Channel 9

Something went wrong getting user information from Channel 9

Latest Achievement:

Loading User Information from MSDN

Something went wrong getting user information from MSDN

Visual Studio Achievements

Latest Achievement:

Loading Visual Studio Achievements

Something went wrong getting the Visual Studio Achievements

CDC - Change Data Capture SQL Server 2008

Download

Right click “Save as…”

CDC or Change data capture is a new feature in SQL Server 2008, which is an ability to record changes to table data into another table without writing triggers or some other mechanism, Change data capture records the changes like insert, update, and delete to a table in SQL server thus making the details of the changes available in relational format.
Find more information on the Topic

http://www.microsoft.com/sql/2008/prodinfo/download.mspx

https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5507

http://blogs.msdn.com/usisvde
http://blogs.msdn.com/ajaiman

Tag:

Follow the Discussion

  • Very Nice Feature.
    Myonly concern were what happened with distributed transaction

    Also Does any body knows any such feature in Oracle?
  • Yes, it is an exiciting feature.
    My concern is about the security trace: is it possibile to trace the current user application name ?
    Normally we connect application to SQL using a unique trusted connection (windows authentication), but we are interested to log all db changes along with the current logged (application) user; otherwise we know the db changes but not who made them.
  • MaurizioR wrote:
    Yes, it is an exiciting feature.
    My concern is about the security trace: is it possibile to trace the current user application name ?

    Normally we connect application to SQL using a unique trusted connection (windows authentication), but we are interested to log all db changes along with the current logged (application) user; otherwise we know the db changes but not who made them.


    With triggers we would do this by getting the user_name from sql and we could insert it into the audit tables. This works well as long as you have a two tier application with each user having thier own connection.

    With 3-Tier or web apps you are unable to use the user_name since you are probably using a single user id or connection pooling. So, what we would do is add a field or fields to each table that the application would populate on each insert/update. This way the audit data could capture who made the change. For delete I guess you would have to get creative like not allowing record deletes and just setting a delete flag... or you could update the record with all blank/null values prior to deleting it.

    I would assume that you could do a similar thing with CDC?

    BOb
  • Hi,
    We already implement a similar mechanism inside our product: for each table, dedicated fields are in charge to store the InserUserName and LastUpdatedUser. We can extend them, adding the DeletedUserName column.

    Anyway it is not acceptable to implement a logical delete for us, while it is possible to think to a solution that updates the DeletedUserName before physically deleting the record.

    Alternatively, an elegant solution could be:
    SQL Server stores a dedicated variable inside the CDC table, i.e. CDCUserName.
    By default, CDCUserName contains the connection user name (SQL auth or Win auth), so the console changes are logged too.

    At runtime, the application should be able to assign CDCUserName with the current application user for that specific session only. The mechanism should be compatible with connection pooling.

    Regards,

    Maurizio
  • see also CDC helper - a GUI for enabling CDC and seeing changes
    http://www.codeplex.com/CDCHelper
  • How do we use CDC for related tables such as a master and detail where master having the PK and some basic details such as created by created on etc and the details having the entity specific attributes?
    How do I retrieve the CDC data for a single record modification by joining the master and details CDC tables?
    Please suggest.
  • wisdomforce DatabaseSync has a feature for transactional audit when source is Oracle and destination could be SQL Server. The destination table can have a columns with previous data and new value along with transaction date and all other details about this transaction
    oracle "change data capture" for auditing.  http://www.wisdomforce.com/products-DatabaseSync.html
    It is a log base CDC replication
  • Virtually every project that I've worked on has required full audit tracking as most of them are ecommerce or financial projects.  Using an IsDeleted flag is the easiest and overall best way to go.  Adding the field to an existing site and fully implementing it should take no more than a couple days.  If it takes longer than that, you are doing something wrong.  For tables that allow changes, we add the following fields at the end of the table:

    [LastChangeBy] uniqueidentifier
    [LastChangeDate] datetime
    [IsDeleted] bit

    For tables that do not allow changes (i.e. Payments), we add the following fields at the end of the table:

    [CreatedBy] uniqueidentifier
    [CreatedDate] datetime

    This way we always know exactly who made what change, and when.

  • Hi,

    It would be nice to know how you would apply this to a real world scenario. Is this used only for auditing? 

    If every change is captured, and updates are captured twice, it may lead to huge change tables over a relatively short time. 

     

    Karim

  • justinjustin

    Hey I have sql 2005 and I have created triggers and table CDC to capture the data. everything functions good and the data is being updated in the _CDC tables. all i need to do now is to generate the updated data as a report - what should I do ......?
    i need to show only the updated columns
    when user selects the date periods and the person name - i need to display any updated info about that person during that period.
     
    thanks
     

Remove this comment

Remove this thread

close

Comments Closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums,
or Contact Us and let us know.