Executive Summary
Product: SQL compliance manager 2.0
Publisher: Idera
Price: $1,495 per SQL Server instance
Key Features:
- Audits virtually all SQL Server activity
- Stores data in central database
- Easy to use reporting
- Highly scalable
- Easy to install and administer
- Price competitive
Introduction
In the good old days of being a SQL Server DBA (before the corporate financial scandals of the early 21st century in the United States), DBAs could focus on performance and uptime. Generally speaking, if they kept their databases up and running, they weren’t bothered by anyone.
Today, times have changed for many DBAs. As the safe keepers of organizational data (much of it confidential), DBAs are now often hounded by inside and outside auditors (plus the government in many cases) wanting to know who is accessing or modifying this important organizational asset.
Because SQL Server (any version) does not have any built-in self-auditing ability, finding out the information requested by outside parties is not only a nuisance, it is time consuming, and often impossible to provide. For example, how can you go back and find out who viewed a particular record on a particular date? This is not possible unless you have kept a full trace of all SQL Server activity, which virtually nobody does because of the performance hit caused by recording all of this data, not to mention storing it and formatting it into reports.
To help meet this need, many software vendors have developed SQL Server auditing tools. Some of these tools create triggers to audit activity, some software reads transaction logs to track activity, some capture profiler information, and others do some combination of the above.
In this review, we are taking an in-depth look at Idera’s SQL compliance manager 2.0, which was released in March 2006. SQL compliance manager is a full-featured SQL Server auditing tool that can audit virtually all SQL Server activity with minimal impact on production SQL Servers. SQL compliance manager collects data from multiple servers, stores it in a central repository, then allows DBAs to manage the product and do all reporting from a central console — across multiple SQL Servers. So there is no need to go to each server to run reports. In contrast, with native SQL Server tools, such as Profiler, you’d have to audit each machine separately.
Review Roadmap
In this review, here is what we are going to look at:
- What are SQL compliance manager’s key benefits, and are they actually met?
- What are SQL compliance manager’s key features, and do they perform as expected?
- How is it architected?
- How does it work?
- Is it easy to install and administer?
- How does it affect performance on production servers?
- Does it meet the needs of the typical DBA?
All of these questions will be answered in this extensive review.
Does SQL compliance manager Provide All the Benefits Claimed?
The major benefits of SQL compliance manager can be summed up this way (taken from the publisher’s Web site and product documentation):
“Database administrators require the ability to monitor and report on SQL Server activity. As mission-critical data is stored in SQL Server databases throughout the enterprise, database administrators are regularly tasked with answering the question “Who accessed my databases, what did they do, and when?” They are required to provide an accurate, immutable audit trail of all access and update actions, schema changes, and security permissions changes. Database administrators must also ensure continual compliance to external standards such as Sarbanes-Oxley, GLBA, HIPAA, and Basel II. Unfortunately, providing this audit information can often mean weeks or months of research and development, or in some cases the deployment of a full-time administrator staff to provide regular reports to auditors.”
“Idera SQL compliance manager eliminates this overhead by providing monitoring and auditing of SQL Server events, such as SELECT statements, data updates, schema changes, security permissions, and logins. SQL Compliance Manager provides quick, easy, accurate, and trusted answers whether you have tens, hundreds, or thousands of SQL Servers in your organization.”
These benefits, as described above, can be summarized this way:
- The ability to monitor and report on SQL Server activity to meet administrative needs.
- The ability to monitor and report on SQL Server activity to meet government requirements.
So, does SQL compliance manager fully meet these described benefits? After carefully reviewing and testing the software (as you will see as you read the review), SQL compliance manager indeed does so. It does so because it has the ability to audit virtually every activity occurring inside SQL Server. This, combined with the built-in reports and ad-hoc reports you can create yourself, allows you to identify and report on any SQL Server activity you want.
The only thing that SQL compliance manager does not do, which some DBAs or auditors might want, is the ability to record what the data was before a change. For example, if a record is updated, the fact that the record was updated is recorded, by who, and when. But there is no record kept of what the data was before it was updated. This is because SQL compliance manager gets its data from traces, and not the database itself. Traces don’t contain the “before” data, so it cannot be captured.
This approach was intentional on the part of Idera, as “before” data is not needed to identify “audit events” for auditing purposes, and because of the extra overhead and complexity required to track “before” data. If “before” data is needed, it can always be retrieved from stored transaction logs using appropriate software.
Another issue I want to bring up is government regulations, and the interpretation of government regulations by auditors, which will change over time and vary among who you ask. Because of this, it is possible that you may run across an auditor who says that SQL compliance manager doesn’t meet some arcane requirement. No one can predict this. But as I stated earlier, SQL compliance manager audits virtually all SQL Server activities, and I would be hard pressed to identify any activities it did not track that auditors would be interested in.