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 https://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?
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.
Comments
- Anonymous
March 09, 2010
The comment has been removed - Anonymous
March 09, 2010
The comment has been removed - Anonymous
March 09, 2010
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. - Anonymous
March 09, 2010
Wow! Thanks, Christian. I wasn't aware of ODBTP. I'll check it out.-Brian - Anonymous
March 10, 2010
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. - Anonymous
March 10, 2010
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-ad88417a6a30I'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. - Anonymous
March 11, 2010
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 - Anonymous
March 11, 2010
Brian-Thanks :D - Anonymous
March 15, 2010
@Slapo- My apologies...still no update. I'm still working to make sure the right team sees the bug I've filed.-Brian - Anonymous
March 21, 2010
@Brian- Thanks, every effort counts. :-)Peter - Anonymous
February 04, 2011
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 - Anonymous
February 04, 2011
Oops... please disregard my previous comment. I misread that sentence, even though you italicized the "does." I originally read it as "does not." - Anonymous
October 26, 2012
SQLSRV does not have all the same functions as mssql. - Anonymous
May 17, 2013
thnkx a lot... - Anonymous
November 12, 2014
worklabs.org/sql-interview-questions-and-answersThank you for the article- Anonymous
March 01, 2017
Thanks, this helped me a lot.
- Anonymous