When Forefront TMG 2010 is installed, an instance of SQL Server 2008 Express is also installed to facilitate local firewall and web proxy logging. Some TMG administrators have reported that the SQL server process (sqlservr.exe) may consume an excessive amount of memory.
This occurs because SQL server performs its own internal memory management. By design it will consume large amounts of memory and hold it in reserve, which may appear to be a memory leak. However, if you observe the memory consumed by SQL server over an extended period of time, you will notice that it will periodically release memory as well.
In most cases, allowing SQL to handle the job of managing its memory without restriction poses no real problem. However, if your system is exhibiting signs of high memory pressure and you are confident that there are no other processes that are consuming excessive amounts of memory, you can configure SQL to limit the amount of memory that it will reserve. Before manually configuring SQL memory limits, review this blog post for a list of recommended settings.
Next, open an elevated command prompt and enter the following command:
osql –E –S .\msfw
After connecting to the SQL instance, enter the following commands:
USE master GO EXEC sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE GO EXEC sp_configure 'max server memory (MB)', 1024 --configure 1GB limit, adjust as needed GO EXEC sp_configure 'show advanced options', 0 RECONFIGURE WITH OVERRIDE GO
This script assumes that you’ve chosen a 1GB (1024MB) memory limit. You can adjust the script above to reflect the values for your environment as required. Once complete, restart the SQL server service for the changes to take effect.
To view the currently configured memory limit, simply omit the numeric value after max server memory (MB), as shown here:
EXEC sp_configure 'max server memory (MB)'
To make things easier you can download these as script files and execute them using the following command:
osql –E –S .\msfw -i <path to script file>
You can find script files to show and set SQL memory limits here:
Keeping the base operating system of your Forefront TMG 2010 firewall up to date is vitally important to the overall security of your edge security solution. To manage system updates, many administrators will configure their Forefront TMG 2010 firewalls to use Windows Update or WSUS, or manage them using System Center Configuration Manager (SCCM) or another third-party systems management platform.
In my experience, SQL server running on the Forefront TMG 2010 firewall is often overlooked and commonly not updated. I believe this happens because updates for SQL server are classified as optional.
So, as a reminder, don’t overlook updates for SQL server on Forefront TMG 2010 firewalls or UAG 2010 servers! Using the Windows Update control panel application, select the option to install the latest service pack for Microsoft SQL Server 2008, which at the time of this writing is Service Pack 3. You can install the service pack directly if you choose; SQL Server 2008 Express SP3 can be downloaded here. After applying the latest service pack you can confirm that SQL has been updated by opening an elevated command prompt and entering the following commands:
osql -E -S .\msfw select @@version [press enter] go [press enter]
The output of the command should indicate that the installed SQL version is Microsoft SQL Server 2008 (SP3) – 10.0.5500.0 (X64).
Note: Applying service packs and updates to SQL is highly recommended to maintain the most secure Forefront TMG 2010 firewall possible. Upgrading the version of SQL installed on the TMG firewall is not supported and definitely not recommended, so don’t attempt to upgrade to SQL Server 2008 R2 Express.