Library

10 Things in HQbird to Please Your Firebird database

HQbird is designed to automate all types of maintenance, backups, and performance monitoring. . Here you can find the 10 most popular tasks which you can automate with HQbird.

Implement proper sweep procedure and get rid of regular backup/restore
Implement complex backup schemes for gbak
Implement incremental backup
Transfer backup to the other computer (and receive files there)
Implement restore of verified and incremental backups on another computer
Recalculate index statistics
Schedule trace performance monitoring and receive performance reports by email
Do general performance monitoring and SQL ping measurement
Analyze database statistics with HQbird DBAnalyst
View details about long-running active transactions

1. Implement proper sweep procedure and get rid of regular backup/restore

Usually, autosweep or manual sweep commands do not clean the database properly, because they did not close long-running writeable transactions. As a result, garbage collection is not performed or performed in a non-effective way.
HQbird Standard has "Sweep Schedule" job which properly implements sweep procedure.

To schedule sweep for Firebird, set “Enabled”, check that sweep time is fine (by default is it set to 23:00 every day), and click Save.

In this configuration, before the sweep, HQbird will gracefully disconnect only connections with long-running writeable transactions (by default, more than 30 minutes, increase if necessary), excluding system tools (gbak, gstat, gfix, etc), and then do the sweep.

After enabling "Sweep Schedule", a database will be cleaned from garbage versions regularly, and there will be not necessary to do backup/restore to “clean” database.

2. Implement complex backup schemes for gbak

Gbak is a pretty simple tool, but it is not an easy task to implement a proper backup scheme.
HQBird allows implementing very complex backup schemes in 1 minute. For example, you can schedule backups for 6 am and 23-00, set to keep only 5 most recent backups, compress them (using standard zip), store to the specific folder:

HQbird always keeps track of backups and sends detailed reports after backup completing the backup (if Send OK report is enabled).

This is not all, of course, you can also set timeouts for backups, enable test restore, specify extra options for the backup command, move backups after completion, and execute the post-backup custom script:

HQbird automatically calculates space requirements, and if there is not enough space for the backup, it will send an alert with error and cancel the backup.

In case of successful backup, the following message will arrive at the specified email:

Regular backup was done successfully. 

Backup 'D:\MyDatabase\backup_20200824_20-00.zbk' 10.3 MB was created at 2020-08-24 20:00:00.011+02:00 took total 00m:02s.808 to complete. 
Test restore is omitted. 
Backup: [OK] Backup [D:\MyDatabase\backup_20200824_20-00.fbk] 10.3 MB done successfully at 2020-08-24 20:00:02.429+02:00, taking 00m:02s.403. 

Other: [OK] Archive [D:\MyDatabase\backup_20200824_20-00.zbk] 1.2 MB done successfully at 2020-08-24 20:00:02.819+02:00, taking 00m:00s.359. 
Regular backup was done successfully. Test restore is omitted.

To open the email alerts configuration, click on the Server Name in the header of your HQbird web console:

and then fill out SMTP email details.

HQbird automatically detects SSL/TLS/etc details of the email and supports Google Mail, Office 365, and regular email SMTP accounts.

Those users who have HQbird Unlimited Subscription, can use HQbird Control Center to collect alerts from several HQbird on the single page and view their statutes: backups, errors, alerts, etc.

3. Implement incremental backup

Incremental backup (with nbackup) is especially useful if your database is big (more than 100Gb) because it is faster. Since incremental backup does not verify database file as gbak does, it is recommended to combine both types of backups. HQbird offers 3 types of incremental backups:

3.1 Dump backup

Dump backup is the simplest way to use incremental backup – it always creates a full copy of the database.

To implement it, simply define when and where dump backup should be done:

If you have checked Send Ok email, HQbird will send notification about successful dump backup:

Dump backup was done successfully 
Dump backup ["E:\MyBackups\nbackup.nbk.nbackupcopy"] 13.5 GB was created at 2020-09-08 18:00:00.0 CAT.

3.2 Simple incremental backup

The second type of incremental backup is the simple incremental backup.

It allows us to specify 3 levels of increments: level 0 weekly, level 1 daily, and level 2 hourly.

Looks scaring, huh? :)
Actually, the minimum configuration is just set the path where to make incremental backups, and set it “Enabled”.

However, yes, it has a lot of settings, so it is better to read a detailed description of the Incremental Backup job in HQbird User Guide.

3.3 Advanced Incremental backup

If you need more flexibility in the scheduling and up to 5 backup levels, HQbird has Advanced Incremental Backup, and it definitely requires to read HQbird User Guide:

4. Transfer backup to the other computer (and receive files there)

