Features Tested
In this section, we take a look at the key features the software publisher claims the product does. Throughout this review, we verify each one to see if the feature does exist, and if it performs as expected.
- Meets today’s internal and external security standards:
- Sarbanes-Oxley.
- GLBA.
- HIPPA.
- Basel II.
- Ensure continuous compliance:
- SELECT statements.
- INSERTS, UPDATES, DELETES.
- Schema changes.
- Fits your environment, no matter how simple or complex.
- Meets enterprise-scale SQL Server implementations.
- All-inclusive package.
- Supports clustering.
- Rapid deployment.
- Low overhead data collection.
- Runs outside and separate from SQL Server, does not modify SQL Server files or services.
- Central Repository of audit data.
- Central Management Console:
- Real-time status.
- Detailed logging of change activity.
- Pre-defined compliance reports.
- Secure ad-hoc queries for auditors.
- Leverage powerful reporting and forensic analytics.
- Efficient, secure data archival.
- Protection of the integrity of audited data.
- Self-auditing.
- Robust and easy-to-use.
Before we look at all of the above features, one by one, let’s learn a little more about how SQL compliance manager works.
Architecture
With any product that is designed to be scalable, the underlying architecture is very important. In this section, we take a high-level look at how SQL compliance manager is designed and works.
SQL compliance manager can be divided into three major parts, each with multiple components:
- Management Console: Used to manage SQL compliance manager.
- SQL Compliance Agent: Runs on each audited server to collect audit events.
- SQL compliance manager Server: A SQL Server that runs the SQL compliance manager Collection Service and stores the Repository databases.
Management Console
The Management Console is the GUI interface used to administer all of SQL compliance manager. It provides real-time status of all SQL Server instances, along with detailed logging of any change activity.
Built into the Management Console are also many predefined reports. But for ad-hoc reporting, or custom reports, you (or report writers) will need to have access to SQL Server Reporting Services development tools. For ease of administration and report development, you may want both the Management Console and Reporting Services Report Developer tool on all DBA workstations, in addition to the SQL Compliance Server.
When the Management Console is used to administer SQL compliance manager, it communicates with the SQL compliance manager server and its assorted databases. The Management Console is also used to install the SQL Compliance Agent on SQL Server instances to be audited.
SQLcompliance Agent
On each SQL Server that you want to audit, you will need to install the SQLcompliance Agent, which is a lightweight service that runs in the background, collecting SQL Server trace events, which are stored in a temporary folder on the server. Every two minutes, trace events from the temporary folder are compressed and moved to the SQL compliance manager server for processing.
SQL compliance manager Collection Server
This is where most of the work occurs for SQL compliance manager. Generally running on a SQL Server box dedicated to SQL compliance manager, this server includes the Collection Server service, multiple databases, and temporary trace folders.
The Collection Service receives trace files from the SQLcompliance Agents located on the audited SQL Servers. When they are first received, they are stored in a compressed form in a temporary folder on the server.
Next, the Collection Server takes the compressed raw trace files from the temporary folder and moves them into the SQL Compliance Processing database where the raw data is converted into a form better suited for auditing.
Next, the processed data is sent to an Event Database. An Event database is created for each instance of SQL Server that is audited, and is used to store all of the audited events for that instance.
Also located on the Collection Server is the SQLcompliance database, which is used to store the application’s alert messages, configuration information, and more. The Management Console connects to this database for most administrative functions.
As more data is collected, SQL compliance manager has a way to archive data from the Event Databases. Archived databases can reside on the same Collection Server, or be stored anywhere that is most convenient.
Implementation Possibilities
In the above example, components of SQL compliance manager are located on multiple servers. This is not a requirement; it was done for ease of explanation. For small operations, all of these components, including the audited SQL Server instance(s) can be located on a single SQL Server.
In medium to large size operations, you will want to separate SQL compliance manager’s components so that you can scale out the application to meet your expected auditing load. The larger your load, the more dedicated servers you will want to spread out the workload. SQL compliance manager is designed to scale both up (larger hardware) and out (more hardware).
Installation and Configuration
Installing and configuring SQL compliance manager is a straightforward process that doesn’t take a lot of initial setup. Below are the key steps of installing and configuring SQL compliance manager on a SQL compliance manager Collection Server.