Installing MSSQL for PHP

Introduction

This short manual is suitable if you are trying to run Moodle 1.7 (and upwards) using the SQL*Server (MSSQL) RDBMS. Steps detailed below must be performed before installing Moodle itself.

First of all, minimum required version of MSSQL has been stabilised to MSSQL 2000 (v.8), although it's highly recommended to use MSSQL 2005 (v.9) or newer, mainly because all the development process has been performed using this release and it could be some unknown problems with previous releases.

While PHP comes with one, more or less, standard extension (mssql) that provides access to MSSQL databases, early we found some hard limits on it. Basically such default extension has some limits that prevent us to use it at all (you can find more info about this problems here.

So, in order to allow PHP (i.e. Moodle) to access to MSSQL DBs properly we have to install some mssql extension alternative to save us from the problems related above. Lucky, we have, at least, two of them:

• FreeDTS: This a 100% client product that it's based in the Tabular Data Protocol supported both SQL*Server and Sybase.
• ODBTP: This is another protocol, Open Database Transport Protocol, that allows to connect virtually to any Win32 DB server.

Installation process

1. You must have one MSSQL Server running and accepting incoming TCP/IP connections on port 1433 (the standard one).
2. You must have one empty DB created in your MSSQL server and you must know the username and password of its owner.
3. You must have the mssql extension alternative installed and running properly in your PHP box (see detailed info about this in the next sections).
4. You must configure your Moodle config.php with this DB related info:

 $CFG->dbtype    = 'mssql';
 $CFG->dbhost    = 'xxx.xxx.xxx.xxx'; //IP of hostname of the machine running MSSQL
 $CFG->dbname    = 'dbaname'; //Name of the empty DB created in previous point
 $CFG->dbuser    = 'dbuser'; //DB owner username
 $CFG->dbpass    = 'dbpass'; //DB password
 $CFG->dbpersist =  false;
 $CFG->prefix    = 'mdl_'; //Prefix, you can change it, but never leave it blank.

1. With all this properly configured, you can continue with standard Moodle installation.

Installing FreeTDS

You can download FreeTDS from http://www.freetds.org. Also you will access to the documentation at http://www.freetds.org/docs.html.

Note that the download link above is one source download, so you will need to install and compile it properly (at the time of writing this, it has been successfully compiled and used under Unix-like servers only, so all the instructions below are exclusively for those systems). If you compile it under Windows and it works, feel free to document your experience here!

Once downloaded and uncompressed you must "configure, make, make install" it. This will deploy some stuff in the "/usr/local" directory of your machine, mainly:

• /usr/local/etc: where the freetds conf files will reside.
• /usr/local/lib: where compiled libraries will reside.
• /usr/local/bin: where some executables will reside.

Then, you must configure FreeTDS to point to your MSSQL DB server. To do so, edit (or create) the /usr/local/etc/freetds.conf file and put in there exclusively these lines:

 [global]
     host = xxx.xxx.xxx (ip or hostname of the MSSQL server)
     port = 1433
     client charset = UTF-8
     tds version = 7.0
     text size = -1

At this point, and previously to build the mssql extension alternative, you can test conectivity with your MSSQL DB using the "/usr/local