Databases for easy comparison

Question

We have an application which has metadata information stored in database (some tables with relations between). The metadata can be edited through web app or directly manipulating values in SQL Server database.

The problem: metadata changes and needs to be merged between different environments (test, staging, production, etc.). There are tools (e.g. RedGate) that help but it is still quite a lot of work to compare databases if autogenerated ID's are being used (as it is now in our DB, and yes, one way is to use natural keys to make comparison easier).

However, our metadata may be stored not necessarily in SQL database - it could be stored as documents in NOSQL databases (MongoDB, CouchDB, RavenDB) or even simple XML databases (maybe Berkeley DB XML?). Storing as XML file seems would work (as it easier to compare and merge files rather than databases) but may not be a good option as there needs to some concurrency mechanisms, some degree of transaction support. We do not need replication to other servers, there is no need for high availability, etc.

The requirements to store data:

• some kind of ACID
• Should run on Windows
• Easy comparison (bi-directional sync)
• (optional) GUI to see what is in database
• (optional) export to file (JSON, XML)

What are the options?

Answer 1

Why conflate the storage with the representation you are performing the diff on?

I'd keep everything in SQL, but when it came time to compare, select all the important data (not the ids) into a XML format, and use an XML differencing tool (or a csv format, and use a plain text comparer).

Answer 2

I have never used it but CouchDB has built-in support for birectional syncing between db's.