Migrating from MS SQL to MySQL: SQLOLEDB? Migration kit login error?

Question

I've been put in charge of migrating a customer's website of MS SQL/ASP to PHP/MYSQL. I have zero experience with MS SQL.

I'm trying to figure out the best way to get the current data migrated to MySQL so I can begin PHP development.

Some details:

• I downloaded SQL Server Mangement Studio Express. I found the following string in a connection file:

  MM_connCompanyName_STRING = "provider=SQLOLEDB;data source=IP_ADDRESS;database=DATABASENAME;uid=USERNAMEpwd=PASSWORD;"

• Using the IP, username, and password from this string, I could successfully connect using Studio Express.

• I downloaded the MySQL Migration Toolkit and entered the same info, but get the following error:

  Connecting to source database and retrieve schemata names. Initializing JDBC driver ... Driver class MS SQL JDBC Driver Opening connection ... Connection jdbc:jtds:sqlserver://IP_ADDRESS:1433/DATABASENAME;user=USERNAME;password=PASSWORD;";charset=utf-8;domain= The list of schema names could not be retrieved (error: 0). ReverseEngineeringMssql.getSchemata :Login failed for user 'USERNAME'.

• I don't have admin or physical access to the current SQL server. I've tried to run some exports through Studio Express, but it saves them to the file system, which I don't have access to.

• I can reach the current/old webmaster, but because he no longer works for the company, his responses are slow and usually un-helpful. So no help there...

• The former admin sent me an MDF file ... no idea what to do with that.
• I found this note above the connection info on the current server (if it means anything):

'this connection is being used because ODBC was causing weird errors, switching to OLEDB fixed them

My questions:

1. Any idea why this would be failing with the same login credentials that works with Studio Express? I'm assuming it has something to do with the driver, but I don't know what next steps to take.
2. Is there a better/easier/more effective way to migrate this data? (I'm hoping I don't find myself running "SELECT *" statements in Studio Express and copy/pasting data into Excel...please god, no)

Thanks in advance for your help.

Answer 1

I believe you can export data from MSSQL choosing different SQL dialects; I believe there's one that's reasonably MySQL compatible. It doesn't solve all your problems, of course, because while you may get the structure properly, stored procedures and triggers and the like will take a bit more work. But that should get you started.

Answer 2

Eww, this is going to be scary (connection string indicates it was tool generated from Dreamweaver, never a good sign).

Any idea which version of SQL Server? You should be able to SELECT @@VERSION. 10.x is 2008, 9.x is 2005 and 8.x is 2000. If it is 7.x run.

The MDF file is the actual database. If I were you, I would buy a developer edition of SQL 2005 (or 8 if it is 2008 which is unlikely), it will be worth the $99 or so it costs.

This will give you the ability to locally install the DB by ATTACHing the MDF file. Once you have a locally running copy, you can use the SQL Server ETL tools with the MySql ODBC drivers to push the data to MySql in a repeatable manner.