Database Background Work based on Business Rule RRS feed

  • Question

  • hi,

     my scenario is simple but which option is best for this? I need to insert a new user entry to the sql table. I am using layered architecture. here i have some setup in the application like logging needed or not ? auto purge enabled or not? so if the admin enable logging, while user entry edited or added i need to add one more entry in my logging table (after insert or update data to original user table) to maintain history. I think you got my scenario..

    I have number of options and based on my knowledge i am listing here.

    1. Write Separate SP for logging and call this SP inside Original insert /Update

        But here SP takes long time to finish and also from business layer i need to give extra parameter  to SP for logging enabled or not?

    2. Call logging process in AfterInsert / Update Trigger

    3. Use SQL Agent and schedule the logging based on success insert / update run

    Which method is suitable for this situation in performance wise and architecture wise? Also explain any other good method is available

    Actually my plan is SQL agent. but based on user input how i control this?

    Thursday, April 12, 2012 1:27 AM

All replies

  • Couple of better options here could help you in database logging -

    First Approach – In memory DataTable class provided by the .NET Framework. The Logger class simply encapsulates the ability to post updates to an in-memory table and then have those updates posted in batch mode to the database on some predefined interval.

    To use the Logger component, add it to the form that needs the logging functionality. Also a DataTable (or DataSet) component and a corresponding SqlDataAdapter component. Assign the table that will hold the cached version of your log writes, to the ActiveTable property of the Logger and assign the adapter to the Adapter property. The default flush time is 15 minutes, which you can change to meet your environment's needs.

    Set the Enabled property of the Logger to true when Admin opts for logging. Then, for each time you want to send an entry to the log, just call the Write method.

    Second Approach – You could log activity from a server trace using profiler and push the profiled data to a table. This is a bit expensive solution and could have some performance issues.

    Thanks, AT

    Thursday, April 12, 2012 10:23 AM
  • You can use Change Data Capture (CDC) feature of SQL Server 2008. This will allow you deliver SQL Server data archiving, versioning, and capturing history without any additional programming.

    You can enable/disable this feature by executing,

    EXEC sys.sp_cdc_disable_db or EXEC sys.sp_cdc_enable_db

    For more you can refer http://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-(cdc)-in-sql-server-2008/

    As per your options “Call logging process in AfterInsert / Update Trigger” looks good to me as SQL Server 2005 introduced the new features of ‘after update’, ‘after insert’ and ‘after delete’ triggers that  almost solved the problem of tracking changes in data, but if you have Sql 2008 think of using CDC.

    I am aware of below methodologies to address this kind of challenge

    1. Timestamps on rows
    2. Version Numbers on rows
    3. Status indicators on rows
    4. Time/Version/Status on rows
    5. Triggers on tables
    6. Log scanners on databases

    and out of all I would prefer to use CDC.

    If you are looking for just logging little information, you can use EntLib Auditing & Logging block with configuration to log to your database. You can call this block from your Business Layer/Data Access Layer. On-demand you can switch-On/Off.

    Lingaraj Mishra

    Thursday, April 12, 2012 12:10 PM
  • thanks friend. I will look in to CDC and come back. But i have doubt that based on user settings can i change this CDC settings. I will check first
    Friday, April 13, 2012 1:27 AM
  • hi friends,

        Now we are using SQL standard Edition and CDC is not supported in Enterprise. So can anyone help me how tyo handle this scenario without CDC?

    Monday, April 16, 2012 6:12 AM