HQbird 2024 R2 User Guide

HQbird includes native replication to create fault-tolerant systems based on Firebird databases:

Native replication is configured through the special plugin, with the ability to exclude records without PK/UK at the plugin level.

HQbird has complete transport to arrange transfer of segments for asynchronous replication for 1-to-1 or 1-to-many schemas, with automatic setup, transfer and validation of replication segments via sockets or FTP. HQbird has command-line commands to set up databases for replication in bulk, to choose databases in the folder, or in nested folders.

If you have an application with inaccessible or missing sources, HQbird can help you change texts of incompatible or most resource-consuming SQL queries “on the fly”, and therefore help to optimize the performance or migrate an application without SQL queries sources. The replacement is easy configurable, it is implemented by pairs of files which contains text of original and replaced queries.

With Advanced Monitoring, you can find SQL queries that cause issues and then configure the substitution for them, even without access to the application’s source code. The replaced query will occur in trace and MON$ tables with the new text.

HQbird has External Datasource plugins for ODBC and MySQL. Using these plugins, it is possible to execute commands EXECUTE STATEMENT ON EXTERNAL with queries to MySQL or ODBC data source, in order to read data from external datasources, or to write data to external datasources.

Plugins support input parameters and correct mapping of data types (however, in case of ODBC it depends on the specific driver implementation).

See example of an external connection below:

See more Working with external data sources (other DBMS)

HQbird can cache BLOBs in temp space, in order to speed up BLOBs operations (+15%-200% faster than in vanilla Firebird), and to prevent growth of the database file in case of mistaken BLOB operations.

HQbird uses an extra firebird.conf parameter BlobTempSpace to control this feature.

The caching option can be:

This feature can improve the performance of repeated queries, especially when using a connection pool (PHP, etc).

Cache keeps a certain number of prepared queries in each connection’s memory. HQbird has this cache in versions 3.0 and 4.0, and you can adjust it with the DSQLCacheSize setting (default is 0, i.e., disabled).

In vanilla version 5.0, there is a comparable feature, regulated by the MaxCompiledCache option, which is measured in Megabytes, the default is 2Mb.

Firebird Streaming is a technology that tracks changes in the database and sends them to another system, such as Kafka, JSON files, RabbitMQ, full text search plugin, etc.

HQbird offers a replication-based Change Data Capture plugin. The plugin creates a change flow that reflects transaction commits/rollbacks.

HQbird provides ready-made plugins for Kafka, RabbitMQ, JSON files, and also supports their configuration for any destination. CDC is useful for processing queues, sending alerts asynchronously, and copying changes to other systems (such as business intelligence or data science pipelines).

CDC plugin available upon request. For more information, contact IBSurgeon support ([email protected]).

See more Firebird Streaming

Full-text search is a technique that allows you to search for any word or phrase within a large collection of documents or data. Full-text search is different from searching based on metadata or partial text, which may not capture the full meaning or context of the query. Full-text search uses a full-text engine, such as Lucene, to perform the search and return the results.

IBSurgeon Full Text Search UDR is a user-defined routine (UDR) that integrates Lucene with Firebird. A UDR is a custom function that can be called from SQL statements. IBSurgeon Full Text Search UDR allows you to perform full-text search on Firebird tables in varchar and BLOB fields using Lucene engine.

This UDR is available in open source, but HQbird, provides a customizable plugin based on streaming for operational update.

More details: https://www.firebirdsql.org/en/full-text-search-udr/

HQbird implements multi-thread maintenance (sweep), backup, restore, and create index operations. Firebird 2.5, 3.0 and 4.0 are supported, and this functionality also appeared in Firebird vanilla version 5.0.

The format of backup files is the same as in the vanilla Firebird. On the test server with CPU with 8 cores and SSD, we have the following results (compared with 1 thread);

The actual acceleration depends on CPU, disk subsystem of the server, and structure of the database. Install HQbird in the trial mode (up to 30 days) and check what results will be on your server!

More details and test results can be found here: https://ib-aid.com/articles/firebird-gbak-backuptips-and-tricks#110hqbirdbackup

HQbird, starting from version 2.5, supports two important features:

These features help to achieve parallel reading of large data sets, and to accelerate 2-10x times export operations (such as for BI exports or data pipeline). These features are also available in Firebird vanilla, from version 4.0.4 onwards.

HQbird has a pool of external connections for Firebird 2.5, 3.0, and this pool is also available in vanilla version since 4.0.

An external connection pool allows you to execute EXECUTE STATEMENT ON EXTERNAL statements with less overhead in reconnecting to the external database.

The feature is controlled in the firebird.conf with ExtConnPoolSize and ExtConnPoolLifeTime parameters.

From the application perspective, no extra steps are needed to use or not use — it is switched on or off in the server configuration, and completely transparent for the applications. It is also possible to disable garbage collection for queries executed in external connections. It is regulated through configuration parameter ExtConnNoGarbageCollect.

See details: Pool of external connections

HQbird supports encryption with Encryption Framework’s Plugin. The main features are:

We can offer examples of client applications in various languages, such as Delphi, NET, Java, PHP, C++, etc., upon request.

Incorrect configuration of DefaultDbCachePages in firebird.conf, databases.conf or in database header is a common configuration mistake, which often happens during the migration between versions. For instance, it can be too large values of Page Buffers in database header for Classic or SuperClassic, or too low for SuperServer.

HQbird will automatically fix the wrong setting in firebird.conf and databases.conf and it will overwrite, if the configuration is unsuitable for a selected architecture.

Advanced Monitoring of Performance in HQbird is a feature that allows you to monitor and analyze the performance of your Firebird databases (version 5.0, 4.0, 3.0, 2.5) in real time. It collects data from various sources, such as Trace API, MON$ tables, lock table, transactions, CPU and RAM usage, and displays them in graphical and tabular forms. You can see the overall performance trends, as well as drill down to the details of each minute, query, or transaction.

You can also identify performance problems, such as slow and frequent queries, long-running transactions, lock table spikes, etc., and view their plans and statistics.

This feature helps to troubleshoot queries that produce large reports, where many records need to be sorted. HQbird can track queries and operations that create sorting files larger than a given size. When such a query is detected, its text is recorded to firebird.log

Configured as a TempSpaceLogThreshold parameter in firebird.conf, which defines the size of the sorting file for monitoring.

If you have many databases stored in the folder, and want to register all of them in HQbird to setup replication, in HQbird v2024 there is new command-line command to generate JSON file from the folder (recursive or not) with the registration information, which can be used for mass registration.

From replica side, there is special version of HQBird Central for Replicas, which allows to store hundreds of replicas (from different servers) on the single server. HQbird Central for Replicas is shipped by request.

With HQbird, you can always keep track of your backups and avoid losing them, no matter how many databases you have or where they are.

HQbird can transfer backups (or other files by mask) via FTP, sockets, or to Amazon S3 (needs plugin which is available on demand).

HQbird also has built-in FTP server and sockets server with easy setup.

Excessive record versions, also known as garbage versions, slow down Firebird databases significantly. HQbird implements the proper combination of sweep operations and “soft” shutdown of long running writeable transactions, and allows to avoid frequent database backups/restores. With HQbird it is recommended to do backup/restore no more than once per year.

Maintenance can also include the recalculation of indices statistics and the verification of indices health, as well as the examination of metadata health.

HQbird allows installation of multiple Firebird instances of different versions on the same server. It makes migration from one version to another easier. HQbird for Windows installs all supported Firebird versions (5.0, 4.0, 3.0, 2.5) by default, each instance with a different port. You can choose to install only one version, or several versions, during the installation.

To install HQbird for Linux with multiple instances, please use united installer (it is a new feature of HQbird v2024), and indicate what versions you want.

The fastest way to install HQbird is to use the silent installation in the command line.

In the example below we will install HQbird with Firebird 3.0 into c:\HQbird, the configuration will be c:\HQbirdData\config, output in c:\HQbirdData\output.

See also:

