Test Results
In this section of the review, we take a look at each of SQL compliance manager’s key features, and see if they perform as expected.
Meets today’s internal and external security standards
Internal and external security standards vary from organization to organization, but based on testing, and as already described in this report, SQL compliance manager does indeed have the ability to audit virtually any SQL Server activity. Because of this, the implication is that it can indeed meet virtually all internal or external security standards, given the limitations of SQL Server itself.
Ensure continuous compliance
Unlike some other SQL Server auditing products on the market, which are designed more for running on an on-demand basis, SQL compliance manager is designed for continuous compliance checking. Built into SQL compliance manager are features that make continuous auditing easy. For example, it has a relatively small hit on performance, and it includes a built-in archiving ability to help ensure that data is retained, but in such a way as not to interfere with day-to-day auditing or production.
Fits your environment, no matter how simple or complex and meets enterprise-scale SQL Server implementations
Simple Environments:
Let’s say, for example, that SQL Server runs on a shared server in a small doctor’s office. Assuming the server is not already experiencing bottlenecks, adding SQL compliance manager to this shared server is very doable.
Another small variation of this would be for small shops that have a small number of servers. Assuming that one of the SQL Servers is not experiencing bottlenecks, SQL compliance manager can be added to one of the SQL Servers, and the SQLcompliance Agent installed on the other SQL Servers. As in the previous case, no additional hardware is required.
Now let’s assume that you have a middle-sized shop with 10-25 SQL Servers, maybe even mixed in with a SQL Server cluster or two (this is like the shop I work in). In this case, because of the greater demands required of many servers, a single dedicated server would be required to run SQL compliance manager. In addition, clustering is fully supported, so this is not an issue.
Complex Environments:
SQL-Server-Performance.Com does not have the ability to test large environments. But based on the testing we performed, and based on the design of their architecture, it appears that SQL compliance manager can scale; but, how much a single, dedicated SQL compliance manager Collection Server can scale we are unable to determine. What also complicates this is that not only are the number of SQL Servers monitored a factor in scalability, but so are the number of databases on each server, the activity of each database, the amount of auditing being used (this can vary), and the location of the servers.
All-inclusive package
SQL compliance manager is licensed on a per SQL Server instance, that’s it. There are no other products or add-ons to buy, and you don’t have to worry about the number of CPUs running on the server, number of users using the product, and so on. The licensing scheme is very simple and easy to understand. Other than SQL Server, which you already have, you don’t have to purchase anything else to use SQL compliance manager.
Supports clustering
Unlike many products on the market, SQL compliance manager is fully cluster-aware, and even includes its own setup specifically for clustering.
Rapid deployment
For most DBAs, installing, configuring, and deploying SQL compliance manager will be fast. This has to do with how the product is architected and with the installation process. Based on my experience, the most time consuming part is deciding what you want to audit. To make this easy, there are “default” auditing options that can be used, which is indeed quick to install and setup. But, if the default audit settings don’t meet your needs, you will need to take a little bit of time to customize the auditing to your specific requirements.
Low overhead data collection
One of the biggest problems with some of the other SQL Server auditing tools is that they incur a lot of overhead when collecting SQL Server activity. This is especially true for those audit tools that use triggers to collect data.
SQL compliance manager uses SQL Server traces to gather auditing activity, which greatly reduces the overhead it incurs when auditing SQL Server instances. Essentially, each SQL Server has an installed agent that takes from 12-25MB of RAM and about 21+ MB of disk space on each audited SQL Server. Comparatively speaking, these are small numbers. Trace data is stored temporarily on the local server and moved at regular intervals to a collection server, where it is processed. Because of this, the impact of the auditing is relatively minimal, and more than a decent tradeoff occurs between performance overhead and the need to audit database activity.
Runs outside and separate from SQL Server, does not modify SQL Server files or services
SQL compliance manager runs outside of SQL Server as a service and does not directly interact with SQL Server, instead listening to traffic between the server and clients using the same extended stored procedures as SQL Profiler does.
Central Repository of audit data
In most cases, except for very small installations, you will want to install SQL compliance manager on a dedicated server. This is also where you will store the data collected from multiple SQL Servers. This dedicated SQL Server will need to have enough storage capacity to store the audited data you collect. The capacity needed will depend on the number of databases being audited, their level of activity, and the amount of audited data being collected. As collected data grows, SQL compliance manager has the ability to archive data, and this data can be moved to tape or other locations as needed.
Central Management Console, including real-time status and detailed logging of change activity
One of the components of SQL compliance manager is a central console that allows the administrator to perform all necessary activities. All configuration is done here, and all logging activity can be viewed here. In addition, canned reports can be started here. Reports are also available directly through Reporting Services.
If required, the console can be installed on multiple desktops, making it easy for administrators to access and monitor SQL compliance manager. The console is a GUI tool and is not HTML-based.
Pre-defined compliance reports
SQL Compliance Manager includes a wide variety of pre-defined reports that cover the bulk of reporting needs. But based on my experience with auditors, odds are that you still will need to create some custom reports. SQL compliance manager provides the ability to create customized reports to meet your specific needs.
Secure ad-hoc queries for auditors
All reports, pre-defined or custom, can be configured for security through the use of SQL compliance manager or Reporting Services. Of course, to do this, the DBA, or a developer, must know how to create custom reports using Reporting Services.
Leverage powerful reporting and forensic analytics
All the audited data is stored in a repository database, and either through pre-defined or custom reports, you may document exactly what has happened (given your chosen level of auditing). It may take some skillful report designing to get everything an auditor may claim he or she needs.
Efficient, secure data archival
Audited data can add up fast. Because of this, you will want to implement a plan to archive critical data for later use, or groom (remove) data you no longer need. Both are easy to do with the built-in features of the Management Console. You can choose to do this manually, or automatically, with many different options to best suit your needs. Before you can audit data, SQL compliance manager verifies the integrity of the data, which helps to ensure that the data is good and has not been tampered with.