I realize that MSDE performance is an oxymoron, but the performance of the MSDE database included with Microsoft ISA Server 2006 is essential to the operation of the ISA firewall. By default, if ISA is unable to write log data to the database, the firewall will enter lockdown mode and stop servicing requests. To ensure the availability of the ISA firewall, it is important to understand the limitations that are inherent with MSDE, and also the steps to take to improve performance when logging to an MSDE database.
There are several logging options to choose from when installing ISA. Selecting the ‘advanced logging’ feature will install the Microsoft SQL Server 2000 Desktop Engine (MSDE) database to support logging.
The advanced logging option using MSDE requires more resources than text file logging (in terms of processor utilization, memory consumption and disk I/O), but unlike text file logging there is the added benefit of viewing historical log data in the ISA management console.
To be honest, I have never been a fan of using MSDE with ISA. In terms of performance, it is not very robust. MSDE was designed originally as an alternative to the Jet database engine included with Microsoft Access and was never intended to be used for enterprise applications. MSDE is based on SQL Server 2000, which by my math is over nine years old as of this writing. That is an eternity in technology time. Also, MSDE on ISA limits the database size to 1.5GB, and by design includes a workload governor that can impede performance in busy environments. Personally, I prefer text logging because it is much more robust, scalable, and higher performing, especially in very busy enterprise deployments. For me, the need to view historical data in the ISA management console is not critical, as I am comfortable with looking through text log files using the command line. I make liberal use of utilities such as findstr.exe and tail.exe (the latter of which is included in the Windows Server 2003 Resource Kit tools), as well as the Microsoft Log Parser. That’s just me though. : )
Of course there are many small and mid-sized deployments for which MSDE is perfectly suited and quite capable of performing adequately. In those scenarios, it is important that we follow some implementation best practices and perform additional maintenance to ensure optimum performance when using MSDE. First and foremost, the MSDE database files should be placed on a separate partition from the system partition. This will reduce disk contention and file fragmentation. By default, the log files are contained in the \Program Files\Microsoft ISA Server\ISALogs folder on the system partition.
You can change the location of the database files by opening the ISA management console, highlighting ‘Monitoring’, then selecting the option to ‘Configure Firewall Logging’ or ‘Configure Web Proxy Logging’.
Next, click the ‘Options…’ button next to the ‘MSDE Database’ option, then select the radio button next to ‘This folder (enter full path):’ and specify a location to store the log files. If you are making this change on an ISA Enterprise array, this location must exist on ALL array members. You have the option to use a system variable here, such as %logdrive%, which can simplify configuration for enterprise deployments.
Although less critical when you have a separate partition for the log files, disk fragmentation can reduce MSDE performance as well. You can use the native Windows disk defragmentation tool (defrag.exe) to defragment the partition, or if you prefer only to defragment the database files themselves you can use contig.exe from Sysinternals.
Note: This screen shot is from a little used test machine, so the level of fragmentation is minimal. On a busy system that has been in production for years, there will almost certainly be more fragmentation than you see here.
In addition to the best practices outlined above, another way to improve MSDE performance is to reduce the amount of data logged in the first place. This can be accomplished in several ways. To begin, review the log fields that are selected by default. You can find the log fields on the Firewall or Web Proxy logging properties window and clicking on the ‘Fields‘ tab. If there are fields that contain information that aren’t required, deselect them. Some fields that are enabled by default and are commonly omitted are the bytes sent and received (and delta) fields and processing time (and delta) fields. Review all of the log fields to determine the minimum data required.
You might also consider not logging traffic processed by the default deny rule. While this can significantly reduce the amount of data logged in busy environments, it does reduce visibility in to what types of traffic the ISA firewall is rejecting. A better alternative is to create specific access rules for uninteresting traffic (e.g. DHCP requests, NetBIOS name resolution broadcasts, etc.) and configure the rule not to log requests that match.
You can also disable the option to log traffic blocked by flood mitigation settings. Flood mitigation settings can be found in the ISA management console by expanding the ‘Configuration’ node, highlighting the ‘General’ node, then click on the ‘Configure Flood Mitigation Settings’ link.
Considering the many limitations imposed by MSDE, you might think that using a remote SQL server is the answer to all of these problems. Having a dedicated system running the latest version of SQL would certainly better than MSDE. However, network connectivity issues and throughput can potentially impede performance using this option. Thankfully Forefront Threat Management Gateway 2010 includes some significant enhancements to logging that address these issues. First, the native database logging option now uses SQL 2008 Express, which is a big improvement over MSDE. Also, TMG database logging now includes an option to queue logged data locally if for any reason the database is unreachable. The log queuing feature of TMG now makes remote SQL logging a viable and compelling option for logging in the future.
For additional information about remote SQL logging, see my previous posts Remote SQL Logging with Microsoft ISA Server 2006 and A Few Notes Regarding Remote SQL Logging with Microsoft ISA Server and Forefront Threat Management Gateway.
For more detail about how logging works in ISA, and for additional information on the various logging options available, please refer to the Monitoring, Logging, and Reporting Features in ISA Server 2006 document on TechNet.
