Clever Geek Handbook

Replicating Microsoft SQL to other databases

I would like to be able to replicate an entire database from Microsoft SQL to another web database like CouchDB or even mySQL.

I will need to do replication on a daily basis, and I'm just wondering if this is possible, and if so, how should I do it with the least amount of coding.

thank

+3


source to share


2 answers


Perhaps with SymmetricDS , an open source solution that can replicate changes between different databases such as SQL-Server and MySQL. Some features:

  • Using change data recording and continuous synchronization
  • Low bandwidth transmission over web protocols
  • Asynchronous operation in the background
  • Automatically recovers from network downtime


It has a bootstrap feature to keep your databases in sync, and then it continually pushes changes as it grabs them. There are many configuration options, so you can set how often it syncs.

+8


source


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

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

  • SQL Server -> Couch. You can write C # - as an example - and deploy it to SQL Server. The code you write appears as a stored procedure. You can write queries in your C # and then serialize the objects to JSON and return them as a result of your stored procedure. I did this and it works well and it is very fast.

Any approach involves knowing what has changed. You can retrieve data where you manage the differences between recipient and origin. For example, only get records changed for a certain date, and then update the date so that the next time it only gets new records, etc.



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

Lots of options.

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

+2


source






More articles:


All Articles