0

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!

1
  • 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, 2012 at 22:42

2 Answers 2

2

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

2
  • Didn't even know this existed...thanks, I'll give it a shot and report back!
    – Tristen
    Jan 10, 2012 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, 2012 at 1:33
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .