Converting MSSQL to MySQL

Recently I've inherited a project build in MSSQL that needs to be converting into MySQL. For anyone who has looked into this you'll quickly realise its not a simple conversion. There are a few tools you can try and also a manual way.

## First the manual way:

This involved writing a script that will read from a MSSQL database and write to a MySQL database, all these scripts are in the same folder:

For the database calls I modified my PDO Wrapper, this version can be accessed at https://github.com/dcblogdev/pdo-wrapper/blob/mssql/src/Database.php

I then include db.php into a config.php file. Set up credentials for the MSSQL that I call `$old` and the credentials for the MySQL database that I call $new

<?php
require 'db.php';

$old_username = 'username';
$old_password = 'password';
$old_database = 'databasse'; 
$old_host = 'ip address';
$old_type = 'mssql';

$new_username = 'username';
$new_password = 'password';
$new_database = 'databasse'; 
$new_host = 'ip address';
$new_type = 'mysql';

$old = Database::get($old_username, $old_password, $old_database, $old_host, $old_type);
$new = Database::get($new_username, $new_password, $new_database, $new_host, $new_type);

I then include config.php into any file I want to run the conversion on.

I truncate the new table so I can run the script as needed. Next I select all records from a table using the $old connection, loop over the data and insert into the MySQL database using the $new connection.

<?php
require 'config.php';

$new->truncate('car_parking');

$rows = $old->select("SELECT * from dbo.CarParking");
foreach ($rows as $row) {

    $data = [
        'id'  => $row->CarParkingID,
        'title'   => $row->Title,
    ];

    $new->insert('car_parking', $data);
}

At any time I can run this in a terminal/command prompt by typing php followed by the filename ie `php carparking.php`

This will work but if you are working with a large database it will take a long time to write the queries for all tables.

Tools

MySQL Workbench

You can use MySQL Workbench to run a migration wizard. I found it fairly complicated to setup and slow going and worse some tables that were see as the wrong format were missed entirly.

MS SQL to MySQL converter

The MSSQL-to-MySQL converter works great, it's easy to setup assuming both databases are on the same machine. You can convert directly into a MySQL database or to a SQL file. Either specifing specific tables or all tables. The trail version is limited to 50 records for a complete conversion you will need to buy the product. At $49 is a bargain will save hours of work, I highly recommend this approach.