Part 5 - Configure PHP to work with Microsoft SQL Server 2005

PHP comes with a standard extension that provides access to Microsoft SQL Server databases but the Moodle developers do not recommend it. The reason being that SQL Server does not support UTF-8 encoding (it only supports UCS-2 encoding) and PHP needs to be able to send and receive UTF-8 encoded data to and from the database server and store it as UCS-2 (see this link for more information). So, to enable PHP (and Moodle) to access to Microsoft SQL Server 2005 properly we will not use this standard extension. Instead you must install an alternative SQL Server extension called FreeTDS. Click here for an explanation of why it's best to run the non-thread safe version of PHP with FastCGI (Overview section).

An alternative way to connect Moodle to your Microsoft SQL Server database is to use ODBTP. Mark Twitty, the project manager of the ODBTP project on SourceForge, has recently compiled a non-thread safe version of the ODBTP dll that will run with the non-thread safe version of PHP and we specifically want to use the non-thread safe version of PHP because we are running PHP with FastCGI. However, the FreeTDS project seems to be more actively developed than ODBTP - the current version of ODBTP, v1.1.4, was released in early 2006 and Robert doesn't have much time to work on the project these days. Click here to read an interesting discussion on ODBTP and thread safety. The non-thread safe version of ODBTP is available on CVS.

However, FreeTDS seems to be more actively developed than ODBTP so we will use FreeTDS. See MDL-14725 for a a discussion on the work being done with FreeTDS and Moodle.

Note:
You should not attempt to install Moodle on versions of Microsoft SQL Server prior to SQL Server 2005. Moodle is only supported on SQL Server 2005 and above.

  1. Get MSSQL Server installed and running and make sure that it can accept incoming TCP/IP connections on port 1433 (the standard port for MSSQL Server).
  2. Download the non-thread safe version of the php_dblib.dll library from moodle.org.

    3. Note:
  3. Put the php_dblib.dll file that you downloaded in the PHP extensions folder: C:\PHP\ext.
  4. PHP must load the FreeTDS library into memory when the server is restarted. To make this happen add the following line to the Dynamic Extensions section of your php.ini file:
    extension=php_dblib.dll
  5. Make sure that any lines in the Dynamic Extensions section of your php.ini file that refer to the php_mssql.dll extension are DISABLED (commented out). You are going to be using the FreeTDS library instead of the php_mssql.dll library to connect to Microsoft SQL Server so you don't want to load the php_mssql.dll library.

    Why shouldn't you use php_mssql.dll?
    PHP requires UTF-8 encoding support to store information and to handle data between clients and servers, but Microsoft SQL Server only supports UCS-2 encoding (click here for more information). So PHP requires an alternative solution that is able to do the automatic conversion between UTF-8 and UCS-2 to enable communication with Microsoft SQL Server. The best solution for this on Windows is the FreeTDS library (php_dblib.dll).
  6. Create a FreeTDS configuration file called freetds.conf in the root folder of your server (e.g. c:\). It should contain the following information:

    [global]
    host = 123.456.78.90 <change this to the ip address of your database server>
    port = 1433
    client charset = UTF-8
    tds version = 7.0
    text size = 20971520

    Note:
    FreeTDS has a debugging option called TDSDump which can be enabled if you need to troubleshoot database connection problems.To enable logging to the dump file add the following lines to the end of the freetds.conf file and set the debug level that you require. Click here for more information.

    # Whether to write a TDSDump file for diagnostic purposes
    dump file = c:\FreeTDSDebug.log
    debug level = 1

    Don't enable this TDSDump option on production servers as it will slow the server down and impact on performance. Additionally, the dump file gets very big very fast so the TDSDump option should be used for debugging purposes only and then switched off.

  7. Reboot the server so that PHP picks up the changes to the php.ini file.

PHP is now configured to work with MSSQL. The next step is to create a new blank database for your Moodle site.

Back | Home | Next
Contact