Mysql to Sqlserver replication

Question

Mysql to Sqlserver replication

sram24_mca

SSCrazy

Points: 2633
More actions
February 22, 2008 at 4:20 am

#179324

Hi guys,
We need to replicate few MYSQL databases to SQL 2005. We have MYSQL server in postion & we have a server class machine with MS Windows 2003 server OS & SQL server 2005 enterprise edition installed & configured in it. I first wanna try a small database replication from MYSQL to SQL 2005 . I am ready to provide u all with other informations that u wanted.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 1

Sram, your best bet is to use some sort of load process (SSIS, openquery(), MySQLdump, etc. ). In conjuntion with the MySQL 3.51 conncetor http://dev.mysql.com/downloads/connector/odbc/3.51.html[/] you can use openquery() to load SQL server tables directly (in a proc or SSIS package). If MySQL is running in a Windows environment you should also be able to utilize the MySQLDump command to create raw dump files. Whether or not SQL Server can read these is another story but worth a shot.

I've been in a mixed environment for over 2 years so if there's anything else you need feel free to ask.

Doug

Simon E Doubt SSCarpal Tunnel Points: 4191 More actions · Answer 2

Doug:

I am very interested in some of the processes you described in your post - most notably loading rows from SQL Server 2005 into MySQL tables, via SSIS, or a stored proc.

You mentioned that you have been working in a mixed environment for two years. I have just begun working in a mixed environement, and would love to know where to get started in terms of creating an SSIS project (or some other solution) for loading rows from SQL Server 2005 tables into MySQL.

Any tips/comments would be greatly appreciated.

Thanks,

Simon

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 3

Simon, my work was primarily migrating from MySQL to SQL Server and it was done through the use of a Linked Server and the OPENQUERY command.

However, it is not too hard to reverse this process...

1) Create your linked server to MySQL see http://www.sqlservercentral.com/Forums/Topic340912-146-1.aspx

2) Insert into mySQL using the OPENQUERY command:

ex: INSERT into OPENQUERY (LinkedServerName, 'Select col1, col2, ... from MySql table' )

VALUES ( col1, col2, ...)

As with any insert, the number and types of columns in the OPENQUERY command must match the number and type of columns/data in the VALUES clause.

Doug

Simon E Doubt SSCarpal Tunnel Points: 4191 More actions · Answer 4

Doug:

Thank you very much for the help.

I wrote the following bit of code, to test:

INSERT OPENQUERY (MYSQL_OE_Dreamhost, 'SELECT WebAccountID, PromotionAbr, PeriodID, OfferID, Feedback, DateAdded FROM oe_dbo.WebFeedback')

VALUES (8,'PS2','PS2803',38,'This is super!','April 24 2008')

And was returned the following error message:

Msg 7390, Level 16, State 2, Line 1

The requested operation could not be performed because OLE DB provider "MSDASQL" for linked server "MYSQL_OE_Dreamhost" does not support the required transaction interface.

Do you remember having to deal with this error in your process?

Thanks,

Simon

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 5

Simon, no such errors. Start with something simple. Just select from the MySQL linked server in SSMS:

select * from OPENQUERY( MySQLLInkedServer 'select * from MySQLTable') to validate that the linked server is created properly.

Doug

Simon E Doubt SSCarpal Tunnel Points: 4191 More actions · Answer 6

Doug:

I flipped a switch in the MySQL ODBC Connector (see pic attached) to allow transactions, and things seem to be working great now.

Thanks so much for helping me get started with the INSERT OPENQUERY syntax. It's exactly what I needed to get started... now I can really start to explore the possibilities.:)

Cheers,

Simon

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 7

Glad I could help Simon.

Now your next goal should be persuading the "higher-ups" to migrate completly from MySQL to SQL Server.

Doug