mssql vs. sqlsrv: What’s the Difference? (Part 1)


[Part 2 of this post is here.]

The SQL Server Driver for PHP (also called the “sqlsrv” driver for it’s API prefix) has been available since July of 2008, but I still often see the “old” driver (the “mssql” driver) used to connect to SQL Server from PHP scripts. Many of the tutorials I come across and many of the questions I see in forums indicate that lots of people are still using the mssql driver even though it is no longer under active development. In this post I want to begin looking at some of the differences between the two drivers and perhaps gain an understanding of why the mssql driver continues to be used (although I have some good guesses). There are lots of comparison points between the mssql and sqlsrv drivers. I’ll break my comparison into two posts, focusing on high-level differences in this post and then dive into an API comparison in the next post.

I’ll start by stating the obvious: the mssql and sqlsrv drivers are not one and the same. Sorry, but I actually think that needed to be stated explicitly. I’m probably just picking nits, but I often see mssql and sqlsrv used interchangeably, or at least that’s what I think I see. Perhaps people are using mssql as a short form for Microsoft SQL Server and not the driver itself (or maybe I’m just easily confused), but the confusion makes me wonder if people are actually aware of the key differences:

·         The sqlsrv driver is built, maintained, and supported by Microsoft. There have been two releases of the driver (v1.0 and v1.1), as well as cumulative updates to release improvements. Different versions of the binaries (depending on what version of PHP you are running) are available here: SQL Server Driver for PHP 1.1. The source code is available on Codeplex at http://sqlsrvphp.codeplex.com/

 

·         The mssql driver is a community-built driver. I’m not sure how recently this driver was updated or maintained as an official PHP extension, but as of the release of PHP 5.3, it is no longer available with PECL. A quick internet search turns up a few places to download the mssql driver, but none of them that I’ve found indicate that the driver is being actively maintained.

 

·         The underlying technologies for the two drivers are different. (See Diagram 1 below.)

o    The sqlsrv driver is built on ODBC, which is actively maintained by Microsoft and provides access to features in the latest releases of SQL Server.

 

o    The mssql driver is built on DB Lib (i.e. it requires ntwdblib.dll) if you are running PHP on Windows. DB Lib is no longer supported by Microsoft

 

o    If you are running PHP on Linux, the mssql driver is built on FreeTDS.

 

 

That last point brings up one last major difference: the mssql driver does provide connectivity to SQL Server from Linux. That said, it would appear that Microsoft is considering ODBC support on non-Microsoft platforms, which equates to support for the sqlsrv driver on Linux. See one of my earlier posts for more information: SQL Server Access From Linux – What Do You Think?

image

Those are the high-level differences between the mssql and sqlsrv drivers (next time I’ll look at the differences in the APIs). If you are using the mssql driver on Windows, I’d be interested in understanding why (as opposed to the sqlsrv driver).

Thanks.

-Brian

Go to part 2 of this post.

Share this on Twitter

Comments (15)

  1. Brian,

    We’re using it because we develop on windows/mac and deploy to linux.  It the sqlsrv driver worked on linux/mac, it’d be a no-brainer to switch, and if it supported PDO, it’d be a negative-brainer (more of a no-brainer? less of a brainer?).  It’d be an easy choice.

  2. Brian Swan says:

    Thanks, David. I thought that might be the case. I hope you have seen this post (which has a link to a survey about accessing SQL Server from Linux): http://blogs.msdn.com/brian_swan/archive/2010/02/24/sql-server-access-from-linux-what-do-you-think.aspx

    You might also be interested in this forum post where the SQL Sever Driver for PHP program manager asks for what you would like to see in a PDO driver: http://social.msdn.microsoft.com/Forums/en-US/sqldriverforphp/thread/4614212e-9193-46d5-babc-5517bd494553

    -Brian

  3. Christian says:

    Another important option for Linux users is ODBTP. (http://odbtp.sf.net/)

    The developer has been updating the driver for a while now.

    Works great with SQL 2008, and supports SPs and prepared statements and XML results; and it’s fast.

    It is even fairly easy to write a Zend_DB adapter for it.

  4. Brian Swan says:

    Wow! Thanks, Christian. I wasn’t aware of ODBTP. I’ll check it out.

    -Brian

  5. Pierre says:

    Hi Brian,

    Also I would recommend to use ODBC in general to connect to SqlServer from an unix platform.

    On windows as well if you don’t have the option to use the SqlSrv driver.

  6. Slapo says:

    It seems there’s a bug in the SNAC driver (SQL Server Native Client), as it doesn’t handle numeric as floats but as strings and trims the leading zero.

    See here for more:

    http://social.msdn.microsoft.com/Forums/en-US/sqldriverforphp/thread/6cc6d0b4-4aec-4f29-9fa5-ad88417a6a30

    I’ve no idea on the progress of the issue, it would be great if there was a public bug tracker for such issues so one would know what’s happening and get notified when a bug fix is released.

  7. Brian Swan says:

    Slapo-

    I’m working on getting an update about that bug, but in the meantime it might be worthwhile to file that bug here: http://connect.microsoft.com. The Microsoft Connect site is the formal file-a-bug-and-get-updates site for MS products. But, like I said, hopefully I’ll have an update for you here soon.

    Thanks.

    -Brian

  8. Slapo says:

    Brian-

    Thanks 😀

  9. Brian Swan says:

    @Slapo- My apologies…still no update. I’m still working to make sure the right team sees the bug I’ve filed.

    -Brian

  10. Slapo says:

    @Brian- Thanks, every effort counts. :-)

    Peter

  11. Josh says:

    Hi Brian,

    Thanks for the informative post. We're currently using the MSSQL driver and are exploring options for migrating to PHP 5.3. We currently running on Windows, so we need to decide if we want to also support Linux.

    The first sentence of the paragraph before the diagram states, "That last point brings up one last major difference: the mssql driver does provide connectivity to SQL Server from Linux."

    Should this say "the sqlsrv driver does not provide connectivity to SQL Server from Linux"? I was kind of confused when I read this.

    Thanks!

    Josh

  12. Josh says:

    Oops… please disregard my previous comment. I misread that sentence, even though you italicized the "does." I originally read it as "does not."

  13. Emile Noustens says:

    SQLSRV does not have all the same functions as mssql.

  14. Shuaib Zafar says:

    thnkx a lot…