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 a constant maintenance to the database, so that you can look after an appropriate performance and working of the system.

Note that each database engine by itself (SQL Server, Oracle) provides the necessary tools for such maintenance, which includes possibilities of pro-active monitor, to run diagnostics (e.g, profiler tools) and to perform tuning tasks.

For more information, refer to System administration.

 

 

User_DBA

 

Monitoring and tuning guidelines

We recommend a DBA to:

 

1. Perform continuous monitoring over the database performance.

Note that the database engine by itself provides the necessary tools to monitor, run diagnostics and interpret results to perform tuning tasks (in addition to relevant log files).

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

For instance, a table scan will 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 or by waits in disk reads and writes, or even due to a high network traffic

 

note_pin

If the DBA detects that the database engine is not performing adequately (not executing queries in a timely fashion), recall that you may scale-up the database server at anytime (or scale-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 amount of cases or expected to process a large amount of activities per day, database tuning is recommended at least on a weekly basis.

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

 

Aspects to tune include mainly:

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.

Ensuring you monitor database filegroups so that their configuration (size, increment, maximum size, drive used, etc) is most adequate according to how these grow.

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

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

Regarding tempdb, it is suggested to 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 recommendations such as: 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, consider paying close attention to the growing behavior of the following tables:

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

Additional ones, 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 jobs 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 aborted frequently) and Reassignlog (especially if in your project, cases are manually reassigned frequently).

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

 

Other than these, 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 observed, you may also review the fragmentation of information (ifilegroups in SQL Server or tablespaces in Oracle).

 

note_pin

Recall that you should 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, consider the following aspects:

You may create non-clustered indexes.

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

As a regular recommendation in 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).

You should not 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 by himself/herself should evaluate such indexes adequately.

Apart from a cost-benefit analysis, you may evaluate it within the query execution plan and usage statistics (e.g. to consider index seeks, index scans, index lookups or index updates).

 

4. Ensure adequate concurrency settings when using SQL Server.

Monitor and watch after having the required management for concurrency enabled in Bizagi's database.

This means verifying that your Bizagi database uses snapshot isolation. This means specifically having:

Allow Snapshot isolation: True (ALLOW_SNAPSHOT_ISOLATION ON)

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

Note that when using the above 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 up exactly every task a DBA needs to carry out regarding database maintenance (the information provided below is illustrative and not exhaustive).

 

You will also need to carry out additional database tuning tasks, regular ones encouraged by the database engine vendor, and as identified from the use of specialized tools (from what observed through logs and traces).

 

5. Ensure the system user is enabled.

Recall that the domain\admon is the system user employed internally by Bizagi (created by default).

You may not disable this user since it is needed to perform automatic tasks such as timers or scheduled jobs, and it is recommended that you check that such user is enabled in the database.

Though, note that it is strongly recommended to edit this user's setting so that it is not set with rights to access administration options at the Work portal.

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

 

6. Ensure 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

 

And that 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.

 

You may also want to double-check that such login is not locked out:

 

Login_enabled

 

Troubleshooting and diagnostics

Whenever you need to troubleshoot and diagnose an issue occurring at the database, then 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.