How to sync two databases for disconnected systems from different companies

Is there a standard messaging protocol(s) / API(s) available to keep databases in syn开发者_开发问答c. Or alternatively API(s) for creating and parsing messages.

Our company is working with another company to provide two different software packages to two different kinds of users. The data sits in two separate databases but parts of it have to remain in sync.

Their system is pretty much a black box to us. And vice versa.

So what would be required would be to track updates, and turn these into messages and send them to a web service, map these back to the destination database fields, and commit them.

The database schemas do not match.

I am aware that we are going to have to roll most of this ourself, but some ideas around messaging or techniques would be good.

One solution : SQL Server Integration Service. It appears from SQL Server 2005. This is exactly what you need. It was called DTS in SQL Server 2000 for Data Transformation Service. This was created to import/export/transform data from one point to an other. This is really easy to use from SQL Server 2005 (DTS is quite horrible).

So basically, you will have to write packages to import data from their database, transform, filter, etc. it exactly how you need it to insert it into your database. And vice versa.

Regarding the black box fact, you should generate the database relational design to make it easier.

EDIT Just in case of you need to install it, I remember bugs from the SQL Server 2005 installer not installing SSIS at all. I had to satisfy all warnings in the installer system requirements step to obtain it.

You have two problems:

• track the changes that have to be synced
• apply the changes to the peer

There is a solution that combines a solution to both issues and I'm sure you are aware of it: replication. Merge Replication would allow both sites to update the data and would also provide merge conflict resolution. But replication only works when the table schema is similar and puts a big constraint on development as schema changes have to be carefully coordinated between the sites. In practice, when the sites are operated by independent companies, is quite difficult to maintain for a long term.

If you want to roll your own the change tracking part has built in support in SQL Server:

• Change Tracking
• Change Data Capture

Both can be used for a sync solution as a mean to detect what changed.

Applying the changes can be resolved by a web service, but there are also built-in solutions in SQL Server that allow for far higher scalability and throughput: Service Broker. Relying on a message defined API for sync allows the two sites to evolve at their own pace and change the schema almost at will, as long as the communication API (the message protocol)remains unchanged.

The answers provided give me some good ideas, but I think we are going to end up doing something a bit different.

We are using MSMQ, and defining a standard messaging system which we will roll ourselves.

As to how we will know what things have changed I am not sure at the moment.