As of version 5.3.3 SecureTransport supports Server Log and File Tracking rotation by using partitioning for MS SQL server. This allows data that is no longer needed to be efficiently offloaded from the database and optionally stored in a MS SQL backup file.

This article provides additional information on the use of partitioning and how to configure related parameters.

IMPORTANT NOTE: that this article is applicable only for new installations of ST 5.3.3 or later and with Enterprise Edition of Microsoft SQL Server.

Resolution

Prerequisites

1. A new installation of SecureTransport LEC server version 5.3.3 with Microsoft SQL.

Partitioning for the server log and file tracking tables can be safely enabled only during database creation time. Therefore upgraded instances will not use the partitioning feature. This will be enabled only during product installation in case supported by the database server.

2. Enterprise Edition of supported MS SQL server version.

Partitioning is available only in Enterprise Editions of MS SQL server. In case a standard version is used the transfer and log entry maintenance applications will delete/archive old data without the use of partitioning. That is the case for ST instances at versions below 5.3.3 and ST instances upgraded to 5.3.3.

3. Two additional file groups: ST_FILETRACKING_ARCHIVE and ST_SERVERLOG_ARCHIVE

The file groups are needed only when transfer and log entry maintenance applications are configured to export data deleted from the database.

4. Sizing guide ST_FILETRACKING_ARCHIVE and ST_SERVERLOG_ARCHIVE filegroups

The above two file groups are used only when the two maintenance applications are actively running. They need to be able to accommodate the data that is going to be exported and deleted. The data will be removed from the two file groups as soon as it is exported to the location specified in the application properties (Export folder).

In case the maintenance applications are scheduled to run on a daily basis the size of ST_FILETRACKING_ARCHIVE and ST_SERVERLOG_ARCHIVE need to be able to store the Server Log and File Tracking data for at least a day.

The sizing guide specified in the Axway SecureTransport Installation Guide (Database size in production, Page 18) can be applied here. It is recommended to plan for situations that might need more space such as maintenance jobs that did not trigger for several days or a higher number of Server Log or File Tracking entries. The use of AutoGrow is recommend to provide additional space. The two filegroups can also be shrunk when they are not used by the maintenance applications.

Note: The above is a summary of the requirements. The full list is available in the product Installation Guide.

Configuration

1. Configuring SecureTransport to use partitioning

This is completely automatic process that is performed during SecureTransport installation. The installer will check if the database supports partitioning and apply all needed settings. Partitioning can be enabled only during ST installation and cannot be turned on or off at a later time.

2. Configuring log export for Transfer Log and Log Entry Maintenance applications.

Export is enabled by editing the two applications, enabling the "Enable logs export" option and specifying an export folder. Specifying export folder is the most important step. The MS SQL server is the one that does the actual log export when partitioning is used. Therefore the export directory needs to be relative to the MS SQL server and not to the SecureTransport server. The export folder should be accessible by the MS SQL server.

For example: An environment consisting of 2 SecureTransport Servers running on RedHat Linux with a MS SQL server database. The export folder will be C:\ST_EXPORT as it can be accessed by the MS SQL server. UNC paths can be used such as \\nas-server\database-backups\. As noted above, the location needs to be accessible by the MS SQL server.

Note: The ST_FILETRACKING_ARCHIVE and ST_SERVERLOG_ARCHIVE filegroups are prerequisites for the export feature.

Summary of operation

The Server Log and File Tracking data is stored in a partition for each day. When partitions are no longer needed because they are older than the days defined in the Maintenance application settings, partitions are deleted with an optional backup.

The first step in the process is attaching the partitions to staging tables that are suffixed with "_temp".

Next the file group is switched to ST_FILETRACKING_ARCHIVE or ST_SERVERLOG_ARCHIVE depending on the maintenance job that is running.

Finally the _ARCHIVE file groups are backed up to the export directory and their content deleted. This will produce one backup file per _ARCHIVE file group (per Maintenance application) and in case the database recovery model is "FULL" a backup file for the transaction log.

After a Maintenance application has completed, the following messages will be logged in the SecureTransport's Server Log:

Ready to export and delete DB data before Aug 05, 2016 Eastern Daylight Time
Exporting filetracking partitions older than 1 days to C:\ST_EXPORT
Exported 2 partition(s) for Transfer Log tables. Backup file: C:\ST_EXPORT\ST_ST_FILETRACKING_2016-08-06.bak
Dropped 2 partition(s) for Transfer Log tables.
Created 2 partition(s) for Transfer Log tables.
Transfer Log Maintenance application finished execution.Duration: 00:00:33

The above is an example for an application that runs every 2 days. It has exported two partitions (one partition for a day) and dropped them. This will result in one backup file no matter how many partitions were exported. In case the database recovery model is "FULL" additional backup file will be created for the transaction log. The export partition is actually a MS SQL backup of a filegroup.

The final step is to create new partitions for future days. It is possible to receive a message for 0 created partitions. That means that the number of available partitions is not below the threshold.