Drupal 7.14 on IIS 7.5, MS SQL Server 10.5

Question

I have been tasked with seeing if Drupal can be made to run on IIS (specifically, IIS 7.5 running on Windows Server 2008 R2) and MS SQL Server (2008 R2). I'm aware of Microsoft's WebPI Drupal package, but since we have an existing site which we wish to migrate to this new environment I'd prefer to add the pieces that are needed so that they can go into the source control environment with the others rather than use a black box package. I am doing the experimentation with Drupal 7.14, which is the latest release at this time.

I have got basic PHP scripts to run (Hello World, phpinfo, etc), and installed the sqlsrv driver 7.x-1.2 (recommended at the present time) into Drupal. The Drupal installation process gets as far as to the database settings screen without any complaints. However, if I select MS SQL Server as backend:

• if I enter invalid database credentials I get an error back, as expected.
• if I enter valid database credentials, after clicking proceed I get a HTTP 500.0 error from IIS saying about php-cgi.exe The FastCGI process exited unexpectedly.

I have enabled request tracing in IIS and found the error code GENERAL_READ_ENTITY_END Reached the end of the file. (0x80070026) in the resulting log file. Googling for the hexadecimal error code produced very little in terms of helpful information.

drupal.org reports 4772 sites reporting using the sqlsrv module, so the module obviously works. Any hints on how to get it to work, preferably other than "use the WebPI packages"?

By setting Instance MaxRequests to 1, keeping Standard Error Mode at ReturnStdErrIn500, disabling friendly errors in IIS and ditto MSIE (thanks for the link @DigitalFire which pointed me in the right direction for this), I was able to get at what I suspect is the actual command output, or at least as close to it as I can easily get in the web browser. What is shown in MSIE's "View Source" is a plain, almost-empty, boilerplate HTML document with an empty body. Before I turned off MSIE's friendly HTTP error messages (who came up with the idea of having that turned on as standard on Server, anyway?) it showed HTTP 500, so this is probably pretty close to the truth.

Using SQL Server's Profiler, I traced the database calls made and toward the end was the following, ignoring pure SELECT statements:

-- repeated a number of times
INSERT INTO system ([filename], [name], [type], [owner], [info]) VALUES (@P1, @P2, @P3, @P4, @P5)

UPDATE system SET [status]=@P1
WHERE  ([type] = @P2) AND ([name] = @P3) AND( ([status] <> @P4) OR ([status] IS NULL ) )

UPDATE system SET [bootstrap]=@P1
WHERE ( ([bootstrap] <> @P2) OR ([bootstrap] IS NULL ) )

INSERT INTO registry ([name], [type], [filename], [module], [weight]) VALUES (@P1, @P2, @P3, @P4, @P5)

The final INSERT occurs only once, with EventClass SQL:StmtRecompile. I'm not sure this helps much...

Answer 1

Try commenting out extension=php_gd.dll in your php.ini file and try again. I found this article in which the OP had a similar problem.

Answer 2

After a lot more digging both by myself and a colleague, we finally ran across what appears to be the answer. In a TechNet Forum post in the thread MSVCR90 fault with Sql Server driver, Pierre Couzy mentioned the issue Error when executing empty queries in PHP 5.3.8 on drupal.org. Attached to that incident report is a suggested patch for the sqlsrv module which changes the substitution for RELEASE SAVEPOINT to a non-empty SQL statement in the file sqlsrv/database.inc.

What's interesting is that it looks like this fix is included in mainline 7.x-1.2, whereas both I and my colleague (downloading and installing the module independently over the last few days) apparently were running against version 7.x-1.1 of the sqlsrv module. It sincerely beats me how that happened (I was sure I'd downloaded 7.x-1.2), but manually applying the patch to the version that we had downloaded seems to have resolved the issue, and actually running 7.x-1.2 shows the same result.

Hopefully this keeps somebody from beating their head against this same problem.

Answer 3

The first thing I was going to ask was what version of PHP and PHP to SQL driver you were using. We were receiving errors like this when using PHP 2.7 and PHP to SQL 2.x. I recommend getting the PHP drivers from Microsoft through the Web Platform installer and getting the latest version of Drupal and the MS SQL driver from drupal.org. Also if you change the MS SQL driver you must run update.php so the changes can be picked up. I highly recommend using Wincache as it will help your performance and can possibly be used as an alternative query backend. Drupal is kind of a database hog so if you are not using an alternative query backend and Entity Cache then you will most likely have performance issues.