PHP & MySQL | Tools | Development

David Carr

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.

Domains are often purchased from multiple providers, keeping track of where a domain is and its DNS settings can be tricky. Domain Mapper solves this by listing all your domains in one place. View your DNS settings and receive reminders to renew your domains. Try it today.

Support my work by donating with PayPal.

Subscribe to my newsletter

Subscribe and get my books and product announcements.

© 2009 - 2021 DC Blog. All code MIT license. All rights reserved.