Post reply

store data changes(insert\update\delete)

  • January 4, 2018 at 4:17 pm

    #342279

    Hi,

    Ii would like to store the information(EventDate        EventDML    LoginName    DatabaseName    TableName    HostName    IPAddress    ProgramName    ) in separate table if any data changes(insert\update\delete)  happens in any table  in the database.

    aAny suggestions please.

    Thank you !!

  • January 5, 2018 at 9:19 am

    #1974714

    adisql - Thursday, January 4, 2018 4:17 PM

    Hi,

    Ii would like to store the information(EventDate        EventDML    LoginName    DatabaseName    TableName    HostName    IPAddress    ProgramName    ) in separate table if any data changes(insert\update\delete)  happens in any table  in the database.

    aAny suggestions please.

    Thank you !!

    One way would be to take a look at using triggers for the auditing - there are a lot of examples online. A couple examples would be:
    Create a Simple SQL Server Trigger to Build an Audit Trail
    Pop Rivett’s SQL Server FAQ No.5: Pop on the Audit Trail

    Sue

  • January 5, 2018 at 9:37 am

    #1974717

    Thinking more along the lines of extended events, but then I may be wrong.

  • January 8, 2018 at 5:59 pm

    #1975010

    Lynn Pettis - Friday, January 5, 2018 9:37 AM

    Thinking more along the lines of extended events, but then I may be wrong.

    Extended event - event category degree_of_parallelism give you lots of info. The logging is minimal so you won't have to worry about disk space. However, you need to take time to filter the vast amount of info to capture only what you need. Here is an example. Sql_text actually captures a significant portion (if not entirety) of the command.

  • January 10, 2018 at 7:41 am

    This was removed by the editor as SPAM

  • January 10, 2018 at 9:35 am

    #1975325

    subramaniam.chandrasekar - Wednesday, January 10, 2018 7:41 AM

    adisql - Thursday, January 4, 2018 4:17 PM

    Hi,

    Ii would like to store the information(EventDate        EventDML    LoginName    DatabaseName    TableName    HostName    IPAddress    ProgramName    ) in separate table if any data changes(insert\update\delete)  happens in any table  in the database.

    aAny suggestions please.

    Thank you !!

    Please use CDC in SSIS. We'd SQL triggers before, nowadays SSIS CDC are useful.

    Eye-opening. I didn't known there was such a thing. Thanks.
    How does it impact the destination tables though? Will it cause locks/blocking?

    Thanks.

  • January 10, 2018 at 9:53 am

    This was removed by the editor as SPAM

  • January 10, 2018 at 10:39 am

    #1975346

    subramaniam.chandrasekar - Wednesday, January 10, 2018 9:53 AM

    RandomStream - Wednesday, January 10, 2018 9:35 AM

    subramaniam.chandrasekar - Wednesday, January 10, 2018 7:41 AM

    adisql - Thursday, January 4, 2018 4:17 PM

    Hi,

    Ii would like to store the information(EventDate        EventDML    LoginName    DatabaseName    TableName    HostName    IPAddress    ProgramName    ) in separate table if any data changes(insert\update\delete)  happens in any table  in the database.

    aAny suggestions please.

    Thank you !!

    Please use CDC in SSIS. We'd SQL triggers before, nowadays SSIS CDC are useful.

    Eye-opening. I didn't known there was such a thing. Thanks.
    How does it impact the destination tables though? Will it cause locks/blocking?

    Thanks.

    Please refer below,

    https://www.sqlservercentral.com/Forums/1314853/A-SSIS-Design-Pattern-for-Change-Data-Capture

    Thanks. I read it and all comments that followed. It is not the best write-up on CDC. I appreciate the pointer though, now I know that such a capability exist.

  • January 10, 2018 at 11:17 am

    #1975350

    RandomStream - Wednesday, January 10, 2018 10:39 AM

    Thanks. I read it and all comments that followed. It is not the best write-up on CDC. I appreciate the pointer though, now I know that such a capability exist.

    That really is not so good of an article...never read that before either. There is a discussion up here that compares trigger and cdc that's pretty good if you wanted to compare and understand the different approaches. If you only want data and not who did what then CDC may work. It's not a replacement for using triggers and wasn't intended to be - it won't capture things like user, spid, application, etc that the poster requested. So it wouldn't work in this case if you read the posters request. It's really data only. It's based on reading the log so it's going to be limited in that respect. I haven't played with using extended events for something like this request but that is an interesting approach, I kind of like the thought of that.
    In any case, the approach really depends on what is needed with the auditing, what's being audited, etc. Here is a link to that discussion - which has some links to real CDC articles:
    Difference in CDC vs Trigger

    Sue

  • January 10, 2018 at 4:23 pm

    #1975411

    To add to it all, it's a huge waste of disk space to audit INSERTs and the INSERTED side of UPDATES because the latest data will ALWAYS be in the original table.  For example, if something is inserted into a table and never changes, why would you also have that something inserted into an audit table.  It's a waste of disk space that causes instant duplication and instant doubling of disk space requirements, tape space, backup time and, more importantly, restore time.

    Record only modifications for audits.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 10, 2018 at 11:40 pm

    This was removed by the editor as SPAM

  • January 11, 2018 at 8:41 am

    #1975525

    Sue_H - Wednesday, January 10, 2018 11:17 AM

    RandomStream - Wednesday, January 10, 2018 10:39 AM

    Thanks. I read it and all comments that followed. It is not the best write-up on CDC. I appreciate the pointer though, now I know that such a capability exist.

    That really is not so good of an article...never read that before either. There is a discussion up here that compares trigger and cdc that's pretty good if you wanted to compare and understand the different approaches. If you only want data and not who did what then CDC may work. It's not a replacement for using triggers and wasn't intended to be - it won't capture things like user, spid, application, etc that the poster requested. So it wouldn't work in this case if you read the posters request. It's really data only. It's based on reading the log so it's going to be limited in that respect. I haven't played with using extended events for something like this request but that is an interesting approach, I kind of like the thought of that.
    In any case, the approach really depends on what is needed with the auditing, what's being audited, etc. Here is a link to that discussion - which has some links to real CDC articles:
    Difference in CDC vs Trigger

    Sue

    And also, CDC (Change Data Capture) is only available in the Enterprise Edition of SQL Server.

  • January 12, 2018 at 4:37 pm

    #1975810

    Lynn Pettis - Thursday, January 11, 2018 8:41 AM

    Sue_H - Wednesday, January 10, 2018 11:17 AM

    RandomStream - Wednesday, January 10, 2018 10:39 AM

    Thanks. I read it and all comments that followed. It is not the best write-up on CDC. I appreciate the pointer though, now I know that such a capability exist.

    That really is not so good of an article...never read that before either. There is a discussion up here that compares trigger and cdc that's pretty good if you wanted to compare and understand the different approaches. If you only want data and not who did what then CDC may work. It's not a replacement for using triggers and wasn't intended to be - it won't capture things like user, spid, application, etc that the poster requested. So it wouldn't work in this case if you read the posters request. It's really data only. It's based on reading the log so it's going to be limited in that respect. I haven't played with using extended events for something like this request but that is an interesting approach, I kind of like the thought of that.
    In any case, the approach really depends on what is needed with the auditing, what's being audited, etc. Here is a link to that discussion - which has some links to real CDC articles:
    Difference in CDC vs Trigger

    Sue

    And also, CDC (Change Data Capture) is only available in the Enterprise Edition of SQL Server.

    Not quite true anymore.  As of 2016 SP1, it's also available in the Standard Edition.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 12, 2018 at 4:59 pm

    #1975813

    Jeff Moden - Friday, January 12, 2018 4:37 PM

    Lynn Pettis - Thursday, January 11, 2018 8:41 AM

    Sue_H - Wednesday, January 10, 2018 11:17 AM

    RandomStream - Wednesday, January 10, 2018 10:39 AM

    Thanks. I read it and all comments that followed. It is not the best write-up on CDC. I appreciate the pointer though, now I know that such a capability exist.

    That really is not so good of an article...never read that before either. There is a discussion up here that compares trigger and cdc that's pretty good if you wanted to compare and understand the different approaches. If you only want data and not who did what then CDC may work. It's not a replacement for using triggers and wasn't intended to be - it won't capture things like user, spid, application, etc that the poster requested. So it wouldn't work in this case if you read the posters request. It's really data only. It's based on reading the log so it's going to be limited in that respect. I haven't played with using extended events for something like this request but that is an interesting approach, I kind of like the thought of that.
    In any case, the approach really depends on what is needed with the auditing, what's being audited, etc. Here is a link to that discussion - which has some links to real CDC articles:
    Difference in CDC vs Trigger

    Sue

    And also, CDC (Change Data Capture) is only available in the Enterprise Edition of SQL Server.

    Not quite true anymore.  As of 2016 SP1, it's also available in the Standard Edition.

    Well, I guess the documentation I read on Microsoft was wrong.  Couldn't find where I read it when I posted this, so maybe I just found an old page some how.  I did find where it is now available beginning in 2016 SP1.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply