RE: Transfer LARGE DB from MySQL to SQL Server 2K5 problems
- From: Kevin <Kevin@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 21 Apr 2008 14:11:36 -0700
Chris,
I think issue is related with MySQL. I am not a MySQL guy. It's better to
find answer on MySQL side.
I also need to get data from MySQL to SQL 2005, and sometime also received
"MySQL client out of memory" error if the table is big in MySQL. The good
thing is that our table is small in MySQL so far. But we may face the issue
you have in the future.
Please post any update when you have.
Thanks
Kevin
"Christopher Burns" wrote:
Can someone give me an idea of what I am doing wrong here at the "managed".
newsgroups?
I have written, what I consider to be, 30+ lucid, detailed, information-rich
questions or requests for help, and have NEVER... N E V E R, received a
single reply. I have to ask Microsoft what the benefit of saying these
newsgroups are managed (and making that a selling point for MSDN
subscriptions) is when the response rate is that low.
It has gotten to be that the absolute LAST place I seek help is the MSDN
forums, which, needless to say, drives me away from the MS web sites, and is
beginning to drive me away from MS in general.
I would love to get some info, insight, and answer, even a friendly "Hello"
to my question here... if someone might be so bold as to break the silence.
Cheers,
Chris
"Christopher Burns" wrote:
Hi gang,
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
- References:
- Transfer LARGE DB from MySQL to SQL Server 2K5 problems
- From: Christopher Burns
- RE: Transfer LARGE DB from MySQL to SQL Server 2K5 problems
- From: Christopher Burns
- Transfer LARGE DB from MySQL to SQL Server 2K5 problems
- Prev by Date: RE: Run a ssis package on SQL Server 2005 Error
- Next by Date: RE: Importing Fixed Width Files with Multiple Occurrences
- Previous by thread: RE: Transfer LARGE DB from MySQL to SQL Server 2K5 problems
- Next by thread: SQL 2000 DTS's
- Index(es):
Relevant Pages
|
