Managing Audit Store Databases

During the initial deployment, your installation only has one audit store database. As you begin collecting audit data, however, that database can quickly increase in size and degrade performance. Over time, an installation typically requires several Microsoft SQL Server databases to store the data being captured and historical records of session activity, login and role change events, and other information. As part of managing an installation, you must manage these databases to prevent overloading any one database and to avoid corrupting or losing data that you want to keep.

One of the biggest challenges in preparing and managing Microsoft SQL Server databases for storing audit data is that it is difficult to estimate the level of activity and how much data will need to be stored. There are several factors to consider that affect how you configure Microsoft SQL Server databases for auditing data, including the recovery method, memory allocation, and your backup and archiving policies.

The sections below provide guidelines for sizing and managing the Microsoft SQL Server databases you use for audit data. For more complete information about managing and configuring SQL Server, however, you should refer to your Microsoft SQL Server documentation.

Selecting a recovery model

Standard backup and restore procedures come in three recovery models:

Simple—The Simple recovery model allows high-performance bulk copy operations, minimizes the disk space required, and requires the least administration. The Simple Recovery model does not provide transaction log backups, so you can only recover data to the point of the most recent full or differential backup. The default recovery model is Simple, but is not appropriate in cases where the loss of recent changes is not acceptable.
Full—The Full recovery model has no work-loss exposure, limits log loss to changes since the most recent log backup, and provides recovery to an arbitrary time point. However, the Full recovery model uses much more disk space.
Bulk-logged—The Bulk-logged recovery model provides higher performance and minimizes the log space used by disk-intensive operations, such as create index or bulk copy. With the Bulk-logged recovery model, you can only recover data to the point of the most recent full or differential backup. However, because most databases undergo periods of bulk loading or index creation, you can switch between Bulk-logged and Full recovery models to minimize the disk space used to log bulk operations.

When a database is created, it has the same recovery model as the model database. Although the Simple recovery model is the default, the Full and Bulk-Logged recovery models provide the greatest protection for data, and the Full recovery model provides the most flexibility for recovering databases to an earlier point in time. To change the recovery model for a database, use the ALTER DATABASE statement with a RECOVERY clause.

Regardless of the recovery model you choose, you should keep in mind that backup, restore, and archive operations involve heavy disk I/O activity. You should schedule these operations to take place in off-peak hours. If you use the Simple recovery model, you should set the backup schedule long enough to prevent backup operations from affecting production work, but short enough to prevent the loss of significant amounts of data.

Configuring the Maximum Memory for Audit Store Databases

Because Microsoft SQL Server uses physical memory to hold database information for fast query results, you should use a dedicated instance to store auditing data. Because SQL Server dynamically acquires memory whenever it needs it until it reaches the maximum server memory you have configured, you should set constraints on how much physical memory it should be allowed to consume.

The maximum server memory (max server memory) setting controls the maximum amount of physical memory that can be consumed by the Microsoft SQL Server buffer pool. The default value for this setting is such a high number that the default maximum server memory is virtually unlimited. Because of this default value, SQL Server will try to consume as much memory as possible to improve query performance by caching data in memory.

Processes that run outside SQL Server, such as operating system processes, thread stacks, socket connections and Common Language Runtime (CLR) stored procedures are not allowed to use the memory allocated to the Microsoft SQL Server buffer pool. Because those other processes can only use the remaining available memory, they might not have enough physical memory to perform their operations. In most casts, the lack of physical memory forces the operating system to read and write to disk frequently and reduces overall performance.

To prevent Microsoft SQL Server from consuming too much memory, you can use the following formula to determine the recommended maximum server memory:

Reserve 4GB from the first 16GB of RAM and then 1GB from each additional 8GB of RAM for the operating system and other applications.
Configure the remaining memory as the maximum server memory allocated for the Microsoft SQL Server buffer pool.

For example, if the computer hosting the Microsoft SQL Server instance has 32GB of total physical memory, you would reserve 4GB (from first 16 GB) + 1GB (from next 8 GB) + 1 GB (from next 8 GB) for the operating system, then set the Maximum server memory for Microsoft SQL server to 26GB (32GB – 4GB – 1GB – 1GB = 26).

For more information about how to configure Microsoft SQL Server maximum memory setting and other memory options, see the following Microsoft article:

http://msdn.microsoft.com/en-us/librms178067(v=sql.105).aspxary/ms178067(v=sql.105).aspx

You should configure the maximum memory allowed for the Microsoft SQL Server instances hosting audit store databases and the management database. However, this setting is especially important to configure on the Microsoft SQL Server instance hosting the active audit store database.

