Transfer selected information from a SQL Server business server to a MySQL web server?

Question

I have a SQL Server dedicated server used for business information on service users etc, and a VPN with MySQL for the main website operation. I wish to transfer some selected tables from SQL Server to MySQL in order to provide statistics on the website, and reduce load on the business server. Primary keys etc are not really important for the transfer - just the main data.

Is it as straight forward as finding a PHP script and setting up a cronjob on the web server to run every hour or so? Really I am unsure about the most practical/safest method to do this?

Answer 1

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.

Answer 2

Finding a pre-existing solution might be challenging, but writing a solution in PHP would not be difficult at all. It's just a matter of establishing connections to both database servers, running SELECT's on your msSQL server to pull the data you're interested in, and then looping over those results and running INSERT queries on your mySQL connection.

There are fancier ways it could be architected, but if you don't need the data to be mirrored immediately, then a cron job once an hour would be fine as long as you're not dealing in huge amounts of data.

Answer 3

Another solution is to use SSIS (assuming you have a full license of SQLServer, it's already included) and install the MySQL ADO.NET connector here. You can use the import/export wizard from SQLServer Management Studio to create the package, selection only those tables you need. Then save the resulting package and schedule it using SQL Agent.

Answer 4

How about this:

a) Export the table structure from SQL Server and create the tables in the MySQL database - this is a one off

b) Setup a scheduled job to export the statistics data from SQL server into an SQL file

c) Setup a cron job to apply the SQL file to the MySQL database created - you may run another script prior to this if you need to empty the tables first