Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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:

  1. 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!)
  2. Copied files over to the SQL Server
  3. 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!

share|improve this question
    
For future visitors: We endorse all types of professional software and hardware here. There is no bashing of one migrating from X to Y. –  pauska Jan 11 '12 at 22:42
add comment

2 Answers

up vote 2 down vote accepted

In Management Studio, the GUI for SQL Server, have you tried right-clicking on your new database (or create one), and selected Import/Export - Import? This will use a part of SQL Server called SSIS, which is a competent ETL (extract, transform, load) tool.

You'll need a ODBC driver installed for mysql, but importing the data should be relatively straightforward - you'll be able to transform the data, if you need to, along the way.

Alternatively, have you tried the Microsoft Migration tool for importing data from mysql to SQL Server? All the vendors provide these tools to help switch from one DB to another!

http://www.microsoft.com/download/en/details.aspx?id=1495

share|improve this answer
    
Didn't even know this existed...thanks, I'll give it a shot and report back! –  Tristen Jan 10 '12 at 23:43
    
I'm trying this out now - there are some errors (I use spatial columns, and need to install SQL Server's equivalent) but the data is importing at a much quicker rate that my file method. Thanks for the feedback! –  Tristen Jan 11 '12 at 1:33
add comment

Loading the data into SQL Server would probably go more quickly using either: BULK INSERT http://msdn.microsoft.com/en-us/library/ms188365.aspx or BCP http://msdn.microsoft.com/en-us/library/ms162802.aspx

The format files can be a pain depending on how closely your raw files match the table structure. Sometimes it can be easier to BULK INSERT or BCP into a staging table, and then move the data into the right places after uploading it.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.