Sign In with your
Trend Micro Account
Need Help?
Need More Help?

Create a technical support case if you need further support.

Large size database migration from Intrusion Defense Firewall (IDF) to Vulnerability Protection

    • Updated:
    • 27 Jun 2016
    • Product/Version:
    • Vulnerability Protection 2.0
    • Platform:
    • Windows 2008 Server R2
    • Windows 2012 Server Essential R2
Summary

When you upgrade IDF to Vulnerability Protection Service Pack 2, the schema of database (DB) will be updated as well. It may take a very long time to upgrade when the customer has separate databases (Non built-in installed, SQL Express database).

As a workaround, SEG has developed an upgrade script that you can run manually prior to the actual patch upgrade.

Download and extract the IDFToVPDatabaseMigrationScript zip file.

The upgrade script supports upgrading the IDF to Vulnerability Protection Service Pack 2 build 2.0.8171. 

Details
Public

Database Integrity Check

Before proceeding with the steps mentioned in the sections below, it is important to check first the database integrity for Intrusion Defense Firewall:

  1. Log in to SQL Server and select IDF Database.
  2. Right-click the database and select New Query.
  3. Go to the unzipped folder then locate and run the IDFToVPDatabaseMigrationScriptTableRecoveryCommand.sql script using Notepad and copy its contents to the New Query area.
    Change the database name right at the top of the script with your IDF database.
  4. Click Parse button on the SQL Server.
  5. Click Execute button on the SQL Server. The result should be similar to the following:
    ---------------------------------------------------------------------------
    @@@ Database Migration Script >>> Recovery Commands Preparing
    ---------------------------------------------------------------------------
    
    *** Checking primary keys
     
    *** Checking foreign keys and indexes
     
    *** Checking all columns to be altered
     
    *** Command Dumping >>> Drop foreign keys
     
        alter table packetloghistory drop constraint FBENLHOLWGZITIVN;
        alter table antimalwareeventhistory drop constraint FCZXQIUMVWEXLZWE;
        alter table integrityeventhistory drop constraint FHFXRBKVSKQXFOPU;
        alter table agentevents drop constraint FJSBKCDXLPQOQMEK;
        alter table activehosterrors drop constraint FPGXLFSJHAOLGFQX;
        alter table systemeventhistory drop constraint FQZMPLNYSPZEXKSB;
        alter table autodiagnostic drop constraint FTPJVOXKTOMDPMDB;
        alter table loginspectioneventhistory drop constraint FTPKKMTABKBJDPWM;
        alter table payloadloghistory drop constraint FTYGLGLOCSQTCIKD;
        alter table activehosterrors drop constraint FVUGHDURLJFEIBVH;
     
    *** Command Dumping >>> Drop other indexes
     
        drop index antimalwareeventhistory_antimalwareeventid on antimalwareeventhistory;
        drop index antimalwarequarantinedfile_antimalwareeventid on antimalwarequarantinedfile;
        drop index integrityeventhistory_integrityeventid on integrityeventhistory;
        drop index loginspectioneventhistory_loginspectioneventid on loginspectioneventhistory;
        drop index packetloghistory_packetlogid on packetloghistory;
        drop index payloadloghistory_payloadlogid on payloadloghistory;
        drop index SYSTEMEVENT_TARGETID on systemevents;
     
    *** Command Dumping >>> Drop primary keys
     
        alter table antimalwarequarantinedfile drop constraint PK__antimalw__4534567F145C0A3F;
        alter table antimalwareeventhistory drop constraint PK__antimalw__5488F68C25DB9BFC;
        alter table antimalwareevents drop constraint PK__antimalw__DCBD755B11158940;
        alter table integrityevents drop constraint PK__integrit__D4E993CB336AA144;
        alter table integrityeventhistory drop constraint PK__integrit__E62B054B408F9238;
        alter table loginspectioneventhistory drop constraint PK__loginspe__B121F28950C5FA01;
        alter table loginspectionevents drop constraint PK__loginspe__DA09231241B8C09B;
        alter table packetloghistory drop constraint PK__packetlo__5B12087165C116E7;
        alter table packetlogdatas drop constraint PK__packetlo__AB87D12B7D439ABD;
        alter table packetlogs drop constraint PK__packetlo__DAE0389B6225902D;
        alter table payloadlogs drop constraint PK__payloadl__396C78AD66EA454A;
        alter table payloadloghistory drop constraint PK__payloadl__EE595C6B740F363E;
        alter table payloadlogdatas drop constraint PK__payloadl__F923185D01142BA1;
        alter table systemevents drop constraint PK__systemev__29E7D8927BE56230;
     
    *** Command Dumping >>> Recreate primary keys
     
        alter table antimalwarequarantinedfile add constraint PK__antimalw__4534567F145C0A3F primary key(AntiMalwareQuarantinedFileID);
        alter table antimalwareeventhistory add constraint PK__antimalw__5488F68C25DB9BFC primary key(AntiMalwareEventHistoryID);
        alter table antimalwareevents add constraint PK__antimalw__DCBD755B11158940 primary key(AntiMalwareEventID);
        alter table integrityevents add constraint PK__integrit__D4E993CB336AA144 primary key(IntegrityEventID);
        alter table integrityeventhistory add constraint PK__integrit__E62B054B408F9238 primary key(IntegrityEventHistoryID);
        alter table loginspectioneventhistory add constraint PK__loginspe__B121F28950C5FA01 primary key(LogInspectionEventHistoryID);
        alter table loginspectionevents add constraint PK__loginspe__DA09231241B8C09B primary key(LogInspectionEventID);
        alter table packetloghistory add constraint PK__packetlo__5B12087165C116E7 primary key(PacketLogHistoryID);
        alter table packetlogdatas add constraint PK__packetlo__AB87D12B7D439ABD primary key(PacketLogDataID);
        alter table packetlogs add constraint PK__packetlo__DAE0389B6225902D primary key(PacketLogID);
        alter table payloadlogs add constraint PK__payloadl__396C78AD66EA454A primary key(PayloadLogID);
        alter table payloadloghistory add constraint PK__payloadl__EE595C6B740F363E primary key(PayloadLogHistoryID);
        alter table payloadlogdatas add constraint PK__payloadl__F923185D01142BA1 primary key(PayloadLogDataID);
        alter table systemevents add constraint PK__systemev__29E7D8927BE56230 primary key(SystemEventID);
     
    *** Command Dumping >>> Recreate foreign keys
     
        alter table packetloghistory with check add constraint FBENLHOLWGZITIVN foreign key(packetlogid) references packetlogs(packetlogid) on delete cascade;
        alter table antimalwareeventhistory with check add constraint FCZXQIUMVWEXLZWE foreign key(antimalwareeventid) references antimalwareevents(antimalwareeventid) on delete cascade;
        alter table integrityeventhistory with check add constraint FHFXRBKVSKQXFOPU foreign key(integrityeventid) references integrityevents(integrityeventid) on delete cascade;
        alter table agentevents with check add constraint FJSBKCDXLPQOQMEK foreign key(systemeventid) references systemevents(systemeventid) on delete cascade;
        alter table activehosterrors with check add constraint FPGXLFSJHAOLGFQX foreign key(endsystemeventid) references systemevents(systemeventid);
        alter table systemeventhistory with check add constraint FQZMPLNYSPZEXKSB foreign key(systemeventid) references systemevents(systemeventid) on delete cascade;
        alter table autodiagnostic with check add constraint FTPJVOXKTOMDPMDB foreign key(systemeventid) references systemevents(systemeventid);
        alter table loginspectioneventhistory with check add constraint FTPKKMTABKBJDPWM foreign key(loginspectioneventid) references loginspectionevents(loginspectioneventid) on delete cascade;
        alter table payloadloghistory with check add constraint FTYGLGLOCSQTCIKD foreign key(payloadlogid) references payloadlogs(payloadlogid) on delete cascade;
        alter table activehosterrors with check add constraint FVUGHDURLJFEIBVH foreign key(startsystemeventid) references systemevents(systemeventid);
     
    *** Command Dumping >>> Recreate uniquekeys for other non-primary key indexes
     
     
    *** Command Dumping >>> Recreate any leftover nonkey indexes
     
        create NONCLUSTERED index antimalwareeventhistory_antimalwareeventid on antimalwareeventhistory(AntiMalwareEventID ASC);
        create NONCLUSTERED index antimalwarequarantinedfile_antimalwareeventid on antimalwarequarantinedfile(AntiMalwareEventID ASC);
        create NONCLUSTERED index integrityeventhistory_integrityeventid on integrityeventhistory(IntegrityEventID ASC);
        create NONCLUSTERED index loginspectioneventhistory_loginspectioneventid on loginspectioneventhistory(LogInspectionEventID ASC);
        create NONCLUSTERED index packetloghistory_packetlogid on packetloghistory(PacketLogID ASC);
        create NONCLUSTERED index payloadloghistory_payloadlogid on payloadloghistory(PayloadLogID ASC);
        create NONCLUSTERED index SYSTEMEVENT_TARGETID on systemevents(TargetID ASC);
    
    
  6. Note the following:
    • The result for the command dumping “*** Command Dumping >>> Drop foreign keys” must have a total of 11 drop constraints for foreign keys list as shown in above output.
    • The result for the command dumping “*** Command Dumping >>> Drop other indexes” must have a total of 9 dropped indexes list as shown in above output.
    • The result for the command dumping “*** Command Dumping >>> Drop primary keys” must have a total of 17 dropped constraints for primary keys list as shown in above output.
    • The names of keys and indexes could be different.

