Using FreeTDS on Unix
Important Note 1: Due to one bug in PHP it's highly recommendable to use PHP > 5.1.6 with FreeTDS (more info).
Important Note 2: Due to one bug in how FreeTDS handles nulls and empty values for some text types it's highly recommendable to use a recent version of FreeTDS (0.64 + official patches) (more info).
FreeTDS 0.91 was released 17th August 2011. Please ensure you are using FreeTDS with latest patches to avoid known issues.
If you web server is on Linux or some other flavour of Unix, try FreeTDS, http://www.freetds.org (documentation at http://www.freetds.org/docs.html) (You can also use FreeTDS on Windows systems as well)
Note that the download link above is a source download, so you will need to install and compile it properly.
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.xxx (ip of the MSSQL server) port = 1433 client charset = UTF-8 tds version = 7.0 (or 8.0 if using FreeTDS 0.82 or later) text size = 20971520
If you want to connect to a particular instance of MSSQL you should specify the instance name:
[global] host = xxx.xxx.xxx.xxx (ip of the MSSQL server) instance = xxx (instance name, e.g. I2) port = 1433 client charset = UTF-8 tds version = 7.0 (or 8.0 if using FreeTDS 0.82 or later) text size = 20971520
At this point, and previously to build the mssql extension alternative, you can test connectivity with your MSSQL DB using the "/usr/local/bin/tsql" executable. Just do this:
tsql -S serverhost -U dbowner -P dbpassword // Or sqsh (freetds-dev does not include tsql) sqsh -S serverhost -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:
and you might get some output from DB. Finally type:
and you'll be out from the "tsql" command line interpreter.
Now that you've successfully built, configured and tested FreeTDS it 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, you'll need to compile your PHP server with the option below adding this new option to the usual ones:
then, after the standard "make and make install" steps, your PHP server will be built with MSSQL support provided by FreeTDS.
Finally, configure your Moodle config.php with this DB related info and continue with a normal Moodle install:
$CFG->dbtype = 'mssql_n'; // Required
$CFG->dbhost = 'xxx.xxx.xxx.xxx'; // IP of the MSSQL server (also proper hostname is allowed)
$CFG->dbname = 'moodle'; // or whatever you called the database you created
$CFG->dbuser = 'yourusername'; // I usually use the 'sa' account (dbowner perms are enough)
$CFG->dbpass = 'yourpassword';
$CFG->dbpersist = false;
$CFG->prefix = 'mdl_'; //Prefix, you can change it, but NEVER leave it blank.