Logging changes and Performance Improvements in TPP 16.2

Applies To:

Venafi Trust Protection Platform 16.2 and higher

Summary:

In older versions of Venafi Trust Protection Platform, it could be difficult for viewing logs within the Web Administration console to be performant after 10s of millions of logs are accumulated.

In Venafi Trust Protection Platform 16.2.0, significant refactoring has been done in how logs are stored in the database.  When executing the 16.2.0 upgrade scripts, the format of the data is modified.  For every 30 million rows of the logs, you can expect the script to take approximately an hour (subject to hardware, SQL Server version, server utilization, and other factors).

It is recommended, if possible, to archive or reduce the amount of logs stored in the TPP database prior to upgrading to 16.2.0.

Additional Information about Custom SQL Log Channels:

If you have additional SQL Log Channels that you have added to your environment, you will need to update those tables as well.

1. Confirm in WebAdmin if you are in fact using Custom SQL Log Channels.
2. Go to the Logging tree and click the Channels plus sign (+) to expand Channels
3. If you see any SQL Log Channels in addition to the SQL Log Channel, then continue with these steps.  IF Default SQL Log Channel is the only log channel, then you can stop now.
4. Locate the Optional Scripts for updating Custom SQL Log Channels
   For MSSQL located at: Database Scripts\MSSQL\Updates\Optional
   
   
   
5. Open the file that represents the version you just upgraded.  For example, if you were on 17.1 and just upgraded to 17.4, then you will want to open the "mssql_custom_log_channel_migration_17.2_to_17.3" file in notepad. 
   
   Note: There where no changes to log tables in 17.2, which is why there is no mssql_custom_log_channel_migration_17.1_to_17.2 file available.
   
   
6. Open the mssql_custom_log_channel_migration_17.2_to_17.3 in notepad and modify the 
   DECLARE @tablename VARCHAR(108) = 'REPLACE';
   and change REPLACE with the table name specified in the settings of the Custom SQL Log Channel.
   
   
7. Save the changes and execute the script against the database that has your custom table.
   
   
8. Perform the same steps of modifying the table and executing them until your log table version is up to date with the version of TPP you are installing.
   
   For example, if you are upgrading from 17.1 to 17.4, you will want to run the following scripts after you have modified the @tablename REPLACE value:
   mssql_custom_log_channel_migration_17.2_to_17.3
   mssql_custom_log_channel_migration_17.3_to_17.4