If your output is exactly what is shown above, it means that your database integrity check is passed. You may now safely proceed with the Pre-requisites before migration section of this article.

If your database shows less than the mentioned output, the database integrity is in unstable condition. Contact Trend Micro Technical Support for further troubleshooting. Do not proceed with upgrading the IDF Manager.

Database Diagram Check

In order to verify if the relationship between tables is correct, check and match the database diagrams:

  1. Log in to the SQL Server and select IDF Database.
  2. Open database > Database Diagrams.
  3. Right-click the Database Diagrams and select New Database Diagrams.
  4. Choose the following table pairs for checking their relationships and then click Add.
    There are 7 sets of tables as mentioned below and their respective relationship diagram is also shown. Please make sure your database relationship diagram also matches the same as shown below for each set.

    SET 1

    systemevents
    activehosterrors
    agentevents
    autodiagnostic
    systemeventhistory

    Set 1

    SET 2

    antimalwareevents
    antimalwarequarantinedfile
    antimalwarespywareitems
    antimalwareeventhistory

    Set 2

    SET 3

    integrityevents
    integrityeventhistory

    Set 3

    SET 4

    loginspectionevents
    loginspectioneventhistory

    Set 4

    SET 5

    packetlogs
    packetlogdatas
    packetloghistory

    Set 5

    SET 6

    payloadlogs
    payloadlogdatas
    payloadloghistory

    Set 6

  1. Back up your Intrusion Defense Firewall (IDF) database.
  2. Check the table usage and free disk space of SQL server.
    1. Use the script IDFToVPDatabaseMigrationScriptTableSpaceUsageSummary.sql to get the summary of table space usage. Below is an example:

      Total Disk Usage for Migration Tables in KB : 103915072 KB Total Disk Usage for Migration Tables in MB : 101479 MB Total Disk Usage for Migration Tables in GB : 99 GB

    2. Reserve another 150% space for migration purpose. For the example above, you need at least 150 GB free disk space to perform the migration script.
  3. Change the SQL server remote query connection timeout to "0 (No Timeout)".

    No timeout

  4. You may opt to truncate data from the migrating tables to improve the migration speed and reduce the disk space usage.
     
    The action is TRUNCATE TABLE, which will clear the table without any records.
     
    1. Open the script DeepSecurityDatabaseBigintMigrationScriptTableTruncation.sql.
    2. Modify the following code to specify which part you want to truncate. By default, it will truncate all the migrating tables.
      declare @altercolumnspec nvarchar(3192) = 'systemevents.TargetID:BIGINT:null|antimalwareevents.AntiMalwareEventID:BIGINT|...';
      --declare @altercolumnspec nvarchar(3192) = 'systemevents.TargetID:BIGINT:null';
      --declare @altercolumnspec nvarchar(3192) = 'antimalwareevents.AntiMalwareEventID:BIGINT|antimalwarequarantinedfile.AntiMalwareEventID:BIGINT|...';
      --declare @altercolumnspec nvarchar(3192) = 'integrityevents.IntegrityEventID:BIGINT|integrityeventhistory.IntegrityEventHistoryID:BIGINT|...';
      --declare @altercolumnspec nvarchar(3192) = 'loginspectionevents.LogInspectionEventID:BIGINT|loginspectioneventhistory.LogInspectionEventHistoryID:BIGINT|...';
      --declare @altercolumnspec nvarchar(3192) = 'packetlogs.PacketLogID:BIGINT|packetlogdatas.PacketLogDataID:BIGINT|...';
      --declare @altercolumnspec nvarchar(3192) = 'payloadlogs.PayloadLogID:BIGINT|payloadlogdatas.PayloadLogDataID:BIGINT|...';
      

      You can comment the default one and remove the SQL comment on the part you want to truncate.
      For instance, if you would like to truncate integrityevents related data, modify the code similar to the following:

      --declare @altercolumnspec nvarchar(3192) = 'systemevents.TargetID:BIGINT:null|antimalwareevents.AntiMalwareEventID:BIGINT|...';
      --declare @altercolumnspec nvarchar(3192) = 'systemevents.TargetID:BIGINT:null';
      --declare @altercolumnspec nvarchar(3192) = 'antimalwareevents.AntiMalwareEventID:BIGINT|antimalwarequarantinedfile.AntiMalwareEventID:BIGINT|...';
      declare @altercolumnspec nvarchar(3192) = 'integrityevents.IntegrityEventID:BIGINT|integrityeventhistory.IntegrityEventHistoryID:BIGINT|...';
      --declare @altercolumnspec nvarchar(3192) = 'loginspectionevents.LogInspectionEventID:BIGINT|loginspectioneventhistory.LogInspectionEventHistoryID:BIGINT|...';
      --declare @altercolumnspec nvarchar(3192) = 'packetlogs.PacketLogID:BIGINT|packetlogdatas.PacketLogDataID:BIGINT|...';
      --declare @altercolumnspec nvarchar(3192) = 'payloadlogs.PayloadLogID:BIGINT|payloadlogdatas.PayloadLogDataID:BIGINT|...';
      

      Truncating the systemevents table will also remove the following tables for data consistency:

      • Activehosterrors
      • Agentevents
      • Autodiagnostic
      • Systemeventhistory
  1. Go to the unzipped folder then locate the IDFToVPDatabaseMigrationScript.sql script.
  2. Open the script and modify the database name for each tenants. For example, if your database name is IDF15, change the script to [IDF15] similar to the following:

    use [IDF15]
    go

  3. Stop the IDF service. If you are in a multi-node environment, stop the IDF service on all IDF nodes.
  4. Run the script using the SQL Management Studio console or the SQLCMD tool.

    Option 1: Using the SQL Management Studio console

    1. Open the SQL Management Studio and start a new query.
    2. Copy the modified SQL script into this new query and press CTRL + A to select all.
    3. Press F5 to run the script, and wait until it is finished.
    4. Check the Messages window to view the result.

      SQL Management Studio

    Option 2: Using the SQLCMD tool

    1. Copy the modified SQL script in C:\ drive of the IDF database server.
    2. Open a CMD command window and switch the current path to C:\.
    3. Run following command to run the sql script:

      C:\>sqlcmd -i UpgradeScriptSqlServer.sql -d IDF15_2 -s 127.0.0.1 > result.log

       
      If username and password are required, use –U $username –P $password to appoint the username and password.
       
    4. Open C:\result.log to check the result.

      Result Log

  5. Save the output message containing the recovery command. In case of an issue, Trend Micro will still be able to use the recovery Vulnerability Protection Manager (VPM) database.
     
    Check the attached IDFToVPDatabaseMigrationScriptSampleOutput.log file for detailed logging information.
     
  6. If you have a multi-tenant environment, run Steps 2 to 4 for each tenant database.
  7. Once the migration script is completed, run the VPM installer to upgrade.

