Manual migration from MS SQL Server to MySQL

Mark Brunelli, News Editor writes "It's no small feat to manually migrate an enterprise from Microsoft SQL Server to MySQL, but it can be done. Here, Mike Sheffey, CEO of Versora, gives step-by-step instructions for two manual tasks: moving the data structure and the data itself."

Comments

Uh, for simple databases maybe

[Anonymous Coward]

If you got lots of stored procedures (Transact SQL or whatever MS SQL uses these days) then you're in for a world of hurt.

Not to mention that MySQL doesn't even enforce referential integrity would would seem much less functional than MS SQL.

I would maybe move to Postgress if you are looking for something on the cheap. I don't understand why MySQL has such a following, it's not that great as for as databases go. Barely a step above Berkley DB.

Re:Uh, for simple databases maybe

[tha_mink]

FROM THE ARTICLE:
A manual migration from Microsoft SQL Server to a MySQL database running on Linux is an extremely tedious undertaking. Each step can require numerous man hours to complete. In addition, the possibility of error is high. Tread carefully.



Why are we doing this again? I am all for hating on MS but yo...Microsoft SQL to MySQL in an Enterprise??? Are you that bored? PostGreSQL would be hard enough...but at least you could keep some of the functionality. I though the purpose fully featured database servers was to keep the logic in the DB.

Re:Uh, for simple databases maybe

[jpkunst]

Not to mention that MySQL doesn't even enforce referential integrity would would seem much less functional than MS SQL.

Will people ever stop saying this? InnoDB tables, which provide referential integrity, have been available for MySQL since version 3.23.34a (2001), are included in binary distributions by default as of MySQL 4.0, and are the default table type on Windows starting with version 4.1.5. See InnoDB Overview.

JP

Re:Uh, for simple databases maybe

[innosent]

Agreed, actually we're considering a move from MSSQL to pgSQL at work, because we have data that would benefit from bitmap indexes (and we prefer to split our DB into several DBs, which makes Oracle/MSSQL expensive very quickly). MySQL is good for small databases, but it's certainly not enterprise ready. Stored procedures, triggers (or at least stored procedures), advanced indexes, and proven storage techniques are REQUIRED. Load up a billion records from one of your big tables, see what happens with MySQL. Now figure out how you can track who changed record #325782910, when they changed it, and what program changed it. On tables of that size, MSSQL blows, but if you're going to do all that work, at least move to something better!

Practical?

[youknowmewell]

Is it practical to switch from SQL Server to MySQL? MySQL doesn't even have views! I can't imagine any scenario where one would want to take the big hit in features, except if you absolutely needed more speed, and didn't have a need for the features. Still, it would make more sense to switch to PostgreSQL since it has as many, if not more, features than SQL Server.

Re:Practical?

[xoboots]

I wouldn't doubt that a lot of databases sitting on MS SQL can just as comfortably run on MySQL. Of course MS SQL is far more capable and has more features but that doesn't mean that they are needed for all databases and applications that a company may deploy.

Does application logic belong on the database server? In a lot of cases, absolutely! In those cases MySQL is obviously not going to be a candidate. Then again, a lot of applications only use the database server as a storage backend; in those cases, porting to MySQL is not particularly unreasonable.

Re:Practical?

[the eric conspiracy]

Is it practical to switch from SQL Server to MySQL?

I guess it depends on how minimal your needs are. But one thing I have noticed is that MySQL maps better to MS-SQL than Postgres. Postgres maps better to Oracle.

Re:Practical?

[youknowmewell]

True, and MySQL can be alot cheaper as well. Still, the flexability PostgreSQL gives for future feature needs (without the need to migrate AGAIN) seems to appeal to me more than MySQL's features (or lack thereof).

Re:Practical?

[the eric conspiracy]

How so? Is it because of dialectic or feature reasons?

For example PG and Oracle use sequences, MySQL and MS use autoincrement fields.

Re:Practical?

[xoboots]

