Converting MSSQL to MySQL

David Carr

Development Tools PHP & MySQL

Table of Contents

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.

Fathom Analytics $10 discount on your first invoice using this link

David Carr - Laravel Developer

Hi, I’m David Carr

A Senior Developer at Vivedia
I love to use the TALL stack (Tailwind CSS, Alpine.js, Laravel, and Laravel Livewire)

I enjoy writing tutorials and working on Open Source packages.

I also write books. I'm writing a new book Laravel Testing Cookbook, This book focuses on testing coving both PestPHP and PHPUnit.

Sponsor me on GitHub

Subscribe to my newsletter

Subscribe and get my books and product announcements.

Laravel Testing Cookbook

Help support the blog so that I can continue creating new content!

Fathom Analytics $10 discount on your first invoice using this link

Subscribe to my newsletter

Subscribe and get my books and product announcements.

© 2006 - 2023 DC Blog. All code MIT license. All rights reserved.