none
PHP mysql server or MS SQL server??

    Question

  • Hi everyone!

    Between MYSQL server and MS SQL server. which one better?. If I design website by PHP

    Sunday, October 09, 2011 10:15 AM

Answers

  • Hi

    Great question, there are pros and cons for both!

    MYSQL is closely aligned with PHP, and it has a large library of SQL functions to work with, performs excellently, and supports all of the SQL you need.  I enjoy using MYSQL very much and will always use it for some web sites, as it has some convenient syntax advantages over SQL Server (e.g. INSERT ... ON DUPLICATE KEY UPDATE, etc.).  The worst part is upgrading MYSQL to a newer version - it is always a tense operation, and often you need to run upgrade statements on your existing databases.

    SQL Server is in a different class - it is faster, more robust and extremely powerful.  You can extend its capabilities, and the T-SQL language is the best defined SQL language I have used - both flexible and strong.

    So for power, speed and ease of maintenance, my choice is SQL Server; for sheer value for money (it's free, but will cost you maintenance time), enjoyment and convenience, I choose MYSQL.

     

     


    Rob
    • Marked as answer by Ho Duy Loi Monday, October 10, 2011 1:03 PM
    Monday, October 10, 2011 10:59 AM
  • No, not really... 

    I feel that you are looking for a short answer, but it's really up to you and depends on a lot of things that I don't know. 

    The short answer is: SQL Server.

    (This is based on no factual information, from my own point of view, and because we are on a SQL Server forum :-).  If I was making my own web site at home, or in a small company, I would choose MySQL.)

    Here's a longer answer about your options:

    1. Use PDO - only the connection string changes and you can use the same code in your PHP application for both databases.  You can switch databases and try them both out.  Remember that you should *only* use ANSI standard SQL syntax with PDO.  It is designed for products that run with the end-user's choice of database.  If you use a server's special features, you will not be able to switch drivers without adjusting your SQL statements.
    2. Use the PHP MySQLi classes for MySQL.
    3. Use Microsoft's procedural sqlsrv_ functions for SQL Server.

    If you asked me to decide between SQL Server and Oracle there would be a clear winner, which would cause a heated response from Oracle fans.  But SQL Server against MySQL is a more interesting comparison because I see a wider contrast in maintenance and price, with functionality and performance (although I think better in SQL Server) being less important factors.

    From a developer's perspective I recommend SQL Server, because the T-SQL language syntax is better than the others, see my list further down.

    I work for a big company that pays the IT bills, and if you do too, I recommend SQL Server, because it pays off in the end.  The only cons for SQL Server are these - and these apply to putting your web site into a production environment:
    • It runs *best* on its own server - recently it's becoming a CPU hog, but performs impeccably; naturally other processes on the same computer will suffer a bit.
    • Price - it's great value compared to Oracle, but still expensive because you need to buy a server for it to run on (as mentioned), with all the licence costs, and then the SQL Server costs on top of that, and of course, DR/backup overheads.
    • Overkill! SQL Server is a best-in-class product and offers many features that a web site will never use.  MySQL on the other hand is just about the right size - of course it depends (see list at the bottom).

     

    Since this is a SQL Server forum, here are a few of the language features that I think make T-SQL the best SQL language:

    • Logical statements - if/else/while, in any SQL statement.
    • Variables - use variables anywhere (except for object names, but see the next point...).  You can even store a temporary table in a variable.
    • EXEC statement that can execute a text statement you have just prepared, allowing you to build a statement using a variable table name etc.
    • Build and use temporary tables as variables, or in the 'temp' database, within any statement.
    • TRY/CATCH blocks.
    • Cursors - enumerate results within your SQL statement in a fetch/while loop.
    • Functions that can return a whole table or single value.
    • ROWNUMBER() OVER ORDER BY clauses for web site paging (MYSQL uses its LIMIT clause with equal value, but it's a newer feature on SQL Server, since 2005 I think)
    • PIVOT tables - might be useful to restructure lists into tables on web sites.
    • ...and more, whatever you want to do there is usually a way...

    Let me add that I *could* create a similar great list for MySQL features, and I couldn't write an exhaustive list for either, because there's too much I don't know.

    Here are some rhetorical questions, just things to consider overall:

    How big will your site be - how many concurrent users?
    How much data will be in your database?
    What equipment and software will host in production?
    Will your database be local (on the same computer as the web site) or remote (on another computer)?
    Are you managing your database, or will a separate team do it?
    Are you controlling the security features of your web site and the database objects and users?
    How much is your budget for production, and your yearly maintenance budget?
    What is your personal taste?



    Rob
    • Edited by Robert Johnson Tuesday, October 11, 2011 12:34 PM
    • Marked as answer by Ho Duy Loi Tuesday, October 11, 2011 3:55 PM
    Tuesday, October 11, 2011 12:33 PM
  • Hi

    I would recommend starting off by installing the SQL Server Express 2008 R2 database, and use the Microsoft Driver for PHP.  It has a class-based PDO driver and a procedural driver.  It's easier to install and get up and running than MySQL.

    For security regarding SQL Server and PHP, check out this blog by Brian Swan, from Microsoft, it has some great examples of how to prevent hackers getting access to your site:

    http://blogs.msdn.com/b/brian_swan/archive/2010/03/04/what_2700_s-the-right-way-to-avoid-sql-injection-in-php-scripts_3f00_.aspx


    Rob
    • Marked as answer by Ho Duy Loi Thursday, October 13, 2011 9:06 AM
    Tuesday, October 11, 2011 6:07 PM

All replies

  • Hi

    Great question, there are pros and cons for both!

    MYSQL is closely aligned with PHP, and it has a large library of SQL functions to work with, performs excellently, and supports all of the SQL you need.  I enjoy using MYSQL very much and will always use it for some web sites, as it has some convenient syntax advantages over SQL Server (e.g. INSERT ... ON DUPLICATE KEY UPDATE, etc.).  The worst part is upgrading MYSQL to a newer version - it is always a tense operation, and often you need to run upgrade statements on your existing databases.

    SQL Server is in a different class - it is faster, more robust and extremely powerful.  You can extend its capabilities, and the T-SQL language is the best defined SQL language I have used - both flexible and strong.

    So for power, speed and ease of maintenance, my choice is SQL Server; for sheer value for money (it's free, but will cost you maintenance time), enjoyment and convenience, I choose MYSQL.

     

     


    Rob
    • Marked as answer by Ho Duy Loi Monday, October 10, 2011 1:03 PM
    Monday, October 10, 2011 10:59 AM
  • Thank you for your idea Rob. So I should use Mysql for my website?
    Monday, October 10, 2011 4:53 PM
  • No, not really... 

    I feel that you are looking for a short answer, but it's really up to you and depends on a lot of things that I don't know. 

    The short answer is: SQL Server.

    (This is based on no factual information, from my own point of view, and because we are on a SQL Server forum :-).  If I was making my own web site at home, or in a small company, I would choose MySQL.)

    Here's a longer answer about your options:

    1. Use PDO - only the connection string changes and you can use the same code in your PHP application for both databases.  You can switch databases and try them both out.  Remember that you should *only* use ANSI standard SQL syntax with PDO.  It is designed for products that run with the end-user's choice of database.  If you use a server's special features, you will not be able to switch drivers without adjusting your SQL statements.
    2. Use the PHP MySQLi classes for MySQL.
    3. Use Microsoft's procedural sqlsrv_ functions for SQL Server.

    If you asked me to decide between SQL Server and Oracle there would be a clear winner, which would cause a heated response from Oracle fans.  But SQL Server against MySQL is a more interesting comparison because I see a wider contrast in maintenance and price, with functionality and performance (although I think better in SQL Server) being less important factors.

    From a developer's perspective I recommend SQL Server, because the T-SQL language syntax is better than the others, see my list further down.

    I work for a big company that pays the IT bills, and if you do too, I recommend SQL Server, because it pays off in the end.  The only cons for SQL Server are these - and these apply to putting your web site into a production environment:
    • It runs *best* on its own server - recently it's becoming a CPU hog, but performs impeccably; naturally other processes on the same computer will suffer a bit.
    • Price - it's great value compared to Oracle, but still expensive because you need to buy a server for it to run on (as mentioned), with all the licence costs, and then the SQL Server costs on top of that, and of course, DR/backup overheads.
    • Overkill! SQL Server is a best-in-class product and offers many features that a web site will never use.  MySQL on the other hand is just about the right size - of course it depends (see list at the bottom).

     

    Since this is a SQL Server forum, here are a few of the language features that I think make T-SQL the best SQL language:

    • Logical statements - if/else/while, in any SQL statement.
    • Variables - use variables anywhere (except for object names, but see the next point...).  You can even store a temporary table in a variable.
    • EXEC statement that can execute a text statement you have just prepared, allowing you to build a statement using a variable table name etc.
    • Build and use temporary tables as variables, or in the 'temp' database, within any statement.
    • TRY/CATCH blocks.
    • Cursors - enumerate results within your SQL statement in a fetch/while loop.
    • Functions that can return a whole table or single value.
    • ROWNUMBER() OVER ORDER BY clauses for web site paging (MYSQL uses its LIMIT clause with equal value, but it's a newer feature on SQL Server, since 2005 I think)
    • PIVOT tables - might be useful to restructure lists into tables on web sites.
    • ...and more, whatever you want to do there is usually a way...

    Let me add that I *could* create a similar great list for MySQL features, and I couldn't write an exhaustive list for either, because there's too much I don't know.

    Here are some rhetorical questions, just things to consider overall:

    How big will your site be - how many concurrent users?
    How much data will be in your database?
    What equipment and software will host in production?
    Will your database be local (on the same computer as the web site) or remote (on another computer)?
    Are you managing your database, or will a separate team do it?
    Are you controlling the security features of your web site and the database objects and users?
    How much is your budget for production, and your yearly maintenance budget?
    What is your personal taste?



    Rob
    • Edited by Robert Johnson Tuesday, October 11, 2011 12:34 PM
    • Marked as answer by Ho Duy Loi Tuesday, October 11, 2011 3:55 PM
    Tuesday, October 11, 2011 12:33 PM
  • Hi Rob,

    thank you for your help, thank you very much.

    I just go out from my university, I'm study about Programing, So I want to design a website social for my friends, me or anyone can share pictures or something about them. I don't know how scurity for my website, if I have large database, so I  asked you to decide between SQL Server and Mysql.

    I'm new and no experience, special scurity website. I scare hacker :D

    Tuesday, October 11, 2011 4:12 PM
  • Hi

    I would recommend starting off by installing the SQL Server Express 2008 R2 database, and use the Microsoft Driver for PHP.  It has a class-based PDO driver and a procedural driver.  It's easier to install and get up and running than MySQL.

    For security regarding SQL Server and PHP, check out this blog by Brian Swan, from Microsoft, it has some great examples of how to prevent hackers getting access to your site:

    http://blogs.msdn.com/b/brian_swan/archive/2010/03/04/what_2700_s-the-right-way-to-avoid-sql-injection-in-php-scripts_3f00_.aspx


    Rob
    • Marked as answer by Ho Duy Loi Thursday, October 13, 2011 9:06 AM
    Tuesday, October 11, 2011 6:07 PM
  • That's some great pointers, Robert. Thanks for finding those! :)

     

    Thanks,

    Jonathan


    This posting is provided 'AS IS' with no warranties, and confers no rights.
    Wednesday, October 12, 2011 12:13 AM
  • I got it. thank you very much Rob :)
    Thursday, October 13, 2011 9:06 AM