Configuring DBSynchronizer

You install and configure DBSynchronizer similar to the way you would install any other AsyncEventListener implementation, using the CREATE ASYNCEVENTLISTENER statement. However, DBSynchronizer requires initialization attributes to establish a connection with a third-party database.

Prerequisites

Preconfigure the third-party database with the schema and table definitions using the same names as those used in the SQLFire database. When you deploy DBSynchronizer, you specify an initialization String of the form DRIVER_CLASS_NAME,DB_URL. DRIVER_CLASS_NAME is the name of the JDBC driver for the third-party database (the class that implements java.sql.Driver). DB_URL is the full JDBC connection string to use to connect to the third-party database. Include any required connection parameters, such as user credentials, database names, and so forth, in the URL. DBSynchronizer uses the string to obtain a java.sql.Connection to the third party RDBMS.

Procedure

The following procedure describes how to configure SQLFire with DBSynchronizer to apply table DML operations to a third-party database.

Note: The example commands in this procedure use MySQL Community Server 5.5.18 as the third-party database with the Connector/J 5.1.18 driver. However, the general steps are the same regardless of which database you use. Consult your third-party database and JDBC driver documentation for more information.
Note: If you are synchronizing tables in a MySQL database, you must configure MySQL to use case-insensitive identifiers. See Restrictions and Limitations.

  1. Configure the third-party database

    Before you configure DBSynchronizer in SQLFire, install and configure the third-party database. Create the database schema and table definitions for all of the tables that you intend to use in SQLFire.

    To ensure that the tables stay in synch with those in SQLFire, only create the table definitions in the third-party database. Insert and update rows only in SQLFire, so that DBSynchronizer can apply your changes to the database.

    For example, begin by starting MySQL if it is not already running:
    sudo mysqld_safe
    In a separate terminal prompt, start the mysql client and create a new database and table definition. Log on using an account that has privileges to create a new database. For example:
    mysql -u my_username -p
    mysql> create database sqlfdb;
    mysql> use sqlfdb;
    mysql> create table sqlftest
        -> (id int not null, name varchar(10));

    You will create a table of the same name, "sqlftest," in SQLFire, and associate it with a DBSynchronizer.

  2. Configure JDBC connectivity to the third-party database

    DBSynchronizer requires a JDBC driver and connection URL to connect to the third-party database and apply DML operations. Before you configure DBSynchronizer, use a Java client application such as SQuirreL SQL to verify that you can connect to the database.

    In the MySQL example shown in this procedure, the components necessary to establish a JDBC connection are:
    • Driver JAR file: mysql-connector-java-5.1.18-bin.jar
    • Driver class: com.mysql.jdbc.Driver
    • Connection URL: jdbc:mysql://localhost:3306/sqlfdb?user=sqlfuser&password=sqlfpassword
      Note: Although you can include the username and password directly in the JDBC connection URL (as shown above), doing so runs the risk of having the plain-text password appear in exception messages. To avoid recording plain-text passwords, this example will use an encrypted secret generated using the sqlf encrypt-password command.
    To ensure that SQLFire can access the JDBC driver class, add the JAR location to your CLASSPATH. For example, open a new command prompt and enter:
    export CLASSPATH=$CLASSPATH:/path/mysql-connector-java-5.1.18-bin.jar
  3. Start a SQLFire data store in a custom server group

    When you create a new AsyncEventListener such as DBSynchronizer, you must assign the listener to an available server group. For example, to start a SQLFire data store and add it to a named server group:
    mkdir sqlfdbsync
    cd sqlfdbsync
    export CLASSPATH=$CLASSPATH:/path/mysql-connector-java-5.1.18-bin.jar
    sqlf server start -server-groups=dbsync

    This starts a new SQLFire data store server and assigns it to the "dbsync" server group.

  4. Encrypt the Database Credentials
    Use the sqlf encrypt-password command with the external option to encrypt the database credentials:
    sqlf encrypt-password external
    Enter User Name: sqlfuser
    Enter password: sqlfpassword
    Re-enter password: sqlfpassword
    Connecting to distributed system: mcast=/239.192.81.1:10334
    Encrypted to 25325ffc3345be8888eda8156bd1c313

    You will use the encrypted secret when configuring the DBSynchronizer initialization parameters, so that the plain-text password never appears in exception messages. The above example uses the default AES transformation and 128-bit key size for encryption. See sqlf encrypt-password for information about changing those options.

  5. Create the AsyncEventListener configuration

    Connect to SQLFire and create a new AsyncEventListener using the DBSynchronizer. Ensure that you have added the third-party JDBC driver JAR file to your classpath, then start sqlf and create the listener definition:
    export CLASSPATH=$CLASSPATH:/path/mysql-connector-java-5.1.18-bin.jar
    sqlf
    sqlf> connect client 'localhost:1527';
    sqlf> create asynceventlistener testlistener
    > (
    > listenerclass 'com.vmware.sqlfire.callbacks.DBSynchronizer' 
    > initparams 
    >   'com.mysql.jdbc.Driver,jdbc:mysql://localhost:3306/sqlfdb,user=sqlfuser,secret=25325ffc3345be8888eda8156bd1c313' 
    > )
    > server groups (dbsync);

    The LISTENERCLASS argument must specify the built-in SQLFire DBSynchronizer implementation.

    The INITPARAMS argument must specify the full JDBC driver class name to use and the JDBC connection string, separated by a comma. Notice that the INITPARAMS also includes the username and secret options to specify the encrypted password of the user, rather than the plain-text password.

    Note: You can optionally install and start the AsyncEventListener configuration after you associate a table with the listener name. Make sure that you use the same listener name with both the CREATE ASYNCEVENTLISTNER command and the CREATE TABLE command.
  6. Start the AsyncEventListener

    Use the SYS.START_ASYNC_EVENT_LISTENER procedure to start the new DBSynchronizer implementation:
    sqlf> call sys.start_async_event_listener('TESTLISTENER');
    Note: The AsyncEventListener name is a SQL identifier and should be entered in uppercase letters with this procedure.
  7. Create identical schema and tables in SQLFire

    After you create the DBSynchronizer listener in SQLFire, create tables and associate them with the listener to have DBSynchronizer propagate subsequent table DML operations to the third-party database. The tables you create should use the same schema, table name, and table definitions as those you used in the third-party database. For example:
    sqlf> create schema sqlfdb;
    sqlf> set schema sqlfdb;
    sqlf> create table sqlftest
    > (id int not null, name varchar(10))
    > asynceventlistener(testlistener);

    Notice that the table definition is the same as that used in MySQL, except that includes the ASYNCEVENTLISTENER clause to associate the table with DBSynchronizer.

    Note: Because the associated listener configuration does not have to be available at the time you create the table, SQLFire does not display an error message if the specified listener name does not yet exist. Make sure that you use the same listener name with both the CREATE ASYNCEVENTLISTNER command and the CREATE TABLE command.
  8. Execute DML and verify synchronization

    After you associate the table with a DBSynchronizer implementation, SQLFire queues DML operations that are executed against the table to the third-party database specified in the DBSynchronizer INITPARAMS argument. For example, while still in sqlf, execute:
    sqlf> insert into sqlftest values (1, '1st Entry');
    Then return to the mysql client and verify that the DML was propagated to your database:
    mysql> select * from sqlfdb.sqlftest;