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
Snapshot replication is used to provide the initial data set for transactional and merge replication; it can also be used when complete refreshes of data are appropriate. With these three types of replication, SQL Server provides a powerful and flexible system for synchronizing data across your enterprise.
Select and right-click on the Source Database, go to Tasks > Export Data. Import/Export Wizard will be opened and click on Next to proceed. Enter the data source, server name and select the authentication method and the source database. Click on Next.
Mirroring is the copying of data or database to a different location. While replication is the creation of data and database objects to increase the distribution actions.
It's possible with SymmetricDS, an open source solution that can replicate changes between different databases, like SQL-Server and MySQL. Some features are:
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.
There are a few approaches you can use. You named two totally different databases, so I'll give quick tips for each.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With