Forefront TMG Enterprise Logging with Remote SQL Server

There are several logging options to choose from when configuring Forefront TMG 2010, including the option to log to a remote SQL server. Logging to a remote SQL server historically presented some challenges. With ISA server, if the remote SQL server was unavailable for any reason, the firewall service would shut down and place the firewall in lockdown mode and block all requests. Thanks to significant improvements in the Forefront TMG firewall’s logging infrastructure, logging to a remote SQL server is now a viable option.

Logging to a remote SQL server provides several key advantages over logging to the default local SQL Express database. The full SQL server product is much more robust and scalable than SQL Express. SQL Express installed on the Forefront TMG server is limited to a single CPU, 1 GB RAM, and has a 10GB limit on database size. By contrast, a full installation of SQL Server Standard edition can use 4 CPUs and 64 GB RAM. SQL Server Enterprise edition can leverage up to 8 CPUs and 2 TB RAM. Both editions of SQL Server can accommodate databases up to 524 petabytes in size. Using SQL server also allows the administrator to leverage high availability and disaster recovery options such as log shipping, database mirroring, and failover clustering.

A dedicated SQL server also addresses the lack of centralized logging for Forefront TMG 2010 Enterprise edition. In this configuration, every member of each TMG array in the enterprise logs to this central data store in SQL. This provides the security administrator with a single, comprehensive view of Internet activity across the entire organization. In addition, if a TMG firewall has to be rebuilt or replaced for any reason, no log data will be lost in the process.

Some of the drawbacks to using a remote SQL server are that it does require additional hardware and the appropriate SQL server licenses, which adds cost to the overall solution. Also, managing a SQL server is non-trivial, both from a security and operational perspective. However, larger organizations typically have dedicated database administrators on staff to meet these needs.

If you choose to configure a dedicated SQL server for Forefront TMG logging, make certain to select the option to Force data encryption. It is also a good idea to use Windows authentication as opposed to SQL authentication. For complete details on how to configure Forefront TMG 2010 to log to a dedicated remote SQL server, click here.

Configuring a dedicated SQL server for Forefront TMG 2010 logging can be beneficial in many deployment scenarios. SQL server’s ability to address more CPUs and memory, along with integrated high availability and disaster recovery features make deploying a dedicated SQL server quite compelling, especially for larger organizations. In addition, SQL server supports exponentially larger databases than the default SQL Express, which allows security administrators to extend their log retention time significantly. For Forefront TMG Enterprise deployments, having a single aggregate view of network traffic can be invaluable. And now the latest release of TMG Reporter fully supports integration with Forefront TMG 2010 when configured to log to a dedicated remote SQL server. Security administrators can take advantage of the benefits provided by logging to a full instance of SQL server and still leverage the advanced alerting and reporting features and real-time dashboard included with TMG Reporter.