Overview

AVEVA Work Tasks stores application data in Microsoft SQL Server. This data is very important as it is required for analytical or legal purposes. However, keeping all data in transactional database increases the size of the database enormously. Although, the latest relational database management systems and allied hardware are optimized to handle such data, it still might affect the overall performance of the business application.

AVEVA Work Tasks can be used across different business domains. Business requirements may require workflows to run continuously, and thus increases the size of some of the database tables. These database tables are critical for optimal performance of the workflow engine, and further to the performance of the business application.

Microsoft SQL Server supports table partitions to move completed transactions to a different partition. The active transactions can be kept in one partition and the completed transactions can be moved to a different partition. This optimizes workflow engine performance as queries are run on the lean partition. Database tables that grow based on workflow instances are considered for partition on completion of workflow instances, the data is moved to the secondary database partition.

Partition Feature

The partition feature can be enabled while creating or upgrading a SQL Server data source. The partition feature is at the database level. Therefore, for multiple repositories based on the same data source, all the repositories will be partitioned. Ideally, each repository should be in a separate data source to avoid partitioning of the same.

Note:
- The partition feature is available only on Microsoft SQL server versions and editions that support table partition feature. Refer to the Microsoft documentation to check the availability of the partition feature.
- The partitioning of the database is recommended only for those business applications that run large number of workflow instances.
- Enabling and disabling partition is a maintenance activity which requires closing the application. So, plan it in advance before you enable or disable partition.
- Enabling and disabling partition must be done with precaution, considering the following points:
- Enable or disable partition under the supervision of database expert, preferably a Database Administrator (DBA)
- Take the Database backup to avoid any eventuality
- Stop the Workflow services and Web application before enabling or disabling partition

For a data source, partition can be enabled either from the DataSource settings in the Central Configuration or from the AVEVAWorkTasksPartitionManager.exe (Partition Manager tool).