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 build:
- 9.6 SP1 Build 9.6.3177
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:
- 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.
Double-click the TrendDBMigrator.exe file.
The following are the MD5 and SHA 256 value of the TrendDBMigrator.exe file:
- 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.
- 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:
- 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.
- Walk through the wizard and select .NET framework 3.5 Feature.
- Complete the installation. This may take several minutes to complete.
- 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.
- 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:
- When the download is finished, a confirmation message appears. Click OK to start the tool.
- You may check the installed dependencies under Control Panel > Program and Features.
- 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 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:
- Click the database name from T0 and do not select any Tn.
- Click the Migrate-Selected Database button.
- 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:
- 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.
- Read the information that will appear and click OK.
- Read the warning that will show and click Yes.
- 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.
- 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.
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.
- Select your tenants from the Selected T0’s Tenants area.
- Click the Migrate-Selected Tenants button to the tenant databases.
- 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.
- 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:
- 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".
- 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:
- Click the tenant from the Selected T0’s Tenants area.
- Click User Name and type the user name.
- 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:
- Select one or more tenants (maximum of 10 tenants) using the Select check boxes in the data grid.
- 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.
- Click its Toggle button under the Toggle Schedule column to enable a job for execution.
- 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.
- 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.
The extracted folder TrendDBUpgrade has these subfolders:
The subfolders contain the following SQL Script files, which should not be modified, deleted, or moved outside this folder.
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
- Kill all active connection to database being migrated.
- Change the database to single user mode.
- Renames database and append it to "_locked". For example if database name is DSM, then it will be renamed as "DSM_locked".
- Change the renamed database to multi-user again.
- Run migration /other scripts on renamed database (usage check does not rename database).
- On completion, kill all active sessions.
- Change database to single user mode.
- Rename database to original name.
- 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:
- Rename the database manually back to its original name by right-clicking the database name and clicking Rename.
- Change the Restrict Access to MULTI_USR by right-clicking the database name, clicking Properties, clicking Options, under State – Restrict Access.
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:
Click OK to continue.
The installer will upgrade DSM to the required version.
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:
- Log in to SQL Server and select Deep Security Database.
- Right-click the database and select New Query.
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.
- Click Parse on the SQL Server.
Click Execute on the SQL Server.
--------------------------------------------------------------------------- @@@ 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 supersededentitys drop constraint FDELRWDQNANZBTDP; alter table activehosterrors drop constraint FGIDLYDCGZSYLSZE; alter table payloadloghistory drop constraint FHBRICICKXPTWOXX; alter table loginspectioneventhistory drop constraint FJVLKGHYWLQTAEST; alter table antimalwareeventhistory drop constraint FQTZMGJBQNJKUDUJ; alter table systemeventhistory drop constraint FSRVJVFEEJUAMZMU; alter table autodiagnostic drop constraint FTTWZQQIRBJVSLJC; alter table attribute2s drop constraint FVKNSJGTTBVZLLTN; alter table integrityeventhistory drop constraint FWKPIPICZXPRQFPB; alter table activehosterrors drop constraint FWWBLAWGOOENKHQM; alter table agentevents drop constraint FXEGNIXMSHDHULND; alter table webreputationeventhistory drop constraint FYJWYDZJJEPUDHKV; alter table packetloghistory drop constraint FYTKZYANYYKOPJUH; *** Command Dumping >>> Drop other indexes drop index antimalwareeventhistory_antimalwareeventid on antimalwareeventhistory; drop index antimalwarequarantinedfile_antimalwareeventid on antimalwarequarantinedfile; drop index antimalwarespywareitems_antimalwareeventid on antimalwarespywareitems; drop index attribute2s_entityid on attribute2s; 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 supersededentitys_wasentityid on supersededentitys; drop index SYSTEMEVENT_TARGETID on systemevents; drop index webreputationeventhistory_webreputationeventid on webreputationeventhistory; *** Command Dumping >>> Drop primary keys alter table antimalwarequarantinedfile drop constraint PK__antimalw__4534567F4890A6B3; alter table antimalwareeventhistory drop constraint PK__antimalw__5488F68C7D5974AD; alter table antimalwarespywareitems drop constraint PK__antimalw__883F3A7F4C613797; alter table antimalwareevents drop constraint PK__antimalw__DCBD755B63CEACD4; alter table entitys drop constraint PK__entitys__9C892FFD0C46B282; alter table integrityevents drop constraint PK__integrit__D4E993CB0623C4D8; alter table integrityeventhistory drop constraint PK__integrit__E62B054B19018F22; alter table loginspectioneventhistory drop constraint PK__loginspe__B121F2892937F6EB; alter table loginspectionevents drop constraint PK__loginspe__DA0923121471E42F; alter table packetloghistory drop constraint PK__packetlo__5B1208713E3313D1; alter table packetlogdatas drop constraint PK__packetlo__AB87D12B3DDE0E16; alter table packetlogs drop constraint PK__packetlo__DAE0389B34DEB3C1; alter table payloadlogs drop constraint PK__payloadl__396C78AD39A368DE; alter table payloadloghistory drop constraint PK__payloadl__EE595C6B4C813328; alter table payloadlogdatas drop constraint PK__payloadl__F923185D41AE9EFA; alter table supersededentitys drop constraint PK__supersed__A9FBAF3D49D9D0A7; alter table systemevents drop constraint PK__systemev__29E7D8924E9E85C4; alter table webreputationeventhistory drop constraint PK__webreput__2DA345B817D860BF; alter table webreputationevents drop constraint PK__webreput__78EABAAB685E57C7; *** Command Dumping >>> Recreate primary keys alter table antimalwarequarantinedfile add constraint PK__antimalw__4534567F4890A6B3 primary key(AntiMalwareQuarantinedFileID); alter table antimalwareeventhistory add constraint PK__antimalw__5488F68C7D5974AD primary key(AntiMalwareEventHistoryID); alter table antimalwarespywareitems add constraint PK__antimalw__883F3A7F4C613797 primary key(AntiMalwareSpywareItemID); alter table antimalwareevents add constraint PK__antimalw__DCBD755B63CEACD4 primary key(AntiMalwareEventID); alter table entitys add constraint PK__entitys__9C892FFD0C46B282 primary key(EntityID); alter table integrityevents add constraint PK__integrit__D4E993CB0623C4D8 primary key(IntegrityEventID); alter table integrityeventhistory add constraint PK__integrit__E62B054B19018F22 primary key(IntegrityEventHistoryID); alter table loginspectioneventhistory add constraint PK__loginspe__B121F2892937F6EB primary key(LogInspectionEventHistoryID); alter table loginspectionevents add constraint PK__loginspe__DA0923121471E42F primary key(LogInspectionEventID); alter table packetloghistory add constraint PK__packetlo__5B1208713E3313D1 primary key(PacketLogHistoryID); alter table packetlogdatas add constraint PK__packetlo__AB87D12B3DDE0E16 primary key(PacketLogDataID); alter table packetlogs add constraint PK__packetlo__DAE0389B34DEB3C1 primary key(PacketLogID); alter table payloadlogs add constraint PK__payloadl__396C78AD39A368DE primary key(PayloadLogID); alter table payloadloghistory add constraint PK__payloadl__EE595C6B4C813328 primary key(PayloadLogHistoryID); alter table payloadlogdatas add constraint PK__payloadl__F923185D41AE9EFA primary key(PayloadLogDataID); alter table supersededentitys add constraint PK__supersed__A9FBAF3D49D9D0A7 primary key(SupersededEntityID); alter table systemevents add constraint PK__systemev__29E7D8924E9E85C4 primary key(SystemEventID); alter table webreputationeventhistory add constraint PK__webreput__2DA345B817D860BF primary key(WebReputationEventHistoryID); alter table webreputationevents add constraint PK__webreput__78EABAAB685E57C7 primary key(WebReputationEventID); *** Command Dumping >>> Recreate foreign keys alter table supersededentitys with check add constraint FDELRWDQNANZBTDP foreign key(wasentityid) references entitys(entityid) on delete cascade; alter table activehosterrors with check add constraint FGIDLYDCGZSYLSZE foreign key(startsystemeventid) references systemevents(systemeventid); alter table payloadloghistory with check add constraint FHBRICICKXPTWOXX foreign key(payloadlogid) references payloadlogs(payloadlogid) on delete cascade; alter table loginspectioneventhistory with check add constraint FJVLKGHYWLQTAEST foreign key(loginspectioneventid) references loginspectionevents(loginspectioneventid) on delete cascade; alter table antimalwareeventhistory with check add constraint FQTZMGJBQNJKUDUJ foreign key(antimalwareeventid) references antimalwareevents(antimalwareeventid) on delete cascade; alter table systemeventhistory with check add constraint FSRVJVFEEJUAMZMU foreign key(systemeventid) references systemevents(systemeventid) on delete cascade; alter table autodiagnostic with check add constraint FTTWZQQIRBJVSLJC foreign key(systemeventid) references systemevents(systemeventid); alter table attribute2s with check add constraint FVKNSJGTTBVZLLTN foreign key(entityid) references entitys(entityid) on delete cascade; alter table integrityeventhistory with check add constraint FWKPIPICZXPRQFPB foreign key(integrityeventid) references integrityevents(integrityeventid) on delete cascade; alter table activehosterrors with check add constraint FWWBLAWGOOENKHQM foreign key(endsystemeventid) references systemevents(systemeventid); alter table agentevents with check add constraint FXEGNIXMSHDHULND foreign key(systemeventid) references systemevents(systemeventid) on delete cascade; alter table webreputationeventhistory with check add constraint FYJWYDZJJEPUDHKV foreign key(webreputationeventid) references webreputationevents(webreputationeventid) on delete cascade; alter table packetloghistory with check add constraint FYTKZYANYYKOPJUH foreign key(packetlogid) references packetlogs(packetlogid) on delete cascade; *** 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 antimalwarespywareitems_antimalwareeventid on antimalwarespywareitems(AntiMalwareEventID ASC); create NONCLUSTERED index attribute2s_entityid on attribute2s(EntityID 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 supersededentitys_wasentityid on supersededentitys(WasEntityID ASC); create NONCLUSTERED index SYSTEMEVENT_TARGETID on systemevents(TargetID ASC); create NONCLUSTERED index webreputationeventhistory_webreputationeventid on webreputationeventhistory(WebReputationEventID ASC);
- Note that the names of keys and indexes could be different.
Database Diagram Check
In order to verify if the relationship between tables is correct, check and match the database diagrams:
- Log in to the SQL Server and select Deep Security Database.
- Open database > Database Diagrams.
- Right-click the Database Diagrams and select New Database Diagrams.
Choose the following table pairs for checking their relationships and then click Add.
There are 8 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.
- Back up your Deep Security Manager (DSM) database.
- Check the table usage and free disk space of SQL server.
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
- 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.
- Change the SQL server remote query connection timeout to "0 (No Timeout)". Remember to change back the SQL server remote query timeout value to default one after you upgrade the Deep Security Manager.
- 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.
If you upgrade from the following DSM versions to 9.6 SP1, there is no benefit to truncate the EVENT tables. You can skip them.
- Deep Security Manager 9.0 SP1 Patch 5 Build 9.0.7007
- Deep Security Manager 9.5 SP1 Patch3 Build 9.5.7008
- Deep Security Manager 9.6 Build 9.6.1589
- Deep Security Manager 9.6 Patch1 Build 9.6.2500
However, if you encountered script failed due to disk space and you decide to truncate Integrity Monitor related table, here are something you need to pay attention:
For truncating Integrity Monitor related table, modify the script declaration as below:
--declare @altercolumnspec nvarchar(max) = 'systemevents.TargetID:BIGINT:null|antimalwareevents.AntiMalwareEventID:BIGINT|... --declare @altercolumnspec nvarchar(max) = 'systemevents.TargetID:BIGINT:null'; --declare @altercolumnspec nvarchar(max) = 'antimalwareevents.AntiMalwareEventID:BIGINT|... --declare @altercolumnspec nvarchar(max) = 'webreputationevents.WebReputationEventID:BIGINT|... --declare @altercolumnspec nvarchar(max) = 'integrityevents.IntegrityEventID:BIGINT|... --declare @altercolumnspec nvarchar(max) = 'loginspectionevents.LogInspectionEventID:BIGINT|... --declare @altercolumnspec nvarchar(max) = 'packetlogs.PacketLogID:BIGINT|... --declare @altercolumnspec nvarchar(max) = 'payloadlogs.PayloadLogID:BIGINT|... declare @altercolumnspec nvarchar(max) = 'entitys.EntityID:BIGINT|...
After truncating the entitys, attribute2s, and supersededentitys tables, you need to be aware:
- All baseline information of every agents will be lost.
- Integrity Monitor baseline of every agents need to be rebuilt from DSM.
- Until baseline rebuilding complete, integrity monitoring functionality will be limited.
- Open the script DeepSecurityDatabaseBigintMigrationScriptTableTruncation.sql.
Modify the following code to specify which part you want to truncate. By default, it will truncate all the migrating tables.
--declare @altercolumnspec nvarchar(max) = 'systemevents.TargetID:BIGINT:null|antimalwareevents.AntiMalwareEventID:BIGINT|... --declare @altercolumnspec nvarchar(max) = 'systemevents.TargetID:BIGINT:null'; --declare @altercolumnspec nvarchar(max) = 'antimalwareevents.AntiMalwareEventID:BIGINT|... --declare @altercolumnspec nvarchar(max) = 'webreputationevents.WebReputationEventID:BIGINT|... --declare @altercolumnspec nvarchar(max) = 'integrityevents.IntegrityEventID:BIGINT|... --declare @altercolumnspec nvarchar(max) = 'loginspectionevents.LogInspectionEventID:BIGINT|... --declare @altercolumnspec nvarchar(max) = 'packetlogs.PacketLogID:BIGINT|... --declare @altercolumnspec nvarchar(max) = 'payloadlogs.PayloadLogID:BIGINT|... --declare @altercolumnspec nvarchar(max) = 'entitys.EntityID: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(max) = 'systemevents.TargetID:BIGINT:null|antimalwareevents.AntiMalwareEventID:BIGINT|... --declare @altercolumnspec nvarchar(max) = 'systemevents.TargetID:BIGINT:null'; --declare @altercolumnspec nvarchar(max) = 'antimalwareevents.AntiMalwareEventID:BIGINT|... --declare @altercolumnspec nvarchar(max) = 'webreputationevents.WebReputationEventID:BIGINT|... declare @altercolumnspec nvarchar(max) = 'integrityevents.IntegrityEventID:BIGINT|... --declare @altercolumnspec nvarchar(max) = 'loginspectionevents.LogInspectionEventID:BIGINT|... --declare @altercolumnspec nvarchar(max) = 'packetlogs.PacketLogID:BIGINT|... --declare @altercolumnspec nvarchar(max) = 'payloadlogs.PayloadLogID:BIGINT|... --declare @altercolumnspec nvarchar(max) = 'entitys.EntityID:BIGINT|...
Truncating the systemevents table will also remove the following tables for data consistency:
- Go to the unzipped folder then locate the DeepSecurityDatabaseBigintMigrationScript.sql script.
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:
- Stop the Deep Security Manager service. If you are in a multi-node environment, stop the DSM service on all DSM nodes.
Run the script using the SQL Management Studio console or the SQLCMD tool.
Option 1: Using the SQL Management Studio console
- Open the SQL Management Studio and start a new query.
- Copy the modified SQL script into this new query and press CTRL + A to select all.
- Press F5 to run the script, and wait until it is finished.
Check the Messages window to view the result.
Option 2: Using the SQLCMD tool
- Copy the modified SQL script in C:\ drive of the DSM database server.
- Open a CMD command window and switch the current path to C:\.
Run following command to run the sql script:
C:\>sqlcmd -i DeepSecurityDatabaseBigintMigrationScript.sql -d dsm95_2 -s 127.0.0.1 > result.logIf username and password are required, use –U $username –P $password to appoint the username and password.
Open C:\result.log to check the result.
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 LogScript.txt file existed in DeepSecurityDatabaseBigintMigrationScript for detailed logging information.
- If you have a multi-tenant environment, run Steps 2 to 4 for each tenant database.
- 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:
- Troubleshoot the issue using the SQL error or any MS SQL server log.
- Once the issue has been fixed, restore the database and run the script again.
To upgrade older Deep Security Manager builds to 9.6 SP1 build 9.6.3177, the process will pass you through two mandatory upgrade steps:
- Upgrade the database schema first, using those scripts mentioned in above section.
- Run the installer to upgrade DSM as usual:
- Run the Deep Security Manager installer until it reaches to following screen asking for upgrade. Select the first option and click Next.
- The Upgrade Confirmation Notice will appear. Click OK.
- Database Schema Upgrade message will appear asking for appropriate selection as shown:
- 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.
- 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.
- 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.
- 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.