If the script failed for any reasons (e.g. space or connection timeout), do the following:

  1. Troubleshoot the issue using the SQL error or any MS SQL server log.
  2. Once the issue has been fixed, restore the database and run the script again.

To upgrade older Intrusion Defense Firewall builds to Vulnerability Protection, the process will pass you through two mandatory upgrade steps:

  1. Upgrade the database schema first, using those scripts mentioned in above section.
  2. Run the VP Migration Wizard to upgrade VP. Please refer to detail usage in the Wizards’s Readme.
  • It is not necessary to apply this update procedure if you are using Built-in SQL Express Database (E.g.: MS SQL 2005 Express or MS SQL 2008 Express) as its database size is small enough to be handled by Migration Wizard.
  • This Database migration is not supported in SQL Server version 2005.
  • If the customer has a database size over 50GB, the migration process might take extremely long time. Expect an extremely long downtime. Or the customer may opt to truncate the data with IDFToVPDatabaseMigrationScriptTableTruncation.sql.
Premium
Internal
Rating:
Category:
Configure; Upgrade; Update; SPEC
Solution Id:
1112838
Feedback
Did this article help you?

Thank you for your feedback!

To help us improve the quality of this article, please leave your email here so we can clarify further your feedback, if neccessary:
We will not send you spam or share your email address.

*This form is automated system. General questions, technical, sales, and product-related issues submitted through this form will not be answered.

If you need additional help, you may try to contact the support team. Contact Support


To help us improve the quality of this article, please leave your email here so we can clarify further your feedback, if neccessary:
We will not send you spam or share your email address.

*This form is automated system. General questions, technical, sales, and product-related issues submitted through this form will not be answered.


Need More Help?

Create a technical support case if you need further support.