‘During several recent briefings, chats with customers, and discussions with existing clients, the topic of data collections methods for Database Activity Monitoring has come up. While Rich provided a good overview for the general buyer of DAM products his white paper, he did not go into great depth. I was nonetheless surprised that some people I was discussing the pros and cons of various platforms with, were unaware of the breadth of data collection options available. More shocking was a technical briefing with a vendor in the DAM space who did not appear to be aware of the limitations of their own technology choices … or at least they would not admit to it. Regardless, I thought it might be beneficial to examine the available options in a little greater detail, and talk about some of the pros and cons here.

Database Audit Logs

Summary: Database Audit Logs are, much like they sound, a log of database events that have already happened. The stream of data is typically sent to one or more files created by the database platform, and may reside at the operating system level or may be contained within the database itself. These audit logs contain a mixture of system resource recordings, transactional events, user events, system events, and other data definitions that are not available from other sources. The audit logs are a superset of activity. Logging can be implemented through an agent, or can be queried from the database using normal communication protocols.

Strengths: Best source for accurate data, and the best at ascertaining the state of both data and the database. Breadth of information captured is by far the most complete: all statements are captured, along with trigger and stored procedure execution, batch jobs, system events, and other resource based data. Logs can capture many system events and DML statements that are not always visible through other collection methods. This should be considered one of the two essential methods of data collection for any DAM solution.

Weaknesses: On some platforms the bind variables are not available, meaning that some of the query parameters are not stored with the original query, limiting the value of statement collection. This can be overcome by cross-referencing the transaction logs or, in some cases, the system tables for this information, but at a cost. Select statements are not available, and from a security standpoint, this is a major problem. Performance of the logging function itself can be prohibitive. Older versions of all the database platforms that offered native auditing did so at a very high cost in disk and CPU utilization- upwards of 50% on some platforms. While this has been mitigated to a more manageable percentage, if not properly set up, or if too much information is requested from high transaction rate machines, overhead can still creep over 15% unless carefully deployed. Not all system events are available.

Network Monitoring

Summary: This type of monitoring offers a way to collect SQL statements sent to the database. By monitoring the subnet, network mirror ports or TAPS, statements intended for a database platform can be ‘sniffed’ directly from the network. This method will capture the original statement, the parameters, and the returned status code, as well as any data that was returned as part of the query operation. Typically an appliance-based solution.

Strengths: No performance impact to the database host, combined with the ability to collecting SQL statements. On legacy hardware, or where service level agreements prohibit any additional load being placed upon the database server, this is an excellent option. Simple and efficient method of collecting failed login activity. Solid, albeit niche applicability.

Weaknesses: Misses console activity, specifically privileged user activity, against the database. As this is almost always a security and compliance requirement, this is a fundamental failing of this data collection method. Sniffers are typically blind to encrypted sessions, although this is still a seldom used feature within most enterprises, and not typically a limiting factor. Misses scheduled jobs that originate in the database. To save disk space, most do not collect the returned data, and some products do a poor job of matching failed status codes to triggering SQL statements. “You don’t know what you don’t know”, meaning that in cases where network traffic is missed, mis-read or dropped, there is no record of the activity. This contrasts with native database auditing where some of the information may be missing, but the activity itself will always be recorded.

OS / Protocol Stack Monitoring

Summary: This is available via agent software that captures statements sent to the databases, and the corresponding responses. The agents are deployed either in the network protocol stack, or embedded into the operating system to capture communications to and from the database. They see an external SQL query sent to the database, along with the associated parameters. These implementations tend to be reliable, and low-overhead, with good visibility into database activity. This should be considered a basic requirement for any DAM solution.

Strengths: This is a low-impact way of capturing SQL statements and parameters sent to the database. What’s more, depending upon how they are implemented, agents may also see all console activity, thus addressing the primary weakness of network monitoring and a typical compliance requirement. They tend to, but do not always, see encrypted sessions as they are ‘above’ the encryption layer.

Weaknesses: In rare cases, activity that occurs through management or OS interfaces is not collected, as the port and/or communication protocol varies and may not be monitored or understood by the agent.

System Tables

Summary: All database platforms store their configuration and state information within database structures. These structures are rich in information about who is using the database, permissions, resource usage, and other metadata. This monitoring can be implemented as an agent, or the information can be collected by a remote query.

Strengths: For assessment, and for cross referencing status and user information in conjunction with other forms of monitoring.

Weaknesses: Lacks much of the transactional information typically needed. Full query data not available. The information tends to be volatile, and offers little in the way of transactional understanding, or the specific operations that are being performed against the database. Not effective for monitoring directly, but rather useful in a supporting role.

Stored Procedures & Triggers

Summary: This is the original method for database monitoring. Using the database’s native stored procedures and triggers to capture activity and even enforce policies.

