SitePoint Sponsor

User Tag List

Results 1 to 24 of 24
  1. #1
    SitePoint Addict Caterwomtious's Avatar
    Join Date
    Dec 2001
    Posts
    222
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL vs MS SQL Server

    Based on Kevin Yank's tutorial I've begun using MySQL and PHP to redevelop my own personal websites, and having found VBScript ASP more difficult and less powerful at work over a similar time period, I'm completely sold on the PHP/MySQL route.

    We're kitted out almost completely with MS products at work, but I recently mentioned MySQL and it's now being considered as an alternative to MS SQL Server, based on cost.

    • Does anyone have any comparative experience of both products?
    • Does anyone know of any comparative reviews already out there?
    • Is the Windows version of MySQL as reliable/powerful as the Linux one (on either NT4 or 2000)?
    • How good is the ODBC support? We're committed to classic ASP at the moment, so wouldn't have the advantages of the PHP integration.
    • I read that transaction support was missing from MySQL, but was on the to-do list - is this finished, imminent, or a long way off? Are there any other important omissions?


    I hope I don't sound too negative about MySQL! IMO it would be brilliant if this worked out, but equally I don't want us to rush into a decision we might regret.

    (Sorry if this is a little off-topic, but the dedicated MySQL forum seems to have disappeared and I thought I'd find the experts in here instead...)

  2. #2
    SitePoint Addict Caterwomtious's Avatar
    Join Date
    Dec 2001
    Posts
    222
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL vs MS SQL Server

    Based on Kevin Yank's tutorial I've begun using MySQL and PHP to redevelop my own personal websites, and having found VBScript ASP more difficult and less powerful at work over a similar time period, I'm completely sold on the PHP/MySQL route.

    We're kitted out almost completely with MS products at work, but I recently mentioned MySQL and it's now being considered as an alternative to MS SQL Server, based on cost.
    • Does anyone have any comparative experience of both products?
    • Does anyone know of any comparative reviews already out there?
    • Is the Windows version of MySQL as reliable/powerful as the Linux one (on either NT4 or 2000)?
    • How good is the ODBC support? We're committed to classic ASP at the moment, so wouldn't have the advantages of the PHP integration.
    • I read that transaction support was missing from MySQL, but was on the to-do list - is this finished, imminent, or a long way off? Are there any other important omissions?

    I hope I don't sound too negative about MySQL! IMO it would be brilliant if this worked out, but equally I don't want us to rush into a decision we might regret.

  3. #3
    SitePoint Enthusiast MaRkY's Avatar
    Join Date
    Dec 2001
    Location
    Spain
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  4. #4
    SitePoint Guru
    Join Date
    Jan 2001
    Location
    Alkmaar, Netherlands
    Posts
    710
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    http://www.eweek.com/article/0,3658,...a=23115,00.asp
    and check mysql.com for coming features like transaction support
    Good luck

  5. #5
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,121
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    We went through the exact same thing and actually found that MySQL didn't stand up. It isn't an Enterprise level piece of Software. When you say you are "kitted with MS products", I'm assuming that means you have much more then SQL Server installed... That being the case, how easy will it be to switch everything over?

    The reality that most companies have found is that it is in fact cheaper to go with a completely MS solution-base then not... I mean free isn't always free, if you know what I mean.
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  6. #6
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are there any other important omissions?
    Depends on what you regard as "important". MySQL also has disadvantages in terms of:

    - row-level locking
    - foreign key support
    - subquery support
    - replication (has it, but not very robust in my experience)
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark

  7. #7
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ummm....Cross-posting is kind of bad form....
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark

  8. #8
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Don't forget about MSDE, the "free" version of SQL server.

  9. #9
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,121
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    What, the one that allows 2 concurrent connections, has a database limit of 200MB and has a quirky driver?

    Yeah, I love that one
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  10. #10
    SitePoint Evangelist Umair.ms's Avatar
    Join Date
    Sep 2000
    Location
    Lahore, Pakistan
    Posts
    567
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I cant believe even a word of that comparison at eweek. Yes MSDE, is the one that comes with ASP.NET Framework (also is included in Visual Studio .NET). But as Jeremy pointed out - it is just for testing purposes.

  11. #11
    SitePoint Addict
    Join Date
    Jun 2001
    Location
    Newcastle, England
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The eWeek study is too limited to show anything conclusive, sadly. SQL Server performed like a dog, but that's probably almost entirely due to the fact the application was written in JSP and the JDBC driver they used was, well, cr*p (and still in beta format). As always, the "right" database depends on the project in hand; MySQL might be perfect for one piece of work and hopeless for the next (same is true of every database of course).

    As to the original question, a major drawback if you've already got a lot of code is that there are significant differences in the "dialects" of SQL used by MySQL and MS SQL. This means you'll probably spend a lot of time re-writing your Transact-SQL statements to get them to work in MySQL, and sometimes even your application logic (MySQL's omissions sometimes mean you need several calls to the database to do things you could do in one call in MS SQL, for example.)

    I'd also count lack of stored procedures as a fairly important omission (if that hasn't been implemented in a recent version? I haven't seen the last release or two of MySQL so I could be out of date).

    Sorry that this isn't conclusive, but as always it's a case of "it depends." If you have the time, test both and see which performs better. If not, I'm afraid it's a case of reading up on both and making a judgement call.

  12. #12
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Jeremy W.
    What, the one that allows 2 concurrent connections, has a database limit of 200MB and has a quirky driver?

    Yeah, I love that one

    haha. actually i haven't had any problems with any "quirkiness". and its database limit is 2GB, not 200 MB. and it doesn't have a set limit on connections but Microsoft claims it is "optimized" for up to five concurrent connections. depending on what you're using it for, MSDE can be a very good choice.

  13. #13
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by CrazyCrane
    The eWeek study is too limited to show anything conclusive, sadly. SQL Server performed like a dog, but that's probably almost entirely due to the fact the application was written in JSP and the JDBC driver they used was, well, cr*p (and still in beta format). As always, the "right" database depends on the project in hand; MySQL might be perfect for one piece of work and hopeless for the next (same is true of every database of course).
    I agree. If you download their code and look at it, they didn't bother to optimize too heavily. Plus, they used bi-directional cursors in Sybase / MS SQL which have historically never been stellar performers.

    Plus I don't think they used stored procedures or custom functions which would help a lot one would think.

    Something tells me they tested the limits of the JDBC driver more than anything.

    To bring the thread back on track, I think if you're already coding in MS SQL Server I see no reason to switch. I used to work at a company who made a couple hundred million dollars a year which was still using MS SQL Server 7 which is really cheap compared to 2000. Plus, if you don't have Enterprise-ish loads you can always look at MS SQL Server 2000/7 Standard which has much cheaper prices.

    Or to keep with the same (mostly) T-SQL environment you can take a look at Sybase ASE. Their prices for workgroup are not terribly expensive either. Sybase also offers SQL Anywhere which is like 'ASE-lite' and has different features. I have no experience with SQL Anywhere so you'll have to take the documentation at face-value.

  14. #14
    SitePoint Addict Caterwomtious's Avatar
    Join Date
    Dec 2001
    Posts
    222
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    MaRkY:

    Many thanks for the links. I'll definitely follow those up tomorrow.


    hillsy:

    Sorry - I didn't mean to, honest!!

    I posted here first but the thread wasn't seeing much action.

    I saw the other forum, and posted there with the intention of deleting this thread, but when I came back to do so, MaRkY had answered. Ack! Netiquette dilemma!

    So I just left it...

  15. #15
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,121
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Ooooh, Sybase, boo hiss

    At the end of the day, is your company broken? Is there really any reason to switch? I mean if the only cost is licensing I can just about guarantee swapping over will be a lot more expensive:

    -Research
    -Driver support/updates aren't as seamless
    -Rewriting of certain aspects of code
    -New limitations to get used to (not that one is better then the other, but every RDBMS has inherent weaknesses)
    -Install and setup costs

    At the end of the day, the cost for SME's is cheaper to stay with a system that works (even if it's FoxPro *shudder*) then with out that MIGHT work better... maybe.

    I have nothing against MySQL or Sybase (or even Interbase), but at the end of the day what is the reasoning for switching... If it's no more then cost, you'll save money "staying the course"
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  16. #16
    SitePoint Enthusiast MaRkY's Avatar
    Join Date
    Dec 2001
    Location
    Spain
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    np, hope the links help you I think you can get a good result about databases

  17. #17
    SitePoint Evangelist
    Join Date
    Oct 2001
    Posts
    592
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Get serious!

    I can't believe you're serious about using MySQL when you're used to MS SQL!

    Since you use MS SQL, you know that it has:
    - Relational tables, meaning CASCADES and RESTRICTS
    - Triggers
    - Transactions
    - ...
    In short: everything a DBMS should have! It's also what MySQL supports badly, or not at all.

    I can see why you don't want to use an expensive, insecure tool anymore, especially as its from that big nasty company in Redmond, but why not consider PostgreSQL then? It's just as free as MySQL, but it is a much more mature DBMS.

    Really, if you want to do any serious database development (and most web sites generally don't fall into this category), don't even think about MySQL. But then again: that's just my opinion...

    Vincent

  18. #18
    web daemon jorasmi's Avatar
    Join Date
    Nov 2001
    Location
    Philippines
    Posts
    296
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've been using MySQL & MS SQL for more than a year now. Lets forget about prizes, its ovbious you'll choose MySQL. Which of the two is better? It depends on your purpose or priority, if your building a database driven website i would suggest MySQL since it doesn't have limitation on how many clients can connect at the same time & speed. On the other hand if your building an application for your company which require more advance queries and tables you better use MS SQL.

    You should also consider using Postgress(if i got the spelling right) which is more powerful than MS SQL and flexible as open source. Postgress is the most advance open source database.

    I've read an article it says there that "NASA shifted to MySQL from Oracle"....

  19. #19
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by jorasmi
    I've read an article it says there that "NASA shifted to MySQL from Oracle"....
    Nasa shifted to MySQL from Oracle for non-critical applications like serving photographs from the Hubble Space Telescope on its websites. It still uses more the more sophisticated and more stable software for critical operations.

    Yahoo uses MySQL for its non-critical data as well. This is mainly its Stock feeds because the data is constantly updated and not critical. For their services it doesn't matter if they miss a few minutes here and there. They still use Oracle and other applications to power their index and other critical services (i.e. the ones they make money on).

    Alta Vista uses a combination of MS SQL Server and Oracle to power their search engine services. When I went to an Oracle DBA class with some of their engineers, they laughed when it was mentioned that MySQL might be able to power their site.

    I could go on and on. Basically if your information is not critical, you don't need failover and sophisticated replication, data farming or data mining features you should use MySQL. In my Opinion the only database that MySQL is a step up from is MS-Access not any of the enterprise level offerings out there.

    And yes, I have worked with MySQL (this site), MS SQL Server, Sybase and Oracle.
    Wayne Luke
    ------------


  20. #20
    SitePoint Evangelist
    Join Date
    Oct 2001
    Posts
    592
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wayne: I couldn't agree more!

    (Except for the part: "if it's not critical ... you should use MySQL." Of course I shouldn't! :-))

    MySQL has only one big advantage: it's very fast. But this is for a reason: it doesn't do internal checking to maintain database integrity. So maybe a query is executed faster, but then the programmer has to execute additional queries just to make sure the database remains okay. Other DBMS are a bit slower (on UPDATEs, INSERTs and DELETEs), but execute these additional queries by themselves, not only saving you the trouble, but also ENSURING that the databases integrity will not be broken.

    In my opinion: the one big advantage of MySQL is also it's problem. I don't even have to think about incorporating in my software: I don't do it, unless my boss tells me to! There are other DBMS's out there that are a lot better, and just as expensive.

    Vincent
    Last edited by voostind; Mar 5, 2002 at 02:20.

  21. #21
    SitePoint Addict Caterwomtious's Avatar
    Join Date
    Dec 2001
    Posts
    222
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It seems I gave the wrong impression about our current setup - the possibility of using MySQL to which I was referring would have been confined to our intranet, which is still pre-launch and in terms of databases is using only Access and a specialised text-intensive flatfile database, not MS SQL. It was to have been a decision about which to go with, not whether to switch.

    However, a little more research has shown that other parts of our organisation already have expertise and an investment in MS SQL which we can take advantage of, so that is the route we will take.

    Many thanks to everyone for the advice and opinions. I still rate myself very much as a beginner with relational databases, and I've learned a lot from this thread.

  22. #22
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just throwing my opinion in here. I think that MySQL is the perfect choice for developers who are running it to power personal/small or medium websites. As soon as you start to need more advanced features, such as those mentioned by voostind you have outgrown it. If you want something more advanced but free, look at PostgreSQL or certain versions of Sybase

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  23. #23
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,121
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Good decision. One database==great integration
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  24. #24
    SitePoint Addict Caterwomtious's Avatar
    Join Date
    Dec 2001
    Posts
    222
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good decision. One database==great integration
    Not one database exactly, no. Just one less extra one.
    Last edited by Caterwomtious; Mar 5, 2002 at 19:09.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •