I have my first SQL Server project lined up. So I am just interested in hearing some stories from the trenches form people that have been there already. How does PHP and SQL Server 2005 work out together? How do they work out together with *nix or Linux specifically? How do they work together with a Mac (since all the developers on the projects use Macs)? What are the limitations?

I heard some issues with NCHAR's when going through FreeTDS. Since this is a swiss client, we will of course have to make the application localized in German, Italien and French. What extensions are you using? Is PDO_DBLIB worth a look or should one better stick to ext/mssql? I do not envision I am going to do ueberfancy things. Well looking through the requirements we will have to read a PDF from the server. We will also make extensive use of FULLTEXT indexes, but that should have no relevance for the client.

Anyways the previous application we did for them is running on RHEL5 (no app stack, which supposedly gives you more current PHP versions than the RHEL5 standard PHP 5.1.6) on MySQL. But the new application has higher reliability requirements, which means they prefer to have the DB be something they can administer with their own DBA's and that is setup for failover etc. So in the process of the project we will probably also migrate to MySQL, which means I will need to figure out how to manage my ordered lists in SQL Server as well.

I did set up FreeTDS on a Mac connecting PHP to a Windows 2003 Server MSSQL 2005 database. However, the queries which I was required to run where only very basic pulls of information to combine it with the data in the MySQL database. I didn't encounter any problems with characters as you mentioned but I will keep a look out for it.

Worked fine for me, both using the FreeTDS MS-SQL extension and the PDO_DBLIB extesnion. We were also using Zend Framework with it's Zend_Db object and PDO.

One thing that we did see is under high loads, threads waiting for a response from the MS-SQL server will have an IOWAIT status and will block on the processor... that doesn't seem to happen as bad with MySQL, but MS-SQL can take much more of a beating (and more complex queries) than MySQL can under heavy load. We didn't run into any problems with characters either, but we were only doing American English and rarely converted among charsets or did UTF-8. All in all, the experience made me actually enjoy working with the MS-SQL database and I've used it in other projects since that needed to do heavier wight OLTP work.

Since we were using CentOS, we typically used Jason Litka's excellent UtterRamblings.com repository of very up-to-date PHP5 packages, which in his case included the MS-SQL bindings... the default CentOS build does not activate this extension and you need to build from source.

We had problems with the initial setup. We generally work on PHP and MySql projects. This was our first PHP-MsSql project.

After the complete setup the first thing we discovered was theres no support for utf-8 in MsSql Server.

PHP’s “mb_convert_encoding” helped us to deal with the problem.

Currently the project is deployed and successfully :)

So you are using latin1 and convert the data before sending/reading it to/from MS SQL?

BTW: Are you guys using persistent connections? I am afraid that connection creation latency will not be much fun and also the admin is a bit worried about too many connections opening and closing. Then again he said he can deal with a couple of hundred open connections (so we just have to configure the apache max childs accordingly). Or are you guys even using some other connection pooling solution?

I just setup a rhel 5 server with php 5.2.6 and mssql support. The only issues I had were

1) by default selinx does not allow the server to make outgoing connections

2) FreeTDS has a really low fieldsize limit if you are working with varchar(max) fields. This can easily be changed in the freetds.conf file.

3) php needs to have mssql.datetimeconvert set to false otherwise the dates come in like this 'Thu Jan 01 2008 02:13:00PM' instead of '2008-01-01 14:13:0.000'.

So far everything has been running just as well as the debian box we had been running on since january.

So that means that the varchar(255) limit mentioned here is no longer valid?

Also by way of that above linked blog post I also come across ODBTP. Anyone played around with that one? Seems like it has full unicode support.

So apparently the varchar(255) limitation still applies. And I have been told to rather look into using ODBC, which at least on Windows is wicked fast. That way I can also get around the limitations with larger varchars and unicode support. This is essentially what the ODBTP guys are going, though I am not yet clear yet what they give me. I presume they circumvent the need for an ODBC client.

You can absolutely get around the varchar(255) issue by editing the text limit in freetds. We've got several varchar(max) fields and after increasing the text limit, we've had no problems.

As far as UTF8 text, how I was able to do it natively is setting up the field as n[var]char, and prefixing data with an N'', as such:

INSERT INTO utf8table (utf8text) VALUES (N'<UTF>')

It's an absolute pain, and completely ridiculous, but Microsoft claims that since it's ANSI standard, that's how it's going to be (..only when it's convenient for them, eh?)

Also, you should also put the following into your freetds.conf:

client charset = UTF8

This runs all the data through iconv to ensure everyone's speaking the same language, so to say.