HQbird Enterprise Test

How to setup Firebird replication with HQbird Enterprise

Download

Download the following necessary files (you should receive links to them in the test email - request it if you didn't):
  • HQbird ServerSide installation - it has integrated Firebird inside
  • HQbird Admin installation
  • If you need the version for Linux - please contact us ([email protected]) to get additional instructions.
Carefully follow the instruction below to setup the HQbird Enterprise properly.

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.5+ and (HQbird for 3.0) with Firebird 3.0 – no changes in ODS are needed, 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.

How to setup Firebird replication with HQbird?

In order to use the replication, install HQbird ServerSide with integrated Firebird, 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.
You can test HQbird for Windows and Linux, Firebird 2.5 and 3.0, 32bit and 64bit.

Please note that you should see HQbird FBDataGuard registered as Enterprise to setup replication.

 

Asynchronous and synchronous replication

HQbird supports 2 types of replication: asynchronous and synchronous. Please review their features to select the best option for your requirements.

Asynchronous replication for Firebird

In the case of asynchronous replication, the master server stores committed changes from the master database to the files (replication segments), which can be consumed asynchronously by one or more replica servers.
Asynchronous replication for Firebird

The main features of asynchronous replication are:

  • Changes are synchronously journaled on the master side (journal better be placed on a separate disk)
  • Practical delay between master and replica is ~1 minute
  • Journal consists of multiple segments (files) that are rotated
  • Filled segments are transferred to the slave and applied to the replica in background
  • Replica can be recreated online
Issues to be considered
  • Delay between master and replica can grow in case of heavy load (due to the delayed processing of replication segments)

Asynchronous replication is the recommended choice for HQbird Enterprise: it provides stability and anti-corruption protection of Firebird database, it can be configured quickly and easily, it does not require big downtime to setup, and it is suitable for distributed environments (when the replica is located in the cloud or at the remote location).

Synchronous replication for Firebird

In case of synchronous replication, master server directly inserts committed changes of the master database to one or more replicas databases:
Synchronous replication for Firebird

The main features synchronous replication are the following:
  • Changes are buffered per transaction, transferred in batches, synchronized at commit
  • Practical delay is 1-2 seconds
  • Follows the master priority of locking
  • Replication errors can either interrupt operations or just detach replica
  • Replica is available for read-only queries (with caveats)
  • Automatic failover can be implemented (with HQbird Cluster Manager)
Issues to be considered
  • Additional CPU and I/O load on the replica side
  • Requires direct and permanent network connection from master to replica(s), 1Gbps recommended
  • Replica cannot be recreated online, initialization of replication requires stop of master
When to use synchronous replication:
  • Custom failover cluster solutions with 3+ nodes (especially for web applications)
  • Scale performance by moving reads to the separate replica server (report servers, data marts or read-only web representation)
  • In combination with asynchronous replication for performance scaling

Installation

HQbird ServerSide installation

HQbird ServerSide with integrated Firebird should be installed (version 2017 or higher) both on the master and replica server.

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

Attention! 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. Stop Firebird
  2. Create a copy of master database file and switch it to replica mode
  3. Setup master server and database for replication with HQBird FBDataGuard
  4. Start Firebird at master server
  5. Put copy of the master database to the replica server and setup it for replication
  6. Start replica server

Steps to setup synchronous replication

  1. Stop Firebird
  2. Create a copy of 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 replica database must be online before master's start.

How to create replica of the master database file

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».

There are 2 ways to create replica:

The easy (recommended) way

   Stop Firebird, copy database file, complete setup of replication, then start Firebird.

For big databases (300Gb+)

  For asynchronous replication of really big databases, if you can't stop Firebird for the time required for copying of the complete database file, it is possible to use nbackup to copy main database file, and then stop master Firebird to copy only the small delta-file:
  1. apply nbackup lock
  2. copy database file to create a replica
  3. complete setup of replication at a master
  4. stop master, copy delta file to replica
  5. start master server and unlock a master database
  6. apply delta to the replica database
  7. start replication at replica server
After creating a copy of the database file it is necessary to set it to the replica mode with gfix and link replica to the GUID of the master database. The {guid} parameter is the unique identifier of the master database. You can get it in the gstat –h output of the master database:

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.

Nore. 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.

Replication configuration

To setup replication, open HQbird FBDataGuard: run modern browser (Chrome, Firefox, etc) and open this local URL: http://127.0.0.1:8082
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».

After that click «Add database» in the right bottom corner and configure nick name and path to the database (not alias!) which will be master (or replica):

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 (please note, that master and replica databases should be on the different Firebird and FBDataGuard instances). When replication is not configured, this dialog is almost empty:

Let's consider in details how to configure replication.

Asynchronous replication at 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.

To setup asynchronous replication on the master server you need to specify 2 mandatory parameters:

  1. log_directory - path to the folder with current logs.
  2. log_archive_directory - path to folder with archived logs ("archived" means that they are ready to be replicated)
The third parameter (it is in the edit "Override log archive command") is optional, leave it empty.

Below you can see how it should look like in FBDataGuard replication dialog:

Previously we have registered H:\dbwmaster.fdb, it is the asynchronous master database in this example.

The replication log segments will be written first to C:\Databases\Replication\Log, and after reaching the maximum segment size, or commit, or another trigger, the default archive command "copy $(logpathname) $(archpathname)" (on Windows) will be started - it will copy archived replication segments to C:\Databases\Replication\LogArch.

You can define your own copy command - just enter it into "Override log archive command", but we don't recommend it - don't change it and use default copy command until you really know how to change it.

Adjust «Log directory» and «Log archive directory» parameters to the actual folders on the server, and click Save to apply replication parameters. We do not recommend to change log archive command.

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.

After replication start you should be able to see replication segment files in the folder specified in «Log directory» immediately after any operation at master database:

The operational segments are rotated by the engine, and once each segment is completed, it is copied to archive log. Default segment size is 16Mb.

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 setup the transfer properly.

Asynchronous replication at replica 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:

In this case, the replica server is configured to import replication logs from c:\Database\arch folder.

Click «Save» and restart Firebird service.

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.

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!

 

Synchronous replication at master

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.

Synchronous replication configuration

In this example, the synchronous replica database is on the remote server with IP address 192.168.1.40 and path D:\Database\dbwrepl.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:

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]

Subscribe to IBSurgeon news

Subscribe