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.
Excellent news! Forefront Threat Management Gateway Administrator’s Companion, authored by Jim Harrison, Yuri Diogenes, Mohit Saxena and reviewed by Dr. Tom Shinder is now available for pre-order on Amazon.com!
The flood mitigation features included in Microsoft ISA Server 2006 were one of many improvements over previous versions of ISA. Enabled by default, this enhanced network protection functionality allows the ISA firewall to withstand direct attacks (e.g. DoS or SYN flood) and provides resiliency in the event of a worm breakout. There are times, however, when this feature can impede valid network communication. If, for example, a host protected by the ISA firewall is very busy it may run in to connection limits imposed by the firewall. When this happens you may see the following error in the event log: ‘TCP connections per minute from one IP address limit exceeded’.
When legitimate network communication is dropped for this reason, it is possible to configure the firewall to allow more connections for this host. This is accomplished by opening the ISA management console, expanding the ‘Configuration’ node in the console tree, then clicking on the ‘Configure Flood Mitigation Settings’ link in the ‘Additional Security Policy’ section.
Too often I see administrators disable flood mitigation altogether. This is strongly discouraged. I also see administrators raise connection limits for ALL hosts by clicking on the ‘Edit…’ button and entering a new limit. This is also a bad practice. The best way to resolve this problem is to add the host(s) to a computer set, then add that computer set to the ‘IP Exceptions’ list.
In my experience this often needs to be done for DNS servers and for busy mail servers. Your alerts will tell you which systems are good candidates for the exception list though, so be sure to monitor your ISA firewalls closely.
In a recent blog post, Yuri Diogenes cautions us that we should not be installing IIS on ISA. I couldn’t agree with him more! There is, however, one exception – when it is installed from the factory on a Celestix MSA or WSA Series security appliance. Celestix installs and configures IIS on our ISA and IAG appliances to support our web-based remote management console. Under no circumstances should the IIS services on our appliances be used to support any other content or application. This configuration is definitely not supported and our support engineers will not be able to assist you if you attempt to do so!