0

A customer has a Microsoft SQL Server database. The largest part of it consists of event and audit trail logs. The logs accumulate at a rate about 4GB per year.

The customer would like to move the logs to a more appropriate storage solution to reduce the load on the main database server. However, for legacy reasons (some logging entries are produced by triggers and stored procedures), the old logging mechanism will remain active.

The plan is to implement a background job to continuously (in batches) move the log records to the new storage.

Requirements:

  • the event log/audit trail storage should be queryable - to be able to find who and when changed a specific field on a specific entity
  • it should provide some easy automatic archival mechanisms. Once in a year we might want to detach the log records that are older than 5 years, possibly compress them and move to some archive storage.
  • ability to easily attach an old archive and inspect it
  • the write speed is less important (it will be a background process anyway) than the read speed. Usually the necessity to look into logs is when something has gone wrong, and then you want to analyze it ASAP. Considering the rate of 4GB a year and archival process for 5 years old data, it might mean about 20GB worth of data to search.
  • a nice feature to wish for - an out-of-the-box GUI tool that can be used to query the data storage and maybe customizable (localizable) with custom presets for the most used typical search queries.

The customer wants to choose a simple solution to avoid overengineering things and to not introduce new possible points of failure.

Which of the modern popular open-source or free database engines is most suited for the task and would deal with it much better than the currently used Microsoft SQL server? Or maybe a dedicated event log/audit trail storage+analysis solution exists that provides everything out-of-the-box?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.