Alright, to start, please don't comment on the switch from MySQL to SQL Server 2008 - I tried to resist this decision - I know it's not ideal, but it's the reality of what I am trying to do. I'm competent with MySQL and a newbie to SQL Server 2008, so if you have any constructive comments on any part the process, please let me know.
Here's what I've done:
- Created scripts through PHP to dump MySQL database to several SQL Server 2008 compatible files - by this I mean there is a file for each table. Each file has CREATE TABLE and INSERT statements. It also contains GO statements every 200th insert so that I can import using sqlcmd (if there is a better utility, please point me to it!)
- Copied files over to the SQL Server
- Using sqlcmd command line utility to import - but this is incredibly slow!!!
The batch size of 200 was chosen arbitrarily - if this should be larger, smaller, or if I should only have a single 'GO' statement at the end of the file, let me know.
The total size of the files is around 1GB.
Ideally, I would like to be able to accomplish the import in under an hour so that the webserver that depends on the database won't be down for too long.
Any suggestions on how I can speed this up would be great!