Using Transact-SQL to Configure Minimum and Maximum Memory

You can control the minimum and maximum memory that the SQL Server buffer manager uses by issuing Transact-SQL commands. For example:

sp_configure ‘show advanced options’, 1
reconfigure
go
sp_configure ‘min server memory’, 60
reconfigure
go
sp_configure ‘max server memory’, 100
reconfigure
go

For more information about configuring SQL Server and setting minimum and maximum server memory using T-SQL, see https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-2017

Estimating Database Requirements Based on the Data You Collect

To determine how auditing will affect database capacity, you should monitor a pilot deployment of 20 to 25 agents with representative activity to see how much data is produced daily. For example, some audited computers might have few interactive user sessions or only short periods of activity. Other audited computers might have many interactive user sessions or long sessions of activity on average.

During the pilot deployment, you want to the following information:

How many interactive user sessions occur daily on each computer?
How long do sessions last on average?
What are the activities being captured, and what is the average size of each session being captured?
How long do you need to store the captured data to balance performance and storage?
What is the data retention period for audited data?

From the information you collect in the pilot deployment and the data retention policy for your organization, you can estimate the database size using the following guideline:

For example, if an average session generated 100 KB in the database and the installation had 250 agents, 10 sessions per agent, and a six-month retention period (about 130 working days), the storage requirement for the audit store database would be 36.9 GB:

250 agents x 10 sessions/agent each day x 100 KB/session x 130 days = 32,500,000 KB

The following table shows examples of the data storage requirement in an installation with Windows agents, typical levels of activity with an average of one session per day on each audited computer, and the recovery mode set to Simple:

Agents	Average session length	Average session size	Daily	Weekly	6 Months
100	20 minutes	806 KB - low activity	79 MB	394 MB	10 GB
50	25 minutes	11.56 MB - high activity	578 MB	2.81 GB	73.36 GB
100	20 minutes	9.05 MB - high activity	905 MB	4.42 GB	115 GB

In this example, an installation with 100 Windows agents with low activity would require approximately 10 GB for the audit store database to keep audit data for 6 months. An increase in the number of interactive sessions, session length, or average session size would increase the database storage required.

If SQL Server requires more space to accommodate the new data, it expands the database file immediately, which can cause degraded performance. To reduce the effect of database expansion on performance, allocate sufficient space to support database growth. In addition, monitor database space and when space is low, schedule a database expand operation for an off-peak time.

Reducing Color Depth to Decrease Disk Usage

If you enable video capture auditing of user activity for an installation, the color depth setting affects the size of sessions stored in the audit store database. Depending on whether you want higher quality video playback or lower disk consumption, you can modify this setting. The growth rate is linear as you increase or decrease the color depth.

Based on a simulation of user activity, changing the color depth from 16-bit to 8-bit reduces disk space by 42%. Changing the color depth from 32-bit to 16-bit reduces disk space by 34 to 39%. If you can accept the lower quality video playback, changing the color depth from 32-bit to 8-bit reduces disk space by 62 to 65%.

Using SQL Server availability groups with multi-subnet failover for audit store databases

If you add an audit store database to a SQL Server availability group that has multiple subnet failover functionality, the SQL Server that hosts the management database must be SQL Server 2012 or above. This restriction applies only to availability groups that have multi-subnet failover configured.

For details about availability group multi-subnet failovers, see https://msdn.microsoft.com/en-us/library/hh213417.aspx#SupportAgMultiSubnetFailover.

Adding New Audit Store Databases to an Installation

When you first set up an installation, you also create the first audit store and audit store database. By default, that first database is the active database. As you begin collecting audit data, you might want to add databases to the audit store to support a rolling data retention policy and to prevent any one database from becoming a bottleneck and degrading performance.

Only one database can be the active database in an audit store at any given time. The computer hosting the active database should be optimized for read/write performance. As you add databases, you can change the older database from active to attached. Attached databases are only used for querying stored information and can use lower cost storage options.

A single instance of Microsoft SQL Server can host multiple databases. Those databases can support different versions of the agent.

Audit store databases have the following characteristics:

A database can be active, attached, or detached.
Only one database can be actively receiving audit data from collectors.
A database cannot be detached while it is the active database.
A database that was previously the active database cannot again be the active database.
If a detached database contains parts of sessions presented to the Audit Analyzer, a warning is displayed when the auditor replays those sessions.

Rotating the Active Database

Database rotation is a management policy to help you control the size of the audit store database and the performance of database operations. There are several reasons to do database rotation:

