SQL database maintenance

This information is provided as a guide only. It is recommended that you follow industry standard best practice and seek recommendations from an experienced database administrator. This information should be used with consideration of the wider impact on the overall environment.

The following databases need to be maintained:

  • InteractDB

  • InteractCacheDB

  • IadaDB

  • AuthenticationServerDB

  • HubDB

  • AuditDB

  • NotificationCenterDB

  • LicenceManagerDB

  • EmailServiceDB

  • BluePrismDecisionDB

  • ImsDB

  • FileServiceDB

    • CacheDB was replaced with FileServiceDB from Hub 4.4.

General recommendations for database maintenance

It is recommended that you:

  • Set auto-growth correctly for all databases. Recommended values are 1024 MB for the data file and 2048 MB for the transaction log.
  • Re-establish value for auto-growth as database size increases to minimize the frequency of auto‑growth events.

    • Do not use % file growth, grow by a fixed amount of megabytes instead.

  • Remove any excessive transaction log file fragmentation. For more information, see the Microsoft online help.
  • Turn on instant file initialization. For more information, see the Microsoft online help.
  • Turn off auto-shrink operations, set the page verification for all databases to checksum, and turn on AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS. Have a regular process in place to update statistics.

    • You can set the following T-SQL for each of the databases installed by Hub and Interact for this purpose:

    ALTER DATABASE [DatabaseNameHere] SET AUTO_CLOSE OFF;

    ALTER DATABASE [DatabaseNameHere] SET AUTO_SHRINK OFF;

    ALTER DATABASE [DatabaseNameHere] SET AUTO_UPDATE_STATISTICS ON;

    ALTER DATABASE [DatabaseNameHere] SET AUTO_CREATE_STATISTICS ON;

    ALTER DATABASE [DatabaseNameHere] SET PAGE_VERIFY CHECKSUM;

  • Have a regular process to run DBCC CHECKDB – It is recommended that an SQL Agent job is run at a minimum of once per day during periods of little to no system utilization. Results should be checked for corruption. It may be beneficial to create SQL Agent alerts to notify an operator group of the below errors;

    • 823 - Hard I/O Corruption

    • 824 - Soft I/O Corruption

    • 825 - Read/Retry Corruption

    • 9100 - Index Corruption

    • Severity 19-25 Errors

  • Optimize for ad-hoc workloads = ON

  • Backup compression default = ON

  • Backup checksum default = ON

  • Cost threshold for parallelism – 50 is a good starting point.

  • Max degree of parallelism - Depends on the NUMA configuration of the SQL Server but no more than the number of cores for a single NUMA node.

  • Set auto close = ON

  • Min Server Memory - Different per SQL Server but should be set.

  • Max Server Memory - Different per SQL Server but should be set.

Disk layout recommendations

It is recommended that separate drives are used for data and transaction log files, temporary databases, and backups.

Create a maintenance plan

Ensure you have a maintenance plan in place to take regular backups. Use your organization's preferred maintenance plan for backing up SQL databases. If your organization does not have a maintenance plan, it is recommended that you research industry best practices and select a maintenance plan that suits your organization's needs.

Take backups

Backups should be designed based on your organization's recovery point (RPO) and recovery time (RTO) objectives.

  • RPO – The point in time you can recover your data following a failure. This determines how much data is lost.
  • RTO – The amount of time you have to recover your data following a failure. This determines the length of time that the platform is unavailable.

When creating a backup and recovery plan for Blue Prism databases it is important to consider and implement the points below:

  • Define both RPO and RTO.
  • Use the FULL recovery model to allow for full, differential, and transaction log backups in-line with your RPO and RTO.
  • Use WITH CHECKSUM and VERIFYONLY options on all backups to verify backups are valid and can be restored if required.
  • Use WITH COMPRESSION option to save disk space and reduce the time taken to back up the databases and optionally restore them.
  • Document the backup and recovery process.
  • Check your backups are reliable by regularly trying to restore them.

How often you perform these backups depends on the size of your organization, and the amount and value of data risk.

It is recommended that full backups are performed during absolute downtime. Incremental backups can be performed without stopping any services with the risk that some data may be lost.

Resolve index fragmentation

Database index fragmentation lowers query performance over time. To prevent this, rebuild indexes as frequently as database downtime will allow. Rebuilding indexes after taking backups and/or deleting large amounts of data is also advised. It is also recommended that you rebuild indexes before taking a full database backup to minimize index fragmentation in the event of having to restore a full backup.

Recommended thresholds for rebuilding/reorganizing index maintenance are: < 30% reorg and > 30% rebuild.

Rebuilding database indexes can be scheduled to run as a job inside the database server and/or added to the database maintenance plan. It is recommended that you run them during periods of low system activity and that they are scheduled to avoid overlapping with the backup and DBCC CHECKDB maintenance.