Recently I produced a website for hosting on a Linux box running with PHP5 and MySQL5. Well it now needs to move over to a Windows 2003 server with MS SQL as the DB and IIS as opposed to Apache.

  1. Using MS SQL Server Management create a new database. (right click on databases in the Object Explorer)

  2. Use the following SQL Server SQL to create a link to your MySQL DB:

    EXEC master.dbo.sp_addlinkedserver @server = N'MYSQL', @srvproduct=N'MySQL', @provider=N'MSDASQL', @provstr=N'DRIVER={MySQL ODBC 3.51 Driver}; SERVER=MySQLServerIP; DATABASE=Db_NAME; USER=MySQLUserName; PASSWORD=MySQLPassword; OPTION=3'

    Execute the above

  3. Use the following PHP script to get a list of tables to import:

    $conn = mysql_connect('host','user','pass');
mysql_select_db('database',$conn);
$SQL = "SHOW TABLES;";
$result = mysql_query($SQL, $conn);
while($row = mysql_fetch_array($result))
    print "SELECT * INTO SQLServerDBName.dbo.{$row[0]}<br />
           FROM openquery(MySQL, 'SELECT * FROM `{$row[0]}`');<br />
";

  4. Copy and paste the output from the above script into your SQL Server query window and execute it

  5. All your data should now have been transferred across along with the table schemas

Don’t forget to change all the references above to your correct database server settings.

Please note I have had some trouble with SQL Server not accepting 0000-00-00 00:00:00 in datetime fields set to not null in MySQL – set your datetime fields to accept null before exporting.

Also primary keys and auto_increment column attributes are not brought across either. So you will need to go into Server Management and manually re-add your auto_increment (Identity in SQL Server speak) and the primary key. It can also be done programmatically like this:

BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_admin_accounts
    (
    account_id int NOT NULL IDENTITY (1, 1),
    username varchar(20) NOT NULL,
    passhash char(40) NOT NULL
    )  ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_admin_accounts ON
GO
IF EXISTS(SELECT * FROM dbo.admin_accounts)
     EXEC('INSERT INTO dbo.Tmp_admin_accounts (account_id, username, passhash)
        SELECT account_id, username, passhash FROM dbo.admin_accounts WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_admin_accounts OFF
GO
DROP TABLE dbo.admin_accounts
GO
EXECUTE sp_rename N'dbo.Tmp_admin_accounts', N'admin_accounts', 'OBJECT'
GO
ALTER TABLE dbo.admin_accounts ADD CONSTRAINT
    PK_admin_accounts PRIMARY KEY CLUSTERED
    (
    account_id
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT

Because SQL Server doesn’t allow you to alter a pre-existing column to be an Identity (auto_increment) column you have to:

  1. Create a temporary table with an Identity column
  2. Allow arbitrary numbers to be inserted into the Identity column (includes previously inserted IDs apparently so you could end up with duplicates!)
  3. Move the data across to the temporary table
  4. Disallow inserting into the Identity column
  5. Drop the original table
  6. Rename the temporary table to the same name as the original
  7. Alter the Identity column to add the primary key

Wow! What a pain.