I often hear people say '”I’m not going to use Microsoft stuff because the don’t want to become victim of vendor lock in.” They often chose “open source” alternatives for pieces of the stack (web server and database to name a few). This isn’t necessarily a bad thing so long as they’ve done their homework, landed on the right design and chose the runtime that best met their needs. However, this seems to rarely be the case. They either assume that if they use one MS component they have to use them all. And second once they choose a stack they implement a poor design that locks them into that particular stack.
Let’s take for example the DB layer. Either you don’t know that MS has a free edition of SQL Server (Express) or you don’t care and you’re just going to use MySQL or PostgreSQL because that what everyone else seems to be using. That’s fine, you should feel comfortable with your decision and the best way to achieve that is to set forth some decision criteria and evaluate each against those criteria picking the one that ranks highest. Ok, you’ve got your DB engine picked, you picked your web server (Apache) and you’re going to code in PHP.
You have three choices for how to access the DB from your code: 1) straight in-line SQL which is platform specific, 2) use PDO (more on that below) or 3) write a full blown data abstraction layer.
The first one will get you started the fastest and as long as you never want to switch to a different backend you should be fine. But be careful there are some things like SQL injection that you’ll have to guard against. The third one, a custom abstraction layer, will be the most expensive one, if you’re going this route you probably have some very specific requirements you need to code for. Or you just like writing data abstraction layers for fun.
The second one, PDO, should be the first choice for all PHP developers.From the PDO manual: PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data. You get a few super nice benefits: PDO manages transactions (however the underlying RDBMS must support transactions) and using prepared statements gives two benefits: better performance on queries that need to be executed multiple times with the same or different parameters and implicit guarding against SQL injection.
Microsoft recently released an update to the SQL Server PHP driver that includes support for PDO. You can download it here. Ignore the SQL Server information on the PHP site, it’s outdated and for whatever reason The PHP Group is reluctant or dragging their feet to get it updated. Makes no sense to me why they wouldn’t want to have a pointer to the latest and greatest.
While I’d like to see all PHP developers use SQL Server I’d much rather see them leverage PDO so that when they finally do see the light and want to try out Express or SQL Azure it’s a much simpler exercise. Your application is far more valuable if you can easily satisfy a client’s requirement to use a particular backend.
Your express editions have a database size limit - so it is not really equivalent.
Yes, SQL Server Express does have a 10GB per DB size limit. But the point of the posting wasn't to do a side-by-side comparison of RDBMS vendors.
Yes, the key to avoiding lock-in is to look for standards. PDO looks good and is supported by a range of vendors, I'll definately consider it for new projects. Over on the Windows platforms, Linq is the one to watch.
With raw SQL you still need to be wary of proprietary extensions to languages e.g Compress in MYSQL is not available in other systems and variables in TSQL such as @@Version would not be portable.