Solved

MSSQL to MySQL

Posted on 2009-04-05
7
616 Views
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?
0
Comment
Question by:jmarbutt
  • 4
  • 2
7 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 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.


0
 
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),

CREATE TABLE MyTable (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name VARCHAR(60),
PRIMARY KEY (id) );

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 ?
0
 
LVL 2

Author Comment

by:jmarbutt
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:

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

Con:
3. Major Conversion
4. More fimiliar with MSSQL

MsSQL:
Pros
1. Know it very well
2. Currently in it now.

Cons
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.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 51

Accepted Solution

by:
Mark Wills earned 400 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...
server-iad-liveperson-net-ch.pdf
ServerIntellect-Quote15194.pdf
0
 
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...
0
 
LVL 2

Author Comment

by:jmarbutt
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
0
 
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...
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now