HQbird’s Admin package (it runs on Windows), includes Database Analyst, a tool that assists a user to analyze in detail Firebird database statistics and identify possible problems with database performance, maintenance and how an application interacts with the database. IBAnalyst graphically displays Firebird database statistics in a user-friendly way and highlights the following problems:

More details: Database structure analysis

HQbird MonLogger is a tool to analyze monitoring tables output in Firebird and find problems with slow SQL queries, wrongly designed transactions (long-running transactions, transactions with incorrect isolation level, etc) and identify problematic applications.

MonLogger can connect to Firebird database with performance problems and identify what is the reason of slowness: is it some user attachment, slow SQL query or long-running transaction?

MonLogger supports Firebird 2.1, 2.5, 3.0, 4.0 and 5.0 — for older Firebird versions or InterBase please use FBScanner (it is not included in HQbird, should be purchased separately).

MonLogger can show you:

HQbird includes license of FirstAID, recovery toold for Firebird. IBSurgeon FirstAID is the tool that can automatically diagnose and repair corrupted Firebird or InterBase databases — it can recover corruptions that neither gbak nor gfix can fix. Supported versions: Firebird 1.0, 2.0, 2.1, 2.5, 3.0, 4.0, 5.0, InterBase from 4.0 to 2020.

It uses its layer for low-level database access without using the InterBase or Firebird engine, so it can perform real "surgical" operations and repair your database when all other standard mechanisms (gfix and gbak) cannot.

HQbird comes with the optimized configuration by default to make the best use of resources of powerful servers and Virtual Machines. To improve HQbird configuration, you can use Cofiguration Calculator for Firebird, where you can choose “HQbird”, to obtain the basic optimized configuration for your system here: https://cc.ib-aid.com/democalc.html.

Please note that Calculator produces conservative configurations, and to create customized configuration, you need to monitor and analyze performance logs. IBSurgeon can assist you to create the ideal configuration in the context of Optimization/Configuration/Audit Incident for Firebird: https://ib-aid.com/en/firebird-interbase-performance-optimization-service/

The fastest way to install HQbird is to use the silent installation command.

In the example below we will install HQbird with Firebird 5.0 into c:\HQbird2024, configuration will be c:\HQbirdData\config, output in c:\HQbirdData\output.

The following parameters are mandatory to perform the silent installation:

Optional parameters for the silent installation of HQbird:

Must be set in pairs, both are required!

/REGUIK="z:\HQBird\test\uik" /REGUNLOCK="z:\HQBird\test\unl"

Please note, that in a case of error, for example, if you are trying to run silent installation to install HQbird to the location which is different from the current location, the error message window will popup and installation will be canceled.

The easiest way is to install Firebird bundled with HQbird – just choose the desired version during the installation. However, sometimes it is necessary to use HQbird with a community version of Firebird.

To install Firebird separately, download the Firebird zip archive from www.firebirdsql.org

Unpack the archive file to a suitable location (for instance, C:\Firebird25), after that copy the optimized configuration file firebird.conf (see Optimized Configurations below) to this folder.

Then, go to the Bin folder and then use the Run As Administrator option to run the batch file with the architecture you need.

Of course, you can choose the SuperServer for 2.5 or Classic architecture for 3.0 if you know what you need.

As a result of running the command file, Firebird of the selected architecture will be installed and run as a service.

You can make sure the Firebird service is installed and running in the Services snap-in (services.msc in command prompt):

In this example, Firebird is installed in the folder H:\Firebird\Firebird-2.5.5.26928-0_x64 and running as a service with the SuperClassic architecture.

Next steps:

Prerequisites: make sure you have libtommath, libncurses5-dev and ICU installed (there will be an appropriate error message if they are not installed).

Next steps:

Prerequisites: make sure you have libtommath and ICU installed (there will be an appropriate error message if they are not installed).

Next steps:

Prerequisites: make sure you have libtommath and ICU installed (there will be an appropriate error message if they are not installed).

Next steps:

If you don’t want to change the existing Firebird installation, please run the following command:

It will install HQbird without Firebird binaries.

Make sure these ports are allowed in your firewall configuration:

These ports can be changed in /opt/firebird/firebird.conf (RemoteServicePort), /opt/hqbird/conf/network.properties (server.port) and /opt/hqbird/conf/license.properties (serverlicense.port).

Make sure to allow these ports in your firewall configuration.

To activate HQbird, you can either use a separate utility included in the server and administrator packages for Windows, or use the registration mechanism embedded into the HQBird Firebird DataGuard web interface (for Windows and Linux), or run any tool from the administrator software and use the built-in activation wizard.

The activation wizard looks and works the same in the tools and in the activation tool. It is enough to perform activation once on any computer that can connect to the server where HQbird ServerSide is installed.

You can launch the registration utility from the Start menu (IBSurgeon\HQbird Firebird Admin\HQbird):

If you click the Register button (or Re-Register for repeated registration), you will see the activation wizard:

After that, specify the IP address or the computer name of the server HQbird is installed on in the upper input field and click Connect to HQbird Server. If you started registration utility on the same computer with HQbird Server, it will be “localhost”, otherwise — some remote address.

Then enter your registration data. If you have a license, enter your e-mail address and password that you used to register with the IBSurgeon Deploy Center and click Activate.

Right after you click Activate, the registration wizard will try to connect to the IBSurgeon Deploy Center () and obtain a license. If it succeeds, you will see the corresponding message. If there are any problems, you will see the error message.

If you forget the password, click the Forgot password…​ button and it will open the browser with the password recovery form.

If you need to purchase a new or additional license or renew your subscription, click Purchase.

Click Close this window after the registration is over.

If the server and all client computers have no access to the Internet, you should use offline activation. To do it, click Offline activation tab and follow instructions there. In case of any troubles please contact.

FBDataGuard web interface works correctly with Firefox, Chrome, Safari, Microsoft Edge and Internet Explorer 11.

If you have configured FBDataGuard to use https, the browser will indicate that this web-site is not safe, and it will recommend leaving web-site. This message is caused by the default security certificate for FBDataGuard web-console.

Please ignore this message and click to open FBDataGuard web-console.

It will ask you for username and password (login dialog can be different for Firefox, Safari or Chrome).

At the first launch FBDataGuard will check computer for installed Firebird servers. FBDataGuard for Windows search registry for Firebird records, FBDataGuard for Linux checks default locations of Firebird installations.

FBDataGuard will show the list of all Firebird copies installed, but only the one instance of Firebird can be monitored by FBDataGuard. Choose it by clicking Add Firebird engine to monitoring >>

If you don’t see Firebird instance in auto discovery list, you can choose Add custom >> and configure instance parameters manually.

FBDataGuard Web-console contains 7 tabs (in the left side of the screen, usually they are collapsed):

Web-console is intended for easy configuration of activities (called “jobs”) which are fulfilled by HQbird FBDataGuard.

Almost all FBDataGuard jobs have 2 purposes: the first is to monitor for some values and to raise alerts if necessary, and the second is to store historical values into logs, so later it’s possible to see the dynamics of important parameters of Firebird server and database.

In this section we will consider general configuration of jobs parameters, but not an analysis of gathered logs.

General approach is the following: each activity is represented by a “widget”, which has the following parts:

Status — it is indicated with color icon and name. Status of database is a summary of all included server-level jobs and databases' statuses, and, respectively, status of database is a summary of all database-level jobs.

CRITICAL means problems, OK means “Everything is fine”, WARNING means some issues which require attention, MAJOR means major issue, MINOR – minor issue, MALFUNCTION means that jobs was not succeeded (something prevents its execution), NOT_AVAILABLE means that job is not available in this server or database version.

OFF means that job is not active, UNKNOWN means that job is active but was not started yet, so actual results are unknown.

Job name indicates the name of activity.

Last run shows the time after the last run of this job.

Period/Schedule to run shows how often or when the job will be started.

More>> is the link which opens the widget and shows more details and suggested action for administrator to resolve the situation.

