Applies to
Summary
This article provides recommended steps as a good practice for ApexSQL Audit usage and preparation deployment in a production environment.
Description
Audit policies are keen to identify processes for managing SQL server audit log after it has been stored, including:
Collecting, evaluating and reviewing SQL audit data
- Storing and disposing it
By careful planning, testing and deploying the SQL audit solution leads to enhanced control management and overall satisfactory experience when meeting the regulatory needs.
Permissions and requirements
First things first, it is recommended to check on Permissions and requirements before deployment to make sure that required permissions and minimum requirements for installing are properly achieved.
Installation
Since ApexSQL Audit is a centralized system which stores the audited data in the central repository database, it is essential to decide between one of the following installing approaches:
- Installing ApexSQL Audit directly in the production SQL Server
- Installing on a dedicated physical machine
- Or using a Virtual Machine as a dedicated server
In a larger business environment, it is recommended to use an approach with a dedicated server to avoid potential overheads in high SQL traffic frequency. Each direction has distinct cons and pros, so the choice is heavily dependent on available hardware resources and allocation, more detailed information can be found in the ApexSQL Audit installation recommendations, best practices and alternatives article.
Auditing filters and configuration
It is very important to determine what is required to be audited and therefore make a configuration that helps to easier evaluate and review the SQL audit trail. Understanding the following pillars can help in the process:
- Define the level of auditing. ApexSQL Audit allows tracking the activity on server, database and table (only DML statements) levels
-
Understand the auditing range. It is important to adjust the auditing filters in order to optimize the auditing process. ApexSQL Audit provides a vast range of capabilities to utilize on while configuring and easily find the sweet spot with the following:
-
High granularity of the auditing filters to further include or exclude certain SQL Operations, Logins, Applications and database objects
Server auditing configuration:
Database auditing configuration:
-
Pre-determined and compliance-ready filter templates:
-
Filtering type with Simple and Advanced filters
Simple filters are an easy and fast approach to define filter criteria by simple checkbox selection inside the server and database level configurations:
Advanced filters, on the other hand, are harder to use; hence it provides unlimited granularity and precision when conditions are defined via logical expressions:
-
Customization on SQL text data stored
Approximately typical audited event weighs around 1-1.5KB, which is valuable information on estimating the storage use, and 1 million events are roughly equal to 1GB in size. The data is being heavily increased with the length of SQL statements; therefore custom setup is available on both server and database level:
-
Data collection technology
Depending on the SQL Server version, configuration to collect the audited data based on the certain auditing technology can also come into play and choose between Extended Events, SQL Audit and Trace. Even though Trace is the most mature technology on the market, the Extended Events and SQL Audit have shown less performance impact while collecting data and are good to be used in an environment with more aggressive transaction flow:
-
-
Access control and SQL audit report
Auditing configuration and data trail are highly sensitive, and it is recommended to allow or deny access to the users or groups inside the business organization. Application-level security is introduced inside the ApexSQL Audit through the Manage Accounts feature and allows user to access the application graphical interface with a different set of permissions based on application roles type:
- Administrator – has unlimited access to entire application features
- Power user – can manage the majority of the application features except user management
- Reader – can manage audit report tasks, with no access to make any auditing configuration changes
With a certain degree of access, every user that is green-listed to use the application can manage it from any client host inside the network by installing remote application GUI. Remote access is a fine asset for reporting personas to create and schedule report tasks.
-
Alerting
ApexSQL Audit helps identifying any suspicious and high-risk actions and notify on the occurrence for an audited SQL Server or Database
Besides system alerts that come as a built-in solution to help achieving good performances with ApexSQL Audit, one can easily create an alert with a custom message to be stored in the application history logs, windows event log or even been sent as an e-mail alert notification
-
Archive and data retention
Archiving is a great mechanism to keep data organized. The amount of collected data and security requirements will help to determine the most appropriate time interval for archiving data
It is recommended to perform archiving jobs at the “quiet” times, so the scheduled archiving will lead to a successful experience that also enables storing the raw data files on the dedicated drives and keep your storage clean and organized
Summary
SQL Server and database auditing is environment specific and depended on SQL traffic, SQL server audit data stream and hardware configuration it is recommended to perform phased deployment cycles.
The phased deployment should start with installing ApexSQL Audit central repository and configuring auditing for servers in groups of several per cycle. After each configuration, perform the spot-checks for any hardware utilization.
The main goal is to decrease the number of unwanted audited events/operations and therefore lower the number of resources consumed in the production environment; you can learn how to achieve high quality auditing trail with the minimum number of audited events.
This guide is oriented to help through the thought process during the planning stage for implementing auditing on SQL Servers and help find the sweet spot with this handsfree SQL server auditing solution.
