SEP Manager upgrade to 14.0 RU1 fails on database schema update

book

Article ID: 170372

calendar_today

Updated On:

Products

Endpoint Protection

Issue/Introduction

You are upgrading a SEP Manager with SQL database to 14.0 RU1 and the upgrade process reports an error during the database schema update.

Errors might be slightly different depending on the environment, but they all reference a dependency on column 'IP_ADDR1'.

Examples of error / exception from "upgrade.log" file: 

 

2017-10-27 11:33:33.410 THREAD 25 SEVERE: SQL Exception:
2017-10-27 11:33:33.410 THREAD 25 SEVERE: SQL Command:                ALTER TABLE SEM_COMPUTER ALTER COLUMN IP_ADDR1 VARBINARY(16) NULL
2017-10-27 11:33:33.410 THREAD 25 SEVERE: SQLState:  S0001
2017-10-27 11:33:33.410 THREAD 25 SEVERE: Message:  The index 'missing_index_4633607_4633606' is dependent on column 'IP_ADDR1'.
2017-10-27 11:33:33.410 THREAD 25 SEVERE: Vendor:  5074
2017-10-27 11:33:33.410 THREAD 25 SEVERE: com.microsoft.sqlserver.jdbc.SQLServerException: The index 'missing_index_4633607_4633606' is dependent on column 'IP_ADDR1'.
2017-10-27 11:33:33.410 THREAD 25 SEVERE:       at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)

 

Cause

SEP Manager 14.0 RU1 upgrades the database schema of the previous version and the column "IP_ADDR1" changes its data type from “big_int” to “var_binary”.

 

IF  EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_SEM_COMPUTER_ID_PLUS' )     DROP INDEX SEM_COMPUTER.I_SEM_COMPUTER_ID_PLUS

IF  EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'I_SEM_COMPUTER_DELETED' )     DROP INDEX SEM_COMPUTER.I_SEM_COMPUTER_DELETED

ALTER TABLE SEM_COMPUTER       ALTER COLUMN IP_ADDR1 VARBINARY(16) NULL

 

To perform this change, all INDEX tables related to "IP_ADDR1" need to be dropped. The upgrade script drops tables normally present on a SEPM database, but if other custom tables are present they aren't handled by the upgrade script and will prevent the change of "IP_ADDR1" column.

 

One common scenario that introduces additional indexes is the usage of "Missing Indexes" feature that is part of SQL server from version 2005.

The script reported in this blog post will use this feature and output queries that can be used to create indexes to improve performance:

https://blogs.msdn.microsoft.com/bartd/2007/07/19/are-you-using-sqls-missing-index-dmvs/

 

Another example in below blog post by Microsoft on adding missing indexes:

https://blogs.technet.microsoft.com/tip_of_the_day/2015/02/27/sql-tip-of-the-day-gathering-missing-indexes/

 

Resolution

Any custom index tables created referencing "IP_ADDR1" will need to be deleted before the upgrade to SEP 14.0 RU1.

 

You can remove the tables referenced in the "upgrade.log" errors and resume the upgrade of SEP Manager running the "upgrade.bat" batch file from path:

"..\Symantec Endpoint Protection Manager\bin".