FairCom DB Notify Concepts
FairCom DB Notify Concepts
The Concepts Guide for FairCom DB Notify explains the use cases, architecture, features, and capabilities of FairCom DB Notify, a data change capture solution for FairCom DB and FairCom RTG. It sends data changes as JSON messages to FairCom MQ for delivery to MQTT subscribers.
FairCom DB Notify is a change data capture (CDC) solution. It tracks data change events and publishes them as JSON messages to FairCom's MQTT broker, FairCom MQ , for guaranteed, real-time delivery to subscribers. An application can subscribe to data changes and push data into any system, including microservices, caches, ERP applications, databases of any type, etc. Each data change is a simple JSON document that defines the data before and after the change event.
This document explains the use cases, architecture, features, and capabilities of FairCom DB Notify.
FairCom DB Notify is optimized for message distribution across various systems instead of high-velocity data replication between two FairCom databases. It is designed to communicate hundreds of data change events per second to unlimited subscribers. One FairCom MQ broker can publish events to hundreds of subscribers and scores of other MQTT brokers and messaging systems. In turn, these brokers can publish messages to subscribers and forward them to other messaging systems. This makes it easy to reliably propagate notifications to massive numbers of subscribers in the cloud, on the edge of the network, and personal devices.
Push data changes to any system in real-time with guaranteed delivery.
FairCom DB
FairCom RTG
Microservices can subscribe to data change events to keep their caches current. Each microservice can have a different subset of data.
Notify a service that a transaction has crossed a threshold, such as a $1000 order.
Replicate data to any SQL and NoSQL database.
Push data changes to data lakes, data warehouses, and data marts.
Send alerts to security systems when sensitive data changes or when a data change violates security policies. For example, a banking system can send an alert when accounting history records are updated or deleted.
Deliver data change events to SaaS applications in the cloud and on-premise ERP systems to process transactions and update master data.
Update massive numbers of mobile devices in real-time with changes to configuration, lookup data, metadata, etc.
Publish data change events to event systems, such as Kafka, AMQP brokers, MQTT brokers, IBM MQ, etc.
Note
FairCom Edge and FairCom MQ are MQTT brokers. They automatically track changes and publish them. Thus, they do not need FairCom DB Notify.
In contrast, FairCom DB Notify will not work without FairCom MQ.
FairCom DB and FairCom RTG provide a notification plugin that monitors tables for record change events and causes FairCom MQ to send these events as MQTT messages to subscribers.
The notification plugin runs on FairCom DB and FairCom RTG. JSON configuration files configure it to monitor specific tables for record change events, which include inserts, updates, and deletes. Configuration determines when notifications are triggered. Configuration defines which tables and events create notifications. The notification engine allows one record event to send notification messages to multiple topics. Configuration can also control which records can trigger a notification. This is done by applying a record filter to the table, which determines which record values trigger notification events. Configuration also controls which fields are included in notification messages.
For each record change event in FairCom DB or FairCom RTG, the notification plugin inserts a record into the appropriate topic table in FairCom MQ. This notifies FairCom MQ to send an MQTT message to subscribers of that topic.
FairCom MQ uses a table to store the messages sent to a topic. FairCom MQ monitors when records are inserted into each topic table. Each time a record is inserted, FairCom MQ publishes a message to that topic.
By inserting records into FairCom MQ, FairCom DB and FairCom RTG notify FairCom MQ when records are inserted, updated, or deleted in their tables. This is a fast, automatic, and reliable process that does not require database replication. It has no performance impact on FairCom DB or FairCom RTG.
The notification engine running in the FairCom DB and FairCom RTG inserts change events directly into FairCom MQ's topic tables, which causes FairCom MQ to publish MQTT messages to those topics. Because MQTT QoS 1 and 2 are chatty, this approach is faster than the source database sending MQTT messages to FairCom MQ. This approach also works when combined with a secondary instance of FairCom DB running on another computer. When the primary computer is down, the secondary instance of FairCom DB becomes the primary and its asynchronous notification engine becomes active.
You may also run FairCom MQ on the same computer as FairCom DB or c-treeRTG. This provides the fastest possible performance because it eliminates network communications with FairCom MQ. For high availability, a secondary computer may also run a read-only instance of FairCom DB and an active, read-write instance of FairCom MQ. This provides high reliability on fewer computers. If the primary computer fails, the secondary computer becomes primary. If FairCom DB on the primary fails (but not the computer), then FairCom DB on the secondary computer becomes the primary. FairCom MQ is an active-active high availability cluster; thus, it can continue running on the primary computer when FairCom DB has failed over to the secondary computer.
FairCom MQ can also take messages it receives, extract the payload, transform it, and store the results into tables in its internal database. As data is inserted into those tables, FairCom MQ can publish notifications for these changes.
FairCom MQ receives record change events from FairCom DB and publishes each data change as an MQTT message that can be processed by any other system. The messages are simple JSON documents designed to be easy to process, such as the one shown below.
{
"operation": "insert | update | delete",
"transactionTimestamp": "2022-03-04T08:40:38.256Z",
"databaseName": "myDB",
"ownerName": "myOwner",
"admin": "myAdmin",
"tableName": "myTable",
"fields": [
{
"fieldName": "myField",
"beforeValue": "Hello",
"afterValue": "World"
}
]
}FairCom MQ can also forward messages to other MQTT brokers and other message systems that support MQTT, such as Amazon MQ, Azure IoT Hub, HiveMQ, RabbitMQ, ActiveMQ, IBM MQ, etc. This makes it easy to integrate notifications with existing messaging infrastructures.
FairCom DB and FairCom MQ can run on the same computer or on different computers. Programs that subscribe to FairCom MQ typically run on their own computers, but they can run on any computer – including the same computer that runs FairCom DB and FairCom MQ.
Configure FairCom MQ for high availability
FairCom MQ provides high availability by setting up two servers and configuring bidirectional replication between them. Both servers are coequal peers. They both share the messaging load. Any MQTT client may connect to either one. MQTT clients are designed to connect to a list of MQTT Brokers and when one fails, the client automatically connects to the next broker in the list. This makes it easy to implement high availability.
FairCom DB Notify watches for data changes to records in specific tables. For each change to each record, it can send messages to multiple topics on multiple MQTT brokers. It can also filter which records and fields are included in the messages.
There is a many-to-many relationship between topics and tables. One table can send messages to multiple topics and one topic can receive messages from multiple tables.
JSON configuration files define the behavior of FairCom DB Notify. There is one JSON configuration file that defines connection and login information for all databases and brokers. There is also one JSON configuration file for each table. The table configuration file defines which insert, update, and delete events trigger messages. For each triggered event, it specifies the brokers and topics to which messages should be sent.
Note
A record filter (like a SQL WHERE clause) can optionally be applied to limit which records cause messages to be sent. A field filter can also be applied to limit which fields are included in messages.
FairCom MQ Data Replication publishes changes to records as messages addressed to topics on FairCom MQ.
FairCom MQ delivers these messages to software programs that subscribe to these topics.
You configure each table to send messages to one or more topics.
FairCom DB Notify tracks one or more of the following record change events:
Insert
Update
Delete
FairCom DB Notify sends record change messages to one or more topics.
FairCom DB Notify includes sends a message only if a record meets the requirements defined in the configured filter expression:
By default, all changes to all records are sent as messages.
If a record filter expression has been defined, a message is sent only if the record passes the filter expression.
FairCom DB Notify includes all fields in a message unless a subset of fields has been configured.
One table can be assigned to multiple topics handled by one or more MQTT brokers.
One topic can receive notifications from multiple tables in one or more databases.
From the database perspective, FairCom MQ Data Replication registers event handlers for each table that sends MQTT messages. For each table and type of event (insert, update, or delete), the handler can send messages to multiple topics on different brokers.
From the MQTT broker perspective, a single topic can receive messages that originate from multiple tables.
The typical use case is for FairCom MQ Data Replication to monitor multiple tables. For each table, it registers a handler for insert, update, and delete changes. When an insert, update, or delete occurs, it sends a message to a single topic on a single broker. It may also send the same message to the same topic on multiple brokers.
For Topic A, it is configured only to send messages when an insert happens and the value of the state field is Texas. It also only includes the id, name, and timestamp fields in the message.
For Topic B, the table is configured to send a message each time a record is inserted, updated, or deleted in Table 1, and it does not filter out records or fields.
The Data Notification Service is configured to send notifications to Topic A when updates occur in Table 1. It is configured to send notifications to Topic A when deletes occur in Table 2.
Tables in FairCom DB can be configured to send messages when specific events occur. To avoid tracking too many events, you can configure FairCom DB Notify to monitor any combination of insert, update, and delete events on each table including limiting which records it tracks.
insert
update
delete
specific changes to a field value
Use the "recordFilter" property to track changes when a field contains specific data. It causes FairCom DB Notify to publish changes only when a field has a particular value. A record filter monitors a subset of records in a table. It treats a table like a SQL "view" where the record filter expression is analogous to a SQL WHERE clause but with a different filter syntax.
{
"databaseName": "myDatabase",
"tableName": "myTableName",
"publishMqttMessages": [
{
"brokerConnectionName": "myBroker",
"topic": "myTopic",
"QoS": 1,
"triggers": [
"insert"
],
"recordFilter": "trans_amount > 1000",
"includedFields": [
"order_id",
"customer_name",
"order_amount"
]
}
]
}"red"Notice the "triggers" property is omitted to track all inserts, updates, and deletes.
{
"databaseName": "myDatabase",
"tableName": "myTableName",
"publishMqttMessages": [
{
"brokerConnectionName": "myBroker",
"topic": "myTopic",
"QoS": 1,
"recordFilter": "!stricmp(color,\"red\")"
}
]
}Important
A filtered set of records may affect how a subscriber interprets change events.
Take the example where a record filter sends change messages only when the "color" field is "red". An update to the "color" field causes FairCom DB Notify to send an update event. If a subscriber assumes that the entire record set only contains "red" records, an update event may seem more like an insert or a delete event because an update can move a record into or out of the filtered set of records.
When a database update changes the color from
"red"to"blue", FairCom DB Notify publishes an update event. The subscriber typically deletes the record from the subscriber’s data set because it is no longer"red".When a database update changes the color from
"blue"to"red", FairCom DB Notifypublishes an update event. The subscriber typically inserts the record into the subscriber's data set because it is now"red".When a database update maintains the color
"red"and changes other field values, FairCom DB Notify publishes an update event. The subscriber typically updates the record in the subscriber's data set because it is still"red".When a database update maintains a non-
"red"value in the color field and changes other field values, FairCom DB Notify does not publishes an update event because the record is not"red". This works for the subscriber because the record does not exist from the subscriber's point of view.When a database insert assigns
"red"to the color field, FairCom DB Notify publishes an insert event because the record is"red". The subscriber typically inserts the record into the subscriber's data set because it is"red".When a database insert assigns a non-
"red"value to the color field, FairCom DB Notify does not publish an insert event because the record is not"red". This works for the subscriber because the record does not exist from the subscriber's point of view.When a database deletes a record containing
"red"value, FairCom DB Notify publishes a delete event because the record is"red". The subscriber typically deletes the record from the subscriber's data set because it was"red".When a database deletes a record containing non-
"red"value, FairCom DB Notify does not publish a delete event because the record is not"red". This works for the subscriber because the record does not exist from the subscriber's point of view.
Each table should be configured to send messages for all inserts, updates, and deletes.
A subscriber should assume its records may or may not be in sync with source records.
It is possible that a subscriber may receive a change event for a record that does not make sense, such as an update or delete event for a record that doesn’t exist in the subscriber’s system, or an insert event for a record that already exists in the subscriber’s system.
Data may start out of sync:
Data may already exist in the source table before a subscription starts. Thus, a subscriber is missing records. The source may send update and delete events to the subscriber for records it may not have.
Data may already exist in a subscriber’s table before the notification engine begins sending notifications. Thus, a subscriber has records that it shouldn’t have. The source may send insert events to the subscriber for records that the subscriber has previously inserted.
Changing data structures in the source or modifying notification settings typically causes data to become out of sync such as when:
Adding more fields in a notification config file.
Changing a record filter in a notification config file.
Adding more fields to a source table’s record structure.
Changing existing fields in a source table’s record structure.
Best practices for synchronizing data include:
When a subscriber should use a bulk load technique to sync or resync its data such as:
A subscriber needs to start a new synchronization process.
A subscriber repeatedly receives events from the source that do not match its data.
The source contains existing data prior to subscribing to change events.
A subscriber contains existing data that is not in the source system.
The source database adds new fields.
The field filter is changed in a notification config file to include new fields.
The source database changes existing fields in a table.
The record filter expression changes in a notification config file.
Note
You can think of a record filter expression like the WHERE clause in a SQL view. Changing a filter expression changes which records exist in the view.
The type of change event is from the source’s perspective:
When the subscriber receives an update message but a corresponding record does not exist in the subscriber’s system, the subscriber must treat the update as an insert.
When the subscriber receives an insert message but a corresponding record already exists in the subscriber’s system, the subscriber must treat the insert as an update.
When the subscriber receives a delete message but a corresponding record does not exist in the subscriber’s system, the subscriber must ignore the delete.