HQbird Replication Test

Assista a este vídeo de 5 minutos sobre como configurar a replicação HQbird na variante mais simples

How to setup Firebird replication with HQbird Enterprise

Download

Download HQbird for Windows or for Linux (or both). For Windows there are the following files:

• HQbirdServer2022.exe - it has integrated Firebird inside, version 2.5 and 3.0 and 4.0
• HQBirdAdmin2022.exe - optional installer for Admin tools, it is not necessary for replication

For Linux - follow this instruction.

What is HQbird Enterprise?

• HQbird Enterprise is the advanced distribution of Firebird for big databases with monitoring, optimization, and administration tools, it also includes the plugin for native master-slave replication.
• HQbird is 100% compatible with Firebird 2.5 and with Firebird 3.0 and 4.0  – no changes in ODS are required, to switch to HQbird and back no backup/restore is required, just stop/start Firebird and replace binaries.
• HQbird replicates DML (INSERT/UPDATE/DELETE, stored procedures, etc) and DDL (CREATE/ALTER TABLE, etc) changes; no triggers needed, the only requirement for the replication is to have unique or primary keys for all replicated tables.

Prerequisites

In order to use the replication, install HQbird, register it (with the trial or with the full Enterprise license) and configure replication. HQbird should be running on the master server and on all replica servers. Below we will consider how to setup Firebird replication with HQbird Enterprise.

Asynchronous and synchronous replication

HQbird supports 2 types of replication: asynchronous and synchronous. Usually, asynchronous replication is the best choice for production Firebird databases.
 

Installation

HQbird Server installation

HQbird Serverwith integrated Firebird should be installed (version 2018, 2020 2022, or higher) both on the master and replica server.

To enable replication you need to have working and registered (trial or full) copy of HQbirdServer 2020 or higher on your master and replica servers. Please refer to section 2 (from page 8) of HQbird User Guide for details of HQbird Server installation.

Attention to users of Firebird 2.5! If you have specified many page buffers in the header of your database, it can affect Firebird performance, because integrated HQbird installs Firebird in SuperClassic mode. To avoid the potential problem, set page buffers in the header of your database to 0, it will ensure that the value from firebird.conf will be used:

gfix –buff 0 –user SYSDBA –pass masterkey disk:\path\database.fdb

Steps to setup replication

There are several mandatory steps to setup replication, they are different for asynchronous and synchronous replication. Steps to setup asynchronous replication

1. Setup database for replication at master
2. Create a copy of master database file with nbackup and switch it to replica mode
3. Setup database for replication at the replica server

Step 1: Setup database for replication at the master server