All jobs in FBDataGuard have default settings, which are very close to recommended values for the 80% of Firebird installations, so after initial configuration server and database will be protected at pretty good level comparing with default installation, however, we recommend additional configuration and tuning for every job. In the next sections we will consider each job and its configuration.

To register auto-discovered server, you need to click at Add Firebird engine to monitoring>> and then adjust auto-discovered settings.

When installing under Windows, if the option to automatically register the master/replica is selected, the server will be added automatically. In this case, you can skip this step.If the option to automatically register a replica is selected, then the database will be added in addition.

Let’s consider what can you see in the Server dialog (and, normally, you don’t need to change them):

By default “Output directory” for Firebird server is ${agent.default-directory}/${server.id}, it corresponds to C:\HQbirdData in case of a default installation.

It can be not very convenient, so we recommend pointing FBDataGuard output directory to more simple path, usually located at disk where backups are intended to be stored, for example F:\myserverdata.

After clicking “Save” FBDataGuard will populate default configurations files and immediately start analysis of firebird.log. It can take a while (for example, 1 minute for 100Mb firebird.log). After that you will see initial web-console with registered Firebird server:

FBDataGuard shows alerts and statuses of monitored objects: if everything is fine, it shows green signs, otherwise there will be yellow or red notifications.

Below we will consider in details each monitored objects and its settings.

Server: Active server widget shows summarized status of all server-level jobs and statuses of monitored databases.

Server: Active server also indicates Firebird currently running or not and shows detailed version of Firebird and HQbird.

If you click on configure link, you will see the same dialog that we have used to register Firebird instance in FBDataGuard, and now it can be used for changing Firebird instance properties:

In general, there is no need to edit Firebird server details after the registration, until you are not reinstalling Firebird — but in this case we recommend reinstalling HQBird too.

FBDataGuard check replication.log for errors. In case of error it sends an appropriate alert (by email) to the administrator.

To enable this job please check “Enabled”.

“Server log” job periodically checks firebird.log and if it detects that file was changed, log analysis starts. The embedded analytic engine checks each entry in firebird.log and categorizes them into several categories with different levels of a severity. According the severity of messages status of job is assigned and appropriate alerts are generated.

Once administrator has reviewed errors and alerts (and performed necessary actions to solve the reason of error), he need to click on “Resolved” link and FBDataGuard will forget old error messages in firebird.log.

In the configuration dialog of “Server log” you can enable/disable this job and set the check period (in minutes).

Also this job watches for the size of firebird.log and if its size exceeds “Size to roll”, FBDataGuard will split firebird.log and rename it according to the date-time pattern.

“Server: Temp files” job is useful to catch and solve performance problems with Firebird database.

While performing SQL queries Firebird stores intermediate results for sorting and merging data flows in temporary files, which are allocated in specified TEMP locations. FBDataGuard shows at “Server: Temp files” widget information about quantity and size of temporary files.

FBDataGuard recognizes locations of TEMP folders and monitors quantity and size of temporary files. Lack of space can lead to the performance problem or more serious errors, too many (or too large) temporary files can indicate problems with SQL queries quality.

Using configuration dialog you can enable/disable this job, set period and thresholds to the maximum size of temporary files (size of all files) and quantity.

If you see that size of temp files is too high and if there is enough RAM on the server, increase TempCacheLimit parameter in firebird.conf to fit all temporary tables into RAM.

Also, HQbird checks other temp files used by Firebird — if you see extreme values (several Gb) for trace or monitor, the good idea will be check the FIREBIRD_TMP folder for outdated files (with old modification timestamps). Please note — the screenshot below is not a real alert (i.e., values are Ok), it was created to demonstrate the output in case of large temporary files.

“Firebird server folder” jobs monitors size, occupied by Firebird installation. It’s enabled by default.

There are several threats prevented by this job: maladministration issues when database volumes or external tables are being created in %Firebird%\Bin folder, very big firebird.log which can exhaust all places at drive with Firebird installation, and some other problems.

Also this job monitors and analyses information, gathered by all space-related jobs (including database-level jobs). At the picture below you can see quick representation of space analysis for all drives where Firebird, databases and backups are stored.

Using configuration dialog you can enable/disable this job, set period of checking and thresholds for server folder size.

By default, we use 1 Gb is a standard setting for Firebird installation.

If the size of your Firebird is larger, please consider clean-up of old logs and other unwanted artifacts, or increase parameter Max occupied (in bytes) to prevent false alerts.

Note for Linux users: if you see red warning regarding the inconsistent space information, add locations with database and backups to Disk Space widget:

You can get idea where is your database and backup is actually located with command df -h.

“HQbird output folder” monitoring is intended to watch space occupied by reports, logs, stats, metadata repository and other data, gathered and generated by HQbird — this folder by default is C:\HQbirdData\output.

For databases unattended for a long time (1-2 years) it is possible that FBDataGuard logs will occupy too much space and lack of space can lead to database outage. To prevent it for sure, “HQbird output folder” is watching for occupied space.

By default, “HQbird output folder” job is enabled.

Also, if someone has ignored recommendations to put backups’ folders to the explicit locations, it is possible that database backup will be created inside Agent folder. In this case you’ll see CRITICAL status immediately — FBDataGuard will recognize and warn you regarding wrong configuration.

And, this job is useful for bundles of FBDataGuard and third-party applications.

In the configuration dialog you can enable/disable this job, set check period (by default it is 10 minutes), and set thresholds for alerts.

Thresholds can be set in % of max size occupied by log or using the explicit size in bytes.

FBDataGuard checks both values and raises alert for the first threshold. If you wish to set % only, you need to set -1 as value to “Max occupied”.

The list of databases monitored by FBDataGuard is in the “Databases” section.

To register database in FBDataGuard, you need to click at the symbol “Plus” in the right corner of “Databases” (there will be a hint “Add database”) and fill the following form:

You can see the list of databases available for registration or their aliases by clicking on the link View database aliases.

After registration, FBDataGuard will populate database configuration with default values and then show web-console with registered database:

You can adjust database settings later; now let’s proceed with alerts setup.

FBDataGuard can monitor several databases at the single server (up to 80 databases). For each database the separate widget is created. At the top widget database status is shown, database nickname (it’s specified during database adding and can be changed). Also database widget shows the full path to the database, its size, status of backups and the number of currently connected users.

Using configuration dialog you can set database nickname, path to database and output folder for the database (to store logs and jobs results).

FBDataGuard checks the validity of path to database and it does not allow specifying the wrong path.

Also, for HQbird Enterprise, in the database widget you can see status of the replication and configure replication by clicking on the icon. Please read more details in the replication configuration section.

Since HQbird 2020, the database widget in HQbird also shows the encryption status of the database.

“Database: Transactions” job is intended to log transactions activity. It monitors 2 important intervals: difference between Oldest Active Transaction and Next transaction and gap between Oldest Snapshot and Oldest Interesting.

If these intervals are out of the frames of the specified threshold, it means problem with transactions management.

