• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 669
  • Last Modified:


I have a project that I am considering moving from a MS-SQL2008 db to a MySQL db. It is about 30 gigs in size in about 200 tables and 500 stored procedures. I also use uniqueidentifiers heavily in the database.

So here are actually a couple questions.

1. What are the pros and cons of doing this?
2. What is the equivalent of uniqueidentifier in mssql in mysql?
3. Do you think there would be a performance gain or loss?
4. Are there any good conversion programs out there to automate this?
  • 4
  • 2
2 Solutions
Guy Hengel [angelIII / a3]Billing EngineerCommented:
1. pro: less license costs (no sql server license, no windows licence (if you move to a free unix server))
   con: huge migration costs

2. only varchar/text field can hold this data. there is no dedicated data type for this in mysql

3. depends if you choose MyISAM or InnoDB as data storage engine:
   MyISAM will be faster, but won't support transactions or foreign keys
   InnoDB will be slower, but does support transactions and foreign keys

4. not that I know of. I would not go that way for a advanced application anyway.

Mark WillsTopic AdvisorCommented:
Well, if you already have SQL server, then the license costs are well a bit irrelevant.

SQL Express seems to be gaining in popularity in hosted space, maybe not quite to the MySQL support, but also cannot support that size database.

There is no uniqueidentifier so as angelIII said, would need to store the original guid as a string, but if used in referential integrity, or primary keys, then you will have to change your approach entirely, you do have auto_increment (more like the identity),

     name VARCHAR(60),

There are also some pretty cool new features in MS SQL 2008 like the spatial data types, hierarchies, added new DATE constructs and a couple of other bits and bobs.

Angeliii has given you the speed bits, but generally I think on large databases you can tune SQL Server more readily for speed.

There are some conversion programs, haven't really used them because invariably there are a few migration type functions that need to happen depending on database design. Could look at something like : http://www.swissql.com/products/datamigration/data-migration.html?ad-main1  and there are others out there.

In short, if you are already SQL2008, then why are you wanting / considering the move ?
jmarbuttAuthor Commented:
Basically it boils down to the most cost effective solution. We are doing some changes on some of our applications and trying to evaluate the options. We currently have dedicated box and host some of our smaller applications on www.mosso.com. Mosso provides SQL on that side but it is $50 per gig. So it is a little pricey but if we switched to MySQL it would save between $1000-1500 a month.

But also really evaluating MySQL to see if it is worth looking into. Mosso also provides a clustered sql servers whether it is MySQL or MSSQL that should scale better then our existing dedicated boxes which are over kill 90% of the time.

We would probably end up writing our own conversion program to meet our standards and really test the integrity of the data.

I am definitely torn because my background is primarily MS SQL but I don't use a whole lot of the special features in SQL 2008.

So here is kind of where we stand:

1. Extremely Affordable
2. More Scalable than our dedicated box currently

3. Major Conversion
4. More fimiliar with MSSQL

1. Know it very well
2. Currently in it now.

1. Less scalable because we would have to stay on our dedicated box.
2. Much more expensive.

I think I still have some testing to do to see if we can meet the performance specs we need to make it work. If the performance is there I feel it would be worth the time because we would save a ton every month on our hosting.
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Mark WillsTopic AdvisorCommented:
MySQL is not that vastly different from SQL Server - bit different when it comes down to tuning large databases, and the 'magic' stuff that makes it sing, but essentially follow the same guidelines with indexes and restrict some of the features the will probably not take a huge amount of time to pick up. The single biggest problem would be migrating and chaning those procedures / processes away from guid's .

But If you consider a full cost per head at around $500 to $1500 per day, then the development work can most likely buy a year's worth of hosting...

Have not checked mosso, but surely they offer that type of service as well. $50 per gig is over the top and surprised a little bit that they will do a 30gig database under that scenario - most of them cap the size under a dollar-per-space arrangement.

If price is the motivator, and agree that you do seem to be paying premium price, why not look at people offering dedicated server support - bit different in so much as you need to use them as a hardware hoster, where as a lot of them are disk space renters...

For example, have a look at http://serverintellect.com/solutions/database/

I went on line and asked a few questions on your behalf, and got back the following information (spending your current $1500 per month), it does include all the operating systems, backups and licenses and is a bit of a beast for SQL (you could easily configure a lesser machine) :

Hope that helps...
Mark WillsTopic AdvisorCommented:
Sorry, out of context - that paragraph about mosso was down the bottom, must have accidentally moved it...
jmarbuttAuthor Commented:
Yeah I think we will probably stay with our dedicated solution for the time being due to the transition costs and learning curve. The quote on there is pretty good but we actually have a little bit better deal and work with a great company already. So most of this was to just get an idea of what options are out there and if it would be worth it. As far as development cost goes also we balance it a little different because we are an extremely small shop (2 people primarily) with a stable product so we are kind of in a refactoring time of our application and looking at ways we can restructure to do things more cost effectively if we can and improve performance and some features at the same time.

Thanks for all the leg work you did, you both have been helpful
Mark WillsTopic AdvisorCommented:
I think there is an old saying that might come into play, or at least has some kind of distant recognition in these cases : "If it ain't broke, why fix it"

Thanks for the points, and happy to help...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now