Introduction
The Blue Prism database is a critical component of any Blue Prism deployment, the Blue Prism database stores users, credentials, processes, objects, work queue items, session logs and audit information. It should be no surprise then that ensuring the database is performant and well maintained is crucial to the success of any Blue Prism deployment. Yet far too often we see deployments of Blue Prism with a poorly maintained database, this post is meant as a high-level guide into the fundamentals of Blue Prism database maintenance.
Backups
The most important responsibility a DBA has is to protect their company's data. This means they need to ensure not just the security of their databases, but also that their databases are backed up and have no corruption. The Blue Prism database is no different, it is imperative that it is backed up and of equal importance that this is done in line with business expectations. Your DBA should know by heart the various recovery models and backup types, know how they are used, what they do and when they should be performed. When planning a Blue Prism deployment, I always advise that customers define a Recovery Point Objective (RPO) and Recovery Time Objective (RTO) for the platform, so what are RPO and RTO?
- RPO – The point in time to which you can recover your data following a failure. I like to present this to business users as "how much data can you afford to lose?".
- RTO – The amount of time you have, to recover your data, following a failure. I like to present this to business users as "how long can the platform be unavailable whilst being recovered?".
Once you know these two metrics, you can implement a backup and recovery plan accordingly. For example, it is pointless taking only a daily full database backup (no differential or transaction log backups) if your RPO is 1 hour, guess what, this just will not cut it as with only a daily full backup your RPO is 24 hours! When creating a backup and recovery plan for the Blue Prism database it is important to consider and implement the below.
- Define both RPO and RTO
- Make use of the FULL recovery model to allow for full, differential and transaction log backups in line with your RPO
- Make use of the WITH CHECKSUM and VERIFYONLY options on all backups to improve the confidence that the backup is valid and if required can be restored
- Make use of the WITH COMPRESSION option to save disk space and reduce the time taken to backup the database and even restore it
- Document the backup and recovery process
- Test your backups
I cannot stress the importance of the final point. Testing backups, and I do not mean using the CHECKSUM and VERIFY options, I mean actually testing them by restoring them somewhere, this is the only way to be certain that a backup is valid. Imagine being accountable for a company's data, having a backup and recovery plan that meets RPO and RTO requirements then actually having to restore a database following a failure only to find out that you can't because your backup is corrupt!
Index Maintenance
Most tables in the Blue Prism database are transactional tables, that is, they are not static but are frequently changing over time. As you carry out normal Blue Prism operations, creating processes, importing releases, running schedules etc the Blue Prism application is inserting and modifying the data in these transactional tables in the Blue Prism database. As these operations occur, we introduce fragmentation in the indexes that SQL Server uses to read the data from the database tables. Index fragmentation occurs when the logical ordering of the index pages, based on the index key value, does not match the physical ordering inside the data file. Index fragmentation is inevitable in transactional database systems and if not maintained results in an unnecessary increase in the amount of disk I/O operations required to read the requested data. In Blue Prism's case the application takes longer to return the results to the client and processes ultimately take longer to run.
SQL Server comes with several options when defragmenting indexes, most notably index rebuild, and index reorganize, there are quite a few major differences between rebuilding and reorganizing indexes that are out of the scope of this post. Microsoft advises that indexes with less than 30% fragmentation should be reorganized whereas indexes with more than 30% fragmentation should be rebuilt.
Index Maintenance is key to the performance of a Blue Prism solution and should be carried out with the below guidelines.
- Carry out index maintenance regularly, nightly, if possible, weekly at the very least
- Carry out index maintenance during quieter periods, for example the early hours of the morning to reduce the impact on operations
- Reorganize or Rebuild indexes based on the 30% threshold mentioned above
- Update statistics as part of the index maintenance
- Where possible carry out index maintenance before a full database backup
Integrity Checks
Checking the integrity of any database is hugely important and again the Blue Prism database is no exception. You want to know if there is any corruption in your database as soon as possible so you can take corrective action. Data corruption can cause your users to get incorrect data, cause queries to fail, or can even take your entire SQL Server instance offline. Corruption can happen anytime and can be caused by a problem in SQL Server, an issue with Windows, or an issue with your storage subsystem. In extreme cases corruption can result in data loss, remember, earlier in the post I said that "The most important responsibility a DBA has is to protect their companies' data", well if you lose data due to corruption you have not protected the data. It does not stop there either, corruption can also manifest itself in backups, so if you are not carrying out regular integrity checks and you're not using backup checksums or verifying and testing your backups, then you really are running the risk of losing data.
Again, SQL Server provides us with the ability to easily check the integrity of our databases in the form of DBCC CHECKDB. DBCC CHECKDB checks the logical and physical integrity of all objects in the database and does this by performing several operations, again the details of which are outside of the scope of this post. To ensure the integrity of the Blue Prism database it is important to consider and implement the below.
- Run DBCC CHECKDB regularly, ideally daily, weekly at the very least
- Consider running DBCC CHECKDB before your database backup, this way you have greater confidence that your backup will be consistent
- Consider restoring the latest backup to another server and running DBCC CHECKDB against that, this kills two birds with one stone, you've tested your backup and ran integrity checks against it
- Remember that Log Shipping, Database Mirroring and Always On Availability Groups do not negate the need to run DBCC CHECKDB, The only valid way to offload DBCC CHECKDB is to run it against a restored full backup
- Ensure you understand just how serious corruption can be and how to take appropriate action when it occurs
The good news is that for Backups, Index Maintenance and Integrity Checks there is a maintenance solution available in the form of Ola Hallengren's SQL Server Maintenance Solution. The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server from version 2008 up to and including 2019. The solution is based on stored procedures, has been designed for the most mission-critical environments, is fully documented and best of all is completely free.
Archiving
The Blue Prism Database is an operational database not a reporting database as such all non-essential data required for day-to-day operations should be archived. One of the most common causes of performance related issues is due to non-existent database maintenance. Archiving needs to be addressed from day 0, that is, to avoid performance problems you must implement an Archiving strategy as part of your deployment.
Session Logs can be archived directly from the Blue Prism System Settings
System Tab → System → Archiving
The in-product archiving has two Archiving Modes
- Automatic – allows for archiving to occur at a regular interval with no user interaction
- Manual – Requires a user to manually archive as required.
The in-product archiving gives users two options
- Export – Exports the data to a folder
- Delete – Deletes the logs without exporting them
It is also very important that several other database tables are regularly maintained by removing non-essential operational data, these are tables with data relating to the below.
- Process History
- Work Queues
- Schedules
Currently these additional tables cannot be archived from within the Blue Prism Interface however TSQL Scripts which can be provided by Blue Prism Global Customer Support on request. These Scripts can be implemented as scheduled SQL Server Agent jobs to periodically "trim" historical data from these additional tables, usually during periods of low system utilisation for example evenings and weekends and in larger Blue Prism deployments during a complete system shutdown.
#BPTechTips
------------------------------
Chris McGowan
Senior Technical Consultant
Blue Prism
------------------------------
