Relocating SQL Database Files on Forefront TMG 2010
When Forefront Threat Management Gateway (TMG) 2010 is installed, an instance of SQL Server 2008 Express is included for Forefront TMG firewall and web proxy logging. By default, the log database files are installed on the system partition, which is less than ideal. Best practices dictate that log database files should reside on a separate, dedicated partition.
I’ve had many people ask how to move these database files once the product is installed. Most assume that the process involves using SQL database management tools to detach the database and manually move the database files to a new partition. Not true! Since Forefront TMG handles all of the underlying SQL database management, the process is actually quite simple.
To move the log database files, first create a folder to store them in the new location. Next, open the Forefront TMG management console, highlight Logs & Reports in the navigation tree, select the Logging tab in the center console window, then click Configure Firewall Logging in the Tasks pane on the right.
Click the Options… button, then select This folder (enter the full path): and enter the new path to store the log database files.
For EMS-managed or standalone arrays, make certain this path exists on each array member. If it does not, the service will not start. If the folder does not exist, TMG will complain.
Repeat this process to move the web proxy log database files. In addition, it would be an excellent idea to also move the Log Queue Storage Folder. This folder should be located on a partition that is separate from the one used to store the log database files. For optimum availability this will be a separate physical disk, allowing for Forefront TMG to continue logging to the queue even in the event of a physical disk failure where the log database files are stored. As with the log database files, this folder must exist on each array member.
A system variable can be used to specify the path to log database or log queue files. For example, %LOGDRIVE%\FWS, where %LOGDRIVE% can be a different drive letter and path on each array member, if necessary. To create a system variable, open the advanced system properties and click Environment Variables….
Under System variables click New…, enter the variable name (e.g. LOGDRIVE), and specify the location where the log files should be stored on this array member (e.g. D:\TMGLogs). Repeat these steps on each array member, specifying the local path where log database files are to be stored.
Confirm the system variable was created properly by opening a command prompt and entering the following command:
The output for our example should appear as follows: