You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

SQL Compliance Manager consists of a light, unobtrusive architecture that easily runs in your SQL Server environment with minimal configuration. All SQL CM components run outside and separate from SQL Server processes. SQL CM does not add to or modify any of your native SQL Server files or services.

Architecture

SQL Compliance Manager provides a robust, easy-to-use SQL Server audit and reporting solution. Behind a friendly user interface, SQL CM offers a unique, loosely coupled architecture that is both flexible and extremely powerful. SQL CM fits your environment, no matter how simple or complex.

The following diagram illustrates the components of the SQL Compliance Manager architecture.

Management Console

The Management Console is a centralized, intuitive user interface that allows you to easily and quickly modify audit settings, monitor events, and report on audit data. This user interface also provides the following information:

  • Real-time status of audited SQL Server instances
  • SQL Server login permissions
  • Detailed logging of change activity
  • Track and prove continual compliance using reports

Repository databases

The SQL Compliance Manager Repository is the central repository that tracks:

  • SQLcompliance configurations, such as audit settings, server registrations, and console security
  • Audited SQL Server events
  • Alert messages
  • SQLcompliance Agent activity

The Repository consists of the following databases. For more information, see How auditing works.

Repository Database NameDescription
SQLcomplianceStores alert messages, audit settings, SQLcompliance Agent events, Activity Report Card statistics, and other SQL Compliance Manager configurations.
SQLcompliance.ProcessingStores processing event data received from the SQLcompliance Agent.
SQLcompliance.InstanceStores processed events collected from a registered instance.
SQLcompliance.Instance_Time_PartitionStores archived events collected from a registered instance.

Collection Server

The Collection Server processes trace files received from the SQLcompliance Agent, stores audit data in the events and archive databases, and sends audit setting updates to the SQLcompliance Agent. The Collection Server runs under the Collection Service account. By default, the Collection Server communicates with the Repository every five minutes (heartbeat) to write processed audit data to the event databases associated with the registered SQL Server instances.

SQLcompliance Agent

The SQLcompliance Agent gathers SQL Server events written to the SQL trace, caching these audited events in trace files. By default, the SQLcompliance Agent calls the Collection Server every five minutes (heartbeat) to receive audit setting updates, and sends trace files for processing every two minutes. The SQLcompliance Agent runs under the SQLcompliance Agent Service account. For more information, see How the SQLcompliance Agent works.

Sensitive Column auditing is supported by SQLcompliance Agent 3.5 or later. To use this feature, please ensure you upgrade your agent to at least version 3.5.

Command line interface

The command line interface (CLI) provides an interface for third-party tools so you can automate and schedule regular tasks, such as audit data archival and grooming, and perform diagnostic tasks. You can also perform integrity checks through the CLI.
The CLI supports the following operations.

CLI OperationsDescription
agentsettingsLists the settings for the SQLcompliance Agent running on a specific SQL Server instance.
archiveArchives audited events collected for registered SQL Server instances.
auditdatabaseEnables auditing on a new database, allowing to specify either a regulation guideline or a custom audit template.
checkintegrityVerifies the integrity of audited events collected for a specific registered SQL Server instance.
collectCollects trace data from the agent.
groomDeletes audited events older than a specified age.
helpDisplays the CLI Help.
listtriggersLists the CLR triggers for DML auditing on a specific registered SQL Server instance.
registerinstanceRegisters a new SQL Server instance and applies audit settings.
removetriggersRemoves the CLR triggers from the subscriber table on the specific SQL Server instance.
serversettingsLists the settings for the Collection Server.
timezonesDisplays the time zones recognized by the computer hosting the Collection Server.
updateindexApplies optimized Repository index configurations to existing events and archive databases.

Trace files and the trace directory

Trace files contain audited SQL Server events collected by the SQLcompliance Agent. The SQLcompliance Agent stores these temporary files in a secure directory on the audited SQL Server instance. When the set directory size threshold is reached, the SQLcompliance Agent stops the SQL trace until the trace files are sent to the Collection Server for processing. When the set file size threshold is met, the trace file is cycled. You can configure the SQLcompliance Agent trace file directory location as well as how the SQLcompliance Agent manages these files, such as how often the agent sends trace files to the Collection Server. For more information, see How the SQLcompliance Agent works.

 

SQL Compliance Manager audits all activity on your server. Learn more > >
  • No labels