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.

   

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?

   

   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.

      

   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

      

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

      

   7. You may check the installed dependencies under Control Panel > 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.

   

   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.

   

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.

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.

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.

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.

   

   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.

      

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.

   

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

   

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

   

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

   

   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.

   

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

   

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

   

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.

   

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:

   

   

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".

   

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.

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.

   

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.

   

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

   

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.

During execution, a scheduled job will produce a log file in theTrendDBUpgrade – 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.

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.

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

   

   SET 2

   antimalwareevents
   antimalwarequarantinedfile
   antimalwarespywareitems
   antimalwareeventhistory

   

   SET 3

   webreputationevents
   webreputationeventhistory

   

   SET 4

   integrityevents
   integrityeventhistory

   

   SET 5

   loginspectionevents
   loginspectioneventhistory

   

   SET 6

   packetlogs
   packetlogdatas
   packetloghistory

   

   SET 7

   payloadlogs
   payloadlogdatas
   payloadloghistory

   

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)".

   

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.

      

   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.

      

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.

      

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

      

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

      

   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.

      

   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.