Strengths: Non-transactional event monitoring and policy enforcement. Even today, triggers for some types of policy enforcement can be implemented at very low cost to database performance, and offer preventative controls for security and compliance. For example, triggers that make rudimentary checks during the login process to enforce policies about which applications and users can access the database, at which time of day, can be highly effective. And as login events are generally infrequent, the overhead is inconsequential.

Weaknesses: Triggers, especially those that attempt to alter transactional processes, are a huge performance cost if in line with transaction processing. Stored procedure and trigger execution, in line with routine business processing, not only increase latency and processing overhead, but can destabilize applications that use the database as well. The more policies are enforced, the worse performance gets. This method of data collection, for use with general monitoring, has been all but abandoned.

Database Transaction Logs

Summary: Database transaction logs are often confused with audit logs, but they are very different things used for different reasons. The transaction logs, sometimes called ‘redo’ logs, are intended to be used by the database to ensure transactional consistency and data accuracy in the event of a hardware or power failure. For example, on the Oracle database platform, the transaction logs records the statement first, and when instructed by a ‘commit’ or similar statement, writes the intended alterations into the database. Once this operation has been completed successfully, the completed operation is recorded in the audit log.

Should something happen before this data is fully committed to the database, the transaction log contains sufficient information to roll the database backward and/or forward to a consistent state. For this reason, the transaction log records database state before and after the statement was executed. And due to the nature of their role in database operation, these log files are highly volatile. Monitoring is typically accomplished by a software agent, and requires that the data be offloaded to an external processor for policy enforcement, consolidation and reporting.

Strengths: Before and after values are highly desirable, especially in terms of compliance.

Weaknesses: The format of these files is not always published and not guaranteed. The burden of reading them accurately and consistently is up to the vendor, which is why this method of data collection is usually only available on Oracle and SQL Server. Transaction logs can generate an incredible quantity of data, which needs to be filtered by policies to be managable. Despite being designed to ensure consistency of the database, transaction logs are not the best way to understand the state of the database. For example, if a user session is terminated unexpectedly, the data is rolled back, meaning previously collected statements are now invalid and do not represent true database state. Also, on some platforms, there are offline and online copies, and which copies are read impacts the quality and completeness of the analysis.

Memory Scanning

Summary: Memory scanners are an agent based piece of software that reads the active memory structures of the database engine. On some pre-determined interval, the memory scanning agent examines all statements that have been sent to the database

Strengths: Memory scanning is good for collecting SQL statements from the database. It can collect the original SQL statements as well as all of the variables associated with the statement. They can also understand complex queries and, in some cases, resource usage associated with the query. In some instantiations, they can also examine those statements, comparing the activity with security policy, and send and alert. This can be desirable when near-real time notification is needed, and the delay introduced by network latency when sending the collected information to an appliance or external application is not appropriate. Memory scanning is also highly advantageous on older database platforms where the native auditing introduces a harsh performance penalty, providing much of the same data at a much lower overall cost in resources.

Weaknesses: The collection of statements is performed on the periodic scan of memory. The agent ‘wakes-up’ according to a timer, and typical timer intervals are 5-500 milliseconds. The shorter the interval the more CPU intensive. As the memory structure is conceptually ‘round’, the database will overwrite older statements that may still reside in memory but have been completed. This means that machines under heavy load could overwrite statements before the memory scan commences, and miss statements. The faster the execution of the statement, the more likely this is to be the case.

Memory scanning can also be somewhat fragile; if the vendor changes the memory structures when updating the version of the database, the scanner is often breaks. In this case it might miss statements, or it may find garbage at the memory location that it expects to find a SQL statement.

Vendor APIs

Summary: Most of the database vendors have unpublished codes and interfaces that turn on various functions and make data available. In the past, these options were intended for debugging purposes, and performance was poor enough that they could not be used in production database environments. As compliance and audit become a big driver in the database security and DAM space, the database vendors are making these features ‘production’ options. The audit data is more complete, the performance is better, and the collection and storage of the data is more efficient. Today, these data streams look a lot like enhanced auditing capabilities, and can be tuned to provide a filtered subset while both offering better performance and lower storage requirements. There are vendors who offer this today, and some who have in the past, but this not typically available, and not for all database platforms. Still, many of the options remain unpublished, but I expect to see more of these made public over time and used by DAM and Systems Management vendors.

Strengths: The data streams tend to be complete, and the data collected is the most accurate source of information on database transactions and the state of the database.

Weaknesses: Not fully public, not fully documented, or in some cases, only available to select development partners.

I may have left one or two out, but these are the important ones to consider. Now, what you do with this data is another long discussion. How you process it, how fast you process it, how you check for security and compliance policies, how it is store and how alerts are generated means there is a lot more ways to differentiate vendors that simply the data that they make available to you. Those discussion are for another time.