Database maintenance

<< Click to Display Table of Contents >>

Navigation:  Automation Server > Automation Server configuration and administration guide > System maintenance and monitoring >

Database maintenance

Overview

An important task for project maintenance and administration of the Bizagi System is to perform regular maintenance to the database, to keep the system working at an appropriate performance level.

Each database engine by itself (SQL Server, Oracle) provides the necessary tools for such maintenance, including ways to pro-activly monitor, run diagnostics (e.g, profiler tools) and perform tuning tasks.

For more information, refer to System administration.

 

 

User_DBA

 

Monitoring and tuning guidelines

We recommend that the DBA:

 

1. Perform continuous monitoring over the database performance

The database engine itself provides the necessary tools and log files so you can monitor, run diagnostics, and interpret results so you can perform tuning tasks.

Through pro-active monitoring you can anticipate undesirable situations such as table scans, database locks or delays, and detect situations which may require database tuning.

For instance, a table scan may suggest that queries/statistics are not tuned, and that maintenance of indexes is needed (adding new ones, redefining existing ones).

Relevant aspects to monitor, either to review your configuration or the characteristics of the underlying hardware/infrastructure, include: potential bottlenecks that may arise due to lack of memory, from waits in disk reads and writes, or due to high network traffic

 

note_pin

If the DBA detects that the database engine is not performing adequately (not executing queries in a timely fashion), you can scale up the database server at any time (or scale it out when using an active-active cluster scheme such as Oracle RAC).

 

2. Carry out database tuning regularly and according to best practices

For processes expected to produce a large number of cases or process a large number of activities per day, we recommend database tuning at least once a weekl.

Best practices include performing tuning during non-working hours, and planning ahead (keeping in mind that such tasks can take some significant time), as well as following recommendations provided by the database engine vendor.

 

Tuning includes:

Verifying the database integrity.

Updating database statistics.

Reorganizing and maintaining indexes (recreate highly fragmented indexes or reorganize them according to the order of the queried columns when using compound indexes).

Performing shrinks to the database.

Monitoring database filegroups so that their configuration (size, increment, maximum size, drive used, etc) is most appropriate to the way they grow.

According to the hardware characteristics of your database nodes (i.e number of hard disks, number of processors or cores), you can configure the data files and logs for enhanced performance by scaling them out.

For instance, you could benefit from parallel I/O operations when using multiple drives with a dedicated drive to separate data files and filegroups (i.e placing log files in its own volume, placing tempdb in their own volume, or having a data file with the most worked on transactional tables in its own volume the one with the best speed).

Regarding tempdb, we suggest that you configure it using multiple data files and filegroups pre-sized equally to avoid auto-growth (using one data file per CPU).

When pre-sizing, consider: disabling auto-growth for data files, having the data and log files use no more than 90% of the available disk space, having the log file twice the size of a single data file, and setting auto-growth for the log file to a specific size.

 

Within Bizagi's model, pay close attention to the growth behavior of the following tables:

Those related to storage of daily work: Workitem, Workitemcl, Wfcase, and Wfcasecl.

Additional stables, according to the characteristics of your project: Asynchwiretry and Asynchworkitem.

Those storing logs: AttribLog, EntityLog, Wistatelog, Transitionlog, Factlog, Casestatelog, Authlog, Attribcharlog, Assignationlog, Joblog (especially if your project involves job execution in a frequent manner), Alarmjobrecipientlog (especially if your project involves alarms in a frequent manner), Wfcaseabortreason (especially if in your project cases are manually cancelled frequently) and Reassignlog (especially if in your project, cases are manually reassigned frequently).

Note that tables storing logs may be partitioned into a separate filegroup.

 

Include in your plans and tuning, additional business entities which are expected to store large volumes of information and to grow significantly.

According to what observe, you may choose to review the fragmentation of information (ifilegroups in SQL Server or tablespaces in Oracle).

 

note_pin

Carry out database tuning right after executing maintenance actions by Bizagi (e.g, such as the maintenance tasks done by the Scheduler, or further options as per delivered in additional tools like Archiving).

 

3. Follow Bizagi guidelines on indexes creation

When creating or reorganizing indexes:

You can create non-clustered indexes.

You can create indexes on tables which are definitions of your project (i.e, business entities which are not actually part of Bizagi's meta model).

As a general recommendation for databases, it is not useful to create indexes on columns which have a small set of possible values (e.g, typically such as boolean or bit type columns).

Do create indexes in an automatic manner (without prior analysis).

This means not relying on the un-assisted index creation tasks of software such as Database Engine Tuning Advisor (SQL Server), because the DBA should evaluate such indexes adequately.

Apart from a cost-benefit analysis, you can evaluate use of indexes within the query execution plan and based on use statistics (consider index seeks, index scans, index lookups or index updates).

 

4. Make sure adequate concurrency settings when using SQL Server

Make sure you have the required management for concurrency enabled in Bizagi's database.

Verify that your Bizagi database uses snapshot isolation. This means:

Allow Snapshot isolation: True (ALLOW_SNAPSHOT_ISOLATION ON)

Is Read-committed snapshot isolation on: True (READ_COMMITTED_SNAPSHOT ON)

When using these snapshot isolation levels, it becomes even more important that you boost the resources and enforce best practices for the tempdb database (i.e, using a separate volume that guarantee high speed I/O operations).

 

SnapshotIsolation

 

note_pin

These guidelines provide a starting point as well as useful recommendations for monitoring tasks, database tuning and diagnostics.

However, these guidelines do not cover every task a DBA needs to carry out regarding database maintenance (the information provided is illustrative and not exhaustive).

 

You also need to carry out additional database tuning tasks, both those encouraged by the database engine vendor, and those you identify based on use of specialized tools and reviews of logs and traces.

 

5. Make sure the system user is enabled

The domain\admon account is the system user employed internally by Bizagi and created by default.

Do not disable this user since it is needed to perform automatic tasks related to timers or scheduled jobs. We recommend that you check that this user is enabled in the database.

We strongly recommend that you edit this user's setting so that it does not have rights to access administration options at the Work portal.

For this, you can exclude this user from those authorized to manage your Bizagi system (i.e this user should not be able to manage users or modify values in Bizagi such as parameter entities, abort cases, etc).

 

6. Make sure adequate database access rights

Make sure that credentials used for both the Work portal and Scheduler instances are set to use credentials with adequate access rights, as instructed and configured during initial set up.

The sample image below displays those rights needed when using SQL Server.

 

Login_SQLAuth

 

The server roles specified for this login must include: public.

 

Login_public

 

The login must include the following user mapping items for that specific database: db_datareader, db_datawriter, public, rlBA_SQL_BizagiWebApp and rlBA_SQL_ExecuteBizagiSPs.

 

Login_mappings

 

note_pin

The db_datareader, db_datawriter, public, rlBA_SQL_BizagiWebApp and rlBA_SQL_ExecuteBizagiSPs user mappings apply for your project's database. This means that you need to apply these mappings once your Bizagi production environment database has been created.

 

Double-check that this login is not locked out:

 

Login_enabled

 

Troubleshooting and diagnostics

When you need to troubleshoot and diagnose an issue occurring at the database, you may choose to:

Rely on a special Bizagi tool for this purpose. For more information about this option, refer to Bizagi Diagnostics.

Rely on specialized tools of your database engine, such as the Profiler tool for SQL Server.

For recommended guidelines on its use, refer to Profiler recommendations.