TRITON reporting database FAQs

Administering TRITON Databases | Web, Data, and Email Solutions | v8.2.x

Applies to:

In this topic

TRITON AP-WEB and Web Filter & Security, v8.2.x

TRITON AP-DATA, v8.2.x

TRITON AP-EMAIL, v8.2.x

Which database tools are required or used?, page 24

Which permissions are required?, page 24

Which database jobs are run?, page 25

How does the installer set up each database?, page 26

How big should the database partitions be?, page 27

How many partitions can be accessed at the same time?, page 27

How do I configure partition rollover?, page 27

What if I need more partitions to run reports?, page 28

Do TRITON databases use named instances?, page 28

Can TRITON reporting databases be hosted in a SQL Server cluster?, page 28

Which database tools are required or used?

TRITON reporting components connect to the SQL Server database engine as clients and perform standard Transact-SQL commands and stored procedures.

TRITON AP-WEB and TRITON AP-EMAIL may use 2 database utilities:

bcp to use bulk insertion for adding logs to the database.

osql to run SQL scripts during Log Database installation.

Which permissions are required?

During TRITON AP-DATA installation, modification, or repair, the account used for database creation and access needs sysadmin server role membership. Also, Backup database permission on the master database is required for installation only. After installation, the account privileges can be reduced to the db_owner of the newly created databases, and no access to any other user database except system databases such as master, tempdb, and model is required. Additionally the dbcreator server role should be granted to enable backup and restore functionality.

If you're using SQL Server 2012 Standard or Enterprise, 2008, or 2008 R2 to install the Web Log Server and Email Log Server, the user account that owns the TRITON database must:

Be a member of the dbcreator server role

In the msdb database:

Have membership in the db_datareader role

Have membership in one of the following roles:

SQLAgentUser Role

SQLAgentReader Role

SQLAgentOperator Role

For SQL Server 2008 R2 Express, the user account requires the sysadmin server role.

Which database jobs are run?

The following database jobs are installed with the Web Log Database and Email Log Database:

The Extract, Transform, and Load (ETL) job runs continually, receiving data from Log Server, processing it, and then inserting it into the partition database. The ETL job must be running to process log records into the Log Database.

The database maintenance job performs database maintenance tasks. This job runs nightly, by default.

ETL jobs are run, then re-run 10 seconds after they finish for SQL Server Standard and Enterprise. For SQL Server Express, 60 seconds elapse between completion of one job and start of the next.

Maintenance jobs are run once every night by default. The jobs are run automatically.

The Web Log Database also installs the following jobs:

The Internet browse time (IBT) job analyzes data and calculates browse time for each user. The IBT database job is resource intensive, requiring significant server resources. This job runs nightly.

When trend data retention is enabled, the trend job uses daily trend data created by the ETL job to update weekly, monthly, and yearly trend records for use in presentation reports. This job runs nightly.

Even when trend data retention is disabled, the trend job processes data from the threats (AMT) partition to provide trend data on the Threats dashboard.

The Advanced Malware Threat (AMT) ETL job receives, processes, and inserts data into the threats partition database. Only log records that include a severity ranking are recorded in the threats partition. Data from this partition is used to populate the Threats dashboard.

When configuring the start time for the (Web and Email) maintenance job and the (Web) Internet browse time job, consider system resources, and IT maintenance tasks and their duration. These jobs can be resource intensive and time consuming, so they can have a negative impact on logging and reporting performance.

Both Log Databases require either the SQL Server Agent service (SQL Server Standard or Enterprise) or Service Broker (SQL Server Express) to run database jobs.

How does the installer set up each database?

The TRITON reporting databases should allow TCP and trusted-mode connections from the TRITON management server, Email Log Server, and Web Log Server, as well as from the any email-capable appliance (Email or Web and Email mode).

Web Log Database

By default, the web protection Log Database includes one catalog database, one standard logging partition database, and one threats (AMT) partition database. Typically, multiple standard logging partition databases are created as Internet activity is recorded.

The catalog database provides a single connection point for the various components that need to access the Log Database: Log Server, presentation reports, and investigative reports configuration. It also contains definitions for the following:

Category names

Risk classes

Users

User-to-group mapping

Database job information

The catalog database also maintains a list of all the database partitions.

Standard logging partitions store the individual log records of Internet activity. New partitions are created based on size (3 or 5 GB, by default) or date interval.

The threats (AMT) partition stores information about requests that have been assigned a severity level, and is used to populate the Threats dashboard.

