Migrating from one RDBMS to another


Here is some of my thoughts on migrating MySQL to Sql Server. It came out of an email discussion. I’d love to hear your thoughts on migrating to a different database platform, not just MySQL to Sql Server.

I actually thought about writing a white paper or even a course on migrating from MySQL to Sql Server, but never got the time to do it. Sometimes a project doing similar things can serve as a launchpad for this endeavour, but that never came along, at least not yet. I am very interested in database interoperability field though. I’ve done MySQL and Oracle admin in the past and have published some MySQL and Oracle stuff in blogs. I have much better technical skills on Sql Server than any other RDBMS platforms, primarily because I’ve worked on it longer.

Here are some of my thoughts. I think most of it applies equally on migration from Oracle, DB2, Sybase, Postgresql, etc., to Sql Server, or the other way around. It might be slightly easier to migrate from Sybase to Sql Server, considering their common root.

1. It is not easy to migrate existing app, unless the app is a simple one. Even for that, there are enough quirks that can throw people off and cause enough frustration to derail the whole projects. I’ve seen that happening twice, having engaged in moving 2 apps from Sql Server to Oracle;

2. Therefore, the best way to migrating to a new database RDBMS, in my opinion, is to start from a new initiative, probably not big initially. When you start things from a clean slate, you don’t have the historical garbage to worry about. Furthermore, you will give the team enough time to learn the new platform, and prepare the team for future migration, if you choose to do so;

3. Having open-minded team members is crucial to a migration project’s success. Too often people have emotional attachments to the platform they are familiar with, possibly out of job security concerns and lack of general curiosity toward new things.

I generally adopt a platform agnostic attitude, and don’t get religious and too carried away on the platform I work on. Having said that, I think these are points that marketing people can spin for persuasion purposes:

1. MySQL has too many storage platforms: MyISAM, InnoDB, MaxDb, and the newly introduced Maria. This can be viewed as a plus, as it provides choice. The downside of it is that it causes confusion for end users;

2. MySQL’s support for relational model is fairly recent. For example, for a long time, MySQL didn’t support Stored Procedures, Views, Triggers, Foreign Keys, etc. One could argue that MySQL is not mature in this area since it is new for them, but I think it is difficult to find evidence to substantiate that claim. Also, running the risk of offending some people, I think the importance of relational model got overblown a bit;

3. Sql Server offers the CLR integration. This can be a great selling point;

4. Sql Server offers tight integration with Visual Studio, Windows network, and all other things Microsoft. This is a huge advantage.

5. Too many people find *nix environment intimidating. Although MySQL works on Windows, but the perception in the marketplace is MySQL works better on *nix.

As far as migrating MySQL to Sql Server in a hosting web environment, my honest opinion is Sql Server will be fighting an uphill battle, because MySQL excels in this arena, especially for small and medium-sized, or departmental organizations, with the proliferation of such LAMP app like blogs, wikies, discussion boards, etc. I believe Microsoft’s weapon of choice in this arena should be SharePoint. Given Microsoft’s clout, it is certainly a battle worth fighting.


5 responses to “Migrating from one RDBMS to another”

  1. Migrations (regardless where from/to) are painful, and often don’t deliver the desired outcome. This is because the original design will inevitably have been optimized for the RDBMS it was written on, and this extends into the OS, hardware and even network infrastructure, as well as the way the application interacts with the RDBMS.
    And you also already noted quirks, specific (mis)features in one RDBMS that may have been used (in fact it’s highly likely any app will use RDBMS-specifics).

    In summary, RDBMS migration in fact involves analyzing and possibly refactoring (redesign) of the entire application and hardware infrastructure. It can be worthwhile in certain cases, but it’s costly.

    And migrating because there’s more expertise with another RDBMS… sorry, bad reason IMHO. Training is cheaper than the abovementioned painful path. Quicker too.

    In reality, there is no such thing as a database agnostic application. Some assumptions and choices are always made, even if just on the hardware infrastructure level. And generic layers like ODBC just give a lot of overhead (20-30%!) and more quirks, plus… are you really going to stay away from any RDBMS specifics? It’d cost in speed and thus in scalability.
    It just doesn’t make sense.

  2. Hi!

    nice post. Now without the intention of starting an argument, I’d like to offer a few arguments that “marketing people” from the other camp “can spin for their purposes”:

    1) “MySQL has too many storage platforms…The downside of it is that it causes confusion for end users”

    Maybe, but this is hardly a reason for migration once you have built your app. There is no need to be confused – there is lots of high quality books, training and online resources.

    2) “MySQL’s support for relational model is fairly recent. For example, for a long time, MySQL didn’t support Stored Procedures, Views, Triggers, Foreign Keys, etc”

    Not so much a counter argument as it is a correction. I would agree that views are a pretty ‘relational’ kind of beast, but despite the fact that Stored routines and triggers are defined in the SQL standard, they are hardly relational types of things.

    3) “Sql Server offers the CLR integration. This can be a great selling point;”

    And exactly how many products and production level projects do you know that use this? How many of those are running CLR inside the db and are achieving results with that technique that could not equally well have been done using an older, more proven technology?

    4) “Sql Server offers tight integration with Visual Studio, Windows network, and all other things Microsoft. This is a huge advantage.”

    Heh, it’s in the eye of the beholder. I would argue that this tight integration is a disadvantage rather than an advantage as it tends to lock you down to using a single toolset. For MySQL development, you can choose from a broad range of tools, including eclipse, netbeans and whatnot. There is even a plugin for MS VS.

    5) “Too many people find *nix environment intimidating. Although MySQL works on Windows, but the perception in the marketplace is MySQL works better on *nix.”

    I can see this point. Maybe Sun/MySQL needs to work harder to change this perception. That said, *nix is becoming less and less intimidating as better distros are coming out. That said, choose MS SQL and you know you are locked down to windows. Not sure what the server platform is doing, but put mildly, there is at least some controversy whether MS is going the right way with their OS.

    kind regards,

    Roland Bouman

  3. Excellent insights, gentlemen. Thanks a lot for your input.

    Arjen: I couldn’t agree with you more, well said. By the way, I checked out your web site, and wonder if you’ve played with EnterpriseDb. I’ve heard good words about it.

    Roland: for point 3 regarding CLR integration, I agree that it is more of a niche feature, and I haven’t seen it personally in production. But I think it can be useful in certain situations, especially for CPU intensive calculations like financial industry

    Yes, the industry is polluted with too much marketing misinformation.

    I will attend the MySQL conference in April to hopefully improve my currently feeble MySQL knowledge. It should be fun, and hope to meet people like you both there.

  4. 5. Too many people find *nix environment intimidating. Although MySQL works on Windows, but the perception in the marketplace is MySQL works better on *nix.

    Yup, it does. There are some things you can do on the *nix side you cannot do properly on the Windows side. The pro here is that though there is a difference in the platform on different OSes, it still works reasonably well in Windows. There is flexibility for the organization because it will run on multiple platforms, but SQL Server will not. 🙂 I love SQL Server, but this is certainly one advantage MySQL has over SQL Server (just like Oracle, Postgre, Sybase, and DB2 also share this advantage).

  5. Good to hear from you Brian. Hope all is well with you.

    I wonder if you’ve sat through the MySQL cert exam. I may do that someday. I’ve also neglected my article and blog writing for SqlServerCentral, because it is hard to find time, with self-employment, family, and such.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.