Ok, good, so you have set up backups with HQbird, and now need to transfer them to another computer? HQbird has a job “Transfer Files” (previously Cloud Backup) to make this task very easy.
“Transfer Files” job does the following: it checks the specified folder for the files with a specified file mask, and if found, sends it to the FTP (FTPS/FTP over SSH).
Transfer Files can also compress files to be sent, and it keeps the journal of the sendings.
To send backups to another computer, enable Transfer Files, specify which folder to monitor, and filemask to send (default is the filter for files with fbk extension):

Then, it is necessary to set up an FTP client connection to send files to.
Up to 5 FTP servers are supported.

The good question is where to get FTP server?
Of course, you can use Filezilla or other FTPs, but HQbird has an embedded FTP server, to receive files from the other HQbirds.
The FTP is managed in the configuration file C:\HQBirdData\config\ftpsrv.properties.

#Dataguard config checker
#Wed Sep 09 14:17:25 AST 2020
ftpsrv.enable=false
ftpsrv.defpsw=defaultpassword2
ftpsrv.defuser=admin2
ftpsrv.homedir=
ftpsrv.port=8721

It can be enabled manually (HQbird FBDataGuard restart is required), or, on Windows, it is chosen during the installation.

5. Implement restore of verified and incremental backups on another computer

If you set up “Transfer Files” on the master server to send backup files to the embedded FTP of HQbird running on the backup server, or with third-party FTP, you can configure automatic restores of that backup.
For this, HQbird has job “RestoreDB”, with a big list of options:

restored checks for the new files in the specified folder (it can be the incoming folder of your FTP), and restore both verified and incremental backups.

It is necessary to set up where to restore the database, and what to do with previous restores – overwrite or rename.

For details, please refer to HQbird User Guide.

6. Recalculate index statistics

Indices statistics in Firebird has to be recalculated. It should not be often task, but it is good to do it once per week for intensively changed databases.
HQbird has “Index Recalculation Job” for this:

7. Schedule performance monitoring and receive performance reports by email

HQbird has powerful performance monitoring, based on Firebird Trace API.
To enable it, open the tab “Performance” in the Dashboard:

After that, choose the database you want to monitor and open dialog:

To enable performance monitoring, set starting time (in CRON format, by default it is set to 10-30 AM every day) and end time (set to 11-00 AM every day), and click Ok

It makes sense to schedule performance monitoring for the most loaded period of the day when users claim about slowness.
After the performance session, HQbird will generate a complete report with the result of the top-10 slowest queries, top-10 queries with fetches (i.e., CPU usage), reads, writes, and times-summary.
The report is generated in Html format and kept in the “Output folder” (up to 50 copies by default), and if you will mark “Send email”, it will be sent automatically to your email.

With this performance report, it is very easy to discover the most problematic queries and concentrate efforts on their optimization, in order to achieve better performance with fewer efforts.

8. Do general performance monitoring and SQL ping measurement

HQbird has a job called “SQL Ping”, to measure database response time, and collect performance statistics:

With this job, HQbird collects main performance statistics, so it is easy to see peak load times. The graphs with results are on the “Graph summary” tab of the HQbird:

9. Analyze database statistics with HQbird DBAnalyst, view sizes of tables, indices, view number of garbage versions, and get recommendations about page size, indices, maintenance

For developers who are focused on improving database performance and transaction management, it is necessary to install tools called “HQbird Admin”, and use the HQbird Database Analyst, and collect database statistics.

This tool shows a lot of information about the database: sizes of tables and indices, number of records and record versions, depth of indices, etc.

Also, Database Analyst gives recommendations based on the database statistics: what page size is better, what indices are useless and can be dropped (to speed up inserts/updates/deletes and restore), what tables are fragmented, etc.

10. View details about long-running active transactions

Another tool from HQbird Admin, MON$Logger, collects and analyses information from the monitoring tables of Firebird.
Especially useful to see attachments with long-running active writeable transactions:

Such transactions prevent proper garbage collection, and even 1 such connection can greatly affect the performance.
MON$Loggers shows details about attachments (IP Address, username, role, IO, etc), transactions, and statements.

What is not included in HQbird Standard

The following features are not included in HQbird Standard, but available in upper editions of HQbird: Professional and Enterprise:

Professional

IBSurgeon FirstAID and IBBackupSurgeon, recovery tools for Firebird
Firebird SQL Studio – administration tool, included in HQbird Professional and Enterprise

Enterprise (included Standard and Professional)

Native replication (asynchronous and synchronous, one-to-many replication without triggers, with DDL replication support)
Multi-threaded backup (up to 6x faster than normal)
Multi-threaded restore (up to 3x faster than normal)
Multi-threaded sweep (up to 6x faster)
Encryption (1 server license)