I decided to write this post because I have not found a simple and
clear description for installing PHP on Windows, and setting this not
very common combination to work with MS SQL 2008.
We are currently working on porting the largest system ever created in Deep Shift Labs from Windows 2000 and MS SQL 2000 to Windows 2008 and MS SQL 2008. I want to speak briefly about the history of this system and why we actually needed to install PHP on Windows. The system is about 800K lines of PHP code and it has been helping people to solve their business needs for the last 10 years. This system is designed for one of our customers. The first few years, the system worked on FreeBSD, Apache and PostgreSQL. Then the client’s company went public, and they needed to switch to commercial software. Sometime in 2004, we have been able, thanks to the fact that we originally used ADODB, to move, with a little effort, from FreeBSD/Apache to Windows 2000/IIS 5 (PHP runs as an ISAPI module). Finally, in 2006 we moved the then small database, both in structure and in size, to MS SQL 2000 from PostgreSQL.
The most difficult part in the distant 2006 was to find a driver which is bug free with MS SQL. We had tried everything supported by ADODB back then, and, in the end, we decided in favor of php_dblib.dll suggested and maintained by Frank Kromann. Frank was maintaining MS SQL extensions in PHP project and helped us immensely. The php_mssql.dll driver was using ntwdblib.dll from Microsoft, that does not work with dates and long texts properly. php_dblib.dll used dblib.dll from FreeTDS and we have not found any problems in it and started using it.
And now came the moment when the customer wants to move to the operating system and database that have Microsoft support (Windows 2000 and MS SQL 2000 are not supported anymore) and such a move, in general, is a common requirement in large enterprises.
Naturally, in the ADODB 4.93 we have been using since 2006, there is no mssqlnative driver support and so the challenge was set as follows:
- Move the database from MS SQL 2000 to MS SQL 2008
- Move the web server from Windows 2000 and IIS 5 (ISAPI) on Windows 2008 and IIS 7 (FastCGI)
- Upgrade PHP 5.1.6 to PHP 5.4.6
- Upgrade ADODB from 4.93 to 5.17 with transition to the new driver ‘mssqlnative’
- To test the system, including the two replications processes to our database from servers running MS SQL 2000 and MS SQL 2005.
This is one of the installation methods (obviously the one we used).
1. Create C:\PHP and C:\tmp folders
2. Load non thread safe PHP version recommended for use here and here. The file will contain _nts_ in the name, for example – php-5.4.6-nts-Win32-VC9-x86.zip (latest version available at the time of writing this post) from the site http://windows.php.net/download/.
3. Unpack it in C:\PHP
4. Load latest drivers from MS SQL Microsoft (file SQLSRV30) and extract it to C:\PHP\ext
5. Download the PHP web installer from Microsoft. They offer two versions and I used the latter one available with PHP 5.3.13. Install it.
You can ask a reasonable question why we install 5.3.13 to switch to 5.4.6. There are two reasons. First – I did not manage to set PHP 5.4.6 and manually configure IIS. Despite my attempts to let PHP see its php.ini file inside the C:\PHP I failed to do so. According to all the manuals you need to create a variable PHPRC assigning the path to php.ini, but in my case, someone has done something wrong.
Since PHP couldn’t see the configuration file, MS SQL driver was not loaded. Being stuck at this point for a couple of hours I asked for help in the forum and started looking for alternatives, waiting until America wakes up and sees my ‘SOS’ message in the forum. It was then that I accidentally came across a site where I found the alternative I was looking for. Maybe I would not have started using the installer if it had not offered to set this magical and wonderful PHP Manager I could not resist at the thought of trying. In the end I found that the PHP Manager allows adding PHP 5.4.6, making its php.ini file perfectly visible.
It installs all the necessary components as well. It took some 10 years for Microsoft to add some really cool things to run PHP on Windows – here you have MS SQL driver and the installer which adds a special PHP Manager inside IIS, and even Windows Cache PHP accelerator for IIS.
After installation, we have PHP 5.3.13 installed in the Program Files and PHP Manager inside IIS.
As you can see from the PHP Manager, you can control the configuration parameters from php.ini, and the extensions are shown in a separate page. Also here we see what php.ini file is being used and where the error logs are written. There is quick access to the FastCGI handler created by the installer. It is possible to add other versions of PHP and switch between them. We have not tested if it is possible to use PHP Manager to set different PHP versions on different virtual hosts. I think this and much more, such as to use different php.ini files for different virtual hosts can be done manually using the excellent article by Ruslan Yakushev from Microsoft. We just needed to add PHP version 5.4.6 to “live happily ever after”.
Now we can add PHP 5.4.6, which we put into in C:\PHP. After adding, we see that IIS now uses a new version of PHP, and that PHP Manager created a new handler, which is used (we do not see it here), but we can check with phpinfo(), which PHP manager can dynamically generate for us (how-cool-it-is!).
Now it is time to look at PHP extensions (modules). I removed mysql and added both versions of the MS SQL driver sqlsrv and pdo_sqlsrv. I forgot to check if IIS sees our changes immediately. I am used to executing “iisreset” after changing configuration.
PHP Manager offers two pre-defined configuration options for error handling for development and production.
When I changed the path to the error log, PHP Manager did not see my changes, and I had to restart the IIS. I really got used to the old fashioned way for a folder for uploaded files, session and logs and store them in PHP C:\tmp. No wonder our periodic scripts are in C:\etc. Options ‘Configure error reporting’ and ‘Set runtime limits’ are kept separately in PHP Manager for convenience only, as they all (except for the extension modules) are available from the ‘Manage all settings’ panel.
If you need to parse HTML files with PHP – create one more mapping by looking at parameters of existing PHP mapping you already have.
Now we can try to connect to MS SQL with the driver only and via ADODB. First you need to make sure the server is configured to allow TCP/IP as well as port 1433 is open on the Windows firewall.
In my case it was not so. If you cannot connect from PHP, it is recommended to install SQLCMD and use it for testing. For example, here we will find the required version of SQLCMD.
I want to draw your attention to the fact that the new Microsoft driver returns datetime as PHP DateTime:: object. But we use ADODB, which does this little trick converting this object into a string on line 794 of adodb-mssqlnative.inc.php file:
Thus, returning us the string ’2012-08-22T23:42:22Z ‘instead of the
usual ’2012-08-22 23:42:22′ and so for backward compatibility, you can
correct adodb-mssqlnative.inc.php this way:
Here is an example, where you can find connection and query snippets which use Microsoft driver directly and via ADODB.
In conclusion, we want to say that the Microsoft site provides a lot
of useful information for running PHP on IIS and using Microsoft drivers
to connect to MS SQL. For example, here
you will find the entry to the forum PHP/IIS, and other useful links.
Forums dedicated to PHP driver for MS SQL databases are located here. I used them more-than-once. They are active and guys helped me a lot.
Finally a couple of handy articles on how to configure PHP on IIS:
- Enable Per-Site PHP Configuration on IIS 7 and IIS 6.0
- Using FastCGI to Host PHP Applications on IIS
- Using FastCGI to Host PHP Applications on IIS 6.0
We are currently working on porting the largest system ever created in Deep Shift Labs from Windows 2000 and MS SQL 2000 to Windows 2008 and MS SQL 2008. I want to speak briefly about the history of this system and why we actually needed to install PHP on Windows. The system is about 800K lines of PHP code and it has been helping people to solve their business needs for the last 10 years. This system is designed for one of our customers. The first few years, the system worked on FreeBSD, Apache and PostgreSQL. Then the client’s company went public, and they needed to switch to commercial software. Sometime in 2004, we have been able, thanks to the fact that we originally used ADODB, to move, with a little effort, from FreeBSD/Apache to Windows 2000/IIS 5 (PHP runs as an ISAPI module). Finally, in 2006 we moved the then small database, both in structure and in size, to MS SQL 2000 from PostgreSQL.
The most difficult part in the distant 2006 was to find a driver which is bug free with MS SQL. We had tried everything supported by ADODB back then, and, in the end, we decided in favor of php_dblib.dll suggested and maintained by Frank Kromann. Frank was maintaining MS SQL extensions in PHP project and helped us immensely. The php_mssql.dll driver was using ntwdblib.dll from Microsoft, that does not work with dates and long texts properly. php_dblib.dll used dblib.dll from FreeTDS and we have not found any problems in it and started using it.
And now came the moment when the customer wants to move to the operating system and database that have Microsoft support (Windows 2000 and MS SQL 2000 are not supported anymore) and such a move, in general, is a common requirement in large enterprises.
Naturally, in the ADODB 4.93 we have been using since 2006, there is no mssqlnative driver support and so the challenge was set as follows:
- Move the database from MS SQL 2000 to MS SQL 2008
- Move the web server from Windows 2000 and IIS 5 (ISAPI) on Windows 2008 and IIS 7 (FastCGI)
- Upgrade PHP 5.1.6 to PHP 5.4.6
- Upgrade ADODB from 4.93 to 5.17 with transition to the new driver ‘mssqlnative’
- To test the system, including the two replications processes to our database from servers running MS SQL 2000 and MS SQL 2005.
This is one of the installation methods (obviously the one we used).
1. Create C:\PHP and C:\tmp folders
2. Load non thread safe PHP version recommended for use here and here. The file will contain _nts_ in the name, for example – php-5.4.6-nts-Win32-VC9-x86.zip (latest version available at the time of writing this post) from the site http://windows.php.net/download/.
3. Unpack it in C:\PHP
4. Load latest drivers from MS SQL Microsoft (file SQLSRV30) and extract it to C:\PHP\ext
5. Download the PHP web installer from Microsoft. They offer two versions and I used the latter one available with PHP 5.3.13. Install it.
You can ask a reasonable question why we install 5.3.13 to switch to 5.4.6. There are two reasons. First – I did not manage to set PHP 5.4.6 and manually configure IIS. Despite my attempts to let PHP see its php.ini file inside the C:\PHP I failed to do so. According to all the manuals you need to create a variable PHPRC assigning the path to php.ini, but in my case, someone has done something wrong.
Since PHP couldn’t see the configuration file, MS SQL driver was not loaded. Being stuck at this point for a couple of hours I asked for help in the forum and started looking for alternatives, waiting until America wakes up and sees my ‘SOS’ message in the forum. It was then that I accidentally came across a site where I found the alternative I was looking for. Maybe I would not have started using the installer if it had not offered to set this magical and wonderful PHP Manager I could not resist at the thought of trying. In the end I found that the PHP Manager allows adding PHP 5.4.6, making its php.ini file perfectly visible.
It installs all the necessary components as well. It took some 10 years for Microsoft to add some really cool things to run PHP on Windows – here you have MS SQL driver and the installer which adds a special PHP Manager inside IIS, and even Windows Cache PHP accelerator for IIS.
After installation, we have PHP 5.3.13 installed in the Program Files and PHP Manager inside IIS.
As you can see from the PHP Manager, you can control the configuration parameters from php.ini, and the extensions are shown in a separate page. Also here we see what php.ini file is being used and where the error logs are written. There is quick access to the FastCGI handler created by the installer. It is possible to add other versions of PHP and switch between them. We have not tested if it is possible to use PHP Manager to set different PHP versions on different virtual hosts. I think this and much more, such as to use different php.ini files for different virtual hosts can be done manually using the excellent article by Ruslan Yakushev from Microsoft. We just needed to add PHP version 5.4.6 to “live happily ever after”.
Now we can add PHP 5.4.6, which we put into in C:\PHP. After adding, we see that IIS now uses a new version of PHP, and that PHP Manager created a new handler, which is used (we do not see it here), but we can check with phpinfo(), which PHP manager can dynamically generate for us (how-cool-it-is!).
Now it is time to look at PHP extensions (modules). I removed mysql and added both versions of the MS SQL driver sqlsrv and pdo_sqlsrv. I forgot to check if IIS sees our changes immediately. I am used to executing “iisreset” after changing configuration.
PHP Manager offers two pre-defined configuration options for error handling for development and production.
When I changed the path to the error log, PHP Manager did not see my changes, and I had to restart the IIS. I really got used to the old fashioned way for a folder for uploaded files, session and logs and store them in PHP C:\tmp. No wonder our periodic scripts are in C:\etc. Options ‘Configure error reporting’ and ‘Set runtime limits’ are kept separately in PHP Manager for convenience only, as they all (except for the extension modules) are available from the ‘Manage all settings’ panel.
If you need to parse HTML files with PHP – create one more mapping by looking at parameters of existing PHP mapping you already have.
Now we can try to connect to MS SQL with the driver only and via ADODB. First you need to make sure the server is configured to allow TCP/IP as well as port 1433 is open on the Windows firewall.
In my case it was not so. If you cannot connect from PHP, it is recommended to install SQLCMD and use it for testing. For example, here we will find the required version of SQLCMD.
I want to draw your attention to the fact that the new Microsoft driver returns datetime as PHP DateTime:: object. But we use ADODB, which does this little trick converting this object into a string on line 794 of adodb-mssqlnative.inc.php file:
View Code PHP
$this->fields[$key] = $value->format("Y-m-d\TH:i:s\Z"); |
View Code PHP
if(is_array($this->fields)) { foreach($this->fields as $key=>$value) { if (is_object($value) && method_exists($value, 'format')) {//is DateTime object //$this->fields[$key] = $value->format("Y-m-d\TH:i:s\Z"); //DSL DateTime output format fix $this->fields[$key] = $value->format("Y-m-d H:i:s"); } } } |
View Code PHP
<?php echo "<B>Direct driver connection</b><BR>"; $connectionInfo = array( "UID"=>'aws_user', "PWD"=>'Q1w2e3r4t5', "Database"=>"AWS_Database" ); /* Connect using SQL Server Authentication. */ $conn = sqlsrv_connect('192.168.3.86, 1433', $connectionInfo); if( $conn === false ) { echo "Unable to connect.</br>"; die( print_r( sqlsrv_errors(), true)); } $tsql = "SELECT GETDATE() AS today"; $stmt = sqlsrv_query( $conn, $tsql); if( $stmt === false ) { echo "Error in executing query.</br>"; die( print_r( sqlsrv_errors(), true)); } $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC); echo "Today is ".$row['today']->format('Y-m-d H:i:s')."</br>"; sqlsrv_free_stmt( $stmt); sqlsrv_close( $conn); ini_set( "include_path", ini_get( "include_path" ) . ";" . $_SERVER["DOCUMENT_ROOT"] . "/include" ); require_once("adodb5\adodb.inc.php"); $user = "aws_user"; $password = "Q1w2e3r4t5"; $server="192.168.3.86, 1433"; $database = "AWS_Database"; echo "<B>ADODB MSSQL Native connection</b><BR>"; $adoConnection = ADONewConnection('mssqlnative'); //$adoConnection->debug = true; $adoConnection->Connect($server, $user, $password, $database); $rs = $adoConnection->Execute($tsql); echo "Today is ".$rs->fields['today']."</br>"; $adoConnection->Close(); ?> |
Finally a couple of handy articles on how to configure PHP on IIS:
- Enable Per-Site PHP Configuration on IIS 7 and IIS 6.0
- Using FastCGI to Host PHP Applications on IIS
- Using FastCGI to Host PHP Applications on IIS 6.0
0 comments:
Post a Comment