Thousands of EEPC v5 customers will be moving to v6 this year. This upgrade includes a database migration step. To make this migration as fast an easy as possible, I've put together a list of best practices for properly maintaining a v5 database. These should be implemented on all v5 databases, but they are especially important for customers planning an upgrade to v6.
Note: Do not make these changes on your production database. First, make a copy of your production database and move it to a test server. Then execute these steps on a test server. Once the steps have been successfully executed and validated in a test environment, simply repeat them on the production server (with the database service stopped).
Step 1: Is My Database Broken?
The first step is to check the database for corrupt objects. Corruption can happen for lots of reasons, but the most common ones are too much concurrency and another application interfering with our service's attempts to write to the database (such as antivirus software). Corruption usually causes errors that are hidden from the administrator, so it is difficult to know if you have corruption. The command line tool (SBADMCL) can be used to check for corruption.
Find corrupt user objects by running the command CleanupUserGroup. This command will find the corrupt objects and delete them. Note that the group parameter does not support asterisk (*) as an input, so you need to do each group individually. Also, this command does not support the -file parameter, so if you want to save the results you'll have to pipe it to a file by appending >filename.txt to the end of the command.
Syntax: SBADMCL -adminuser:admin -adminpwd:password -command:cleanupusergroup -group:"endpoint encryption users"
Find corrupt machine objects by running the command CleanupMachineGroup.This is the same as CleanupUserGroup, but it evaluates machine objects.
Syntax: SBADMCL -adminuser:admin -adminpwd:password -command:cleanupmachinegroup -group:"endpoint encryption machines"
Find orphaned user objects and restore them with the RestoreUsers command. This is the same as running a group scan from the UI, but using the command line allows you to automate it.The -group parameter allows you to specify the group to which orphaned objects should be restored.
Syntax: SBADMCL -adminuser:admin -adminpwd:password -command:restoreusers -group:"orphaned users"
Find orphaned machine objects and restore them with the RestoreMachines command. This is the same as the RestoreUsers command, but for machine objects.
Syntax: SBADMCL -adminuser:admin -adminpwd:password -command:restoremachines -group:"orphaned machines"
The goal of this first exercise is to find all invalid objects and deleted them, and then to find all orphaned objects and restore them. If you complete these steps, you will have found all instances of database "corruption" and fixed them.
Step 2: Purge
Like all databases, the EEPC v5 database needs to be pruned regularly. The best practice is to first delete old objects, and then purge the audit data for the remaining objects.
Identify old objects with the ShowOldUsers and ShowOldMachines commands. Then use this data as input to the DeleteUser and DeleteMachine commands. This cannot be done with a single command, so you will need to use a custom script. I have attached an example batch script here, but would recommend using professional services to assist with this script. McAfee cannot support custom scripts, only the underlying commands.
Clear the audit data for all users with the DumpUserAudit command. You can use the -cleardaysold parameter to set how many days of data to retain. This command does support the asterisk (*) in the -group parameter. Consider retaining more data for administrators whose audits contain more meaningful events (admin activities like password resets, etc). For end users I recommend retaining no more than 10 days of data.
Syntax: SBADMCL -command:dumpuseraudit -adminuser:admin -adminpwd:password -file:all_users_audit.txt -group:* -cleardaysold:10
Clear audit data for all machines with the DumpMachineAudit command. This is the same as the DumpUserAudit command, but it looks at machine objects.
Syntax: SBADMCL -command:dumpmachineaudit -adminuser:admin -adminpwd:password -file:all_machines_audit.txt -group:* -cleardaysold:10
The goal of step two is to remove old an unnecessary data from the database. If you have deleted old objects and purged the audits for the remaining objects, then you are done with this step.
Step 3: Tune for Maximum Performance
We have written a general best practices guide for EEPC. I recommend reading that in its entirety, but I'll highlight three key points in this document.
Index the database with a dbcfg.ini file. If you already have indexing in place, make sure you are properly managing the re-indexing. To enable indexing, you simply drop a dbcfg.ini file into the SBDATA directory. It will then index the database and create a names.dat and several names.0xx files in the database's file structure.
Example in C:\Program Files\McAfee\Endpoint Encryption Manager\SBDATA\00000001
Check that the timestamp next to these files is in the last one day for smaller deployments (25,000 or less) and within the last seven days for larger deployments. This ensures that your database is re-indexing on its expected schedule. If it is not, then work with professional services to get a toastcache script or to work out another process.
Check your antivirus settings. It is not sufficient to simply exclude the SBDATA directory from scanning. You should also mark all of the applications as "low risk" or exclude them from scanning. The most important application is sbdbserver.exe, but others like sbadmin.exe sbadmcl.exe should be considered as well. If you are using McAfee VSE, do not scan these applications on reads or writes.
Finally, monitor the number of concurrent connections on your server. You can do this by logging into the console and going to the system tab. Then expand the list of servers and right-click on your active server. Select "Show Status". In the log window, it will show the number of concurrent connections. If this is above 100, you should tune your sync settings. Increase the regular sync to 360 or 480 minutes (or higher if necessary). As a reference point, I have a customer in the US who has 115,00 nodes reporting to their database and their average number of concurrent connections is between 75 and 90. If you have more than this, you probably have set your sync settings too aggressively.