Thursday, December 13, 2007

How to Do Database Logging/Monitoring "Right"?

So, people sometimes ask me about how to do database logging/auditing/monitoring and log analysis right. The key choice many seem to struggle with for database auditing and monitoring is reviewing database logs vs sniffing SQL traffic off the wire. Before proceeding, please look for more background on database log management, auditing and monitoring in my database log management papers (longer, more detailed - shorter) The table below summarizes the situation with database monitoring and auditing - now you can make your choice more intelligently (items in bold are the ones I consider key):

Pro Con
Sniff SQL traffic from the wire
  • No database performance impact
  • Awareness of returned content (for SELECTs)
  • Guaranteed role separation
  • Better for DBA monitoring
  • No agents
  • No database configuration changes
  • Extra device needs to be purchased, deployed and managed
  • Doesn't work with encryption
  • No local access monitoring
Collect and analyze database logs
  • No extra $$$ - use your existing logging tool
  • Can user review activity across log sources, from databases to servers
  • Satisfies compliance demand for "database log review"
  • Can monitor ALL access to data in the database, even over APIs and local
  • Performance impact possible (*)
  • Database config changes needed
  • Usually not truly "real-time" (polling)

Choose logs if you care for the relevant Pros (esp key ones) associated with them; choose sniffing if you care for the Pros and are NOT undermined by their Cons (e.g. lack of support for encrypted traffic)

Comments? Additions? Concerns?

(*) Nobody really knows what it will be in each particular situation: 0-40% were observed under various conditions by various people ...

UPDATE: Rich adds his option #3, but I am skeptical since it is not very sexy. Dedicated agents on each databases just aren't that exciting...

Dr Anton Chuvakin