It is more difficult to manage one large database than multiple small databases.
Performance is better with multiple small databases.
Backing up, restoring, archiving, and deleting data all take significantly more time if you work with one large database.
Database operations take very little time when you work with multiple small databases.

For audit and monitoring service, you can implement a database rotation policy by having the collector write data to a new database after a certain period of time. For example, the collector in site A writes data to the database siteA-2015-11 in November, then write data to database siteA-2015-12 in December and to the database siteA-2016-01 in January. By rotating from one active database to another, each database stays more compact and manageable.

Creating a New Database for Rotation

You can rotate from one active database to another at any time using the Audit Manager console.

To create a new database for rotation:

Open Audit Manager.
Expand the installation node, then expand Audit Stores and a specific audit store name.
Select Databases, right-click, then select Add Audit Store Database to create a new database.

For details on setting up the database, see Creating the first audit store database.
Select the Set as Active database option so collectors start writing to the newly created database.

You can also use Delinea application programming interfaces (APIs) to write a script that automates the database rotation process. For API details and sample code, see the Delinea SDK documentation.

Database Archiving

To implement periodic archiving, add a new active database, leave one or more previous databases attached, and take the oldest database off-line for archiving.

Queries During Rotation and Archiving

If the database backup program supports online backups, the Audit Analyzer can still query the database while the backup is in progress. However, the backup program may block updates to the session review status. If the backup program does not support online backup, the database will be offline until the backup is complete.

Database Backups

You can back up a database whether it is attached to the audit store or detached from the audit store.

Reattaching a Restored Backup of a Database

If you need to query sessions from an older database that is offline and detached, you can restore the database from a backup and reattach it to your auditing installation. You might need to do this is your auditing installation is large, you do frequent database rotation, and you don’t keep many databases attached and online.

Understand that after you restore a database from a backup, you need to fix a couple of settings in the database before you can reattach it to your auditing installation. During the backup operation, the database owner and trustworthy properties get set in such a way that prevents you from reattaching the database to your auditing installation unless you fix these properties.

To reattach a restored database to your auditing installation:

Run the following command to reset the database owner to [sa]:

ALTER AUTHORIZATION ON DATABASE::<db_name> TO [sa]
Run the following command to reset the trustworthy property:

ALTER DATABASE <db_name> SET TRUSTWORTHY ON

You can now reattach the database to your auditing installation.

Allowed Incoming Accounts

You can specify the accounts that are allowed to access the audit store database. By configuring these accounts, you can control which collector computers can connect to the audit store database and which management databases have access to the data stored in the audit store database.

Your account must have Manage SQL Login permission to configure the incoming accounts.

To configure allowed accounts:

Open Audit Manager.
Expand the installation node, then expand Audit Stores and select a specific audit store name.
Select a database under the audit store, right-click, then select Properties.
Click the Advanced tab.
Click Add to add a collector or management database account. For example:
Select an authentication type.
- If you select Windows authentication, you can browse to select a computer, user, or group to add.
- If you select SQL Server authentication, you can select an existing SQL Server login or create a new login.

Connections should use Windows authentication whenever possible. However, computers in an untrusted forest cannot connect to an audit management database using Windows authentication. To allow connections from an untrusted forest, add a SQL Server login account as the incoming account for the management database.

Detecting Data Tampering and Verifying Session Integrity

When you create your audit store database, you have the option to enable data integrity checking. Data integrity checking provides the ability to detect if auditing data has been tampered with.

For example, data integrity checking can detect if a user who has write privileges over the Audit Store database directly manipulates the audited session data by making a direct connection to the Microsoft SQL Server database.

Session data that is stored in audit store databases is typically accessible to database administrators and/or database owners in an unrestricted fashion. For these users with write privileges on the audit store database, it’s fairly easy to tamper with data in such a way that it can help manipulate the outcome of an AQL query.

For example, someone could change the searchable tags in such a way so that the session is never returned by a query. Or, someone could remove suspicious activity from a recorded session, such as by changing the list of commands that are executed or changing the command output.

Data integrity checking cannot detect tampering if a database administrator deletes an entire session or database. Also, data integrity checking is not yet available for audit trail events.

Once you enable data integrity checking, you can do the following:

Use the Audit Analyzer console or a PowerShell cmdlet to check the integrity of audited sessions.
Determine if any data in the following tables has been modified and where it was modified:
- Session
- RawData
- Command
- SyscallCommand
- SyscallFilemon
- WashData
- WashEvent
Determine if any database rows belonging to an audited session were permanently deleted.

If you have not enabled an audit store database for data integrity checking and you try to check session integrity in Audit Analyzer, an error message appears.