Post reply

Strategy to build a history

  • March 30, 2008 at 2:10 pm

    #184660

    Hi,

    First of, sorry if i've duplicated this question...

    My name is Koen and I'm fairly new to the whole SQL/Database thing. I'm a low level developer at origin and my SQL knowledge is fair to say low. Of course i know a thing or 2 about joins, stored procedures and sub query's, as well as the technique of normalizing a database. Designing a database is something else, I'm currently working on a project where we keep track of +- 100 000 systems and update their status every x (depending on the hardware, approximately 3) minutes. When updating the status, we'd like to store the old status of the server. Not just in log files but in the database itself, This is so we can think out some nice collective intelligence algo's which can produce some nice stats. To make things more 'complex?', each system has like 20 properties which can independently vary during each update. As example, that would mean that in update X, only property 8 has changed. Then comes update Y in which property 8 remains the same but property 4 and property 10 changed.

    *Note that after some tests, on average 10% of the property's changes every update*

    One idea i have is the most basic i could think of, namely: one big table where in every update, a new row will be inserted with the new stats, The problem with this approach is ofcourse that you probably trow away tons of space and add many useless records.

    Now to form my question: What would be a good approach in my situation?

    Thanks in advance!

    Koen

  • March 30, 2008 at 5:18 pm

    #795741

    Do a search for "Audit Log"...

    --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)

  • March 31, 2008 at 12:30 pm

    #796106

    Thanks for your answer Jeff however i don't think thats really going be a big help.

    I looked up some articles about auditing here at SQLServerCentral and i came to the following list of advantages from auditing (src = http://www.sqlservercentral.com/articles/Monitoring/basicsofc2auditing/1547/ )

    Detect the misuse and prevent it being happened again.

    Take legal actions against the people who are responsible for the misuse.

    Take recovery action from the point where something has gone wrong.

    Instead of having some kind of backup/log, i need all data available at all time as fast as possible to generate stats. More precisely: a client must be able to know what a specific server was doing 6 months, 10 days, 11 hours and 20 minutes earlier. (just for example)

    I don't think auditing is going to be a good solution ( but i might be wrong ).

    Any disagreement on this or an alternative suggestion? please feel free to share.

    Thanks again,

    Koen

  • March 31, 2008 at 12:54 pm

    #796116

    In its most basic form - an audit log is likely to be your best bet. It would capture all of the changes so that you could have a history of all of the changes that occurred. Since it sounds like you need to have this for your reporting, etc....

    You are right that this could turn into a lot of data, so depending on what is deemed important in this scenario, you may need to decide what constitutes a "change". For example, checking every 3 minutes for server uptime might mean that the ONLY change you record (when it was up and is still up) is the fact that you just checked whether it was up. Now - you COULD record a row each and every time anything changed, but that would add up rather quickly; I'd recommend instead making a determination what is a "loggable" event (such as changing from UP to DOWN, major activity change, etc...), and record only those in the history (and perhaps an aggregate or summary or the other items).

    Assuming you do this in a set-based fashion - you'd probably want to look up DML triggers, and the UPDATE(colname) syntax, so as to make your decisions work.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 4 posts - 1 through 4 (of 4 total)

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