I agree that PostgreSQL is far more feature rich than than MySQL. It is also a better development environment. Still, I don't think that implies it is the best choice for every application. It is very hard to argue against the applicability of MySQL -- what it does do, it does very well. If you are migrating an application that doesn't require the additional features of a more robust database, it is a worthy candidate. Particularly since if you do plan to need advanced features you actually have much less reason to port to another environment to begin with.

Perhaps a more important point is to consider the OS environment one is likely to find MS SQL users running -- windows. While PostgreSQL has just very recently began fully supporting windows, MySQL has been there for a long time. In many ways, its a much softer sell for that reason.

I haven't been arguing for MySQL over PostgreSQL because I see them as different tools suitable for different jobs. For the same reason, there are many cases where it would be more appropriate to stay with a MS SQL (or Oracle) solution.

What I like about the article is that it is another signpost. The more that any of the leading FOSS rdms's get into the corporate space, the better it is for all FOSS rdms's to prove their case and improve their chance of acceptance and adoption. Its really hard to break the stranglehold of Oracle and MS SQL in the corporate environment so even baby steps are important at this time.

Best Regards.

Never had anyone ask about it.

[WebHostingGuy]

Being a web hosting company and offering MySQL, MS SQL and PostgreSQL, we have NEVER had anyone ask about converting from MS SQL to MySQL. While the article is comprehensive the people and applications using the MS SQL database are not the same ones using MySQL. When someone setups a MS SQL database they are doing so because they are looking for certain features and/or requirements from software they are installing. Under these circumstances the two are not interchangeable.

I suppose if you have a roll-your-own db interface and wanted to drop the license fee for MS SQL you would do this. But realistically the ones who are using MS SQL have already sunk big $$$ into the license (or are renting for a fraction of the cost which would make switching for the money irrelevant). I doubt they would chuck MS SQL just for the security issues associated with Microsoft products.

Re:Never had anyone ask about it.

[BrookHarty]

And those MS SQL licenses dont include the OS, or other MS related products. What about if you are required to upgrade to Windows 2003 server for all your servers, it could be so expensive that the one time headache of converting is worth it.

I think any articles that helps you from being locked into one product is worth it. Having dealt with multiple products and for the telcom industry, they love taking a standard product and adding some piece of software that seems to lock you in. Radius, DHCP, DNS, all standard products, but put a gui and database, provide some backend API for provisioning, now your locked in.

Give me unix and text configs anydays over a gui anyday.

Re:Never had anyone ask about it.

[FullMetalAlchemist]

That depends on the subscription, at my current job and at my previous we get and got all the stuff for free.
Though in all fairness we where just 3000 people at my last job and now we are just 50 :)=

Still being a Microsoft Partner and IBM Premium Partner is a real advantage.

Re:One more thing to move, not mentioned in TFA...

[Anonymous Coward]

I usually don't need DB provided consistency when I can do ... without the performance penalty ... MySQL can ... PostgreSQL cannot.

Obviously you don't process any important data.

Similar migrations

[MobyDisk]

This is the kind of news that PHBs need to hear. These types of migrations are possible! I plan to write some similar articles on migrating some other proprietary produces to F/OSS software, including:

Microsoft Office --> VI
ASP.NET --> sh scripts
Peachtree Accounting --> GnuCash
PeopleSoft --> awk
Rational Rose --> GIMP

Re:Similar migrations

[innosent]

Peachtree Accounting --> GnuCash

Wait, I thought you were being sarcastic! Or were you just testing us for "which one of these is not like the others?" Seriously, have you tried Peachtree? Had to delete lock files 10 times a day? Had it crash and destroy all your data? Had it create an incomplete backup? I mean, GnuCash isn't even the same product family. GnuCash actually works. Peachtree is more closely related to the Vaporware family, since it makes all sorts of claims about how well it works, then the features disappear the first time you run it, taking half your data with it.

Great Revenue source for MS

[Lord Kano]

If they released a product to automate the migration from their SQL server to MySQL or PostgreSQL.

Seriously, they could squeeze a few more bucks out of the people who are no longer going to be using one of their products.

LK