MySQL vs. SQL Server

[chainsawal]

Quick question for those of you experienced with this... 

I am coding a site that is using MySQL and PHP. Works well, but I dislike interfacing with MySQL... I am experienced with SQL Server as I use it at work, but am a rookie with PHP.  I know there are some aftermarket software programs you can buy for minimal cost to make it look like SQL Server's Management Studio, etc. but before I go there, I would like to stick with SQL Server and Management Studio, etc.

My question is, what are the pros and cons with using MySQL vs. SQL Server with my PHP driven site? 2008 vs. 2012 make any difference? Every time I look up using PHP with SQL server, I get links that tell me I need to download additional drivers or Sql Server, etc.. and at first it appears as if it is NOT that simple switching everything over.

Before I go through the process of trying to switch my site over to SQL Server and finding out it isn't as simple as changing the functions, database name, password, etc. , I would love to hear from so of you who have experience in this area who can confirm I should learn this or let me know if it is not that simple??

 

Is it as simple as changing the connection code in php from mysqli_connect to mssql_connect? (along with the corresponding path, dbase name, password, etc.)

 

Thanks,
Chainsawal

Edited September 4, 2013 by chainsawal

[Barand]

If I were a DBA I'd prefer all the tools that come SQL Server.

 

As a developer I prefer a DBMS built by developers for developers viz. MySQL. For example, SQL Server's date handling functions are not a patch on the datetime function library that MySQL provides. In practice I've also found MySQL to to be much faster than SQL Server.

 

For example a database with millions of records of bus times at all bus stops throughout Greater Manchester plus dozens of other joined tables. The application retrieved the times of all buses in the next hour from local stops. In a city centre there are many dozen local stops. With SQL Server the search radius had to be limited to 100 metres to stop it timing out (30 secs). With MySQL it would happily search a radius of 5km retrieving data for hundreds of stops.

 

For a front end for server admin you can download MySQL Workbench for free.

 

My 0.02 worth

[requinix]

I'd also like MySQL but with SQL Server's tools.

[edit] Actually in general I like MS's software development tools. Like Visual Studio

 

Is it as simple as changing the connection code in php from mysqli_connect to mssql_connect? (along with the corresponding path, dbase name, password, etc.)

Hardly. To name a few things:

- Drivers. Unless it's not available for your system (eg, not running on Windows), use sqlsrv instead of the defunct mssql extension. But sqlsrv has its own requirements too.

- Do you use ...LIMIT X in your queries? Change that to SELECT TOP X...

- Use LIMIT X, Y? Change it to something more complicated

- Quote names? []s and "s instead of `s.

- Quotes with strings and dates? By default you have to use single quotes.

- Do you sometimes SELECT non-aggregated columns that aren't present in the GROUP BY? SQL Server doesn't allow that.

- Rely on magic_quotes? For one, stop that right now. For two, that won't help you with T-SQL's quoting style.

Edited September 4, 2013 by requinix

[chainsawal]

Thanks for your feedback..both of you.

 

Great feedback on the speed issue as well as the differences between quotes... so It clearly sounds like I need to do a little more learning on PHP before trying to migrate over to sql.   I literally did a fast course on how to connect to server, connect to dbase, run my query, close dbase.. and do a little pagination along with pulling images back.  That, and the basic functions in PHP are about all I know at this point...

 

I am on Windows 7 which seems like it will take some of the complexity out.. but still, dangerous given I am ready to launch a site and don't want to delay it in the event they are "issues" with the migration.

 

Thanks again for your quick and insightful responses!

 

Chainsawal

Edited September 5, 2013 by chainsawal

[vinny42]

Database migrations are never as simple as you had hoped. At first it seems a matter of changing a few quotes and some alter-table statements, and just when you think you're ready, one query refuses to work and it will turn out that a major part of your application relies on a databasequirk that the new database doesn't have. Sites developerd on MySQL are usually full of those "mistakes" because MySQL hardly does any sanity checks.

 

So: if you're close to launch, stick with MySQL for the launch and take your time to do a proper migration later.

 

Once you do migrate, I'd suggest that you also look at PostgreSQL, which is opensource, free of licence fees, and *much* more mature than MySQL. It follows the SQL standard much more closely while keeping things like LIMIT. It has good support for XML and the upcoming 9.3 release will add a bunch of JSON features so your webapp can send data to the database directly in the shape of a JSON object, and the database can return information as a JSON object, practically removing the need for PHP. 

 

But I'm letting myself get carried away as usual. :-)