Understanding TMG Logging (Part 2)

If you would like to read the other parts in this article series please go to:

Introduction

In part 1 of our series of articles on TMG logging, we discussed the key uses of firewall logs and some of the improvements that were incorporated into the logging function in TMG, including new fields. In this, part 2, we’re going to take a look at logging options, disk space considerations and how the TMG firewall works with various forms of SQL.

TMG firewall logging provides many options you need to understand so that you can configure them to do what you want them to do. First, let’s look at a problem that many folks seem to encounter right off the bat. When you start configuring logging, if you choose any log destination other than SQL Express, you will see the dialog box as shown in the figure below.

Figure 1

You see this because the SQL Reporting Services instance used by the TMG firewall for report generation only operates against the on-box SQL Server Express instance.

Now let’s take a closer look at the logs. The Firewall service and Web proxy logs have many settings in common:

Log type – The TMG firewall can be configured to send log data to SQL server or a text file.
Log destination – When the TMG firewall writes to a SQL instance, the TMG firewall can send log data to an SQL instance on the local computer or a remote SQL instance.
Log format – When the TMG firewall writes to text logs, you can configure the firewall to write the logs to ISA or W3C formatted logs.
Log file maintenance – When the TMG firewall is using local-host SQL or text logging, the firewall can manage log retention and disk space usage.
Enable Logging – The TMG firewall can be configured to write no log data at all.

Now let’s go into how to set it all up. Perform the following steps to configure TMG logging:

1. Open the TMG management console.

2. Expand Forefront TMG [ArrayName].

3. Select Logs And Reports.

4. In the right pane of the console, select Configure Firewall Logging as shown in the figure below. Because the Firewall and Web proxy logging options are the same except for the log fields, we’ll use only the Firewall log for the following demonstration. The process is the same.

Figure 2

The figure below shows the default options for the Firewall and Web proxy logs.

Figure 3

There are essentially three options on this tab:

SQL Server Express Database (on local server) – This option configures the TMG firewall to use an SQL Server 2008 Express instance on the local computer. In practice, this is identical to logging to an SQL server except that only the local TMG firewall may log into this database. The reason for this has to do with SQL Server Express configuration and TMG policies. SQL Server Express is configured by the TMG firewall installer so that it only accepts shared memory connections as shown in the figure below. This configuration, along with the TMG firewall’s default firewall policy disallows SQL connections to the TMG computer. You can get to the SQL Server Configuration Manager by going through All Programs, Microsoft SQL Server 2008, Configuration Tools, SQL Server Configuration Manager.

Figure 4

SQL Database – This option sets the TMG firewall to log all data to a SQL Server instance on a remote server or the local computer. This is identical to logging onto a local SQL Express instance except that TMG no longer manages disk space consumption and the network connection becomes an important factor in logging.
File – When you select this option, it configures the TMG firewall to use text-file logging in one of two formats. What you need to know about the differences between file-based logging vs. SQL-based logging include:
- You can compress text logs, and
- Text logs consume the least system resources; however,
- You cannot do historical queries in the log viewer if you use text logging
Enable Logging For This Service – This toggles logging for the firewall and Web proxy services. You would typically disable logging to help isolate performance problems. It’s important to note that any log data that was discarded while logging is disabled and cannot be recovered by any means.

The figure below shows the default settings in the Options dialog box for log files. The defaults are the same for SQL Express and local text logging with the only difference being file compression can’t be enabled for SQL logging.

Figure 5

Now let’s examine these options in a little more detail:

Logs folder – This option defines the location of the log files. The default selection of the logs folder places the database files in the %ProgramFiles%\Microsoft ISA Server\ISALogs folder, where %ProgramFiles% is typically found as C:\Program Files.
This folder (enter the full path) – This option allows you to select any location on the TMG computer for the database files. If you choose this option, the destination folder specified in this option must exist on every server in the TMG array.
Limit total size of log files (GB) – This option allows you to define the maximum space in gigabytes (GB) that may be used by all of the log files that are used by this service (Firewall or Web proxy). The TMG firewall will delete old logs starting with the oldest database files when the total size of the active log files exceeds 80 percent of this value.
Maintain free disk space (MB) – This allows you to specify the minimum space on the drive in megabytes (MB) where the TMG firewall will start to delete old logs related to this service. It’s a good idea to keep a close watch on your logs so that you will understand what limits are appropriate for your environment.
Maintain log storage limits by (deleting old log files as necessary) – This option configures the TMG firewall to delete log files starting with the oldest logs. It’s important to understand that the TMG firewall queries SQL Server Express for the database file list, and if any of the database files have been disconnected from SQL Server Express, they will not be accessible to the TMG firewall and will not be automatically deleted. Be very careful when you delete or reattach any databases that you have detached, so that TMG can manage the disk space properly.
Maintain log storage limits by (discarding new log entries) – This option configures the TMG firewall to stop logging for this service until the logging control point that triggered this action is corrected. Keep in mind that if you select this option, the TMG firewall will not log anything for this service until the trigger state is corrected. Most importantly, understand that any log data that is lost during this time is not recoverable. Only use this option during extreme service outages caused by logging failure.
Delete log files older than (days) – This option configures the TMG firewall to remove log files that are older than the number of days specified in this field. Even if the file and disk space constraints are within specified limits, if a log file date is older than specified in this option, the firewall will delete the file.

The SQL Express Database is the default logging option for the TMG firewall. Unlike with ISA firewall, you can’t remove this through Control Panel, Programs and Features (Control Panel, Add/Remove Programs in Windows Server 2003). One of the advantages of using SQL Server 2008 Express is that the SQL 2008 Reporting Services does not require Internet Information Service (IIS).

When you click the Options button, you are given several options where you can change a number of settings in SQL Express logging. SQL Server 2008 Express imposes limits on the database file sizes and the TMG firewall is designed to work within these levels when monitoring the file size and disk space consumption.

The files that are created by SQL Server Express are located in the destination folder that we mentioned earlier as ISALOG_YYYYMMDD_SVC_###.ldf and ISALOG_YYYYMMDD_SVC_###.mdf. Here’s what all that means:

YYYYMMDD is, as you probably figured out, the system date—for example, 20090606.
SVC is the TMG service log. FWS is used for the firewall service log and WEB is used for the Web proxy service log.
### is the file index for the TMG service for that day. This number increases by one for each new log file that is created during on the same day. If the traffic logs reach 80% of the maximum size of the file (4GB for SQL 2008 Express), the TMG firewall will force SQL Express to create a new log file.
.ldf is the SQL transaction log file for the log database file.
.mdf is the actual database file.

Note that SQL Server 2008 Express has a file size limit of 4 GB (in contrast to MSDE, which has a 2 GB file size limit). The default value of 8 GB for the maximum disk usage control may only allow two active log files if your TMG Server is dealing with a high daily traffic load. Look at your firewall’s disk space usage for logging to determine whether you will need to increase this value.

Summary

In this article, we looked at some of the log file settings that are available to you when configuring the TMG firewall. Then we looked at the database logging capabilities. In the next article in this series, we’ll take a closer look at SQL configuration and some of interesting settings that are related to logging in the TMG firewall. See you then! –Deb.

If you would like to read the other parts in this article series please go to: