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

Create a technical support case if you need further support.

Manually updating the Deep Security Manager (DSM) database schema before upgrading to 9.6 GM or 9.0 SP1 Patch 5

    • Updated:
    • 29 Sep 2016
    • Product/Version:
    • Deep Security 9.0
    • Deep Security 9.5
    • Deep Security 9.6
    • Platform:
    • Windows 2003 Server R2
    • Windows 2008 Server R2
    • Windows 2008 Server R2 Enterprise
Summary

Welcome to the landing page for the Trend Micro Deep Security database migration tool. You are being routed to this page because the Deep Security Manager upgrade you are performing requires some database schema changes. These changes are required only for Microsoft SQL Server databases.

You can download the tool or use the script to upgrade the database:

  • Option A. Upgrade your database using the Trend Micro DB Upgrade tool (Recommended)

    The tool can migrate the database schema to either of the following versions:

    • 9.0 SP1 P5 (or above)
    • 9.5 SP1 P3 (or above)
    • 9.6 SP1
    • 9.6 SP1 P1 (or above)
  • Option B. Manually upgrade your database using the Trend Micro DB Upgrade script

    This script supports DSM upgrade to the following builds:

    • 9.0 SP1 Patch 5 Build 9.0.7007
    • 9.5 SP1 Patch 1 Build 9.5.7008
    • 9.6 Build 9.6.1589
Details
Public
 
Please make sure that you have done the database backup before upgrading.

Option A. Upgrade your database using the Trend Micro DB Upgrade tool (Recommended)

Download the Trend Micro DB Upgrade tool. This tool mainly consists of a .Net executable binary and specifically designed SQL scripts.

To run the installer:

  1. Unzip the TrendDBUpgrade.zip file in a temp directory, preferably on your system drive. The zip file contains the following:
     
    There might be some little discrepancies between the screenshots shown in this article and the version of the product you are working with.

    Content of Trend DB Upgrade Tool

  2. Double-click the TrendDBMigrator.exe file.

    The following are the MD5 and SHA 256 value of the TrendDBMigrator.exe file:

    • MD5 value:
      7C9A35AB6B40B9841CBEB3A9C2DB3171

    • SHA 256
      973EFA5C59D214F80BF57C6883196863F681BF3C60FA81F43344AB995D690498

  3. The installer will check for dependencies. If the dependencies are not met, it will display the following message:

    Microsoft SQL Server 2008 R2 Command Line (SQLCMD) could not be located. Program can not continue without required dependencies.
    Would you like this tool to download and install them for Microsoft Download Center now?

    Program can not continue without required dependencies.

    If you click Yes, it will install the dependencies as shown in Step 3.
    If you click No, proceed to Step 4.

  4. The tool has three (3) dependencies that you should install:
    • Microsoft .Net Framework 3.5+
    • Microsoft® SQL Server® 2008 R2 Native Client
    • Microsoft® SQL Server® 2008 R2 Command Line Utilities (SQLCMD)

    In order to have the dependencies mentioned above, follow this procedure:

    1. If your server is not equipped with Microsoft .Net Framework 3.5+, click the Server Manager icon on Taskbar and select Add roles and features.
    2. Walk through the wizard and select .NET framework 3.5 Feature.
    3. Complete the installation. This may take several minutes to complete.

      .NET framework 3.5 Feature

    4. Continuing with the tool installation wizard, click Yes to download the two other dependencies (Microsoft® SQL Server® 2008 R2 Native Client and Command Line Utilities) from the Microsoft Website. Your system must have access to the Internet. Installation may take few minutes depending on your network speed.
    5. While downloading the Microsoft SQL, the tool creates a folder named InstallMe under the same extracted folder from where you have run the installer TrendDBMigrator.exe. The newly created folder contains these two (2) executable files and their respective log files:
      • SqlCmdLnUtils
      • Sqlncli

      SqlCmdLnUtils and Sqlncli

    6. When the download is finished, a confirmation message appears. Click OK to start the tool.

      Installed dependencies

    7. You may check the installed dependencies under Control Panel > Program and Features.

      Program and Features

  5. If the download fails or you choose No, you will see the following message:

    Please download and install Microsoft SQL Server 2008 R2 Command Line Utilities (SQLCMD), from Microsoft download center.

    Please download and install Microsoft SQL Server 2008 R2 Command Line Utilities

    When you click OK, a page corresponding to the missing dependencies will open in a browser, where you can manually download and install the dependencies before running the tool.

    How to get SQLCMD?

Once you have installed the tool, you can now utilize it using these three (3) tabs:

  • Database Settings
  • Schedule Migration Jobs
  • Real-time Log

Database Settings

Database Settings is the main tab where you must enter the required information and credentials to connect to your SQL Server. Once you've entered the SQL Server IP, User Name, and Password (Instance is optional and normally left empty), you can click the Connect button. If the information is correct, you should see a list of all T0 databases present on that SQL Server.

All the data in the grid is sortable. To sort the data in the grid, click the appropriate column header. The data will sort in either ascending or descending order based on the previous state.

List of T0 databases

 
If you want to connect to the database using a domain account, enable the Integrated Security option. You can see the location of this option in the screenshot above.

The image above shows a list of T0 databases. In the Migration Status column, there are results showing "Already Migrated" or "Not Migrated". The status "Already Migrated" indicates that the database has all the required fields converted to BIGINT or it is the latest installation and does not need to be migrated. Databases that are already migrated are highlighted in green.

For databases that are "Not Migrated", you can click them one by one and migrate them. You have two (2) options to migrate a database:

  • 9.6 SP1 P1 (or above)
  • 9.0 SP1 P5 and 9.5 SP1 P3 (or above)

If you switch between the two Migrating T0 to options, click the Connect button again to fetch the associated results for that version.

If the Show Multi-tenants DSM Database Only box is checked, you will see only T0 databases that are multi-tenant. If this box is unchecked, you will see all Deep Security databases present on the SQL Server.

 
All red buttons are for schema changes. Green buttons cannot change the schema. You can run simulations by clicking Run Simulation – Selected Databases. Also, you can check space using the Check Space – Selected Databases button.

Below is a sample scenario wherein the image shows a database named "ak-test2" is selected. Its two (2) tenant databases (Tn), named "ak-test2_1" and "ak-test2_2", are displayed. Please note the Migration Status of both tenant databases is "Cannot Migrate". The reason is that the T0 database (ak-test2) has not been migrated yet. This tool does not migrate tenants together with a T0. It is essential to migrate the T0 first and then migrate all related tenants in one step.

Cannot Migrate status

If the tenant appears gray in the grid, it means its database schema has already been migrated to Bigint.

To migrate a database from T0:

  1. Click the database name from T0 and do not select any Tn.
  2. Click the Migrate-Selected Database button.
  3. Note that this tool calculates the space available before migrating the database. If there is no enough space, it will display the following warning and close:

    Required space is NOT available for migration. It is recommended to allocate more space before migration.

    Required space is NOT available for migration

    The image above indicates that the available space is 0.2890625 MB. This is not the Disk Drive space calculation. You can calculate the available space by right-clicking the database in SQL Server and navigating to Properties > General > Database > Space Available. If the space does not reach at least 150% of the space currently used by the database, the said warning will appear.

    To change the initial size settings:

    1. Right-click the database in SQL Server and click Properties.
    2. Select Files.
    3. Make the changes in the Initial Size column to make it more than 150% of the current space and click OK.

      In the sample below, the size has been changed to 2048 MB to accommodate 150%, which is 544.74609375 MB.

      Initial size

  4. After fixing the available space, click again the Migrate-Selected Database button in the tool. It should now show that the space is enough to migrate, then click OK.

    Required space is available for migration

  5. Read the information that will appear and click OK.

    Please note that DSM will not work while the migration script is executing

  6. Read the warning that will show and click Yes.

    This tool should only be used to migrate DSM schema from version 8 , 9, 9.5, and 9.6

  7. Check the real-time log in the tool as it starts migrating the database.

    Real-time logs

    Once the migration is completed, you will see the log file. The log file name indicates information like the operation executed, date, time, and database on which that operation was executed.

    Details about migration

    This log file is placed under the extracted TrenDBUpgrade – Log folder.

    TrenDBUpgrade – Log folder

    At the end of the log file, you will see that the schema migration is completed and the script executed successfully.

    Completed migration

  8. Go back to tool and you will observe that the Migration Status column for "ak-test2" database is now set to "Already Migrated".

    The tool needs exclusive access to database, so it kills all active sessions and connections, puts the database in single user mode, executes, and then changes the database back to multi-user again.

    The tool also verifies that the credentials used are correct and the user used to connect to the database has the db_owner role. If not, the credentials are rejected. If the tool produces an error message about wrong credentials for the selected TN, you can edit the username and password field by clicking the respective cell.

Tenant's Migration

Now it’s time to migrate the Tenant databases. The "ak-test2" database in our example has two (2) tenant databases. You can migrate a maximum of 10 tenants at a time.

  1. Select your tenants from the Selected T0’s Tenants area.

    Selected T0’s Tenants area

  2. Click the Migrate-Selected Tenants button to the tenant databases.
  3. The tool will check the available space for both tenants and if there is enough space, it will display the following information, which is repeated for each tenant that you selected for migration. Click OKto dismiss each message.

  4. The amount of time that a tenant migration takes depends on how many databases you have selected. When the migration is completed, a log file is opened for each tenant, showing that migration has been finished. You can verify the completed migration in the sample log files below:

    Completed migration of ak-test2-2

    Completed migration of ak-test2-1

  5. You can check all the log files saved in the same TrendDBUpgrade – Log folder. You can also verify in the tool under the Selected T0's Tenants area that each tenant is displayed as "Already Migrated".

    Already Migrated

  6. Go back to the Deep Security Manager and try to run the database upgrade. The installer will now upgrade the Deep Security Manager smoothly.

Distributed Database Servers for Tenant Databases

If there are many tenants and their databases are distributed over more than one SQL Server, the tool will need credentials for the tenant databases that are on the servers other than T0. All those tenants will display "Connection Error" under Migration Status. You will need to manually enter those credentials for each tenant.

To enter the usernames and passwords:

  1. Click the tenant from the Selected T0’s Tenants area.
  2. Click User Name and type the user name.
  3. Press the TAB key and type the password in the User Password column.

The "Connection Error" status will change to "Not Migrated".

As the name indicates, if you do not want to migrate manually, you can run the migration process as a scheduled batch job.

Once you are connected to the SQL Server and have populated the database lists on the Database Settings tab, the same tenants list will appear on the Scheduled Migration Jobs tab.

 
Schedule Migration Jobs are only available for tenants and are not applicable for T0. You need to migrate T0 using the Database Settings tab, as described in previous section.

To schedule migration jobs:

  1. Select one or more tenants (maximum of 10 tenants) using the Select check boxes in the data grid.
  2. After selecting the required tenants, click Add to Scheduled Job. If the selected database is not already migrated, it will be added to the Scheduled Jobs list.

    Add to Scheduled Job

  3. Click its Toggle button under the Toggle Schedule column to enable a job for execution.
  4. Once you toggle the job, a new dialog will appear where you can select a date and time when you want to run the job. Set the appropriate time and click OK.

    Select Job Schedule

  5. You can verify the toggled job under the Scheduled column showing a "Yes"status. The Execution Date and Time are also shown.

    Scheduled Job

To remove the schedule from the Scheduled Job, click Toggle and the Scheduled column will display "No".

To remove a job from the list of Scheduled Jobs, select the job and click Remove Selected Job. If you want to remove all the jobs, click Remove all Jobs and it will remove all jobs from the list.

Once the tool is executing a job, it cannot be removed from the list. Once the job is scheduled, you cannot exit the tool. It must be kept running. Logging out of the machine will kill the tool and any scheduled jobs will be lost. If you kill the tool, you have to recreate and reschedule all the jobs again.

Cannot close the application

During execution, a scheduled job will produce a log file in theTrendDBUpgrade – Log folder.

Scheduled jobs in TrendDBUpgrade – Log folder

Messages related to scheduled jobs do not appear in the Real-time log. When a job is done, it is removed from the Scheduled Jobs section and the Migration status under T0 Tenants List is changed to "Migrated".

If you scheduled a job under the Schedule Migration Job tab, it is scheduled to be run at a certain time. If you go back to the Database Settings tab and click Disconnect, it will disconnect the connection from the SQL Server. However, the batch job will still remain under the Scheduled Migration tab and the job will run at its scheduled time. You will not be able to close the tool and if you attempt to do so, a message will be displayed, saying that the batch job is scheduled. If you remove the scheduled job, it will close the tool.

This tab shows messages thrown by various operations being executed. The same messages are saved in their respective log files as well.

 
In scenarios where the database size is hugs (in several GBs) it may take hours for tool to migrate the database and it requires patience. It is strongly suggested to run this tool residing on MS SQL Server itself to avoid any network interruption which may cause problems in upgrading large database schema.

The extracted folder TrendDBUpgrade has these subfolders:

  • SQL_1
  • SQL_2
  • SQL_3

The subfolders contain the following SQL Script files, which should not be modified, deleted, or moved outside this folder.

  • DeepSecurityDatabaseBigintMigrationScriptSimulation.sql
  • DeepSecurityDatabaseBigintMigrationScriptTableSpaceUsageSummary.sql
  • DeepSecurityDatabaseBigintMigrationScriptTableTruncation.sql
  • DeepSecurityDatabaseBigintMigrationScript.sql
  • UnlockDatabase.sql

The tool will not run if any of the files mentioned above are missing. You MUST NOT change these files. If you do so, you may lock or corrupt your database.

Remote Query Timeout

This tool, on execution of any operation (other than Usage Summary), sets the Remote Query Timeout value to "0" (which means no timeout). The default value is 600 seconds. It reverts back this value at the end of the script execution. If the script fails for any reason, you can find the original value from the log file and manually revert it.

Tool Migration Logic

  1. Kill all active connection to database being migrated.
  2. Change the database to single user mode.
  3. Renames database and append it to "_locked". For example if database name is DSM, then it will be renamed as "DSM_locked".
  4. Change the renamed database to multi-user again.
  5. Run migration /other scripts on renamed database (usage check does not rename database).
  6. On completion, kill all active sessions.
  7. Change database to single user mode.
  8. Rename database to original name.
  9. Change database to multiuser.

If the migration script fails for any reason, it may leave a renamed database or leave the database in single user mode. To fix this, perform the following steps:

  1. Rename the database manually back to its original name by right-clicking the database name and clicking Rename.
  2. Change the Restrict Access to MULTI_USR by right-clicking the database name, clicking Properties, clicking Options, under State – Restrict Access.

Settings

Connection and Command timeout can be changed using these options. Please do not change the DSM Constraints Count without consulting SEG engineers.

During testing, it was observed that if customers are upgrading from Deep Security Managers with previously installed versions from 7.5 or 8.0 to 9.6 SP1 onwards, and that the DSM is a Multi-Tenant Environment, the installer during upgrade may see a hiccup while accessing the Database Schema and throws an error “Unable to Access Database Schema”.

If you encounter this issue:

  1. Click OK to continue.

    The installer will upgrade DSM to the required version.

  2. Log onto the DSM console, and then click Perform Database Upgrade by clicking each tenant.

    It will then upgrade the Database for all respective Tenants.

 

Option B. Manually upgrade your database using the Trend Micro DB Upgrade script

Download the Trend Micro DB Upgrade script. Updating the database using this script lets you visualize the technical details during the process.

Database Integrity Check

Before proceeding with the steps mentioned in the sections below, it is important to check first the database integrity for Deep Security Manager:

  1. Log in to SQL Server and select Deep Security Database.
  2. Right-click the database and select New Query.
  3. Go to the unzipped folder then locate and run the DeepSecurityDatabaseBigintMigrationScriptTableRecoveryCommand.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 DSM 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:
    ---------------------------------------------------------------------------
    @@@ Deep Security 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 integrityeventhistory drop constraint FDHWRNDGVSXGZGEN;
        alter table antimalwareeventhistory drop constraint FEPXAYYXJTHEXHHY;
        alter table payloadloghistory drop constraint FFOIMMXCPSVLEINU;
        alter table packetloghistory drop constraint FHJISFEARDLHSTVM;
        alter table webreputationeventhistory drop constraint FLAYJBRFYRTWJNQK;
        alter table agentevents drop constraint FRWQQDGGMZFNONZC;
        alter table activehosterrors drop constraint FSMKZGIFLEQVANFB;
        alter table loginspectioneventhistory drop constraint FWLRPXPOEPNVBAII;
        alter table systemeventhistory drop constraint FXVABGTWKSVZXOBC;
        alter table autodiagnostic drop constraint FXYGHGSHNSBGKZKP;
        alter table activehosterrors drop constraint FZOGNHGXVTVCZBKB;
    
    *** Command Dumping >>> Drop other indexes
    
        drop index antimalwareeventhistory_antimalwareeventid on antimalwareeventhistory;
        drop index packetloghistory_packetlogid on packetloghistory;
        drop index webreputationeventhistory_webreputationeventid on webreputationeventhistory;
        drop index loginspectioneventhistory_loginspectioneventid on loginspectioneventhistory;
        drop index payloadloghistory_payloadlogid on payloadloghistory;
        drop index SYSTEMEVENT_TARGETID on systemevents;
        drop index integrityeventhistory_integrityeventid on integrityeventhistory;
        drop index antimalwarequarantinedfile_antimalwareeventid on antimalwarequarantinedfile;
        drop index antimalwarespywareitems_antimalwareeventid on antimalwarespywareitems;
    
    *** Command Dumping >>> Drop primary keys
    
        alter table antimalwarequarantinedfile drop constraint PK__antimalw__4534567F1C605F9B;
        alter table antimalwareeventhistory drop constraint PK__antimalw__5488F68CB15CF160;
       alter table antimalwarespywareitems drop constraint PK__antimalw__883F3A7FDACC2ACA;
        alter table antimalwareevents drop constraint PK__antimalw__DCBD755B6B175FB2;
        alter table integrityevents drop constraint PK__integrit__D4E993CB9CB73273;
        alter table integrityeventhistory drop constraint PK__integrit__E62B054B2579A25E;
        alter table loginspectioneventhistory drop constraint PK__loginspe__B121F2899691DDB9;
        alter table loginspectionevents drop constraint PK__loginspe__DA0923121FCED7DE;
        alter table packetloghistory drop constraint PK__packetlo__5B120871DF817AEB;
        alter table packetlogdatas drop constraint PK__packetlo__AB87D12BF464FECA;
        alter table packetlogs drop constraint PK__packetlo__DAE0389B3ED367FB;
        alter table payloadlogs drop constraint PK__payloadl__396C78AD4AF2A5B0;
        alter table payloadloghistory drop constraint PK__payloadl__EE595C6B498824A3;
        alter table payloadlogdatas drop constraint PK__payloadl__F923185D54DF87E8;
        alter table systemevents drop constraint PK__systemev__29E7D892CA717E33;
        alter table webreputationeventhistory drop constraint PK__webreput__2DA345B882BA7F98;
        alter table webreputationevents drop constraint PK__webreput__78EABAABE1439FDA;
    
    *** Command Dumping >>> Recreate primary keys
    
        alter table antimalwarequarantinedfile add constraint PK__antimalw__4534567F1C605F9B primary key(AntiMalwareQuarantinedFileID);
        alter table antimalwareeventhistory add constraint PK__antimalw__5488F68CB15CF160 primary key(AntiMalwareEventHistoryID);
        alter table antimalwarespywareitems add constraint PK__antimalw__883F3A7FDACC2ACA primary key(AntiMalwareSpywareItemID);
        alter table antimalwareevents add constraint PK__antimalw__DCBD755B6B175FB2 primary key(AntiMalwareEventID);
        alter table integrityevents add constraint PK__integrit__D4E993CB9CB73273 primary key(IntegrityEventID);
        alter table integrityeventhistory add constraint PK__integrit__E62B054B2579A25E primary key(IntegrityEventHistoryID);
        alter table loginspectioneventhistory add constraint PK__loginspe__B121F2899691DDB9 primary key(LogInspectionEventHistoryID);
        alter table loginspectionevents add constraint PK__loginspe__DA0923121FCED7DE primary key(LogInspectionEventID);
        alter table packetloghistory add constraint PK__packetlo__5B120871DF817AEB primary key(PacketLogHistoryID);
        alter table packetlogdatas add constraint PK__packetlo__AB87D12BF464FECA primary key(PacketLogDataID);
        alter table packetlogs add constraint PK__packetlo__DAE0389B3ED367FB primary key(PacketLogID);
        alter table payloadlogs add constraint PK__payloadl__396C78AD4AF2A5B0 primary key(PayloadLogID);
        alter table payloadloghistory add constraint PK__payloadl__EE595C6B498824A3 primary key(PayloadLogHistoryID);
        alter table payloadlogdatas add constraint PK__payloadl__F923185D54DF87E8 primary key(PayloadLogDataID);
        alter table systemevents add constraint PK__systemev__29E7D892CA717E33 primary key(SystemEventID);
        alter table webreputationeventhistory add constraint PK__webreput__2DA345B882BA7F98 primary key(WebReputationEventHistoryID);
        alter table webreputationevents add constraint PK__webreput__78EABAABE1439FDA primary key(WebReputationEventID);
    
    *** Command Dumping >>> Recreate foreign keys
    
        alter table integrityeventhistory with check add constraint FDHWRNDGVSXGZGEN foreign key(integrityeventid) references integrityevents(integrityeventid) on delete cascade;
        alter table antimalwareeventhistory with check add constraint FEPXAYYXJTHEXHHY foreign key(antimalwareeventid) references antimalwareevents(antimalwareeventid) on delete cascade;
        alter table payloadloghistory with check add constraint FFOIMMXCPSVLEINU foreign key(payloadlogid) references payloadlogs(payloadlogid) on delete cascade;
        alter table packetloghistory with check add constraint FHJISFEARDLHSTVM foreign key(packetlogid) references packetlogs(packetlogid) on delete cascade;
        alter table webreputationeventhistory with check add constraint FLAYJBRFYRTWJNQK foreign key(webreputationeventid) references webreputationevents(webreputationeventid) on delete cascade;
        alter table agentevents with check add constraint FRWQQDGGMZFNONZC foreign key(systemeventid) references systemevents(systemeventid) on delete cascade;
        alter table activehosterrors with check add constraint FSMKZGIFLEQVANFB foreign key(endsystemeventid) references systemevents(systemeventid);
        alter table loginspectioneventhistory with check add constraint FWLRPXPOEPNVBAII foreign key(loginspectioneventid) references loginspectionevents(loginspectioneventid) on delete cascade;
        alter table systemeventhistory with check add constraint FXVABGTWKSVZXOBC foreign key(systemeventid) references systemevents(systemeventid) on delete cascade;
        alter table autodiagnostic with check add constraint FXYGHGSHNSBGKZKP foreign key(systemeventid) references systemevents(systemeventid);
        alter table activehosterrors with check add constraint FZOGNHGXVTVCZBKB 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 packetloghistory_packetlogid on packetloghistory(PacketLogID ASC);
        create NONCLUSTERED index webreputationeventhistory_webreputationeventid on webreputationeventhistory(WebReputationEventID ASC);
        create NONCLUSTERED index loginspectioneventhistory_loginspectioneventid on loginspectioneventhistory(LogInspectionEventID ASC);
        create NONCLUSTERED index payloadloghistory_payloadlogid on payloadloghistory(PayloadLogID ASC);
        create NONCLUSTERED index SYSTEMEVENT_TARGETID on systemevents(TargetID ASC);
        create NONCLUSTERED index integrityeventhistory_integrityeventid on integrityeventhistory(IntegrityEventID ASC);
        create NONCLUSTERED index antimalwarequarantinedfile_antimalwareeventid on antimalwarequarantinedfile(AntiMalwareEventID ASC);
        create NONCLUSTERED index antimalwarespywareitems_antimalwareeventid on antimalwarespywareitems(AntiMalwareEventID 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 Deep Security 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 Deep Security 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

    Database Diagrams System Event

    SET 2

    antimalwareevents
    antimalwarequarantinedfile
    antimalwarespywareitems
    antimalwareeventhistory

    Database Diagrams Antimalware

    SET 3

    webreputationevents
    webreputationeventhistory

    Database Diagrams Webrepution

    SET 4

    integrityevents
    integrityeventhistory

    Database Diagrams Integrity Monitor

    SET 5

    loginspectionevents
    loginspectioneventhistory

    Database Diagrams Log Inspection

    SET 6

    packetlogs
    packetlogdatas
    packetloghistory

    Database Diagrams Packet

    SET 7

    payloadlogs
    payloadlogdatas
    payloadloghistory

    Database Diagrams Payload

  1. Back up your Deep Security Manager (DSM) database.
  2. Check the table usage and free disk space of SQL server.
    1. Use the script DeepSecurityDatabaseBigintMigrationScriptTableSpaceUsageSummary.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) = 'webreputationevents.WebReputationEventID:BIGINT|webreputationeventhistory.WebReputationEventHistoryID: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) = 'webreputationevents.WebReputationEventID:BIGINT|webreputationeventhistory.WebReputationEventHistoryID: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 DeepSecurityDatabaseBigintMigrationScript.sql script.
  2. Open the script and modify the database name for each tenants. For example, if your database name is DSM95, change the script to [DSM95] similar to the following:

    use [DSM95]
    go

  3. Stop the Deep Security Manager service. If you are in a multi-node environment, stop the DSM service on all DSM 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 DSM 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 DSM95SP1P1UpgradeScriptSqlServer.sql -d dsm95_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 DSM database.
    Check the attached DeepSecurityDatabaseBigintMigrationScriptSampleOutput.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 DSM 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 Deep Security Manager builds to 9.5 SP1 Patch 2 build 9.5.3.6511, 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 installer to upgrade DSM as usual:
    1. Run the Deep Security Manager installer until it reaches to following screen asking for upgrade. Select the first option and click Next.

      Upgrade Verification

    2. The Upgrade Confirmation Notice will appear. Click OK.

      Confirm Upgrade

    3. Database Schema Upgrade message will appear asking for appropriate selection as shown:

      Database Schema Upgrade

    4. From above screen, if you have already performed the Database Schema Upgrade through script, and have checked the box under “Please Confirm” section, the installer will continue as usual and will upgrade the DSM.
    5. If you have not performed the schema upgrade process mentioned previously in this KB, have checked the box under “Please Confirm” section, and click NEXT, the following screen will appear.

      Upgrade not successful

    6. The installer will not continue and will exit when you click OK.
  • It is not necessary to apply this update procedure if you are using Oracle Database.
  • This Database migration is not supported in SQL Server version 2005.
  • In a multi-node environment, you need to run the upgrade installer separately. However, if Node 1 has been upgraded and schema change has already been done, upgrading the Node 2 will be the usual upgrade process.
  • In a multi-tenant environment, you have to upgrade the database schema for each tenant and then run the DSM upgrade installer.
  • In some cases, the DSM installer may not ask for upgrading the database schema before upgrading the DSM. This is because the already installed Manager’s database has already been upgraded for required schema changes. As an example, when upgrading the DSM from 9.0 SP1 Patch 5 to 9.5 SP1 Patch 2 and the installer does not ask for schema upgrade, you may proceed upgrading the DSM safely.
  • It is not recommended to do the DSM upgrade in silent mode. If you are using silent mode, the database schema upgrade window will not appear.
Premium
Internal
Rating:
Category:
Update
Solution Id:
1112218
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.