Something

by Jack

Installing Moodle using IIS7 and SQL Server 2008

So I love open source projects. Loads. Sometimes however you have good reasons for not using them. Like all the DBA’s and server admin’s in an organisation are Microsoft junkies. I also love Moodle, it is a powerful open source tool for Course and Lesson management. (An LMS).

The requirement to get moodle up and running on using IIS 7 and using SQL Server 2008 (this guide will work for SQL Server 2008 Express as well).

This is a guide with a few hints and tips I discovered while trying to get this working. A couple of useful guides, a bit out of date, which I used are here:

Installing SQL Server for PHP
Moodle on Windows

Installing php to work with IIS used to be a complete pain the arse. Not any more! Microsoft have a Web Platform install for it. Yes, it automatically installs and configures php to work with IIS. Astounding! Well done fellas, you can get it here.

There are still a few changes that need to be made, mostly to do with the lack of Unicode support in the default MSSQL driver and the size of file uploads, but we will come to those in a bit.

Next download the latest moodle release, create a folder, extract it. Then create a virtual directory in IIS and point it to the moodle folder. Convert your folder into an application.

Database

Now create a blank database in SQL Server Management Studio. You can use the default settings except for:

Set ANSI NULLS Enabled to true Set Quoted Identifiers Enabled to true Create a SQL user to access it with the dbo schema and the following roles: db_datareader, db_datawriter, db_ddladmin

You need to make sure that the SQL Server Browser service is running (This isn’t on SQL Server Express by default, and tripped me up for a while), and that in the SQL Server Configuration tool TCP/IP access is enabled, and that port 1433 is open on any firewalls.

moodle config.php

Now we need to make a few changed to the moodle config.php file. Firstly we need to specify some settings for the database.

Moodle config.php file database settings for SQL Server default instance locally:

$CFG->dbtype    = 'mssql_n';
$CFG->dbhost    = 'localhost';   // eg localhost or db.isp.com
$CFG->dbname    = 'Moodle';      // database name, eg moodle
$CFG->dbuser    = 'moodle';    // your database username
$CFG->dbpass    = 'password';    // your database password
$CFG->prefix    = 'mdl_';        // Prefix to use for all table names
$CFG->dbpersist = false;         // Should database connections be reused?

Moodle config.php file database settings for SQL Server Express instance locally:

$CFG->dbtype    = 'mssql_n';
$CFG->dbhost    = 'localhost\sqlexpress';   // eg localhost or db.isp.com
$CFG->dbname    = 'Moodle';      // database name, eg moodle
$CFG->dbuser    = 'moodle';    // your database username
$CFG->dbpass    = 'password';    // your database password
$CFG->prefix    = 'mdl_';        // Prefix to use for all table names
$CFG->dbpersist = false;         // Should database connections be reused?

A few location settings: wwwroot is the URL that will be used, dirroot is the moodle site directory. dataroot will have to be a new blank folder that is created to store content.

$CFG->wwwroot   = 'http://localhost/moodle';
$CFG->dirroot   = 'D:\iis-base\moodle';
$CFG->dataroot  = 'D:\iis-base\moodle_data';

Extended PHP config

We need to tweak a few things to get PHP to talk to the SQL Server database quite the way we like.

Moodle have compiled a new SQL Server driver called FreeTDS (details and more advanced instructions here).

You need to install the .NET 1.1 framework.

The automated install from Microsoft uses the none thread safe version of PHP, so you need to download the following PHP extension. You need to copy php_dblib.dll to C:/Program Files/PHP/ext

You then need to create a config file for FreeTDS. By default this is picked up from c:\ You can create a Environment variable called FREETDS to set a different location. The file needs to be called freetds.conf and contain the following…

For Express edition of SQL Server locally:

[global]
host = 127.0.0.1
instance = SQLEXPRESS
port = 1433
client charset = UTF-8
tds version = 8.0
text size = 20971520

For a default install of SQL Server something like:

[global]
host = 127.0.0.1
port = 1433
client charset = UTF-8
tds version = 8.0
text size = 20971520

Now time to edit the php.ini file to make some changes (this can be found C:/Program Files/PHP/php.ini). Add the following line to the Dynamic Extensions section:

extension=php_dblib.dll

Comment out (use a ; at the beginning of the line) the following two lines:

[PHP_MSSQL]
extension=php_mssql.dll

You should also make changes to the following settings:

mssql.textlimit = 20971520
mssql.textsize = 20971520

I also wanted to increase the file size upload to something bigger than 2MB, as I had some chunky SCORM packages to upload. I increased it so:

upload_max_filesize = 20M
post_max_size = 20M

Restart your IIS Server and go to http://localhost/moodle/install.php (or what ever address you used).