Upgrading a remote SQL database for Forefront TMG SP1

  • Article

(This post is applicable only for deployments that use remote SQL Server logging.)

Forefront TMG 2010 SP1 changes the Forefront TMG log schema. Specifically, the field SoftBlockAction was added to TMG Web Proxy logging schema.

When logging to a local instance of SQL Server Express, schema changes do not require anything from the Forefront TMG administrator. However, when logging to a remote SQL Server, since the Forefront TMG administrator is responsible for creating the logging database(s) and tables, the administrator is also responsible for upgrading/downgrading (when installing/uninstalling Forefront TMG 2010 SP1, respectively) the tables’ schemas.

 

Forefront TMG 2010 SP1 can log to the log schema of both versions of Forefront TMG 2010, release (also known as RTM) and SP1. However, the RTM version of Forefront TMG 2010 cannot log to a Forefront TMG 2010 SP1 log schema. Therefore, the Forefront TMG 2010 SP1 upgrade/downgrade process should be managed in such a way that at no time should a server running the RTM version of Forefront TMG 2010 be configured to log to a remote SQL Server that has Forefront TMG 2010 SP1 log table schemas.

 

This means that when upgrading to Forefront TMG 2010 SP1, all Forefront TMG servers that log to a specific table must be upgraded, and only then should you upgrade the log tables’ schemas. Similarly, if you need to downgrade from Forefront TMG 2010 SP1 to the original release version of Forefront TMG 2010, the log tables’ schemas must be downgraded before the service pack is uninstalled from the array members.

 

Below is a step by step guide for doing just that.

 

Upgrade to TMG 2010 SP1

** **

1.       Upgrade all Forefront TMG servers that write to the table that is to be upgraded.

2.       Create a new log table with the new schema.
Since only the Web Proxy log schema was changed in Forefront TMG 2010 SP1, only a Web Proxy logging table needs to be created – there’s no need to create a new Firewall logging table.
To create the new table, follow the same procedure as described in Forefront TMG 2010 documentation for creating the logging table except that the table creation script file W3PROXY.SQL must be taken from the Forefront TMG installation directory of one of the Forefront TMG servers that were already upgraded to Forefront TMG 2010 SP1.
Note: it is possible to create a new table in the existing database, in which case the table name in the script may need to be changed to be able to create the new table alongside the old table in the same database.

3.       Configure all the Forefront TMG servers to log to the new table

4.       Make sure that all Forefront TMG servers are using the latest configuration to ensure that all are logging to the new table.
The configuration status can be verified through the Configuration tab in the Monitoring node in the Forefront TMG Management console.

5.       Retire the old (Forefront TMG 2010 RTM version schema) table.
This may involve moving the data to the new table, backing up the table or simply deleting it.
Note that once Forefront TMG is configured to use the new table it’s no longer aware of the Forefront TMG 2010 RTM version table. Thus, unless the content of the retired table is moved into the new table, the Forefront TMG Management console’s Log Viewer cannot be used to query logs from before the move to the new table.

6.       If the new table has a name different than the original table, it may be desirable to rename it back to the old table’s name, e.g. if there are log analysis tools that rely on the table’s name.

 

Downgrading from Forefront TMG 2010 SP1

** **

1.       Create a new log table with the Forefront TMG 2010 RTM schema.
Since only the Web Proxy log schema was changed in Forefront TMG 2010 SP1, only a Web Proxy logging table needs to be created – no need to create a new Firewall logging table.
To create the new table, follow the same procedure as described in Forefront TMG 2010 documentation for creating the logging table, except that the creation script file must be taken from <Forefront TMG 2010 DVD Root>\Fpc\Program Files\Microsoft ISA server\W3PROXY.SQL. This file cannot be taken from the Forefront TMG installation directory because at this point that file contains the script for creating the table with the Forefront TMG 2010 SP1 logging schema.
Note: it is possible to create a new table in the existing database, in which case the table name in the script may need to be changed to be able to create the new table alongside the old table in the same database.

2.       Configure the Forefront TMG servers to log to the new table (with the Forefront TMG 2010 RTM version schema)

3.       Make sure that all Forefront TMG servers are using the latest configuration to ensure that all are logging to the new table.
The configuration status can be verified through the Configuration tab in the Monitoring node in the Forefront TMG Management Console.

4.       Uninstall Forefront TMG 2010 SP1 from the Forefront TMG servers that are now writing to the new (Forefront TMG 2010 RTM version schema) table

5.       Retire the old (Forefront TMG 2010 SP1 schema) table.
This may involve moving the data to the new table, backing up the table or simply deleting it.
Note that once Forefront TMG is configured to use the new table it’s no longer aware to the Forefront TMG 2010 SP1 table. Thus, unless the content of the retired table is moved into the new table, the Forefront TMG Management Console’s Log Viewer cannot be used to query logs from before the move to the new table.

6.       It may be desirable to rename the new table to the old table’s name, e.g. if there are log analysis tools that rely on the table’s name

 

Note: The new log field is named SoftBlockRule in the logging table and Overridden Rule in the log viewer. While a Forefront TMG 2010 SP1 server is logging to a table of the Forefront TMG 2010 RTM version schema, a Log Viewer query that includes the Overridden Rule field in its criteria will fail. Obviously, while a Forefront TMG 2010 SP1 server is logging to a Forefront TMG 2010 RTM logging table, there is no room for the new SoftBlockRule field information in the logging table and it is lost. However, the Forefront TMG feature that causes this field to contain meaningful data, called User Override, is new in Forefront TMG 2010 SP1 so as long as the logging schema is upgraded before User Override is enabled, no meaningful logging data is lost.