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.

15 Comments

  1. Alex@Net says:
    October 16th, 2007 at 9:23 am

    I’ve worked with MSSQL PHP adapter a couple of years ago and it was just unusable.

    It is great to see that both parties now moving in right direction.

  2. Julian says:
    October 16th, 2007 at 9:30 am

    This is really excellent news, although it’s worth pointing out that John Lim’s ADODB library has supported several more modern ways to talk to SQL Server for a very long time. I’ve run a number of PHP sites on Windows & SQL Server (although using Apache instead of IIS) for several years and it’s a great combination.

  3. Andra P žurn?ls » Blog Archive » Microsoft n?k klaj? ar PHP paplašin?jumu priekš MSSQL says:
    October 16th, 2007 at 10:41 am

    [...] Stu On PHP – Microsoft’s First PHP Extension: SQL Server 2005 Support [...]

  4. Stu says:
    October 16th, 2007 at 12:13 pm

    Hi Julian,

    That’s a very good tip about using ADODB to talk to SQL Server using the ADO or ODBC interfaces instead.

    http://phplens.com/adodb/code.initialization.html#odbc_mssql

    Best regards,
    Stu

  5. developercast.com » Stuart Herbert’s Blog: Microsoft’s First PHP Extension: SQL Server 2005 Support says:
    October 16th, 2007 at 3:01 pm

    [...] the release of Microsoft’s SQL Server extension for PHP, Stuart Herbert has posted some of his thoughts on the matter to his [...]

  6. Wez Furlong says:
    October 16th, 2007 at 3:54 pm

    Hi Stu,

    That .exe file is a self-extracting zip file; you might be able to get away with renaming it .zip and unzipping it on your mac. Inside are the docs and dlls.

    Regarding MSSQL and PDO, we provide php_pdo_mssql.dll, which does some of what you want (you can also build PDO_DBLIB against freetds). My personal experience with freetds has been “ok”, but I’m missing working unicode support when used in conjunction with unixODBC.

    I’ve been talking to the MS guys about these things; they say that they get asked a lot about supporting a linux client for SQL server but don’t have any immediate plans to do that–maybe one day. I’ve also been talking to them about a PDO driver (this initial extension is independent from PDO). We don’t have any timelines on PDO support at this point, however.

  7. Mike says:
    October 16th, 2007 at 8:02 pm

    You guys should really check out ODBTP !

    http://odbtp.sourceforge.net

    It provides access to any ODBC based RDBMS from any platform.

  8. Microsoft's SQL Server 2005 driver for PHP | MT-Soft Website Development says:
    October 19th, 2007 at 3:22 am

    [...] for PHP Posted in October 19th, 2007 by admin in Databases, PHP News Stu’s recent post Microsoft’s First PHP Extension: SQL Server 2005 Support contains a few thoughts on why PHP requires improved driver for SQL Server 2005. I can add that a [...]

  9. Central Florida PHP » Blog Archive » One for the History Books says:
    November 13th, 2007 at 5:16 am

    [...] few days before the Summit buzz, people were blogging about Microsoft’s first-ever PHP extension for their MSSQL Server. Apparently, the previous extension was almost completely [...]

  10. kodeexii.net » Blog Archive » LAMP.. WAMP.. WIMP.. WISP says:
    November 20th, 2007 at 5:03 pm

    [...] extension for SQLServer – the MSSQL Extension. Those of you who think that should head on over to Stu on Php for a little bit of enlightening of the issues pertaining to the existing MSSQL [...]

  11. SQL Server stored procedure output parameters. | keyongtech says:
    January 18th, 2009 at 4:19 pm

    [...] which has eluded me thus far. > > Thanks, > David. Hi David, This might help a little: http://blog.stuartherbert.com/php/20…-2005-support/ The lower section named: What

  12. SQL Tutorials says:
    May 1st, 2009 at 2:18 am

    Does anyone know if there is another language or set of commands beside SQL for talking with databases?

    I’m working on a project and am doing some research thanks

  13. SQL Native Client as MSSQL driver for Zend Framework » Koolphp.cn says:
    November 18th, 2009 at 2:11 pm

    [...] Herbert in his blog more than a year ago pointed some key Microsoft Sql extension for PHP pros: What’s Wrong With The Existing MSSQL Extension For [...]

  14. Zend Framework and Microsoft IIS » Koolphp.cn says:
    November 18th, 2009 at 2:14 pm

    [...] but also because MSSQL is much more integrated into Windows. However, MSSQL driver in PHP has some limitations. Nevertheless, Microsoft has released a native driver, which seems to have fixed all previous [...]

  15. Josh Geake says:
    July 15th, 2011 at 1:16 pm

    Woah I’ve worked with an adapter that allowed PHP to interface with MSSQL before and it was so unstable I had to stop and go the full hog on MySQL and then interface that to MSSQL.

    Does anyone have idea what the PHP SQL Server 2008 adapter’s like or even if they’ve updated this recnetly? I’ve been given a project where I may need to use it.

    Thanks,

    Josh.