Email Log Database

The Email Log Database includes one catalog database and (initially) a standard logging partition.

The catalog database provides a single connection point for the various components that need to access the Log Database: Log Server, the TRITON AP-EMAIL quarantine daemon, and the Email module of TRITON Manager (presentation reports, dashboard, log database configuration page). It also includes definitions for the following:

TRITON AP-EMAIL actions

Mail direction

Message type

DLP severity level

Email appliance mapping

The catalog database also maintains a list of all the database partitions.

Database partitions store the individual log records, including connection log, message log, policy log, delivery log, status log, and hybrid service status log. New partitions are created based on size (5 GB, by default) or date interval.

How big should the database partitions be?

For Web, see Partitioning, page 21.

For Email, see Partitioning, page 22.

For Data, see Rate of network and endpoint incidents, page 19.

How many partitions can be accessed at the same time?

TRITON AP-DATA maintains incident partitions independently of the database engine, based on quarters (3-month periods). By default, SQL Server Express maintains 8 partitions are online simultaneously, and other SQL Server editions maintain 12 partitions online. You can choose to move any number of partitions online simultaneously as long as your disk space and SQL Server database permit it.

With Web and Email solutions, you can access all enabled partitions.

How do I configure partition rollover?

With Web and Email solutions, partition rollover can occur automatically when partitions reach a specified size or (SQL Server Standard or Enterprise) date.

When partition rollover is based on size, all log records are inserted into the most recent active partition that satisfies the size rule. When the partition reaches the designated maximum size, a new partition is created.

When partition rollover is based on date, new partitions are created according to the established cycle. For example, if the rollover option is monthly, a new partition is created as soon as any records are received for the new month. Log records are inserted into the appropriate partition based on date.

Partition rollover can also be initiated manually.

For information about configuring automatic or manual rollover, see:

"Configuring database partition options" in the Administrator Help for your web protection solution.

"Configuring Log Database options" in TRITON AP-EMAIL Administrator Help.

For Data solutions, partition rollover is configured on the Settings > General > Archive Partitions page in the Data module of TRITON Manager. Here, you configure when to create an archive partition and when to restore it. For instructions, refer to "Incident partitions" in the TRITON AP-DATA Help.

What if I need more partitions to run reports?

For Web and Email solutions, the available Log Database partitions, both enabled and disabled, are listed on the Settings > Reporting > Log Database page in the respective Web and Email modules of TRITON Manager. To include data from a disabled partition, first enable it, then run the report. You can use this page to disable the partition again once you have retrieved the desired data.

For TRITON AP-DATA, when you want to run a report and some or all of the data you want is stored in an offline partition, you must bring that partition online, or the generated report will not contain all the data you need.

Do TRITON databases use named instances?

If you are using SQL Server Standard or Enterprise to host your TRITON reporting databases:

The web protection Log Database can be hosted by one instance, and the Email Log Database and Data Incident and Configuration Database can be hosted elsewhere (on another server or instance).

All 3 reporting databases can be hosted by the same SQL Server instance.

Can TRITON reporting databases be hosted in a SQL Server cluster?

If your organization uses a SQL Server cluster to provide failover for your database servers, the TRITON reporting databases can be hosted by the cluster if:

A virtual IP address is assigned to the cluster.

The data managed by SQL Server is housed on a reliable shared disk array.

When you install reporting components in a network that uses a SQL Server cluster, it is imperative that the cluster's virtual IP address is used to configure the reporting database connection. When this is done, reporting data is sent to SQL Server via the virtual IP address.

If you configure TRITON reporting components (like Web and Email Log Server) to use the IP address of an individual node in the cluster, they cannot take advantage of the failover protection of the cluster.

If the configured node becomes unavailable, reporting components cannot process data into the reporting database.

For Web and Email solutions, log cache files continue to be saved on the Log Server machine. These files can build up quickly and fill the disk, causing additional problems.

When failover occurs, reporting components must wait briefly while the secondary SQL Server is made primary. When SQL Server begins accepting data over the virtual IP address again, reporting data is once again sent successfully.

This pause in recording data occurs both when failover occurs in a SQL Server cluster and when a standalone SQL Server installation fails and is later brought back online. Any records that were actively being processed into the reporting database when the primary SQL Server fails are lost.

For Web and Email solutions, if you are using BCP log insertion, the loss is generally a full batch (log cache file) of filtering records.

With ODBC log insertion, fewer records may be lost.