Symantec Data Center Security: Server Advanced Management Server leverages SQL Server partitioning feature

Products

• Data Center Security Monitoring Edition
• Data Center Security Server
• Data Center Security Server Advanced

Issue/Introduction

Purge operation used to take long time when there is a high incoming event rate, which resulted in Symantec Data Center Security: Server Advanced (SDCS: SA) agents going offline for the duration when the purge operation was in progress.

Environment

Applicable to SDCS: SA Management Server version 6.6 MP1 and earlier.

Cause

With high incoming event rate, purge job used to take time to get completed. The time taken to purge the event depends on the number of events in the database, taking exclusive lock and repeatedly blocking inserts. This causes other queries to wait and in turn the requests coming from agents to servers are in hung state since tomcat connection pool is exhausted. This was resulting in a failed communication between agents and management server. As a result, agents were showing offline in UI.

Resolution

Symantec recommends limiting the event data stored in the database. From SDCS: SA 6.7 onwards purge operation is mandatory. The purge feature is redesigned to use the MS SQL Server partitioning feature.

The CSPEVENT, ANALYSYS_EVENT, and PROFILE_EVENT tables are partitioned based on the Date column. 732 physical partitions are created during the installation. Each partition stores data for one day. Based on the purge configuration settings, individual partitions are dropped during purge operation. This leads to significant improvement in time to complete the purge operation.

With SDCS: SA 6.7 and later, purge operation is mandatory and is enabled by default. The maximum data retention limit for Purge is 550 days. This is to keep the data in manageable limits, and help computers run smoothly. 

If you are upgrading SDCS: SA Management Server from 6.6 MP1 or earlier versions to 6.7 or later versions, following changes are made in purge settings based on the pre-upgrade use cases:

Use case 1: If purge is enabled pre-upgrade, then post-upgrade:

• If data retention limit is set to more than 7 days and less than 550 days, then the installer retains the data retention value post-upgrade
• If data retention limit is set to less than 7 days, then the installer sets the data retention value to 7 days
• If data retention limit is set to greater than 550 days, then the installer sets the data retention value to 550 days

Use case 2: If purge is disabled pre-upgrade, then post-upgrade:

• The purge is enabled 
• Data retention limit is set to 550 days

The SQL Server partitioning feature for the SDCS: SA Server is only available in SQL Server Enterprise edition. You must have this version to be able to utilize the partition feature. On all the other SQL Server editions, Purge operation will work as it was in the prior releases on the SDCS: SA.