SymmetricDS Notifications

SymmetricDS is an open source application that allows for data replication across database platforms. For some, this means replication of thousands to millions of rows per minute on their database. While SymmetricDS attempts to auto resolves issues, many find it beneficial to be alerted of any problem that may arise, whether it be slow performance or batches in error. For this reason, email notifications can be set up on SymmetricDS.

There are three steps to setting up SymmetricDS email alerts: mail server, monitor(s), and notification. For SymmetricDS Pro, these three sections are found under the Configure tab in the user interface. For the purposes of this article, the following steps will show the process for setting up alerts on an open source installation of SymmetricDS. Note that this article assumes that your nodes have already been set up and configured.

Mail Server:

The first step in setting up notifications for errors, logs, etc. is creating a mail server. This is done through parameters that are included in the sym_parameter table. The following parameters are needed to set up the mail server:

• smtp.allow.untrusted.cert
• smtp.auth
• smtp.from (The email address that the notifications will be addressed ‘from’. This can be set to as own email address)
• smtp.host
• smtp.password (Your email password)
• smtp.port
• smtp.starttls
• smtp.transport
• smtp.user (Your email address)

All of these parameters are needed in sym_parameter for your mail server and the specifics for these parameters are dependent on the email server. The following examples in this article are an example setup for gmail. Note that any changes to the sym_parameter table will synced down through all nodes ( databases) because it is a configuration table. Therefore, it is only necessary to insert the parameters into one database.

The following insert statements show examples for all the necessary parameters. Keep in mind that the syntax may be different on other databases; the databases used in this example were H2:

insert into sym_parameter (external_id, node_group_id, param_key, param_value, create_time, last_update_by, last_update_time) values ('ALL’, ‘ALL’, ‘smtp.allow.untrusted.cert’, ‘true’, current_timestamp, ‘admin’, current_timestamp);
insert into sym_parameter (external_id, node_group_id, param_key, param_value, create_time, last_update_by, last_update_time) values (‘ALL’, ‘ALL’, ‘smtp.auth’, ‘true’, current_timestamp, ‘admin’, current_timestamp);
insert into sym_parameter (external_id, node_group_id, param_key, param_value, create_time, last_update_by, last_update_time) values (‘ALL’, ‘ALL’, ‘smtp.from’, ‘example@gmail.com’, current_timestamp, ‘admin’, current_timestamp);
insert into sym_parameter (external_id, node_group_id, param_key, param_value, create_time, last_update_by, last_update_time) values (‘ALL’, ‘ALL’, ‘smtp.host’, ‘smtp.gmail.com’, current_timestamp, ‘admin’, current_timestamp);
insert into sym_parameter (external_id, node_group_id, param_key, param_value, create_time, last_update_by, last_update_time) values (‘ALL’, ‘ALL’, ‘smtp.password’, ‘********’, current_timestamp, ‘admin’, current_timestamp);
insert into sym_parameter (external_id, node_group_id, param_key, param_value, create_time, last_update_by, last_update_time) values (‘ALL’, ‘ALL’, ‘smtp.port’, ‘465’, current_timestamp, ‘admin’, current_timestamp);
insert into sym_parameter (external_id, node_group_id, param_key, param_value, create_time, last_update_by, last_update_time) values (‘ALL’, ‘ALL’, ‘smtp.starttls’, ‘false’, current_timestamp, ‘admin’, current_timestamp);
insert into sym_parameter (external_id, node_group_id, param_key, param_value, create_time, last_update_by, last_update_time) values (‘ALL’, ‘ALL’, ‘smtp.transport’, ‘smtps’, current_timestamp, ‘admin’, current_timestamp);
insert into sym_parameter (external_id, node_group_id, param_key, param_value, create_time, last_update_by, last_update_time) values (‘ALL’, ‘ALL’, ‘smtp.user’, ‘example@gmail.com’, current_timestamp, ‘admin’, current_timestamp);

You can specify your mail server to send notifications from a specific node, a specific node group, or all nodes. In this example, I have configured my mail server to send notifications for all nodes (‘ALL’) in all node groups (‘ALL’). If you want the mail server configured for a specific node, you can specify the external_id and the node group for that node (Example: ‘000’ for the external_id and ‘corp’ for the node_group_id). Additionally, you could send notifications for ‘ALL’ nodes in a specific node group (Example: ‘ALL’ for external_id, ‘corp’ for node_group_id).

Monitors:

Once the parameters for the mail server have been inserted, SymmetricDS has been configured to send emails. The next step is to create system monitors in the sym_monitor table. A configured monitor compares a system metric’s present value to the threshold value. For example, you can use a monitor to check the CPU usage, batch errors, unrouted data, etc. Some example monitors:

insert into sym_monitor (monitor_id, node_group_id, external_id, type, threshold, run_period, run_count, severity_level, enabled, create_time, last_update_by, last_update_time) values (‘SystemLogMonitor’, ‘ALL’, ‘ALL’, ‘log’, 1, 1, 1, 300, 1, current_timestamp, ‘admin’, current_timestamp);
insert into sym_monitor(monitor_id, node_group_id, external_id, type, threshold, run_period, run_count, severity_level, enabled, create_time, last_update_by, last_update_time) values (‘SystemBatchErrorMonitor’, ‘ALL’, ‘ALL’, ‘batchError’, 1, 1, 1, 300, 1, current_timestamp, ‘admin’, current_timestamp);

Some things to note about the monitors are the type column and severity_level column. The type specifies the type of monitor to execute, SymmetricDS includes the following built-in types: cpu, disk, memory, batchError, batchUnsent, dataGap, dataUnrouted, and log. The severity_level on the monitor table is used by a notification row in the sym_notification table to tie to a monitor (explained in more detail in the next section). Some possible severity_levels include: 100(INFO), 200(WARNING), 300(SEVERE).

Notification:

The last step to setting up email alerts is to add a row to the sym_notification table. The notification rows are tied to the monitors by the severity_level specified on the monitors. For example, if you set up a notification for a severity level of INFO (100), then it will send emails for all monitors that are configured with the severity level of 100 or above. Similarly, notifications with a severity level of ‘SEVERE’ (300) will send emails for all monitors with a severity level of 300 or above. In this article, all of the monitors created above were specified with a severity_level of 300. Therefore, only one row is needed in sym_notification:

insert into sym_notification (notification_id, node_group_id, external_id, severity_level, type, expression, enabled, create_time, last_update_by, last_update_time) values (‘notify_severe’, ‘ALL’, ‘ALL’, 300, ‘email’, ‘example@gmail.com’, 1, current_timestamp, ‘admin’, current_timestamp);

Another thing to note is the expression column of the sym_notification table. The value for this column is the emails that the notifications should be sent. To include more than one email, separate the emails with a comma. For example, ‘example@gmail.com, example2@gmail.com’.

Testing:

Once all of the configuration changes for sym_parameter, sym_monitor, and sym_notification tables have synced to all nodes, you can test your notification system by making some change on your SymmetricDS service that will trigger a monitor. For example, a batch error to trigger the batchError monitor can be created by syncing a table row to a node (database) that does not have that table. Once the monitor fires you will see a message, similar to the following, in your symmetric log, indicating that a notification has been sent:

[store-001] — NotificationTypeEmail — Sending email with subject ‘Monitor event for batchError from node 001’ to example@gmail.com