Go Premium for a chance to win a PS4. Enter to Win



Posted on 2009-04-05
Medium Priority
Last Modified: 2012-05-06
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?
Question by:jmarbutt
  • 4
  • 2
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 24072415
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.

LVL 51

Expert Comment

by:Mark Wills
ID: 24072541
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 ?

Author Comment

ID: 24072850
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 Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

LVL 51

Accepted Solution

Mark Wills earned 1600 total points
ID: 24074226
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...
LVL 51

Expert Comment

by:Mark Wills
ID: 24074230
Sorry, out of context - that paragraph about mosso was down the bottom, must have accidentally moved it...

Author Comment

ID: 24074378
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
LVL 51

Expert Comment

by:Mark Wills
ID: 24074392
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...

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question