Our Experience Converting From MS SQL to MySQL and PostgreSQL

As a premier Oklahoma Web Hosting firm, The Worx has dealt with a lot of different customers. And nearly every SQL variant is running somewhere in our Oklahoma DataCenter. But for this dicussion, I am going to limit my comments to MySQL and PostgreSQL.

We use both. Our primary database is MySQL as that is the more commonly supported database for most of our PHP projects. We have a large, custom system that we began work on last year. It was originally running as a MS SQL database. The customer needed more power (read CPU count) and wanted redundancy. In order to provide all that he wanted, MS SQL licenses were quoted to us at $80K. So, we offered to convert the entire application and database to use MySQL.

The Database conversion went smoothly, and the application conversion wasn't too bad either. But early in the testing phases, we found that we were in a major world of hurt. The MS SQL system had been worked on by 4 prior consultants and was simply put, a mess! There were reports that were using compound views. You know, where you create a view for something and then later create another view that uses the original in it's definition. Ya well, we found them nested 5 deep in some cases. And that is where the problems came into play for MySQL. The 4.x release would not recognize indexes used in the physical tables when referenced by a second level (or greater) view.

So, response time was dead!... At this point we reviewed the status of MySQL 5.x and it was too far away for our deadlines. So, we turned to PostgreSQL. It went in slick, and provided analogs to everything that we were using in MS SQL. Views, procedures, triggers, etc... The datapump went back to work and we had the whole database moved and checked in under a week. The testing revealed that everything was going to work fine... And the bottom line to the customer saved over $60K at the end of the project.

We have continued to use PostgreSQL as well as MySQL. I have never subscribed to the “all eggs in one basket” theory and I am happy to have two, similar, but competing SQL implementations to use for our Clients. We do still work with DB2, MS SQL, and Oracle.... But only in legacy situations.