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.