Replicate Microsoft SQL to other databases

问题:

I'd like to be able to replicate an entire database from Microsoft SQL to another web friendly database such as CouchDB or even mySQL.

I'd need to do replication daily and was just wondering if it's possible and if so, how would I do it with the least amount of coding.

Thanks

回答1:

It's possible with SymmetricDS, an open source solution that can replicate changes between different databases, like SQL-Server and MySQL. Some features are:

• Uses change data capture and continuous synchronization
• Low-bandwidth transfer over web-based protocols
• Asynchronous operation in the background
• Recovers automatically from network downtime

It has an initial load feature to get your databases in sync, then it continuously sends changes as they are captured. There are a lot of configuration options, so you can set how often it syncs.

回答2:

There are a few approaches you can use. You named two totally different databases, so I'll give quick tips for each.

1. SQL Server -> MySQL. This should be really straight forward. At the minimum you could write an application in C# or Java or whatever that simply reads from SQL Server and then writes data to MySQL. You put that application on a schedule and you're done.

2. SQL Server -> Couch. You can write C# - as an example - and deploy it to SQL Server. The code you write gets exposed as a stored procedure. You can write queries in your C# and then serialize objects to JSON and return those as the result of your stored procedure. I've done this and it works well and it's very fast.

Either approach involves knowing what has changed. You can pull data, where you manage the differences between destination and the source. For example, only get records modified past a given date and then update the date so the next time it only gets new records etc.

You can also push data. You can use triggers to run stored procedures that write to a queue (external or internal) and then have something watch the queue and push to Couch/MySQL.

Lots of options.

We use Mule ESB at work to move data around between different systems (SQL Server->Mongo, SQL Server->Couch, MySQL->Mongo) and it works great.