I’ve just seen Wez’s post about Microsoft’s new Community Technology Preview for their SQL Server 2005 extension for PHP.

Why Is It Important For Microsoft To Support PHP On Windows?

I can’t speak for the rest of the world, but Windows is an important platform here in the UK. About half the customers I work with run their websites on Windows Server. Every customer who prefers Windows Server over Linux automatically wants to use the full Microsoft stack, and that means IIS and Microsoft SQL Server.

Starting with Windows Server 2003 Service Pack 1, IIS became a viable platform for running production PHP sites on. SP1 has a fix for a nasty problem in the Windows kernel which stopped PHP working well as a CGI program under IIS. It’s true, IIS has serious limitations compared to Apache which cost time and money to overcome – chiefly no out-the-box equivalent to mod_rewrite – but it performs well and is simple enough for many folks to admin more easily than Apache (although the new IIS7 admin interface might be a step backwards there). With the new FastCGI support recently released, it’s now possible to further optimise an IIS setup too.

SQL Server is a true heavyweight database server. The most commonly deployed version that I come across is SQL Server 2000, although SQL Server 2005 is starting to appear in various enterprises. It is fast, scalable (in the old-fashioned way unfortunately, which is expensive), and when it comes to old-fashioned replication (as opposed to scale-out) I think that it leaves MySQL for dead. Although expensive to buy outright, if you buy your hosting boxes from places like Rackspace, the cost tends to be much more reasonable.

PHP comes with an MSSQL extension that’s based on the old TDS data protocol, which I believe is a legacy protocol from the old Sybase days (SQL Server is based on Sybase). TDS is also supported on Linux through the FreeTDS project, which allows PHP running on Linux boxes to connect to Microsoft SQL Server running on Windows.

What’s Wrong With The Existing MSSQL Extension For PHP?

… or, why do we need an improved SQL Server extension for PHP? :)

The existing MSSQL extension works well, but has a few practical limitations that have to be worked around.

  • Limited to varchar(255) support. SQL Server 2000 and later support varchar columns longer than 255 bytes in size, but unfortunately the old TDS-based MSSQL extension can only support up to varchar(255).
  • No support for unicode columns like nvarchar. The size of a varchar column is specified in bytes, not characters. If you’re working with UTF8 or UTF16 encoded data, one non-ASCII character takes up multiple bytes of space. This cuts down on the amount of characters you can store in a varchar field, and it makes things like HTML form validation – er – interesting. nvarchar, by contrast, is advertised as a variable-size datatype for storing multi-byte characters. nvarchar(255) holds 255 characters, not 255 bytes.
  • No PDO drivers. Although there’s some debate about the performance merits of PDO, PDO’s prepared statement support is a real boon when it comes to preventing SQL injection attacks.
  • Poor error reporting. The MSSQL extension doesn’t provide an equivalent to mysql_error() et al, which is a bit of a pain.

At the moment, I’ve no idea whether Microsoft’s extension addresses any of these issues. There’s no documentation online, just a .exe file that isn’t going to run under OS X :) I’ll have a look at it when I get to my Windows PC at work, and see what it can – and can’t – do.

Comments are closed.