To setup replication, open HQbird: run a modern browser (Chrome, Firefox, Edge, 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 (not the alias!) which will be master:

​

Please note that database should be registered with its explicit path, not with the alias - the replication will not work with the alias.
After the successful registration of the database click on the icon in the header of the 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:

Let's consider in details how to configure replication.

Asynchronous replication at the master

Asynchronous replication writes all changes in the master database to the replication log: the set of files called «replication segments». Replica server pulls these segments and inserts into the replica database.

Previously we have registered H:\dbwmaster.fdb, it is the asynchronous master database in this
example. To configure the asynchronous replication on the master side: select replication role:
Master, then Asynchronous, and click “Save”.

There are several options you can change if you click "More>>" (but you don't have to - in case of clean installation everything should work with basic setup):


Let's consider all the parameters in this dialog (no need to change it, just for the information):

• «Log directory» – folder where operational logs will be stored. It is a system folder, completely operated by Firebird. By default, no need to change its default value "${db.default-directory}\ReplicationLog". With default settings, {db.default-directory} is a placeholder for the database folder in C:\HQbirdData\output\....
• «Log archive directory» – folder, where archived logs will be stored. According the default value "${db.path}.LogArch", HQbird will create folder "DatabaseName.LogArch" in the folder with the database, so there is no need to change this parameter. 
• The third parameter ("Override log archive command") is optional, leave it empty.
• The fourth parameter «Force flush committed data in, seconds» is also optional, it indicates how often we should move committed data to the archived segments. By default, it is set to 90 seconds.

Please note that replication parameters are initialized at the first connection to the database. That's why we recommend restarting Firebird service (or all connections in case of Classic) after replication configuration – such restart ensures that replication will start properly.

In this case, the replication log segments will be written first to ${db.path}.ReplLog (by default located in the same folder where database is located), and after reaching the maximum segment size, or commit, or another trigger, the default archive command will be started - it will copy archived replication segments to ${db.path}.LogArch (in the same folder where the database is located). We have set the flush timeout of committed data for every 90 seconds.

After the replication start, you should be able to see replication segment files in the folder ${db.path}.ReplLog, specified in «Log directory» immediately after any operation at the master database:

​

The operational segments are rotated by the engine, and once each segment is completed, it is copied to the archive log. You don't need to do anything with operational segments!

After the commit and specified timeout of committed data (i.e., after Commit), you will see archived segments in the folder, specified by «Log archive directory».

Archive replication log is essentially the chronologically ordered list of completed operational segments. These files should be imported by replica server into the replica database.

How to copy replication segments from master server to the replica server?

Please read this short guide to set up the transfer properly.

Step 2: Create a copy of a master database file with nbackup

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 is created automatically when you are clicking on the "Reinitialize replica database". The resulted database will be in the same folder as 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

Step 3: Setup database for async replication at the replica (slave) server

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.

The replica database should be registered in HQbird FBDataGuard.

Please note: the database should have replica database GUID before the registration!

Then complete the replication setup - the only required parameter is a path to the folder with archived replication segments:

To apply configuration, click «Save» and restart Firebird service.

You can change the detailed parameters if you click "more>>", but, in case of clean installation, no need to change them:

By default, the replica server is configured to import replication logs from the folder - it is located in the same folder with the replica database.

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 the file with the name {DATABASEGUIDE} – Firebird stores there some internal information about replication progress.

Note: It is not recommended to store archived replication segments from the different databases into the same folder! Always allocate the separate folder for each pair of master-replica databases!

 

Steps to setup synchronous replication

1. Stop Firebird
2. Create a copy of the master database file, switch it to replica mode and copy it to the replica server(s)
3. Setup replica server(s) and database(s) for replication with HQbird FBDataGuard
4. Start replica server(s) - before master server
5. Setup master server and master database for replication with HQBird FBDataGuard
6. Start master server

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

Synchronous replication at master and replica

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 the server name replicaserver 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.

Replication parameters for testing

In the case of testing HQbird Enterprise 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:

 

How to create replica copy manually

Also, it is possible to create a replica file manually without stopping the master server, with nbackup. It is very useful for asynchronous replication, it also makes possible to create additional replicas online – i.e., without stopping a master.
​

Of course, it is still possible to create a replica with the simple copy process: stop Firebird, copy database file, complete setup of replication, then start Firebird.

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

1. apply nbackup lock

   nbackup –l database_path_name  -user SYSDBA –pass masterkey

2. copy locked database file to create a replica

   copy database_path_name  replica_path_name

3. unlock master database

   nbackup –n database_path_name  -user SYSDBA –pass masterkey

4. Fix up replica database

   nbackup –f replica_path_name_name

5. Switch the database to replica mode

   gfix replica_path_name –replica {DATABASEGUID}  –user SYSDBA –pass masterkey	

What is {DATABASEGUID}?

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:

gfix disk:\path\mydatabase.fdb  -replica {guid} -user SYSDBA -pass masterkey

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

gfix disk:\path\mydatabase.fdb  -replica {} -user SYSDBA -pass masterkey

Note: If you don't see Database GUID in gstat –h output, connect to the master database using Firebird binaries from HQbird distribution (with isql or any other application), and run gstat –h again.

Note. You can run any users operations with replica database which do not change metadata or data. Essentially, it means all read-only operations, like SELECTs.

Please see the video on how to configure asynchronous Firebird replication with HQbird:

Do you think that replication setup is too complex? We can make a 30-minutes remote desktop session to demonstrate all steps and help you to get familiar with it. Don't hesitate to contact us: [email protected]!

Licensing

The single license of HQbird Enterprise includes 1 master and 1 replica server, additional replicas can be purchased separately as 0.5 of the full license price. Please purchase it here.

Contact us

IBSurgeon Support will be glad to help you with setup and troubleshooting for HQbird Enterprise: please contact us: [email protected].