Database experts - mySQL to MSSQL Replication

[nekrum]

Hi

I need to replicate data between two servers - one is web based running Linux and mySQL and the other is office based running Microsoft SQL server. Has anyone done this or can anyone suggest a way of doing this?

I've done MSSQL to MSSQL over vpn links with no problems but have no experience with mySQL.

Any thoughts or advice would be most helpful. Thanks in advance..

[Plotloss]

Use MS DTS to mine into the mySQL database via ODBC...

There is a DTS wizard in SQL somewhere that pratically does the work for you...

[nekrum]

Plotloss said:

Use MS DTS to mine into the mySQL database via ODBC... There is a DTS wizard in SQL somewhere that pratically does the work for you...

Isn't DTS just for import / export?!.. I do need to update the data at least twice a day.

[Plotloss]

You say you want to replicate?

Place the DTS script being executed by a scheduled task and there you have timed replication...

Edit: Ahh, unless you want to replicate only changed/updated records?

>> Edited by Plotloss on Thursday 24th February 14:37

[nekrum]

Plotloss said:

You say you want to replicate? Place the DTS script being executed by a scheduled task and there you have timed replication... Edit: Ahh, unless you want to replicate only changed/updated records? >> Edited by Plotloss on Thursday 24th February 14:37

ahhh penny dropped! Just looking at DTS at the moment - can you suggest as way to ensure that only changes data is brought down?!.. Thanks Plotloss

[lanciachris]

use DTS??

good luck

The animation showing your data being mangled between 2 cogs as its transferring is spot on.

[Plotloss]

Its good for simple data types and its the easiest way of getting from one platform to another...

[nekrum]

lanciachris said:

use DTS?? good luck The animation showing your data being mangled between 2 cogs as its transferring is spot on.

I assum from that you have had some bad times with DTS?! Any suggestions on how to replicate the mySQL data to MSSQL? Thanks

[lanciachris]

Sorry, I dont actually have any better suggestions, but yup, I have had some great fun with DTS. its the most fickle piece of software in the world ever and when it does go wrong its messages are the least informative things ever 'failed to copy tables'. Great. which ones? why? etc...

[ErnestM]

www.dbtools.com.br/EN/index.php

The learning curve is high but worth it. I use it to replicate MySQL (Windows) and FoxPro (Unix)-(don't ask - yes, yes, yes the guy that created the ap had a mullet and it was the eighties).

I believe the current version supports MySQL and MS SQL native or through ODBC...

ErnestM

[nekrum]

ErnestM said:

www.dbtools.com.br/EN/index.php The learning curve is high but worth it. I use it to replicate MySQL (Windows) and FoxPro (Unix)-(don't ask - yes, yes, yes the guy that created the ap had a mullet and it was the eighties). I believe the current version supports MySQL and MS SQL native or through ODBC... ErnestM

Thanks for that - i've have a look - do you use the Task Builder feature for the replication?...

[TheExcession]

lanciachris said:

its messages are the least informative things ever 'failed to copy tables'. Great. which ones? why? etc...

Reminds me of a load of code we had developed in India. Most of the messages said 'Failed to insert record'

Still, what's the issue when half the planet can't connect their satellite phones, the Ground Station Operators are going ballistic down the phone at you and the Indians just say 'Well, why don't you read through the Oracle exception log', that you then notice hasn't be rotated in the 12 months and is now 1.8Gb in size.

Been there done that, joy.

best
Ex

[ErnestM]

nekrum said:

ErnestM said: <a href="http://www.dbtools.com.br/EN/index.php">www.dbtools.com.br/EN/index.php</a> The learning curve is high but worth it. I use it to replicate MySQL (Windows) and FoxPro (Unix)-(don't ask - yes, yes, yes the guy that created the ap had a mullet and it was the eighties). I believe the current version supports MySQL and MS SQL native or through ODBC... ErnestM Thanks for that - i've have a look - do you use the Task Builder feature for the replication?...

I use an earlier release and I think it was called something else, but essentially, yes.

ErnestM

[longq]

nekrum said:

Hi I need to replicate data between two servers - one is web based running Linux and mySQL and the other is office based running Microsoft SQL server. Has anyone done this or can anyone suggest a way of doing this?

Are you just needing to copy the data (changes only) from a master server database to a slave database where the databases are identical other than the technology in use?

Or are you sharing data from one system with another without re-keying but WITH a need to check data integrity?

Replicate suggests the former - but I thought it worth checking. If the latter I know of some interesting apps that might enable the requirement with no programming necessary.

[nekrum]

Hi all - thanks for your suggestions so far.

Just to clarify I'll try to explain what I need to do - our client is using a web based (Linux mySQL) administration system via a third party for allowing their brokers to remotely process mortgage applications. The client wants all the processing data replicated into their head office and integrated with the MIS system we are currently developing for them. We need to use the data for reporting and generating their financials to enable them to process the incoming PROC and commission fees and pay out to the brokers etc etc. Over time the data will become substantial so we only require new / changes data to be replicated etc. As this is mission critical data the solutions need to be very reliable and automated.........

[lanciachris]

For top reliability id be looking at writing a custom app to do it. Chances of that being within your budget are not so good

[IPAddis]

nekrum said:

Hi all - thanks for your suggestions so far. Just to clarify I'll try to explain what I need to do - our client is using a web based (Linux mySQL) administration system via a third party for allowing their brokers to remotely process mortgage applications. The client wants all the processing data replicated into their head office and integrated with the MIS system we are currently developing for them. We need to use the data for reporting and generating their financials to enable them to process the incoming PROC and commission fees and pay out to the brokers etc etc. Over time the data will become substantial so we only require new / changes data to be replicated etc. As this is mission critical data the solutions need to be very reliable and automated.........

1) Get an ODBC database driver set up in SQL Server DTS for the mySQL database.

2) Write a custom SQL task that selects all the new rows in the mySQL database (based on some kind of updated_on field) and then pumps them into SQL.

3) Repeat step 2 for all the other tables, making sure that all the steps are joining the transaction (no half-completed imports please) and joined using an On_Success workflow.

4) Schedule the DTS package to run frequently (say every 15 minutes).

I have run some VERY big imports using DTS and it a very powerful tool. I can't see a problem with doing a regular import using this method. If I can make DTS access a DataEase database, I'm sure you can get it to access a mySQL one.

You need to bear in mind the order in which dependant tables are imported however to avoid referential integrity (which you ARE using aren't you) problems.

Let me know if you need any help, we have a lot of DTS experts.

Ian A.

[nekrum]

IPAddis said:

nekrum said: Hi all - thanks for your suggestions so far. Just to clarify I'll try to explain what I need to do - our client is using a web based (Linux mySQL) administration system via a third party for allowing their brokers to remotely process mortgage applications. The client wants all the processing data replicated into their head office and integrated with the MIS system we are currently developing for them. We need to use the data for reporting and generating their financials to enable them to process the incoming PROC and commission fees and pay out to the brokers etc etc. Over time the data will become substantial so we only require new / changes data to be replicated etc. As this is mission critical data the solutions need to be very reliable and automated......... 1) Get an ODBC database driver set up in SQL Server DTS for the mySQL database. 2) Write a custom SQL task that selects all the new rows in the mySQL database (based on some kind of updated_on field) and then pumps them into SQL. 3) Repeat step 2 for all the other tables, making sure that all the steps are joining the transaction (no half-completed imports please) and joined using an On_Success workflow. 4) Schedule the DTS package to run frequently (say every 15 minutes). I have run some VERY big imports using DTS and it a very powerful tool. I can't see a problem with doing a regular import using this method. If I can make DTS access a DataEase database, I'm sure you can get it to access a mySQL one. You need to bear in mind the order in which dependant tables are imported however to avoid referential integrity (which you ARE using aren't you) problems. Let me know if you need any help, we have a lot of DTS experts. Ian A.

Thanks IPAddis - I am waiting for the hosting company to set up a mirror test system so that I can have a play.....

[toolsnstuff-couk]

we do exactly this change based replication to and from our mysql and MSSQL servers for our website and backend ordering systems.

We have been through all the tools and wound up writing a VB / SQL based custom app to do it, as we found that only this way could we deal with all the possible execptions in the data, transformations required and mapping of additional fields etc

[richb]

Nekrum YHM. Our ETL product generates the SQL code (native to the specific database type) and handles CDC. If you want real "real-time" we can set this up with a MOM (WebSphere MQ, Sonic messaging etc.) Could worth a look? Rich...