Sql-server – Schedule replication of SQL Server views to MySQL remote database

I've had to do this a couple of times, with varying complexity. My typical approach is to install the MySQL ODBC connector on the SQL Server host, create an ODBC DSN to the MySQL database, then set it up as a linked server in SQL Server.

Once that's out of the way, you can write T-SQL procedures/scripts to synchronize and replicate the necessary data.

A few thoughts. It sounds to me like you may be able to get away with a couple options if replication is what you end up with. First a word of caution - Replication isn't something that should just be configured in production and enabled/used unless you have some experience with it. This becomes truer with more important workloads and busier systems. Replication can cause headaches if not properly done. I'd invest in mentoring/training/consulting to help you along the path from folks who have been burned by mistakes already.

That said. It looks to me like your requirements don't seem to have any 2-way changes that need to be synced up or merged. It looks like you have a bunch of things that need to go one directional. Orders never come in locally, only on the website. Order status never gets generated on the website, only consumed, etc.

If that is the case - this sounds like Transaction Replication to me. Merge is more for when you need to have multiple versions that can make changes and receive changes at the same time. The tired old Microsoft example still works - a bunch of sales folk all with a local app that does lead management and a big central server. They can get their leads when in the home office and get data, then they work disconnected and sync up their changes. With Merge you have to deal with conflicts (if the same data is modified in two places, who wins?) Transactional is more what is used when you have changes going in one direction (but you can have it go bidirectional here too even).

And you can set replication up to have various tables included or not included in the publication as articles.

A couple links to give you a bit more info:

Transactional Replication Overview

Merge Replication Overview

Now if you have data that can be updated on each and needs to be "merged" then merge may be a better answer. When I work with replication - I prefer to stay as close to Transactional as I can, though. It is easier to administer and there is no conflict resolution worries if you don't need it.

Depending on your volume - if it is low enough - and your latency - if some lag time is acceptable - you might also consider something more programatic or ETL like. Perhaps staging data and sending it over in batches. Sending messages across as data changes in one side, etc.