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.
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.
Subscribe to my newsletter for the latest updates on my books and digital products.
Find posts, tutorials, and resources quickly.
Subscribe to my newsletter for the latest updates on my books and digital products.
