locked
History / Log Table Design RRS feed

  • Question

  • hi,

     i designed one database for my application. Now i need to design archive tables for tables. When a new row insert / update to the original table i need to insert the data to history table. So i have all the changes history for future reference. How can i design this tables and handle this?

    I have 2 questions regarding this?

    1. How i design this table?

         a) Is i duplicate the original tables with some other naames indicating history ?

         b) Is this history table created in any seperate schema or in same schema as originaal (dbo)?

    2. How i log the changes in the history table?

       a) This history is based on application setup. So when user enable for logging for a particular table then only logging need to active. So how can i controle this? I created seperate thread for this. You can aalso check that here

    Monday, April 16, 2012 6:43 AM

Answers

  • Table design depends on your business requirements means what data you have to store on auditing tables..

    CREATE TABLE real (id INT, data CHAR(1)) 

    In auditing table you would need to have a date of audit

    CREATE TABLE Audit (id INT, data CHAR(1), datemodified DATETIME) 

    INSERT INTO Audit  SELECT id,data,GETDATE() FROM real


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Monday, April 16, 2012 8:55 AM
  • How often do you have your data updated? In case if it does not happen often, I'd probably consider to create only audit trail for updates and deletions with some additional attributes in the original rows (Data when created, created by whom, etc).

    Another thing I would consider is to write audit trail information (for example XML with old and new column values) rather than copy entire row by itself. A lot of things depend on requirements, physical data structure and how data is updated.

    Keeping audit/log tables at the separate server has some benefits - you can use low end hardware and utilize different maintenance schedule if server keeps only such data although it would require you to implement auditing asynchronously (one of the options is service broker) as well as introduces additional challenges in terms of client code.

    At bare minimum you should move audit data to separate tables in the different filegroups. You need to think about 3 things - first - IO - for audit table you are more about the storage size rather than performance - so you can use tiered (slower) storage for that. Second, you need to think about disaster recovery and piecemeal restore with enterprise edition. In case, if you don't have enterprise edition another database could make sense because it would "kind of" emulate piecemeal restore. Lastly, you should think about backup strategy and utilize read only filegroups for the old data.


    Thank you!

    My blog: http://aboutsqlserver.com

    Thursday, April 19, 2012 9:40 PM
  • If the purpose is displaying the changes, I'd go with deltas (columns that were changed and old/new values). Copy of entire row would help if you need to do some analysis of the changes or want to display multiple versions of the row and don't want to put extra work to the client/app server to reconstruct the data.

    Delta table can look like that:

    CREATE TABLE dbo.Audit
    (
    	MainRecId int not null,
    	OnDate datetime2(0) not null
    		constraint DEF_Audit_OnDate
    		default GetUTCDate(),
    	[Action] char(1) not null, -- I/U/D
    	LoginName sysname null
    		constraint DEF_Audit_LoginName 
    		default ORIGINAL_LOGIN(), 
    	UserName sysname not null
    		constraint DEF_Audit_UserName
    		default USER_NAME(),
    	Delta xml not null,
    	
    	constraint PK_Audit
    	primary key clustered(MainRecId, OnDate)
    )
    

    Problem with that approach - it would not handle versioning of binary/LOB data very well. Again, think about your requirements and your data.

    As for implementation - simplest one is using triggers. But that's performance hit. Could be OK though if data has not been modified all the time. Alternatively if all activity goes through app servers and you don't worry about direct DB modifications, you can put that code to data access layer.

    In order to be more specific we should have more information about your system, data and use cases for audit data usage.


    Thank you!

    My blog: http://aboutsqlserver.com

    Friday, April 20, 2012 12:26 PM

All replies

  • You can have

    1) Implementing triggers

      create trigger tru_MyTable on MyTable after update
    as

    if @@ROWCOUNT = 0
        return

    insert MyAuditTable
    select
        i.ID
    ,   d.MyColumn
    ,   i.MyColumn
    from
        inserted    i
    join
        deleted    d    on    d.ID    = o.Id

    2) OUTPUT clause

    Now from this example, you can see the integration of OUTPUT clause with existing DML syntax.
     
    Another common scenario is auditing of data in a table using triggers. In this case, the trigger uses information from the inserted and updated tables to add rows into the audit tables. The example below shows code that uses OUTPUT clause in UPDATE and DELETE statements to insert rows into an audit table.
     
    create table t ( i int not null );
    create table t_audit ( old_i int not null, new_i int null );
    insert into t (i) values( 1 );
    insert into t (i) values( 2 );
     
    update t
       set i  = i + 1
    output deleted.i, inserted.i into t_audit
     where i = 1;
     
    delete from t
    output deleted.i, NULL into t_audit
     where i = 2;
     
    select * from t;
    select * from t_audit;
     
    drop table t, t_audit;
    go

    3) Move the data to the archive later on

    --SQL2008
    insert into dbname..tbl 
    select getdate(),d.*
    from (delete top (1) 
            from foo..tbl
            output deleted.*) d
            go


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Monday, April 16, 2012 6:51 AM
  • Hi,

    As Uri mentioned triggers are one way of handling but I suggest to keep the history for only selected table not for all becuase to many Triggers can effect your database performance..I prefer to maintain the history for only main transaction tables in your application.

    hope it will help you.


    ------------------ Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Maqbool.

    Monday, April 16, 2012 8:21 AM
  • Thanks friends. my logging is based on user selection. I gave option to users and based on thier selection logging enabled for that table.

    I didn't got answer for first question.. ie where and how table design?

    Monday, April 16, 2012 8:51 AM
  • Table design depends on your business requirements means what data you have to store on auditing tables..

    CREATE TABLE real (id INT, data CHAR(1)) 

    In auditing table you would need to have a date of audit

    CREATE TABLE Audit (id INT, data CHAR(1), datemodified DATETIME) 

    INSERT INTO Audit  SELECT id,data,GETDATE() FROM real


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Monday, April 16, 2012 8:55 AM
  • thanks friend. what about log table place? some did in separate database and some in separate schema.. I think performance wise separate database not good.  Can you guide for this also ?
    Tuesday, April 17, 2012 2:15 AM
  • How often do you have your data updated? In case if it does not happen often, I'd probably consider to create only audit trail for updates and deletions with some additional attributes in the original rows (Data when created, created by whom, etc).

    Another thing I would consider is to write audit trail information (for example XML with old and new column values) rather than copy entire row by itself. A lot of things depend on requirements, physical data structure and how data is updated.

    Keeping audit/log tables at the separate server has some benefits - you can use low end hardware and utilize different maintenance schedule if server keeps only such data although it would require you to implement auditing asynchronously (one of the options is service broker) as well as introduces additional challenges in terms of client code.

    At bare minimum you should move audit data to separate tables in the different filegroups. You need to think about 3 things - first - IO - for audit table you are more about the storage size rather than performance - so you can use tiered (slower) storage for that. Second, you need to think about disaster recovery and piecemeal restore with enterprise edition. In case, if you don't have enterprise edition another database could make sense because it would "kind of" emulate piecemeal restore. Lastly, you should think about backup strategy and utilize read only filegroups for the old data.


    Thank you!

    My blog: http://aboutsqlserver.com

    Thursday, April 19, 2012 9:40 PM
  • thanks for the response. The following is my scenario

    I gave the tracing option to the user. User can enable logging for the selected tables (master / transaction) . So when user select 10 transaction table for logging (from front end) i need to log all transactions (insert / update / delete) in the history table. I mean i need the history for that selected tables' transactions. All this logging is base on user selection. Now can you help based on this scenario?

    Now i created separate table same as original and insert new record to this table when any transaction happened in original. Actually for a small status column changed the entire row is insert to history table. But any better way for this like find only changed column or any other design something like this. My aim is to show the trace log when user needs

    I checked audit trial in SOL Server and this is nice. But the trace is maintained in the file. Anyway my standard edition is not supported this feature


    • Edited by akhilrajau Friday, April 20, 2012 3:51 AM
    Friday, April 20, 2012 3:45 AM
  • If the purpose is displaying the changes, I'd go with deltas (columns that were changed and old/new values). Copy of entire row would help if you need to do some analysis of the changes or want to display multiple versions of the row and don't want to put extra work to the client/app server to reconstruct the data.

    Delta table can look like that:

    CREATE TABLE dbo.Audit
    (
    	MainRecId int not null,
    	OnDate datetime2(0) not null
    		constraint DEF_Audit_OnDate
    		default GetUTCDate(),
    	[Action] char(1) not null, -- I/U/D
    	LoginName sysname null
    		constraint DEF_Audit_LoginName 
    		default ORIGINAL_LOGIN(), 
    	UserName sysname not null
    		constraint DEF_Audit_UserName
    		default USER_NAME(),
    	Delta xml not null,
    	
    	constraint PK_Audit
    	primary key clustered(MainRecId, OnDate)
    )
    

    Problem with that approach - it would not handle versioning of binary/LOB data very well. Again, think about your requirements and your data.

    As for implementation - simplest one is using triggers. But that's performance hit. Could be OK though if data has not been modified all the time. Alternatively if all activity goes through app servers and you don't worry about direct DB modifications, you can put that code to data access layer.

    In order to be more specific we should have more information about your system, data and use cases for audit data usage.


    Thank you!

    My blog: http://aboutsqlserver.com

    Friday, April 20, 2012 12:26 PM
  • thanks for the quick response. Anyway your audit specific to database. Am i right? that the user name is db login name only. Also i didn't got in Delta field.

    I just give 2 sample tables and my expectation.

    Tables

    1. Users

    2. Roles

    3. UserRoles

    First 2 are masters and 3 is link table which includes userid and role id from previous tables.

    • First i need to maintain history of all logged activities like when users logged in and logged out etc general activity logging and it is mandatory
    • Next i gave a setup page to users with the specified tables list so that user has to select which tables needs to be logged(he may select all tables). Based on this selection i enable one flag in my TableList master table. If user enabled logging for UserRoles, i need to maintain all the changes when user update or insert or delete records in UserRoles table. (This logging happening only for tables which have logging flag enabled).

    I think now you got my scenario.Yes like you told i am using layered architecture only. Can you guid me about this?

    Friday, April 20, 2012 2:06 PM