MySQL vs SQL server

damnyanks

Ok I dont know a huge amount of databases and after talking to Phaxx on MSN he was very confident that MySQL is better then SQL server.

Could someone please tell me which would be better ?

Please leave the whole free, open source and microsoft argument out of this.

If I want to have a site which will use up lets say 3gb a day bd and make alot of connections to the db and concurrent connections which would be the better option ?

I am pretty sure its SQL from what I've herad MySQL cant take that many connections etc.

jd

Well it depends-will you have a need for constraints, triggers proper transactional support..

bonkey

Originally posted by damnyanks
Ok I dont know a huge amount of databases and after talking to Phaxx on MSN he was very confident that MySQL is better then SQL server.

In general, he's wrong.

Please leave the whole free, open source and microsoft argument out of this.

Did you ask Phaxx to do this too? Because this is the main reason MySQL would be ranked higher in the eyes of many.

For a start, no licensing costs. A site which handles a lot of connections and lots of data can be very expensive to run when you start looking at licensing costs for SQL Server, Oracle, DB2, etc.

If I want to have a site which will use up lets say 3gb a day bd and make alot of connections to the db and concurrent connections which would be the better option ?

That still depends on what it is you're doing.

If you're using an application server which has connection caching, then your "lot of concurrent connections" becomes "a fixed pool of maintained connections" and removes the question.

If you're not using an application server, then you'll probably fnd that your coding language of choice and web-server of choice will also impact the relative merits of one DB over the other.

For example, if your application has a java back-end, then you will find you lose a lot of performance from MSSQL Server because their JDBC driver is a slow POS (relative to their native ODBC driver) and the JDBC-ODBC bridge is adding an extra layer to that ODBC driver, thus still costing you performance. Similarly, if your web-server side of the application is not running on a Windows platform, you have to consider any issues going cross-platform with MSSQL, where MYSQL will run on anything.

Also, as already mentioned, MySQL is missing some of what I would consider to be the essentials of database systems : full transactional support, stored procedures, triggers, etc.

MSSQL Server has, IMHO some of the best design tools out there for a database, as well as a bunch of extra functionality which is part of the package, but not part of the DB server (e.g. The Data Transformation Services, Job Scheduler, and Alerting services are all simple to use, and wonderfully powerful).

Of course, if you just want to compare "a free DB" with MSSQL, then perhaps have a look at sap db, as the two are more feature-set compatible from a database-server point of view (as opposed to the complete package).

To be honest, at the end of the day, I would tend to say that MSSQL is still a superior database server to MySQL. However that does not mean that in certain solutions, MySQL is not the better choice. I would go one step further and say that by limiting yourself to a choice of 2, you are already throwing away a lot of viable (and possibly preferable) alternatives.

As an analagy, consider : which is a better vehicle : a Ferrari 575M Marinello, or an Opel Vectra? In general, it would be fair to say that the Ferrari is a better car, but you can obviously engineer test cases or situations where the Vectra would be the better choice. You would also say that limiting your choice to only a 575M or a Vectra is discarding viable/preferable alternatives, depending on the given situation.

While MSSQL is no Ferrari, and MySQL no Vectra, the analagy still stands.

jc

seamus

Is there anything you *don't* know bonkey?

damnyanks

So basically your offering to buy me a ferrari or vectra ? Either will do to be honest.

Who's Bonkey ?

Hecate

If it's functionality your looking for you might want to try PostgreSQL; it's also a free, open source (under the BSD license) DBMS that incorporates most things you would expect to find in commerical dbms' from oracle, microsoft etc...

As distinct from MySQL it doesnt sacrifice features in favour of speed, so it would be a good choice for any kind of complex application, and certainly a realistic alternative to MSSQL server.

anthonymcg

Well if NASA trust mySQL then I've no problem doing the same.

Talliesin

Trust it for what?

You don't think they have any life-critical systems running of a MySQL system do you?

Tools should be predicated on the job you need done. MySQL is unreliable and unsuitable for anything you aren't prepared to risk getting blown away and/or left in an inconsistent state one day. However if the risk (mitigated by regular back-ups, so you can't lose too much) is acceptable then MySQL is cheap and fast (it's fast precisely because it doesn't do a lot of things RDBMSs should).

Not everything that NASA do will result in the deaths of astronauts if it goes wrong. NASA are pretty good at balancing risk/cost on a case by case basis.

jmcc

Originally posted by Talliesin
Trust it for what?

You don't think they have any life-critical systems running of a MySQL system do you?

Unlike the US Navy who made the mistake of installing Windows NT on a destroyer a few years ago. The server crashed and the destroyer had to be towed back into port.

MySQL = simple db, no subselects and no transactions.
SQLServer = complex, real db.
Postgres = complex, real db.
Interbase = complex, real db.

As Talliesin said, evaluate the situation then choose what db you want. If it is a simple site with irregular updates and non-realtime updates, MySQL may be fine. If realtime and continual updates are required, look at a real db solution.

MySQL is blazingly fast for some applications but the lack of subselects and transactions can be a real problem.

Another very important aspect is to make sure that the machine that the db will run on has enough RAM to store the whole db. While MySQL will run on very low memory, the RAM requirement for real dbs is important.

Regards...jmcc

jmcc

Originally posted by damnyanks

If I want to have a site which will use up lets say 3gb a day bd and make alot of connections to the db and concurrent connections which would be the better option ?

Just what kind of db do you want to run? 3GB is a lot of data. If you are talking about web access, then read up on persistent connections and ODBC.

Can you state the requirements realistically? I think that the full WhoisIreland database (ie/com/net/org/info) is only about 1G and that is a hell of a lot of data. If you are storing images or multimedia stuff then I think that Oracle has add-on modules for these. Eircom were using it in ADSL trials in 1998.

The simplest thing to do is the sit down and write out what your db/web solution is meant to achieve. Then start coming up with the solution. At the moment, it looks like you haven't thought the requirements out.

Regards...jmcc

Typedef

Bonkey is right and wrong.

I would consider Stored Procedures fairly vital to what an sql server does, in fact I would find it hard to imagine life without them. That said if you want to run MSSQL you have to run windows.
Therein lies the problem, windows is insecure, slow, expensive and it's parent company is anti-competitive.

As a rule (which I will no doubt get flamed for), if you can avoid using software originating from Microsoft, do so, because whilst some of Microsoft's software can sometimes be quite well designed (spits on the ground), using Microsoft Software locks you into using Windows and consequently all software based on the Windows platform, and unfortunately as is blatently obvious the majority of software originating from Microsoft is bugridden, unstable, overpriced and ultimately aimed at locking the consumer into Microsoft dependance.

In terms of a paradigm one could choose when deciding on a direction to go when designing a development model, if one has a choice, never ever ever ever choose Microsoft, because it will come to pass that Microsoft will and are in the process of attempting to annex x86 (commonly called IBM compatable) hardware, through a project called Palladium. When Palladium comes to pass, you the consumer will be even further without choice but using the software Microsoft dictates you must use and if that software is found to have a bug, you the cosumer will be forced to live with the bug or to fix the bug in the way Microsoft deems fit. Unfortunately all too often Microsoft deems 'upgrade', an upgrade you have to pay Microsoft for, as the appropiate way to fix broken software. Hold on... so I buy a car... the car doesn't work as expected... and the car salesman says either I buy a new car (which he assures me does work) or I live with my broken one.
<<What's wrong with this picture?>>

This Palladium project is anti-competitive and seeks to in an Orwellian fashion impose major 'hardware based' constraints on your freedom to run whatever software you choose on hardware you buy. Par example http://www.theregister.co.uk/content/4/25940.html .

The instability of Microsoft software is well know throughout the IT industry, it is commonly recognised, but Microsoft holds such an all embracing monopoly on most facets of 'certainly the end - user' computer industry that most software providers have 'no choice' but to develop on a Windows platform, thus entrenching Microsoft's monopoly even further. For example the windows 9x series were so utterly laughable attempts at operating systems, but are considered 'normal' for the uninitiated, precisely because Microsoft has imposed it's top heavy monopoly with increasing orders of magnitude.

My advice is to attempt to find a non-Windows based solution, that is not to say you cannot use propietry software, but do attempt to use a platform that is not part of an undulating self ingratiating monopoly. You could and should find an alternative to Windows in Linux, and on the Linux platform you have a plethora of available database options.
Oracle,DB2,Sybase,Postgresql,MySQL and others.

Aside from the anti-Microsoft rant, there is a strong pro-Open Source rant if you want to hear it.

Linux is free, stable, you can if you wish modify and perhaps even recompile any and all parts of most Linux distrobutions and you can have full disclosure of exactly what is running on your system, so issues of delibrately implated backdoors (as is the alleged case with Windows) become ostensibly negated.

Due to the utilitarian approach of some Linux distrobution vendors, it is quite common for Linux to deliver better performance than Windows and Linux will run on much older hardware, thus negating the need to simply upgrade every time the Redomond giant dictates your current hardware setup is too old.

From an essential paradigm of freedom, of access to the very make up of your operating system, it's binaries, kernel, what hardware 'you the consumer' decide to run it on, it is the case in relatively all instances that Open Source software is in my opinion always perferable to the quasi-theistic Microsoft Monopoly.

bonkey

Originally posted by Typedef
Bonkey is right and wrong.

<snip>

That said if you want to run MSSQL you have to run windows.
Therein lies the problem, windows is insecure, slow, expensive and it's parent company is anti-competitive.

Ah - well - see, I would have taken that stance except that we were asked to leave out the OpenSource and anti-MS arguments....which I would tend to agree with your take on were they to be a consideration.

I would also point out that asking a question about "which is better for job X" should never limit the options to two players. DB2, Sybase, and Oracle are three other big "proprietary" players who should be considered. Similarly, there are a myriad of Open Source / Free Software options out there other than MySQL.

Also, as jmcc pointed out....you really need a close look at the whole deal...what will you be doing, what language, what OS, how much data *really*, budget, expertise, etc. etc.

jc