Posts Tagged ‘sql server’

Databases: Musings on mySQL vs SQL Server 2005 Express

Database engines are a crucial fixture in any dynamic website. One of the primary factors affecting the choice of a database engine has been cost. This could explain the wide adoption of mySQL’s community edition server in the web context. Other factors such as speed and tight integration into scripting languages (such as PHP) have also contributed to the success of the engine, however, With Microsoft now offering a free version of its much acclaimed SQL Server 2005 database engine, one could argue that choosing mySQL as your persistence layer is no longer a ‘no-brainer’.

SQL Server 2005 Express is packed with all the features you would want from a relational database management system (RDBMS). With a mature set of enterprise level features and tools (such as SQL Server 2005 Management Studio Express), Microsoft have finally responded to the challenge that mySQL’s community edition server presents. Microsoft has gone out of their way to ensure a smooth development experience when working with their development tools (such as Visual Studio 2005) and the database engine. With only a 4GB maximum database size restriction, most of the features offered with the standard edition of the server are present.

Before SQL Server Express, a developer wanting to stick to a tight budget would more often than not have chosen a database engine such as mySQL. This would inherently lead them to using a language such as PHP for dynamic scripting due to the integration that PHP offers. Of course I am generalising but I don’t think anyone could argue that the success of Linux, Apache, mySQL or PHP (LAMP) is not due in most part to the great way the different systems integrate (well, certainly in the web context anyway). mySQL also operates in a Windows environment along with PHP and Apache so certainly the rationale behind choosing the engine was extended into the realms of cross platform operability.

In my opinion, mySQL’s community edition has one downfall, its tools. I have had countless issues using the tools provided such as mySQL Administrator and mySQL Query Browser. This could be due (in part) to the fact that I am running the tools in a Windows environment but with statistics showing that most downloads of the engine are for the Windows version, mySQL should really focus on fixing the issues that are no doubt detracting from an otherwise superb product. In terms of a mySQL vs SQL Server argument, here is where I would put one tick in SQL Server’s column. SQL Server’s Management Studio Express is a great tool that focuses on all the features that one would require out of a simple database management tool. If I had one gripe it would be that it lacks the normal data import / export features that Enterprise Manager (for SQL Server 2000) and the full edition of SQL Server Management Studio offer. Of course, I understand the Express product is free and inherently needs to lack features however it must be said this feature in particular is sorely missed.

I am not going to go down the route of comparing each engines performance. There is plenty of other material out there referencing this topic. In my opinion you only really need to entertain this discussion if you are planning on using a database for real time logging or for developing the next Facebook. In the latter case I would expect you to be looking into the realms of commercial / licensed engines anyway.

I really have skimmed over the pertinent points regarding the choice between the two database engines in the web context, however most of the time the decision comes down to a mandated requirement (such as the system needs to run on a Linux machine) and personal preference. Those of you who have not yet used SQL Server 2005 Express or any of the Express products (Visual Studio etc), consider using them for your next project and I doubt you will be disappointed. We certainly develop many of our solutions using the LAMP model, however more often we are finding room for projects that fit perfectly within the Express paradigm.