MS Access / PostgreSQL

[Donovan J. Edye]

C,

I have to agree with Matt that this is a pipe dream. However there are a
couple of things that you can do to make the down time as minimal as
possible:

== On SQL Server Side Of Things ==

- All 10 million records are not all "live" I would guess. Part of that data
holding would be static / historical data. Dump this first and load onto
MySQL. (There must be a way to horizontally partition your data so that you
do not have to transfer it all once you migrate)
- Use bcp to get the data out of SQL server. It is much faster than the
fancy GUI to transfer things.
- Don't do your bcp dumps across a network. Do it to local disk if you can
as that will be heaps faster.
- Assuming you have no table conflicts and your MSSQL hardware you may be
able to run multiple bcp sessions against the database
- Obviously choose some "quiet time"  for the migration. Even a DB used 24/7
has some "quiet" time

== On MySQL Side Of Things ==

- Create DB Schema
- Do not add refrential integrity constraints, indexes, triggers (This will
just slow down the import process. Turn them on once the data has been
loaded)
- Run mysqlimport in parallel to the bcp dumps as they come off the SQL
server
- Add referential integrity etc. back

On my PIII 1gig with 512MB memory it takes 2.2seconds to dump 15,046 records
with a record length of 360 bytes. Assuming a worst case scenario (you dump
all the data at once, all your records are this long, you can do no other
speed optimisations) it will take ~25 minutes to dump the 10million records
in the DB. Assuming you are doing things in parallel to MySQL that gives you
about ~35-40 min turnaround to get things going on MySQL. I am sure with
careful planning you could improve on that time dramatically and then
"...having fries with that...." should indeed be possible.

HTH's

-- D

 -----Original Message-----
From: 	linux-admin at lists.samba.org [mailto:linux-admin at lists.samba.org]  On
Behalf Of Matthew Hawkins
Sent:	Monday, 14 October 2002 17:11
To:	linux at samba.org
Subject:	Re: MS Access / PostgreSQL

 << File: ATT03645.dat >> Chris Fletcher (cf at netspeed.com.au) wrote:
> Can anyone recommend a 'safe' way to import the entire contents of an MS
> SQL7 database table into a MYSQL database.  The MS SQL database contains
> about 10 million records and cant really be taken offline so it needs to
be
> done live.  And without impacting performance either.

This sounds like a pipe dream, as a live database presumably is in an
inconsistent state and hence you won't ever get an accurate copy of it
while it is live.  Asking for a full dump to not impact performance is
only going to get a positive answer if you've got undercommitted
hardware and/or undercommitted use of the database happening at the time
;)

If you could, you could start off the mysql server from data stored in a
backup of the live system, then replay the commit logs for the time
since that backup was done to bring it up to date.  Not sure if MSSQL
stores that kind of useful information though.

As for plain transferring, I'd be inclined to dump it as CSV and import
that into mysql, since I wouldn't trust MSSQL to use any form of SQL
compatible with mysql (or anything else, for that matter - including any
other version of MSSQL), and it's much easier to fix CSV than to parse
whatever flavour of SQL and try to fix that.

--
Matt