Stop Guessing if Your Data is Correct

SymmetricDS promises to sync your data, but how do you know it really worked? How do you really know 2 databases are in sync? Or, maybe you have well-meaning users that come along after the sync, changing data unexpectedly, and throwing production out of whack.

Whatever the case, wouldn’t it be nice to be able to compare any 2 databases, and know exactly what the differences are and be able to prove they are in sync? That’s where a new, experimental feature of SymmetricDS comes in. It’s called simply dbcompare. Dbcompare is slated for SymmetricDS 3.8, but if you watch closely, it’s fully included starting in SymmetricDS 3.7.30.

We’re excited about dbcompare because it comes out of the gate with some exciting features:

• Compare any 2 databases, even if they are on different platforms. Have a customer table in SQL Server and a copy of that data in MySQL? Yep, dbcompare can compare the data.

• Dbcompare can use your SymmetricDS config for an nice, automatic comparison of 2 of your nodes. This includes using table transforms that map table names and column names. So for example, if you have an H2 “transactions” table sync’ing up to a table called “TX_TRN” on Oracle, dbcompare can handle that comparison across different database platforms and table names.

• Dbcompare is designed to be a standalone tool, as well, that can run on and compare 2 databases, even if those databases have nothing to do with SymmetricDS. Download a copy of SymmetricDS and use the dbcompare command line utility against the databases you want to compare.

• Dbcompare generates a useful report that spells out the state of all tables, whether they are different or not.

• The tool can also optionally general full SQL output that will will bring the target database into sync with the source database. So that’s all the INSERTs, UPDATEs, and DELETEs, with the correct formatting, syntax, etc. for the target database.

• Dbcompare is part of the open source core of SymmetricDS.

Dbcompare could be a nice tool to set up a cron job for extra monitoring of your syncs, or something used for occasional troubleshooting.

Technically, dbcompare is a command line utility in the “bin” directory of the SymmetricDS installation. To run dbcompare, you will need two engines.properties files with database connection information. One properties file is the “source” database, and the other is the “target” of the comparison.

Here’s an example of running dbcompare for two source tables “transaction” and “sale_return_line_item”:

<symds>/bin/dbcompare transaction,sale_return_line_item -s ./path/to/source.properties -t ./path/to/target.properties –output_sql ./%t.diff.sql

Let’s break this down a little:

transaction,sale_return_line_item = a comma separated list of tables to include in the comparison. You can omit this if the source database uses SymmetricDS. If omitted, then dmcompare will run for all tables with SymmetricDS triggers.

-s = The source engines.properties file to use for the comparison. If you’re using SymmetricDS, just grab the engines.properties file from your engines folder, or point this to that file. If you are not using SymmetricDS, you’ll need to set up your DB connection info in a new properties file.

-t = This is the target engines.properties file for the comparison.

–output_sql = This specifies a file to write out SQL statements that would being the target database into sync with the source datasource. Note the use of “%t” in the name here. If you use that %t, you will get one .sql script per table, with the %t replaced with the table name.

Running this command generates a report (to standard out, which you can redirect > or pipe to file) and the SQL files.

Here’s an example of the report and SQL.

The 10 missing transactions server to create this TRN_TX.diff.sql:

Feel free to download dbcompare and try it out, or to contribute to our community site (http://www.symmetricds.org/download) . Let us know what you think!

View the source code here on githib:

https://github.com/JumpMind/symmetric-ds/blob/3.7/symmetric-core/src/main/java/org/jumpmind/symmetric/io/DbCompare.java