8

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

Eish
  • 1,051
  • 1
  • 9
  • 16
  • 5
    Hi, have you considered Googling `Replicate Microsoft SQL to other databases`? That should be the first step. – Pekka Feb 14 '13 at 12:11
  • 1
    What exactly is a "web friendly" database? –  Feb 14 '13 at 12:20
  • What I meant by Web Friendly would mean something outside of Microsoft's ecosystem and more suited for Web functionality. Yip tried Google, but came up only with replicating to Postgres – Eish Feb 14 '13 at 14:11
  • 5
    @Pekka웃: i am now in an infinite loop because I came here after I googled `Replicate Microsoft SQL to other databases` – Thomas Nov 09 '15 at 17:04
  • @Thomas but there were other results, no? SO's search engine juice is really unfortunate in these cases though. – Pekka Nov 09 '15 at 21:31

2 Answers2

12

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.

Eric Long
  • 926
  • 4
  • 3
  • Eric: I'm currently in the need of a solution like this. Let's say I want to replicate from MSSQL to MySQL, but the MySQL machine is heavily restricted: the MySQL server doesn't have a public IP and can only be accessed via the web frontend. (Similarly, the MSSQL machine is behind a firewall). Can SymmetricDS do an "offline" replication by uploading a log/script manually (even everyday) to the web server and then doing the other part manually? – Rick Garcia May 30 '14 at 22:26
3

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.

ryan1234
  • 7,237
  • 6
  • 25
  • 36