Replication with Symmetric DS

See the Replication page.

We can no longer recommend SymmetricDS as a stable solution.  At least one user has experienced ongoing mild corruption that we have not been able to resolve.  The issue is that once an insurance payment has been entered, that weeks or months later it will be discovered that the payment balance no longer matches the splits.  This has been verified with backups.  This is probably happening because SymmetricDS uses triggers on the rows rather than simply passing the SQL commands.  Our SQL commands are designed to only change single column values, and their replication strategy seems to be marking entire rows as superior to other rows instead of just the individual column values.

Updates with SymmetricDS
(this is for historical purposes only.  Do not use Symmetric DS)

If using SymmetricDS for replication, replication MUST be stopped before updating major or minor versions in EZ 2000 Plus Dental.  And after the update, the Symmetric schema must be rebuilt.  New builds of EZ 2000 Plus Dental, such as upgrading from 6.8.3 to 6.8.9 are guaranteed to be backwards compatible and not contain any schema changes.  So for upgrading the build in EZ, the replication does not need to be stopped.

If using SymmetricDS, tables must be converted from MyISAM to InnoDb in order to use row-level locking instead of table-level locking.  InnoDb also allows a failed transaction to retry instead of skip.

Installation of SymmetricDS:

JumpMind

1.  Download SymmetricDS 2.0.6.5 to the central 'Symmetric'(.20) server and each active remote server.

2.  Unzip the contents to the c:symmetric directory.

3.  Stop the Symmetric service after all data entry is halted and final one way replication has been verified.

4.  Run the uninstall_service.bat file from the original bin directory.

On the corporate Master database only...

1.  Copy customized corp.properties, sym_service.conf, and insert_sym.sql into the new conf directory.

2.  After importing the newly Merged master database, use mysql utility to setup and grant permissions for symmetric userid.

3.  Run sym using corp.properties and --auto-create to create the Symmetric tables in the master database.

4.  Use mysqldump to create the create_opendent.sql file that will be used to create the remote databases.

5.  Run the customized insert_sym.sql script to add the channels, routers, and triggers.

6.  Run the install_service.bat script to install the Symmetric service.

7.  Run the customized insert_rep.sql script to create the random primary key ranges.

8.  Update the my.ini file with the proper server-id number for EZ 2000 Plus Dental Software random primary keys.

9.  Restart MySQL.

10.Start the new Symmetric service.

11.As appropriate, Open Registration for each remote node.

12.As appropriate, configure each node for an initial load.

On each remote server...

1.  Copy customized remote.properties and sym_service.conf into the new conf directory.

2.  Update external.id number in remote.properties as appropriate.

3.  Run mysql using create_opendent.sql to create the database.

4.  Use mysql utility to setup and grant permissions to symmetric userid on remote database.

5.  Run the install_service.bat script to install the Symmetric service.

6.  Update the my.ini file with the proper server-id number for EZ 2000 Plus Dental Software random primary keys.

7.  Restart MySQL.

8.  Start the new Symmetric service.

9.  Verify Registration.

10.As appropriate, receive initial load.