Clever Geek Handbook

How to get Transactional MySQL data into SQL Server database

I am working on a project that has a MySQL transaction database supporting a web application. The company uses SQL Server for back office and reporting. What's the best way to update SQL Server with data from MySQL? Now we are dumping MySQL data and doing a full restore. This may not be feasible due to the increased size of the database.

I would prefer a solution that only copies recently inserted and updated rows. I also need the SQL Server database to be static after the updates are applied. Basically, it should be changed once a day. I can upgrade SQL Server from my local copy of MySQL (i.e. not produce). Is there a way to apply MySQL replication to the slave server at specific intervals? The ideal solution is to run a one-time daily MySQL update that synchronizes the database over time.

0


source to share


5 answers


Can you find a way to take a snapshot of the mySQL database and then make a copy? This will make a snapshot logical copy of the database, which will be frozen in time.

http://aspiringsysadmin.com/blog/2007/08/13/consistent-mysql-backups-using-zfs-snapshots/

The ZFS file system can do this, but you did not specify your hardware / OS.



Also, perhaps you can limit the data you pull - no matter the time, so that your pull will receive data older than 1 hour if your pull takes 45 minutes. Or make things a little more secure - how about just pulling the day before?

I believe there is a new module in SSIS 2008 called the "maintain" table that does the general task of updating / inserting records and, if necessary, deleting.

+1


source


Have a look at the DTS tool, Microsoft ETL. It's pretty nice. Make a mapping, assign it as a cron job and Bob is your uncle.



0


source


Regardless of how you import into SqlServer from MySQL clones, I don't think you need to worry about limiting MySQL replication at certain points in time.

MySQL replication only requires one thread on the master and basically just offloads the transaction log to the slave. If you can, place the master and slave MySQL servers on the private LAN segment so that replication traffic does not affect web traffic.

0


source


if you have SQL Server Standard or higher, SQL Server will take care of all your needs.

  • use ssis to capture data
  • use an agent to schedule your scheduled tasks.

btw - I am doing the same as you. SQL Server is great - it was easily set up (I'm a noob for SSIS) and it worked in the first snapshot.

0


source


It looks like you need to set up a script to start and stop replication on the slave database. If you can do it with a script, then you can set up a workflow in SSIS like:

  • Stop replication to MySQL slave database
  • If replication is stopped, then take a snapshot of the subordinate databases
  • If snapshot is taken, then a = Start replication to production MySQL database b = Import MySQL database slave copy into SQL Server

NB: 3a and 3b can work in parallel.

I think the best choice in such a scenario would be to use SSIS to enable and disable MySQL database replication for the slave, and also to take a snapshot of the slave database. Then you can manage all of this with the SQL Server Agent engine.

Hope it helps

0


source






More articles:


All Articles