Best Practice for SQL Maintenance – Calabrio WFM
With the ongoing upgrades being performed by the Calabrio development team and the ever evolving versions of the WFM application, the team has taken a moment to put together some of the best practice suggestions to maintain and monitor you environments health.
Database maintenance and health checks
As you may already know, Calabrio products come with a clause that clients remain responsible for monitoring and maintaining the database at a healthy status. Below ares ome suggestions that have been provided to us by the team include the following verifications to be performed by a Database administrator.
Weekly index rebuilding
When the database tables start to become too fragmented it is a good idea to run a defragmentation to rebuild the table indexes. You will notice slower response time between selections or freezing in the application during navigation. These are all potential symptoms of high fragmentation.
Updating the instance memory allocation
SQL server is an application that will always utilize all available server memory by default. The application is designed to dynamically free memory and re assign as needed. It’s sometimes in the best interest of clients who are not running high performance servers to configure the SQL instance with a maximum memory allocation. This has the added benefit of also keeping your server running at optimal speed if you have a 1 server setup where the database is installed on the same server as your WFM application.
Daily backups of the CWFM database off board
Most installations of WFM will have backups being performed on the CWFM database. Back up must be kept safe, as recommended in most Disaster Recovery plan, it’s not optimal to have them saved to the same location as the main database. The best practice is to save these backups to an off board location which allows for easy access in a worst case scenario where the server falls into disrepair and makes it so that the production database is no longer accessible.
Single instance server
Since WFM is a web application with regular read and write requests being sent, it is recommended to have your SQL database in its own instance with no other databases installed. This allows the allocation of memory to be used completely in performing WFM related requests without having to share resources with other data sources. To take this a step further, if possible, it would be suggested to have only one database installed on the server so that no other database could risk interfering with Calabrio’s WFM workflows.
Weekly reboots of the SQL server during maintenance hours
Memory on a server is finite and can sometimes remain allocated to a process that no longer have a need for it. To optimize memory it’s suggested to schedule, when possible, weekly reboots of the server. This will free up all allocated memory and allow for the system to start running anew without it being bogged down by missing memory.
It is a good practice to check updates available for the SQL version used, Service Packs might bring performance improvements, but they also might include changes that could impact applications using the database engine. It is recommended to validate each Release Notes before applying any updates. It is also important to maintain the server’s operating system by regularly installing any security or performance updates on the server. When performing updates of this nature it is suggested to keep your application support provider aware so that they can assist with an intervention plan if required or be available to provide live assistance.
Questions of informations?
For any questions on these best practices, please contact our Support Team at email@example.com