Database Maintenance

BizTalk360 database eventually can grow due to various factors based on your configurations. In that scenario, there is a chance that database fragmentation will increase, which will slow down the SQL Query execution of the BizTalk360 database. The database maintenance feature will give insight into the health of the BizTalk360 database and also help the user to Rebuild or Reorganize the BizTalk360 database table indexes when it reaches the threshold limit. This action is handled via a sub-service called "Database Maintenance" which runs under the BizTalk360 monitoring service. 

Threshold configuration

In default, the toggle (Enable Database Maintenance) is enabled. The fragmentation threshold is updated with a default value as below.  This default threshold value can be changed as per your database usage and business needs. By increasing the fragmentation threshold value for reorganize, automatically the rebuild values get increased.

• Rebuild Index - Fragmentation threshold (70%) and Page Count (1000)
• Reorganize Index - Fragmentation threshold (50%) and Page count (500)

Based on this threshold value system will list the indexes and their status, whether any actions like rebuilding or reorganizing are required. The BizTalk360 system will automatically take action against fragmented indexes. Users can also create a schedule to take action only during that particular time.

The health of the index is also updated in the header along with the status and number of indexes that are beyond the threshold limit.

Steps to Configure Schedule for Database Maintenance

1. Navigate to Settings->BizTalk360 health->Database Maintenance
2. Update the threshold value for fragmentation and page count to rebuild the index
3. Update the threshold value for fragmentation and page count to reorganize the index 
4. Enable the schedule to rebuild or reorganize the table indexes and select the frequency (Daily, weekly, and Monthly). Update the day & time to execute the schedule. So that the Rebuild or Reorganize action will be taken only during the configured time.
5.  The Send Notification option is used to send the notification if it is enabled, If the option is disabled the alert notification will not sent to the admin when the rebuild and reorganize are performed. 

In situations where the database maintenance processes are causing unexpected downtime or performance degradation during crucial business hours, temporarily disabling maintenance could be considered to ensure uninterrupted service. So, at that time, you can disable the toggle (Enable Database Maintenance), rebuild, and reorganize will stop performing and configurations get disabled. 

Points to remember

• Rebuild or Reorganize action will be taken offline so the table Indexes that are opted for Rebuild will not be available for I/O operations during that time(only for a second).
• BizTalk360 Monitoring service will take action on indexes immediately once the threshold violation occurs. Enable schedule to perform the Database maintenance in quiet hours.
• It's always advised to use the BizTalk360 purge policy to avoid data growth.