Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Raconteur
Starting Member
2 Posts |
 Posted - 2008-04-09 : 18:47:16
|
| Hi gang,This is a cross-post from the MSDN Managed Newsgroups, which I never seem to get a response on... hoping someone over here can help me out!I am having a problem and could also use some help understanding something...We have a DB in MySQL (5.0.51) that I am transferring to SQL Server 2K5 using DTS. I am going through the MySQL ODBC v3.51 driver, and bringing data over via query.My query is just SELECT * FROM <table>, and it take approximately 1 minute to run on the MySQL server.First, the problem... the source table is 5 columns (2-ints, 1-float, 2-varchar(255)s) and 22M rows. Looks to me like about 600MB of data.When I go through the DTS wizard it runs for about an hour and then I get a "MySQL client out of memory" error. After a bit of research, I found a switch in the ODBC driver to use forward-only cursors, which seems to have alleviated that problem, however... and this is where I need help in understanding the process, when I get to the page in the wizard where I enter the query, I click next, and the machine gets busy for an hour. Looking at the processes on the MySQL server, I can see the entire result set is streaming across the wire to my SQL Server box. Then the page comes back, and I specify the table to import into, click Edit Mappings... and wait for another 30+ minutes for the mapping screen to come up. Once I enter the mappings, I can complete the wizard, but the Performing Operation page takes about an hour again to complete.The duration of all of this seems ridiculous to me... especially in moving such a (relatively) trivial amount of data (MySQL server has 100s of gigs of hard drive space available, plus 8GB or RAM, and my SQL Server box has 3GBs of RAM and plenty of drive space... plus we are running gigabit ethernet).It appears that the query page is retrieving all of the rows just to take me to the page where I map the columns. That, assumedly, is the case because on that page I can Preview the data. Is there any way to supress this?Is there anything anyone can suggest to speed this whole ordeal up?I am wondering (haven't tried this yet), if I save the DTS Package and run that if it will execute more rapidly because much of the work is already done by that point.Any info would be greatly appreciated!Cheers,Chris |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-09 : 22:48:26
|
| Tried create linked server to MySql and move data via it? |
 |
|
|
Raconteur
Starting Member
2 Posts |
Posted - 2008-04-10 : 01:53:57
|
| No, I haven't... that's an interesting idea though. Never done that before. It is something simple that you can give me a quick rundown on how to do it? Or point me to a help page?Right now I am running a DTS package that is doing a good job, except that it executes the SQL statement at least twice and returns the entire result set over the wire.IS there any way to turn that off? When I try to map columns from one DB to another, instead of bringing back only the table structure, the DTS (or SSIS I guess it is now called) is bringing back all 24M rows. I map the columns, save the package, then execute the package, and it goes off and pulls the 24M rows AGAIN to do the transfer.Thanks for any insights!Chris |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-04-10 : 03:07:09
|
can you export data from mysql to flat text files, like csv? if so you could export all tables, and then import them to sql server using BCP or similar.
elsasoft.org |
|
|
|
|
|
|
|
|
Problems moving LARGE DB from MySQL to SQL Server