Maintenance / Performance Best Practices for EngageIP Billing

Summary

This article is provided as a guideline for daily, monthly and yearly system maintenance and backup of the EngageIP Billing platform.

The content below describes file, folder and general server maintenance required to keep EngageIP running optimally and ensuring that a minimum level of backups are kept in case of system failure or data integrity issues.

It's also suggested that you keep regular off site backups in addition to having a dedicated backup hard drive attached to the server for miscellaneous data storage and backups.

Note: LogiSense does not support SQL / Server maintenance plans but we've found the steps below to be the best practices for EngageIP installation and maintenance

Performance Checklist - Front End / Adminportal

The basic steps below can be used to troubleshoot observed speed/performance issues:

Workstation specific performance issues:

Ensure the web browser is up to date and performance issues have not appeared since switching to an alternate browser
Ensure there are no CPU usage issues (this can be caused by having too many browser tabs open or multiple applications running)
Ensure you have rebooted your workstation to rule out any temporary OS or application issues impacting performance
Determine if the issue is network related (e.g. does the speed/performance issue only persist when accessing EngageIP over a VPN or the Internet)

Server Side Checks:

Check the EngageIP Event Log (Reports tab > Event Log) to see if any major activity is occurring (billing, credit card processing, etc.)
Determine if any reports are being run which can temporarily tie up system resources (Accounting personnel running an aged report for example)
Determine if any jobs are in the job queue which the EngageIP is processing (Reports tab > Jobs). If so ensure performance issues do not continue when jobs are complete
Ensure EngageIP Services are running and operating normally. E.g. if the Event Service is running but workflows are not executing or emails being sent then restart the Event Service. For more information on EngageIP Windows Services see the EngageIP Windows Services Overview article

Configuration Tweaks:

The blue boxes in the image below indicate the locations you can adjust to best fit your requirements and limit or manage database size and data retention.

Logging Check Boxes: Do you need to use domain model logging which is a deeper level logging than just web UI logging?
Retention Periods: Do you need to maintain data beyond 2 or 3 months? Minimize the retention period as much as possible. Remember that if you're storing backups of the database, those too will have record of logs if you need to locate information.

Performance Checklist - Server Related

In SQL and on the Webserver review the following items to ensure the best performance from EngageIP:

If possible, separate Operating system files and EngageIP billing files (program folders or database files) onto different physical hard drives (unless you're using a RAID configuration)
Ensure that there is enough RAM on the server for processes. RAM is usually easy to increase on servers and provides a tangible performance increase. If there are is only a small amount of free RAM on the server, consider increasing the RAM so that the system has a 'fair amount' of free RAM available for any periods of high activity (processing spikes)
- If there are further questions about whether there is enough RAM on the server, please consult LogiSense Customer Care for suggestions and assistance
Ensure that the server(s) are only running EngageIP and that there are no other processes taking up CPU / RAM / IO from hard drives. Processes unrelated to EngageIP can leach performance from EngageIP, therefore it is recommended that EngageIP servers (web and SQL) are dedicated to run the EngageIP platform alone
Check for Windows Event Log errors that may indicate a problem on the server which may impact performance
Check that the connection method between SQL and web servers is based on IP address. In some cases using host names can slow communication if the hostname needs to be resolved each time. A slow/busy DNS server can also be the cause of performance issues when a hostname is used instead of an IP
Create SQL maintenance plan (as described below) to keep SQL running at peak performance
Add OFF server and OFF site backup systems or drives in case of hard drive or file corruption or damage. Some examples are as follows:
- External USB drives
- Network backup servers
- Mirrored / Clustered SQL services

Threading Detailed

EngageIP can also utilize threading if you have multiple CPUs. A configuration option in the EngageIP database can be set to define how many threads to use when executing bill runs. It is recommended to set threads to equal but not exceed 2/3rds of the cores in the system. For example, if you have an eight core system the optimal threading configuration would be five or six threads. Since the ideal configuration depends on other factors such as system memory the threads setting may need to be adjusted higher or lower to match your given system specs. Please email or call LogiSense support to determine the ideal configuration given your server specs.

Bill Run threading will bill multiple users simultaneously, specifically one user per thread. For example if a bill group exists with multiple users then user-packages for customer A will be billed by one thread while user-packages for customer B are billed by another thread. If EngageIP were configured to use eight threads then up to eight users can be billed at a time.

Daily / Regular Maintenance Schedule

Setup SQL server maintenance plan to index, backup, and check integrity weekly or twice a week, whichever is desired. The databases for EngageIP 8 include (though all may not be installed): BOSS, BOSSCollector, BOSSRate (usually this db is not present) and EngageIP Master
Setup Microsoft Backup to keep weekly backups of EngageIP Billing folder, etc. that may need backing up. This is located in 'Programs / Accessories / System Tools / Backup'
Perform analysis of system performance including hard drive space and RAM
1. Ensure that SQL is setup optimally for use of RAM, CPU attenuation, overall integrity of SQL installation. Maximum server memory should be set to 90% of available RAM, minimum server memory should be set to 80% of available RAM. A minimum of 2GB should be left for the operating system
2. Review running applications and tasks on the server to ensure they are not interfering with EngageIP (e.g. anti virus, scheduled tasks, etc.). For example, ensure that real-time virus scanning is not configured to scan the EngageIP Billing folders as files may need to be read or written to as part of regular processes, antivirus real-time protection may inhibit this action
Perform analysis of SQL Server CPU Configuration
1. Set the "max degree of parallelism" SQL Server can use for a parallel query to 50% of available CPU Cores
2. Increase the default "cost threshold for parallelism" from 5 to 20
Perform an analysis of the SQL Server tempdb Configuration
1. Ensure that the tempdb files are multiplexed. A good rule of thumb is one tempdb file for every two CPU cores up to eight tempdb files. Beyond eight tempdb files brings limited benefits
2. All tempdb files should be the same size after a system start. Tempdb files will individually grow in size to the maximum transaction size. Ensure there is sufficient disk space to allow this growth to happen

Real-Time Database Indexing

As of EngageIP 8.5.27.0 Index maintenance procedures, scripts and SQL Agent jobs are included to continuously maintain the EngageIP indexes. Schedules are created to maintain different groups of indexes by size and or by type. Index reorganizations, rebuilds may be performed as well as Index statistics being generated, all on different schedules. These procedures and jobs are all parameterized to allow fine tuning of the Index maintenance to fit any environment

Real-Time Database indexing is used to Improve Invoice Rendering Speed and General Interface Performance.

To index the database in real-time as needed:

Open Studio Management
Click the 'New Query' button
Then paste in the SQL below and select the appropriate database (typically named 'boss')
USE BOSS --Enter the name of the database you want to reindex DECLARE @TableName varchar(255) DECLARE TableCursor CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN DBCC DBREINDEX(@TableName,' ',90) FETCH NEXT FROM TableCursor INTO @TableName END
Update the database name in the query if its not ‘boss
Execute
Note: this query may take several minutes, wait until it's complete and then review invoices / the interface to determine if there is any performance improvement

Monthly Maintenance Schedule

Check Windows Event logs, EngageIP Event Log and log files for miscellaneous errors that may indicate issues
Ensure integrity of maintenance plan and backed up database files

Yearly Maintenance Schedule

Clean up hard drive of contents that are no longer be needed
Check overall health of system
Check for malicious processes or any other software that may indicate system has been compromised in a way that may affect EngageIP Billing
Perform a system review and determine if software upgrades, security fixes or any other notable enhancements would be beneficial or crucial for the operation of EngageIP

Manually Reindexing A Specific Database:

Open Query Analyzer or a new query in SQL 2005
Paste the following code
The below indexes the WHOLE database including usage table
USE BOSS --Enter the name of the database you want to reindex DECLARE @TableName VARCHAR(255) DECLARE TableCursor CURSOR FOR SELECT TABLE_NAME FROM information_schema.tables WHERE table_type = 'base table' OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN DBCC DBREINDEX(@TableName,' ',90) FETCH NEXT FROM TableCursor INTO @TableName END

The below index script skips the usage tables so as to shorten the run time and index the critical GUI critical tables

DECLARE @sql NVARCHAR(500) DECLARE @TableName varchar(255) DECLARE @Message varchar(255) DECLARE @fillfactor INT SET @fillfactor = 80 DECLARE TableCursor CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN SET @Message = 'Starting ... '+ @TableName IF (CHARINDEX('CDR',@TableName) <>0 OR CHARINDEX('Log',@TableName) <>0 OR CHARINDEX('UDR',@TableName) <>0) BEGIN SET @Message = 'Skipping '+ @TableName RAISERROR (@Message , 0, 1) WITH NOWAIT; END ELSE BEGIN RAISERROR (@Message, 0, 1) WITH NOWAIT; SET @sql = 'ALTER INDEX ALL ON [' + @TableName + '] REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' EXEC (@sql) END SET @Message = 'Done ... '+ @TableName RAISERROR (@Message , 0, 1) WITH NOWAIT; FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor DEALLOCATE TableCursor EXEC sp_updatestats
Update the reference of the database (in this case it reads 'BOSS')
Click Execute
Wait for it to finish, then you can close the window

Database Backup Process

For MSSQL 2014, see this article: http://msdn.microsoft.com/en-us/library/ms187510.aspx

EngageIP UDR Storage and Large Files

UDR Records on a system may take significant amounts of space. For usage, the file EngageIPUDRFILEGROUP.ndf may get very large. This is a data storage format file for SQL server which houses data related to the UDR table in the EngageIP database. If this file gets large, you will need to purge / archive UDR data. Please contact LogiSense for details on archiving data or reference this article: https://developer.logisense.com/archiving-old-records-udr-system/

v9 Documentation