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.

5. 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/bin/tsql" executable. Just do this:

 tsql -S anything -U dbowner -P dbpassword

If everything is ok, you'll get this output:

 locale is "es_ES.UTF-8"
 locale charset is "UTF-8"
 1>

just type, for example:

 sp_help sysobjects

and you will get some output from DB. Finally type:

 exit

and you'll be out from the "tsql" command line interpreter.

Now that you've successfully built, configured and tested FreeTDS is time to create the mssql extension alternative that will provide us with the capacity of handling MSSQL DBs from within Moodle. To do so, just configure your PHP server adding this new option to the usual ones:

 --with-mssql=/usr/local/ 

then, after the standard "make and make install" steps, your PHP server will be built with MSSQL support provided by FreeTDS.

Note that all the steps above assume that we have to build everything from source code (freetds and PHP). If you find the FreetTDS executables and mssql extension alternative in binary formats, it only will be necessary to install them in your server (binary packages...) without the need to recompile anything (just the freetds.conf edition step above will be necessary). Of course, it will be really welcome to have all those binary alternatives documented here.

Installing ODBTP

You can download ODBTP from http://odbtp.sourceforge.net/. Also you will access to the documentation from the same page.

The downloaded package includes both the source code and some binaries to be installed in the server and some ready-to-use mssql extension alternatives for some platforms/PHP versions (so you won't need to compile it if your PHP server/version binary package is present).

First of all, we have to install the Win32 service that comes with the package. Let's assume that it's going to run in the same Win32 machine where your MSSQL server is running (although it can run in any other Win32 server in your network).

To do do, following the instructions present in http://odbtp.sourceforge.net/install.html, you must:

1. Create a directory on the Windows host where the service program files will reside, i.e., md odbtp.
2. Copy the files odbtpctl.exe, odbtpsrv.exe and odbtpsrv.ini files from the winservice directory into the directory created in step 1.
3. Open a command prompt (cmd) window on the Windows host.
4. Change to the directory to which the service program files were copied, i.e., cd odbtp.
5. Run the following commands to install and start the service:
   • odbtpctl install
   • odbtpctl start

with these steps you should have one new service running in your host called "odbtp". Verify it's present in the "Services" control panel.

1. Don't forget to enable TCP/IP incoming connections to port 2799 in the host you have installed the service!

Now it's time to build the mssql extension alternative. First of all, verify if, in the downloaded package, under the "php" dir, there is one extension suitable for your PHP server/version. If it's present, you can simply copy it to the php/extensions dir in your PHP server and skip next points about compiling it from source. It's important to point that, inside each directory, you'll find two different libraries/dll files. The one that must be copied to the extensions dir is the one called "php_odbtp_mssql.xxx"!

If in the downloaded package isn't present the extension matching your PHP platform/version, you should build if from source files. To do that, just "configure, make, make install". That will create some stuff under "/usr/local".

Now that you've successfully built ODBTP is time to create the mssql extension alternative that will provide us with the capacity of handling MSSQL DBs from within Moodle. To do so, just configure your PHP server adding this new option to the usual ones:

 --with-odbtp-mssql

then, after the standard "make and make install" steps, your PHP server will be built with MSSQL support provided by ODBTP.

Finally, independently if we are using the binary extension provided in the download or if we have build it from source files, it's time to configure the extension. To do so, add this lines, if no present, to your php.ini file:

 extension=php_odbtp.dll

(only for Win32 PHP servers!)

And, for all the server platforms:

 [odbtp]
 odbtp.interface_file = "/path/to/your/odbtp.conf"
 odbtp.datetime_format = mdyhmsf
 odbtp.detach_default_queries = yes

(where /path/to/your/odbtp.conf" is usually "/usr/local/etc/odbtp.conf"" for Unix systems and "C:\odbtp\odbtp.conf" for Windows systems)

Then, edit such "odbtp.conf" file and put there these contents:

 [global]
 odbtp host = xxx.xxx.xxx (ip or hostname of the MSSQL server)
 type = mssql
 unicode sql = yes
 use row cache = yes
 right trim text = yes
 var data size = -1

With this, your PHP server will be able to connect with the MSSQL DB server using ODBTP. From here, just continue with the installation.

Finally, if you find the ODBTP executables and mssql extension alternative in binary formats, it only will be necessary to install them in your server (binary packages...) without the need to recompile anything (just the php.ini and odbtp.conf edition steps above will be necessary). Of course, it will be really welcome to have all those binary alternatives documented here.