These logs can be analyzed to get helpful insight regarding database performance and application quality (see more information here http://ib-aid.com/en/articles/ibanalyst-what-you-can-see-at-summary-view/).

This job also monitors the implementation limit in Firebird: maximum transactions number in Firebird versions before 3.0 should be less than 2³¹-1. Near this number database should be backup and restored. It will throw an alert if transaction number will be close to the restrictions.

Also, the transaction dynamics is shown on the tab “Graphs gallery”:

“Lockprint” job monitors the information from the lock table of Firebird. It is very important for architectures Classic/SuperClassic and useful for SuperServer.

The lock table is an internal Firebird mechanism to organize access to the objects inside Firebird engine. HQbird monitors the important parameters of the lock table:

“Database: Index statistics recalculation” is an important job which helps to keep performance of indices at optimal level, and performs additional checking of a database health.

“Database: Index statistics recalculation” allows to run re-computing of indices selectivity values. During this procedure Firebird quickly walks through leaf pages of indices, and renews statistics about selectivity. By visiting these pages Firebird also verifies their integrity and if index is corrupted, the warning will be thrown.

Also, this job verifies that all indices are active in database. Inactive or non-activated indices usually indicate corruption and lead to performance degradation.

By default this job is disabled, but we recommend enabling it after careful selecting of indices for the recalculation.

There are three modes in this job: AUTO, ALL, SELECTED.

ALL is the mode where all indices will be checked.

AUTO is the default mode. It is very similar to ALL, but it also checks the size of database and do not touch indices if database is bigger than 3.6Gb.

SELECTED is the recommended mode. It allows choosing of indices which should be recomputed or those which should be avoided.

To include indices into the list of recomputed, you need to specify indices names (divided by comma), and to exclude – perform the same in the appropriate field.

As you can see at configuration dialog screenshot, there are fields to enable/disable job, to set update mode, and to include or exclude indices. “DB size to switch, bytes” is to set limit where AUTO mode is working. “Check index activity” switch should be always on, until you are not performing special manipulations with inactive indices.

“Database: Verified Backup” is one of the key jobs to guarantee the safety of data stored in the protected database. During the development of HQbird we had in mind certain recovery scenario, and this scenario implies that the key goal of database protection is to minimize potential losses of data. If we have healthy backup, recovery can be concentrated on saving the most recent data (just entered into the database), and it greatly decreases the time of overall outage.

As you will see below, “Database: Verified Backup” is not just a wrapper for standard gbak functionality and scheduler, this is a smart job which has many built-in rules to prevent problems with backups and provide suitable interface for backups management.

Initially “Database: Verified Backup” job is shown as Ok, though backup was not tried. In this case OK means that backup at least scheduled.

Also this job recognizes files according the name pattern (see below information regarding configuration), and shows the totals number of backups.

After the backup will be done, the widget information will be changed: creation time of last successful backup will be shown, and also the time took to actually perform the backup (only 1 minute 12 seconds at the screenshot with example).

Also, the detailed alert will be send to your email and/or HQbird Control Center:

“Database: Verified Backup” checks the free space at the drive with backup destination, and if it detects that there is not enough free disk space, CRITICAL alert will be sent, and current backup will be canceled (if necessary).

Let’s consider the configuration dialog for backup in more details:

If we will click on button More>>, the advanced backup options will appear:

Incremental backup is a job to schedule and manage incremental backups in Firebird.

Please note that we recommend to use incremental backups only in combination with verified backups, since incremental backup performs coping of database pages changed since the last backup (in case of multilevel incremental backup).

HQbird FBDataGuard implements 2 types of multilevel incremental backup: Simple and Advanced incremental backups, and also Dump backup (see Database: Dump backup).

Multilevel backup in Firebird must follow the following steps:

Incremental backup with simple schedule allows planning 3 levels of backups: weekly, daily and hourly.

You can see summary information for such incremental backup configuration at the following screenshot of its widget:

In order to setup Simple incremental backup, click on Settings “gear” of the widget and select “Simple schedule” (selected by default). The following dialog will appear:

There are 4 main areas in this dialog, let’s cover them one by one.

The top area is devoted for general settings of the incremental backup – they are the same for Simple and Advanced schedules:

Max duration, sec — it limits the maximum duration of backup process, by default is 1 day (86400 seconds).

Minimum free disk space (bytes) — minimal size of free disk space to prevent backup to start, by default ~9Mb

Backup folder — where incremental backup for the selected database will be stored. It is necessary to store incremental backups for each database separately from backups of other databases: i.e., the separate folder for each database.

It is necessary to specify backup folder with enough free disk space to store all level of backups!

Journal name — file name details information about incremental backups files, for internal use only.

Path to nBackup — it is possible to specify other nbackup tool than standard nbackup (not recommended).

Backup name pattern — pattern for files of incremental backup (no need to change it).

Options — additional options for nbackup command line tool (no need to change it).

Do not check existence of backup files — this option should be checked if you plan to delete or more incremental backups to another location.

Do not check GUID chain — this option should be checked if you want to skip existence check of previous levels of incremental backups.

Immediately create non-existing low-level backups — by default this option is On. It means that if you have scheduled the initial start moment of level 1 backup earlier than the initial start moment of level 0 backup, DataGuard will automatically fix it and create level 0 backup right before level 1. The following backups of level 0 will be fulfilled according the regular schedule.

Send OK email for levels 0, 1, 2 — enable this option to receive notifications about incremental backups (highly recommended!)

After setting main set of parameters the schedule itself should be set. As you can see on the screenshot below, you need to specify day of the week and time to do level 0 (weekly) backup, days of week and time to start level 1 (daily) backups and hours and minutes of level 3 (hourly backups).

For each backup level you can specify how many files to keep in history.

By default it is set to keep 5 weekly backups, 7 daily and 24 hourly backups.

However, sometimes more flexible schedule is required, for this purpose Incremental Backup widget has Advanced schedule:

As you can see, the upper part of the configuration screen is the same as in Simple schedule, and the difference is in the way how backup levels are scheduled.

Advanced schedule allows to setup up to 5 levels of backup, and plan them with flexible CRON expressions.

For example, you can setup it to create full copy (level 0) backup every 3 months, level 1 copy every month, level 2 — every week, level 3 every day and level 4 — every hour.

This job also utilizes nbackup functionality in Firebird, but unlike multilevel backups, it always performs a full copy (level 0) of the database. Such job is useful to quickly create a copy of working database.

The configuration of “Database: Dump backup” is trivial:

You just need to setup when and where DataGuard should copy a full copy (level 0 incremental backup), and how many copies it should keep.

One of the often tasks of the database administrators is restoring database from the backup. There could be many reason to do restore, the most popular reasons are regular check of the stored backups and necessity to have fresh restored copy for quick rollback. HQbird FBDataGuard can automate restoring of backups (which were created with gbak or “Database: Verified” backup) with Database: RestoreDB job. Let’s consider the options and parameters of this job.

By default, restore is disabled — and, since restoring can be long and resource-consuming job, please plan when to restore carefully.

The database can be restored from different types of backups. To specify which types of backups are used during recovery, use the Restore Source switch.

Below you can see the configuration dialog for Database: RestoreDB in nbackup mode:

In gbak mode, the configuration dialog for Database: RestoreDB looks like this:

The purpose of "Transfer Replication Segments" job is to send replication segments produced by async replication from master to replica server. In the case of distributed environment of the asynchronous replication, when the network connection between master and replica server is unstable, or with high latency, or when servers are in the different geographical regions, the best way to transfer replication segments will be through FTP or FTP over SSH.

Below we will consider how to setup Cloud Backup for this task.

First, the asynchronous replication master should be configured to save replication segments into the some local folder — by default, it will be ${db.path}.LogArch — as it is shown in the example below:

Then we can setup Transfer Replication Segments job to monitor this folder for the new replication segments and upload them to the remote FTP server.

As you can see at the screenshot above, Transfer Replication Segments job checks folder, specified in “Monitor this folder” with an interval, specified in “Check period, seconds”.

Please note — Transfer Replication Segments sends files in the order of their names, not dates.

To check that transferred files are valid replication segments, and to support automatic re-initialization of the replica databases, the checkmark “Enable/disable” must be enabled.

By default, Cloud Backup compresses and encrypts replication segments before send them. The default password is “zipmasterkey” (without quotes), which can be specified in the field next to the “Compress segments” checkbox. FBDataGuard creates the compressed and encrypted copy of the replication segment and upload it to the specified target server.

To disable packing and encryption, uncheck the “Compress segments” checkmark.

Checkbox “Send Ok report” — send email to the specified in Alerts address every time when replication segment is uploaded. By default it is off.

As a result, FBDataGuard will upload encrypted and compressed replication segments to the remote server. To decompress and decrypt them into the regular replication segments, another instance of HQbird FBDataGuard should be installed on the replica server, and Cloud Backup Receiver job should be configured — see more details in the section Database: File Receiver.

Next, let’s look at the settings for each file transfer protocol.

The purpose of "Transfer Files" job is to send backup files from master to replica server. In the case of distributed environment, when the network connection between master and replica server is unstable, or with high latency, or when servers are in the different geographical regions, the best way to transfer files will be through FTP or FTP over SSH.

Below we will consider how to setup "Transfer Files" for this task.

First, the database server should be configured to save backup files into the some local folder — by default, it will be ${db.default-directory}/backup — as it is shown in the example below:

Then we can setup Transfer Files job to monitor this folder for the new backup files and upload them to the remote FTP server.

As you can see at the screenshot above, Transfer Files job checks folder, specified in “Monitor this folder” according to the schedule specified in “Activate cron expression”. Please note — Transfer Files sends files in the order of their names, not dates.

By default, “Transfer Files” compresses and encrypts backup files before send them. The default password is “zipmasterkey” (without quotes), which can be specified in the field “Encrypt when compressing”. FBDataGuard creates the compressed and encrypted copy of the backup and upload it to the specified target server.

To disable encryption, uncheck the “Encrypt when compressing” checkmark.

As a result, FBDataGuard will upload encrypted and compressed files to the remote server. To decompress and decrypt them into the regular files, another instance of HQbird FBDataGuard should be installed on the replica server, and File Receiver job should be configured — see more details in the section Database: File Receiver.

Next, let’s look at the settings for each file transfer protocol.

The purpose of the “Pump Files” task is to transfer files from one directory accessible to the DataGuard to some other location, usually remote, with the possibility of using various methods that can be connected to the DataGuard in the form of plugins and selectable in the task configuration with the ability to set unique for each plugin parameters. HQbird includes two file transfer plugins: fpt and sftp. There are other file transfer plugins. Transfer plugins are jar files and are located in the Firebird DataGuard/plugins folder.

Let’s consider the options and parameters of this job.

The algorithm of this task is as follows:

In general, “File Receiver” is designed to decompress files from zip archives, and the most often it is used in the pair with “Transfer Replication Segments” to transfer archived replication segments.

“File Receiver” checks files in the folder specified in “Watch for incoming files in”, with interval equal to “Check periods, seconds”. Its checks only files with specified mask according “Filename template” (.journal* by default) and specified extension (.replpacked by default). If it encounters such files, it decompresses and decrypts them with the password, specified in “Decrypt password”, and copies to the folder, specified in “Unpack to directory”.

If the Monitor for reinitialization parameter is enabled, then the File Listener will also monitor files intended for replica reinitialization, such files have the prefix specified in “Prefix for incoming reini- files”.

There are the following additional parameters:

After setup of Cloud Backup Receiver, configure the replica to look for replication segments: set in the “Log archive directory” the same path as in “Cloud Backup Receiver” → “Unpack to directory”.

“Database: Low level metadata backup” is one of the key jobs of DataGuard, it ensures database protection at low level.

First of all, this job stores raw metadata in special repository, so in case of heavy corruption (due to hardware failure, for example) of database it is possible to use this repository to recover database.

The second purpose of this job is to constantly check all important system tables for consistency. Every 20 minutes it walks through all important system tables in the database and ensures that there are no errors at metadata level.

The third purpose is to warn administrator about too many formats for each tables.

There is an implementation limit in Firebird to have 256 formats per table, however even several formats can greatly increase a chance of hard corruption and can slow down the performance. It is recommended do not change tables structure at production database and keep only one format per each table. If it’s not possible, administrator should try to perform backup/restore more often to transform all formats into the single one.

Validation of Firebird database requires exclusive access: i.e., no users should be connected during validation. “Database: Validate DB” job shuts down the database and performs validation of database, and then turns it on.

By default, this job is OFF.

Please consider carefully, is it possible to provide exclusive access for database. Validation can also take significant time.

Using configuration dialog, you can enable/disable this job, set time to run, set the shutdown timeout (time to wait before launch validation), and also shutdown mode (FORCE, ATTACH, TRANSNATIONAL). If you have no deep knowledge n what you are doing, it’s better to keep default parameters.

“Database: Validate DB” will send alert with critical status if there will be any errors.

Also, Firebird will write errors into firebird.log, and they will appear in the alerts generated by <<>> job.

FBDataGuard includes special job to run an explicit sweep, in case if automatic sweep was disabled. By default, job is disabled.

The recommendation is to schedule explicit sweep with disconnection of long-running transactions for all databases where such transactions are detected. The recommended period is once per day (usually during the night, after backup’s completing).

By default, sweep is set to 00-15, which can be not a good time, because default verified backup starts at the same time, so better change it.

Please note: by default, check mark “Disconnect connections with long-running active transactions before sweep” is enabled. It means that HQbird will find and disconnect long-running transactions (more than 300 minutes) before sweep — in order to make sweep efficient. If long-running active transactions will be not disconnected, sweep cannot clean old records versions.

“Do not disconnect processes with name pattern” — in this parameter specify SIMILAR TO expression for processes names which will be not disconnected. By default, we exclude gfix, gbak, gstat and fbsvcmgr processes.

“Disconnect processes older than (min)” — HQbird will disconnect processes which have long-running active writeable transactions, by default threshold is 300 minutes. The practical upper limit for this parameter is 1440 minutes (it is highly unlikely that transaction does something useful more than 1 day).

“Use NN CPU cores to sweep” — HQbird Enterprise can use multiple cores to perform sweep operation, in order to make sweep 4-6 times faster. We recommend to specify no more than 1/2 CPU cores in case of the single database on the server, or 1/4 of CPU cores if there are several databases. For example, if you have 16 cores and 1 big database, set this parameter to 8, if there are several big databases, set 4.

This job watches for all objects related with database: database files (including volumes of multi-volume database), delta-files, backup files and so on.

“Database: Disk space” job analyzes the growth of database and estimate will there be enough free space for the next operation like backup (including test restore) on the specific hard drive.

It generates several types of alerts. Problems with disk space are in the top list of corruption reasons, so please pay attention to the alerts from this job.

This job also contributes data to the server space analysis graph.

By default, this job is enabled.

Using configuration dialog, you can specify check period and thresholds for free space. The first reached threshold will be alerted. To set threshold only in % of disk space, you need to set explicit space in bytes to 0.

If you are using incremental backups (or Dump backup), this job is critically important. It watches for delta-files lifetime and size, and warns if something goes wrong. Forgotten delta-files are the often reason of corruptions and significant losses of data.

This jobs finds all delta files associated with database and check their age and size. If one of these parameters exceeds thresholds “Maximum delta size” or “Maximum delta age”, administrator will receive the alert and database status will be set to CRITICAL.

This job is very useful to capture performance problems and perform overall check of database at low-level without making backup.

We recommend running this job every day and storing a history of statistics report.

Then, with HQbird Database IBAnalyst it is possible to find problems with database performance and get useful recommendations how to fix them.

This task allows you to check the availability of the replica database. After a specified period, it changes the value of the specified generator and compares the value of the generator on the replica side and the master database.

Min diff to alert — the difference between the values of the generator on the master and replica side, after which alter are sent.

Many administrators use the following pattern to solve performance problems — backup-restore-replace. This is usually necessary if the application does not manage transactions well, which leads to garbage accumulation. With proper application design, there is no need to constantly perform backup and restore with replacement. Nevertheless, HQbird provides the ability to automate this process and run it both manually (via the Web console) and automatically (according to a schedule).

The backup-restore-replace pattern consists of the following steps:

However, there are many pitfalls in this seemingly simple process:

In case of any of the above problems, it is necessary to cancel the backup-restore process, that is, a rollback to the original state of the database is required.

HQbird fully automates the process and avoids most problems. It monitors disk space, disconnects active users before starting backup, and monitors the performance and integrity of the backup and restored database.

The “Group sweep” task allows you to perform an intelligent sweep for all databases located along the specified path, including subfolders.

In the dialog “Group sweep” it is possible to specify where databases are situated, configure include and/or exclude mask to perform sweep only for intended databases, and configure time when the process should start.

The “Group sweep” job has the same parameters as [hqbird-config-sweep], plus the following additional parameters:

The “Group master replication” task is intended to simplify the setup of replication of many databases (can reach several hundred). This set of databases (as a group) is integrated into DataGuard, preserving the current functionality of DataGuard and its individual database settings (their individual replication settings and maintenance tasks). “Individual” databases registered with DataGuard are considered to be “very important databases” (VID), and for databases registered with "Group master replication" only the tasks of transferring replication segments to other servers are performed.

One of the reasons for creating a separate “Group master replication” task is that the current implementation of DataGuard has limitations on the number of databases it can process. This limitation is mainly due to limitations in the browser handling web console logic. No such restriction was found for “Group master replication”. Another reason is the complexity of setting up multiple databases when registering them individually.

Setting up the “Group master replication” task consists of several parts.

First of all, you need to set general parameters for all tasks: where the databases are located, what template to use to search for suitable databases, where and how to send their segments (sending protocol, addresses, encryption) and how often to scan directories with segments.

You can specify up to five different nodes — for each node, typical parameters (as in the task of sending segments) are specified:

Notes:

As with a regular database, if FTP upload is not configured (but the database is already in the wizard, which means it generates segments), then the restriction filter comes into effect — how many “unsent” segments are allowed to be stored on disk. Segments over the limit will be deleted at each iteration of the task. This is done so that you can turn on the database as a master without fear of the disk being full, and then configure unloading (and finally re-initialize it to send it to a replica).

To obtain the initial list of databases for batch processing, click the button “Rescan folders and recreate databases list and replication config”.

DataGuard traverses the directory and reconstructs the list of databases (in accordance with the task settings — patterns, excludes, recursion). The list includes only those databases that are not registered in DataGuard as “individual” (VID). If the user has registered a database in DataGuard, then it does not need to be included in the task exclusion template; it will be excluded automatically.

In the same section there are additional buttons for group reinitialization of all registered databases and for setting publishing flags for all databases (relevant for Firebird versions 4.0 and higher).

The “Group verified backup” task allows you to perform backup and test recovery for all databases located at the specified path, including subfolders.

In the dialog “Group verified backup” it is possible to specify where databases are situated, configure include and/or exclude mask to perform backup only for intended databases, and configure time when the process should start.

The “Group verified backup” job has the same parameters as Database: Verified Backup, plus the following additional parameters:

If you click the More>> button, advanced backup options will appear:

The “Group dump backup” task allows you to perform backup for all databases located at the specified path, including subfolders.

In the dialog “Group dump backup” it is possible to specify where databases are situated, configure include and/or exclude mask to perform backup only for intended databases, and configure time when the process should start.

The “Group dump backup” job has the same parameters as Database: Dump Backup, plus the following additional parameters:

The “Group Update Indexes Statistics” task allows you to update index statistics for all databases located at the specified path, including subfolders.

In the dialog “Group Update Indexes Statistics” it is possible to specify where databases are situated, configure include and/or exclude mask to perform backup only for intended databases, and configure time when the process should start.

The “Group Update Indexes Statistics” job has the same parameters as [hqbird-config-db-update-stat], plus the following additional parameters:

During the start FBDataGuard looks for in registry for configuration and output paths:

These values specify the paths to FBDataGuard configuration and output folder — these values are chosen during installation.

One of the most frequently asked questions is how to adjust port for web-console application (by default it is 8082). It can be done by changing port setting in file %config%\agent\agent.properties (%config% is C:\HQbirdData\config or /opt/hqbird/conf).

%config% — folder to store configuration information, it is specified in .

You can specify its password in the file access.properties (in C:\HQbirdData\config or /opt/hqbird/conf)

After setting the password, restart FBDataGuard, and new password will be encrypted and applied.

There is read-only user to access HQbird FBDataGuard, with the name guest.

To setup replication, open HQbird FBDataGuard: run modern browser (Chrome, Firefox, etc) and open this local URL: http://127.0.0.1:8082 (port if configurable in HQBird ini files)

Enter default name and password: admin/strong password.

Register Firebird server, and the following picture will appear:

Check that you are actually connected to the correct Firebird version — in the upper left corner in “Active server” widget should be version “…​ Firebird 2.5 HQbird” or “…​ Firebird 3.0 HQbird” or “…​ Firebird 4.0 HQbird”.

After that click “Add database” in the right bottom corner and configure nick name and path to the database which will be master:

After the successful registration of the database click on the icon in the header of database to setup replication:

After that the main configuration dialog for master and replica databases will appear.

When replication is not configured, this dialog is almost empty:

To start replication we need to create an initial copy of the database file, which will be used as a target for the replication process. Let’s refer to such database file as “replica”.

Starting with HQbird 2018 R2, the replica will be created automatically in the folder which will you specify in the dialog after clicking on “Reinitialize replica database”.

If you have enough space in the folder with the database, just leave the path empty, and click Submit, and replica will be created near the database. Or, you can specify other destination on the local drives with enough free space.

If you click Submit, HQbird will start the process of replica creation. There will be an appropriate message about it:

In case of default action, the resulted database will be in the same folder with the database. The name of the replica will be DATABASE_NAME.EXT.DD-MMM-YYYY_NNNN.4replica — for example, employee30.fdb.17-Apr-2018_142507.4replica

All stages of replica creation are listed as alerts in HQbird (also sent by email):

After completing the configuration of asynchronous replication on the master server, we need to configure it for the replica database at the replica server instance.

First of all, we assume that you have successfully installed HQbird on the replica server. We recommend to use on replica server SuperClassic for Firebird 2.5 and SuperServer for Firebird 3.0 (these are default configurations of HQbird).

Firebird Classic Linux users: If you run Firebird on replica server in Classic mode on Linux, you need to run additional Firebird replicator process with the command fb_smp_server -r.

Second, the replica database should be registered in HQbird FBDataGuard. If you intend to use automatic re-initialization, you can register some small database (employee.fdb) with the required name, and the do re-initialization: as a result, replica database will be automatically transferred from the master server.

Third, we assume that you have managed to setup transfer of logs with Database: Transfer Replication Segments/Database: File Receiver, or with network share.

Then complete the replication setup — the only required parameter is a path to the folder with archived replication segments, and by default it is already set — HQbird will create folder with logs near the database:

So, no need to change anything here, just click Save.

Assuming the replica database is configured in D:\DATABASE\DBWREPLICA.FDB, the HQBird will create folder D:\DATABASE\DBWREPLICA.FDB.LogArch, and replica will import replication segment files from it.

Click Save and restart Firebird service (to ensure that replication parameters were applied).

After restart, the replica server will start to consume the replication segments from the folder — please note, after the import all processed segments will be deleted. Also, it will create file with the name {DATABASE-GUIDE} — Firebird stores there some internal information about replication progress.

If Database: Transfer Replication Segments/Database: File Receiver are configured, it is possible to perform the complete re-initialization with 1 click to “Reinitialize replica database”.

Once clicked, the master HQbird will do the following:

Next steps will be done by replica HQbird instance:

Replica is back to the normal mode.

If you have setup asynchronous replication, but it does not work, the first thing is to enable job Server: Replication Log on the master and on the replica. This job parses replication.log files, and if there are errors, creates the appropriate alert.

Also, the good thing is to enable “Verbose” option on the replica, and restart Firebird. Verbose will make Firebird to write a lot of details about replication into the replication.log file (near firebird.log).

Usually the text of the error is self-explanatory, but since there are some popular questions which occur regularly, we decide to create the table with the list of main problems with asynchronous replication and ways to resolve it.

As you can see, the downtime required for initialization the synchronous replication is bigger than downtime to configure asynchronous replication, because replica database must be online before master’s start.

Synchronous replication is designed to write changes from the master database directly to the replica database. The big advantage of synchronous replication that replication delay can be very small, but the disadvantage is that in the case of the lost connection between master and replica servers there will be gaps in transmitted data.

In this example, the synchronous replica database is on the remote server with IP address replica server and path /data/test2.fdb.

No setup is necessary for synchronous replication on the replica server, except gfix –replica <master-guid> for the replica database to switch it to the replica mode.

In the case of testing synchronous replication of HQbird on the production system, we recommend setting parameter disable_on_error to true.

It will switch off replication in case of replication error, and the master server will continue to work without replication.

To reinitialize replication the replication log should be analyzed and all initialization steps should be done again.

Also, please enable job “Replication log” in HQbird FBDataGuard to monitor replication log for errors and warnings:

Let’s consider how to create replica for asynchronous replication using nbackup:

Database GUID is the unique identifier of a master database.

To find out {DATABASEGUIDE}, run command gstat –h:

To switch database to the replica mode run the following command:

To switch database to the normal (master) mode run the same command with the empty {} instead of database GUID:

for Firebird 2.5 and 3.0

for Firebird 4.0

If you run gstat –h database_name, the output will contain the keyword “replica” in Attributes section for database configured as replica:

For master database there is no special marks in Attributes.

In Firebird 2.5 and 3.0, there is a context variable REPLICA in the SYSTEM area that contains information about database status:

In Firebird 4.0 use another context variable REPLICA_MODE:

Also in Firebird 4.0 you can use the MON$DATABASE monitoring table:

Database replica mode:

This feature can be enabled in firebird.conf with the parameter DSQLCacheSize:

The number specifies how many recent queries for each database connection to cache.

To apply the new value, Firebird restart is required.

By default, cache of prepared statements is 0, it means OFF. We recommend to carefully enable it: start with values like 4, 8, 16, to find the best performance effect.

In Firebird 5.0, the prepared statement cache was redesigned and included in the "vanilla" build.

The cache of prepared statements is controlled by the MaxStatementCacheSize parameter in firebird.conf.

By default, the prepared statement cache is enabled. To disable it, you need to set the MaxStatementCacheSize parameter to 0.

HQbird monitors all aspects of Firebird server and database functioning, and includes continuous monitoring and detailed monitoring.

The continuous monitoring is performed by HQbird FBDataGuard. It is low-invasive, but very effective monitoring which can help to find and resolve the majority of issues with databases performance and stability.

FBDataGuard performs the following monitoring activities:

FBDataGuard can graphically represent information gathered during the monitoring, for example, transactions and number of users:

In the version 2020, HQBird introduces the new approach for the automatic performance monitoring with Firebird MON$ tables and TraceAPI.

Now it is possible to schedule the regular check of database performance in less than 1 minute.

For this just open tab Performance and setup monitoring for transactions and queries:

In order to setup Performance monitoring, specify its mandatory parameters in the dialog:

The first mandatory parameter is “Enable performance monitoring” — it must be enabled to run traces by schedule.

The next important parameters are “Start trace session at” and “Stop trace session”. They contain CRON expressions which specify when tracing starts and stops.

By default, trace is set to start at 10-30 and to end at 11-00. It is recommended to adopt tracing schedule for your needs. Below you can see the table with some popular options.

The next important parameter is time threshold for the slow queries, it is set in the field “Log SQLs with execution time more than”. In this field you need to set time threshold (in milliseconds), after exceeding it logs will be stored and analyzed.

By default, the time is set to 1000 milliseconds, or 1 second. It means, that only queries which take more than 1 seconds, will be logged and analyzed.

We recommend keeping 1000 ms as a basic value, until your database is very slow: in this case 3000-5000 ms can be a good start.

“Send email” check mark indicates if there necessity to send the performance report. The email settings from Alerts configuration will be used to send performance report.

For more advanced settings, “Performance Monitoring” dialog has additional parameters (normally, you don’t need to adjust them).

As a result of this job, HQbird will generate the performance report, which will be stored in the Output folder as a file with the extension html, and it will be sent by email (in case if “Send email” is enabled). Also the most recent performance is available for review and download in the HQBird interface.

The HQbird FBDataGuard performance analysis provides 3 types of reports:

In the beginning of the report you will see the graphical representation of most problematic SQL queries:

When you click “Sort by duration” (it is a default option), you will see SQL queries and stored procedures which took the longest time to execute first.

Normally there will be long-running reports and other big SQLs.

When you click on “Sort by frequency” link in the header of the report, you will see most frequent queries: i.e., those queries which started frequently (among logged queries).

For example, in this case the statement SP_GET_INVOICE_REPORT was run 46 times. It means that this query heavily affects the overall performance, and it should be optimized first.

When you click on “Sort by summary”, you will see the queries which took the most part of the time (among logged queries). These queries usually are the best candidates for the optimization.

FBDataGuard is the first line of a defense for Firebird database; once FBDataGuard encounters something suspicious inside the monitored areas, it sends an alert with description of the issue.

After receiving such alert from FBDataGuard the database administrator should proceed with detailed investigation of the problem.

The choice of tool for detailed monitoring depends on the type of detected problem.

If FBDataGuard reports long-running active transaction (Next-OAT), it is necessary to use HQbird Mon$Logger to detect the source of currently running active transaction.

If stuck of oldest interesting transaction is reported, database administrator must plan an explicit sweep to clean uncollected garbage with FBDataGuard sweep job (if it is necessary) and then plan tracking of forced rollbacks with Performance Monitoring in FBDataGuard.

If users report slowness problem with some queries, it is necessary to configure Performance Monitoring and use Advanced Performance Monitoring.

The problems with general database performance and occasional or periodic slowness require an analysis of database structure, which can be done only with HQbird Database Analyst.

Below we will consider how to work with HQbird monitoring tools in more details.

At the first screen we can see aggregated statistics for database connections, and identify connections with the biggest problems:

At the second tab you can find aggregated performance statistics for statements.

This statistics better reflects the momentary situation in the database — since monitoring tables collect information since the beginning of each object life, statements you can see here are those which were running during the moment when snapshot was taken.

The third tab is “Attachments”. You can open this tab directly to jump there by clicking one of the records at “Aggregated performance statistics”.

“Attachments” shows the list of users connected to the Firebird database, with many useful details: USER and ROLE of attachment, start time and ID of attachment, is there garbage collection enabled for the attachment, name of remote process which established an attachment, and several accumulated performance counters for the attachment: number of sequential reads (done by attachment since its start), number of indexed reads, number of inserts, updates and deletes, as well records backouts, purges and expunges.

By default, some of columns of attachment are switched off, to show only most important information.

Of course, every time you click on attachment, you can jump to transactions running inside it, and then to statements. There is a checkbox in the left upper corner of “Transactions” and “Statements” tabs, which controls the behavior — when checked, only transactions and statements marked by selected attachment ID, will be shown.

Tab “Transactions” shows active transactions at the moment when snapshot was taken.

If checkbox “Link to selected attachment” is enabled, only transactions for selected attachment will be shown, otherwise all transactions are shown.

One of the most important characteristics is a lifetime of transactions: since Firebird is designed to work with short write transactions, it is important to keep them as short as possible. MonLogger highlights transactions with isolation modes and read-write settings which hold Oldest Active transaction and therefore provoke excessive record versions to be not cleared. If you see such transaction and it started a while ago, it means that it can be responsible for excessive records versions.

Sort on “started at” column and look for old transactions, marked in red: all writeable transactions and read only snapshots stuck Oldest Active Transaction and provoke excessive record versions to be hold. Identify where these transactions started (right-click and select “View parent attachment”) and fix your code to commit this transaction earlier.

Tab “Statements” shows statements active at the moment of snapshot: if you need to catch all statements, configure Performance Monitoring and use Advanced Performance Monitoring.

If “Link to selected attachment” is enabled, only statements for specific attachment will be shown, otherwise all active statements are in the list.

Some statements have no associated transaction id (=0): these queries are prepared, but not executed.

The graph displays the performance counters Fetches, Reads, Writes, Marks based on monitoring tables. You can drill down to each time point by clicking on it or selecting "Data for time" from the list.

The graph displays the number of active users and requests, as well as the ping time. You can drill down to each time point by clicking on it or selecting "Data for time" from the list.

The graph displays the performance counters Fetches, Reads, Writes, Marks and statement execute time based on data from trace logs. You can drill down to each time point by clicking on it or selecting "Data for time" from the list.

The graph displays the consumed memory, as well as the processor load based on tracking by the wmic utility.

The same as "RAM and CPU Windows", only in Linux.

The graph displays the number of active transactions and the gap between the counters OST-OIT, Next-OAT.

The graph displays data to the load on the lock manager (relevant in Classic and SuperClassic).

Summary contains the most important information extracted from database statistics. Usually full statistics of database contains hundreds of Kbytes and it is not easy to recognize the important information.

Below is the description of database objects and parameters that you may see in Summary. For description of visible problems (marked red or yellow) see column hints or Recommendations output.

Summary page shows a lot of information, but the most valuable is transactions state (please read description of possible transactions states in IBAnalyst help, it is available by clicking F1 or in menu Help).

At this screenshot you can see that some transaction is active for a long time, “60% of daily average”. IBAnalyst marks such transaction’s state by red, because this transaction may prevent accumulated versions to be considered as garbage by server, and so, to be garbage collected. This is a possible reason of slowness: the more versions exist for some record, the more time it will takes to read it.

In order to find this long-running transaction you can use MON$Logger, or perform direct query of MON$ tables. Then, to find out which tables were affected by long running transactions (tables with a lot of record versions) you need to go to “Tables” view of IBAnalyst.

View Tables contains the information about all database tables. It represents important statistical information about each table. All table warnings are marked (see details below).

You can see the following columns (Columns Records, RecLength, VerLen, Versions, Max Vers are visible only if statistics was generated with gstat -r or with “Include record/rec versions” checkbox enabled):

At “Tables” view you can see tables and their important parameters: number of records, number of record versions, record length, maximum number of versions, etc.

You can sort this view to find the largest tables. Especially we are interested tables with many record versions — many record versions will make garbage collection for affected tables longer. Usually it is necessary to change update and delete algorithms to get rid of many record versions.

Row Versions show total versions count for particular table, and row Max Vers shows maximum versions reached by some record. For example, if you look at table SITE, there are 40611 records, total versions are 76142, but one record has 501 versions. Reading and parsing such packet from disk takes more time, so, reading this record is slower than reading others.

This picture also shows a lot of tables where data was deleted. But, because of long running transaction, server can’t delete these versions, and they still on disk, still indexed, and still being read by server when reading data.

View Indices represents all indices in your database. You can estimate the effectiveness of indices with the following parameters (problem indices are marked red — see smart hints for details)

Some production databases can have indices with the only key value indexed. This can happen because database was developed “to be extended in the future”, or, someone just experimented with the indices during development or tests. You can see these indices as “Useless” in IBAnalyst: I_NUMBER, etc, built on the column that has only one value for all rows. These indices are really useless, because

Of course, that is not all that you can find about your database in IBAnalyst. You can also find

Please download demo package with client applications examples from https://ib-aid.com/download/crypt/CryptTest.zip

At the point, we suppose that you have some database to be encrypted. Put unencrypted database to some path, for example, into c:\temp\employee30\employee.fdb

We suppose that all actions are made with 64-bit version of the Firebird 3.0.3+, in case of 32-bit version simply use the files from WinSrv32bit_ServerPart folder.

After that, the database is encrypted with server-side authentication: the keys are located in the file KeyHolder.conf.

At the figure below you can see files we have on the server to enable the encryption, and what we need on the client side:

After the initial encryption, we suppose that the database will be copied to the customer environment, where access to it will be done only through the authorized application.

To imitate such environment, we need to remove (or simply rename) the file with keys (KeyHolder.conf) from the folder plugins.

Without KeyHolder.conf, the encryption plugin will require receiving the key from the connected application. The example of such application is included in the archive with demo plugin — there is a compiled version and full sources for it on Delphi XE8.

The code to initialize encrypted connection is very simple — before the usual connection, several calls should be done to send an appropriate key. After that, the client application works with Firebird as usual.

Run the demo application to test the work with the encrypted database, it is in the folder CryptTest\EnhancedCryptTestClient\Win32\Debug.

Do the following steps:

In the example of the client application, all database operations (connection, transaction start, transaction’s commit, query start, etc) are made in the very straightforward way to demonstrate all steps of the operation against the encrypted database. You can use this code as an example for the implementing encryption in your applications.

The full verified backup with gbak.exe is the primary backup method for Firebird databases. The standard Firebird distribution includes command line tool gbak.exe to perform it, however, it will not work with the encrypted database in the production mode (without keys on the server). After the encryption, only authorized applications can access an encrypted database, and standard gbak is not an authorized application.

We all know how important backup and restore for the database health and performance, so, in order to perform backup and restore for the encrypted databases, we have developed gbak.exe with the encryption support, and included it into the FEPF.

It is important to say, that this gbak.exe produces the encrypted backup file: it encrypts the backup with the same key as for the database encryption.

If you run gbak.exe from the plugin files with the switch -?, you will see the new parameters of gbak.exe, which are used to work with the encrypted databases:

Let’s consider how to use gbak.exe with encrypted databases and backups.

The following query can be used to test the work of the authentication plugin for ESOE:

As a result, this query will return username, connection id and user role from the destination database db1.

The connection string for the MySQLEngine plugin must begin with the prefix :mysql: followed by the connection parameters as <param>=<value> separated by semicolons.

Possible options:

The MySQLEngine plugin supports the following types of queries:

Data types returned from a SQL queries

Sample query with multiple fields:

A SELECT statement always returns a cursor.

In MySQL, CALL statements can return values through parameters of type OUT and INOUT. The Firebird parser knows nothing about the CALL operator, so returning parameters of OUT and INOUT types is not supported.

However, you can return OUT and INOUT parameters using local variables and executing multiple queries in sequence.

Suppose you have the following stored procedure:

Then the result of such a procedure can be returned as follows:

CALL statements can also return a cursor or multiple cursors. Cursor return from CALL statements is not supported in the current version. Working with multiple datasets using the EXECUTE STATEMENT …​ ON EXTERNAL statement is not supported.

The MySQLEngine plugin supports the use of parameters in SQL queries. Parameters can be unnamed (positional) or named.

An example of using unnamed parameters:

An example of using named parameters:

For named parameters to work, the EDS (EXTERNAL DATA SOURCE) subsystem uses an internal preparser of queries, which replaces all parameters of the form :<name> with ? and retains the binding of the parameter name and its number. Therefore, this only works for queries whose syntax is similar to Firebird’s. For example, for CALL statements, named parameters will not work. In this case, you must use unnamed parameters.

If you replace the named parameters with unnamed ones, then the query will successfully complete

When executing prepare, Firebird obtains the types, sizes, and other properties of input and output query parameters. Further, based on these data, input and output messages are built, buffers for data exchange are allocated. MySQL is able to return the types, sizes, and properties of output parameters (columns), but for input parameters, only their total number is returned. The MySQL C-API is designed so that the types, sizes, and other attributes for input parameters are set by the client application. However, in the Firebird API it is not possible to fully define the input message on its own, it is only possible to convert the input message returned after prepare to another message (type compatible).

Since it is impossible to know the types of input parameters, all parameters are assumed to be of type VARCHAR(8191) CHARACTER SET UTF8. Most Firebird types can be converted to and from a string. However, you cannot pass binary data (types BINARY(N), VARBINARY(N), and BLOB SUB_TYPE BINARY) to such parameters, as they will be malformed. Also, you cannot pass BLOB SUB_TYPE TEXT as parameters if the text is longer than 8191 characters.

The connection string for an ODBCEngine plugin must begin with the prefix :odbc: followed by the connection parameters. There are two options for connecting to the database: using DNS or using the full connection string for the specified driver.

Possible connection string options:

On Windows, if Firebird is running as a service, only system DSNs are visible.

Here are examples of connecting to a MySQL database. Let’s say you configured a system DSN named test_dsn, then the connection string would look like this:

Another option is to connect to the same database using the full connection string. The set of valid parameters in the connection string depends on the selected driver.

For example, for the MariaDB driver, the connection would look like this: