Post reply

Which type of logs do you save from your databases? Same or in a different database?

  • September 9, 2014 at 8:10 am

    #299162

    Hi!

    I'm creating this topic to try to understand the best practices for saving databases logs.

    Currently in my database(it's a database which saves clinical data, patients, users), I have two types of logs:

    - tables where I save the modified clinical/patient data, which data was modified, when and who modified it;

    - tables where I save exceptions and other log messages from log4net framework;

    Some collegues keep me saying that I should use a separate database to save the logs..

    Which type of logs do you save from your databases?

    Do you save them in the same database or in a different one?

    What are the advantages/disadvantages?

    Regards

  • September 9, 2014 at 10:40 am

    #1744191

    For auditing/logging changes to data in the database (your first logging) I would do it in the same database.

    For application logging I would probably use a separate exceptions or logging database.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • September 11, 2014 at 3:39 am

    #1744672

    Thanks for your opinion!

    Which advantages do you see in separating the exceptions and error logging to a different database?

    Regards!

  • September 11, 2014 at 6:13 am

    #1744724

    I mostly log my ETL and data processing tasks.

      Data Sources (API's) - I log all data source downloads from API's that I've developed to a flat file. The flat file is imported into the database.
      ETL System - Once data is in, I log the entire ETL process of the data.
      Data Processing - Then I do a lot of pre-aggregation for certain tasks and reports that are logged too.

    I do this so every step of the data pipeline is logged from when it's downloaded with the various API's (cross-platform) to when it's processed by the ETL system on the way into the database to when it's pre-processed by other tasks. All of these are stored in the database in steps with timestamps and additional points of information like new records, updates, and so forth.

    I've been told that I have too many logs by people with 10 to 20 years more experience than me. But, in their systems, I couldn't tell the point of failure between the various systems. In my system, I can surely tell you exactly where the problem is occurring and it will likely give you or anyone unfamiliar with the system a good idea where the problem lies in said system.

    That's what I want. You to clearly see the problem. Not for you to go on a treasure hunt. :hehe:

  • September 11, 2014 at 6:44 am

    #1744732

    amns (9/11/2014)

    Thanks for your opinion!

    Which advantages do you see in separating the exceptions and error logging to a different database?

    Regards!

    Here are some:

    1. You can centralize error and exception logging.

    2. Sometimes logging can be very verbose and logging to a separate database can improve performance (writing to a different transaction log mainly), reduce storage requirements (you don't have to backup an error log as often as a mission critical database), and it helps recovery time in disaster because the database is smaller so faster restore.

    3. You can move the logging database to a different server if your main application SQL Server is experiencing any issues.

    I don't think any of this is earth-shattering or anything, but they are little things that as the database grows or usage increases could make a difference.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • September 12, 2014 at 10:00 am

    #1745141

    Thanks, they seems to me valid reasons!

Viewing 6 posts - 1 through 5 (of 5 total)

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