Tuesday, February 10, 2009

Installing MSSQL for PHP


Installing MSSQL for PHP
From MoodleDocs
Jump to:
navigation, search
Moodle1.7
Contents[
hide]
1 Introduction
2 Installation overview
3 Using FreeTDS on Unix
4 Using FreeTDS on Windows
4.1 Troubleshooting
5 Using ODBTP on Unix or Windows
6 Using ODBC on Windows
7 Related links
if (window.showTocToggle) { var tocShowText = "show"; var tocHideText = "hide"; showTocToggle(); }

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 2005 (v.9), although it might work with MSSQL 2000 (v.8) or newer. All the development process has been performed using MSSQL 2005 and there 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 these problems here).
So, in order to allow PHP (i.e. Moodle) to access to MSSQL DBs properly we have to install a mssql extension alternative to save us from the problems related above. See the sections below for details about the various options.

Installation overview
1. Get MSSQL Server installed and running. (
A free limited version, SQL Server Express Edition is available for testing.)
Make sure that you choose mixed authentication (Windows and local accounts) to keep things simpler later. You'll be asked to define the "sa" account password (it's the default System Administrator account which has full access to all databases by default).
2. Make sure MS SQL Server can accept incoming TCP/IP connections on port 1433 (the standard one).
You might need to explicitly allow this in your Windows firewall (see the Control Panel). You may also need to edit options in the :SQL Server Configuration Manager -> Network Configuration -> Protocols -> TCP/IP enabled
3. Open the "SQL Server Management Studio" and create a new empty database. If you are using the "sa" account then you don't need to do anything else here.
4. Configure these settings in your created (and still empty) database:
ANSI NULLS Enabled = true (ALTER DATABASE mdl_HEAD SET ANSI_NULLS ON GO)
Quoted Identifiers Enabled = true (ALTER DATABASE mdl_HEAD SET QUOTED_IDENTIFIER ON GO)
5. Get PHP installed with a web server. Unless you want to do it under IIS or some other way, the packages on the
Moodle download page are a good solution.
6. Choose one of the following specific sections for your server to install the mssql extension alternative installed and running properly on your PHP box.
7. Set the following settings in your php.ini file
mssql.textlimit = 20971520
mssql.textsize = 20971520
Also, don't forget to set one of the following alternatives, in order to get all the data properly "slashed":
magic_quotes_gpc = Off or
magic_quotes_gpc = On and magic_quotes_sybase = On
8. With all this properly configured, you can continue with a
standard Moodle installation.
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).
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)
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
text size = 20971520
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 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: sp_help sysobjects
and you might 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 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 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.
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.

Using FreeTDS on Windows
If your web server is on Windows, use php_dblib.dll from Frank Kromann (
original details here). Despite the name, it's FreeTDS compiled for Windows. Many thanks to Frank for providing this DLL. Here's how to set it up:
1. Download the appropriate copy of php_dblib.dll from the list below, and save it into your /PHP/ext directory. (php_dblib.dll does not appear to be available for PHP 4.x at this time.)
php_dblib.dll for PHP 5.1.x
php_dblib.dll for PHP 5.2.x
php_dblib.dll for PHP 6.x
(here you can find some
instructions to build those freetds extensions under win32 yourself)
2. FreeTDS requires the .NET Framework v1.1 to be installed. You can
download it from the Microsoft website along with its service pack. Alternatively, if you do not wish to install this framework, you can download the required DLL from Frank's site, and save it into your /PHP root directory.
3. Edit your /PHP/php.ini file and add this line: extension=php_dblib.dll
Make sure that any lines referring to the php_mssql.dll extension are DISABLED (commented out).
4. Create a file called C:\freetds.conf with: [global]
host = xxx.xxx.xxx.xxx (ip of the MSSQL server)
port = 1433
client charset = UTF-8
tds version = 7.0
text size = 20971520
5. Your Moodle config.php should include lines like these: $CFG->dbtype = 'mssql_n'; // Required
$CFG->dbhost = 'localhost'; // assuming MS SQL is on the same server, otherwise use an IP
$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.
If you don't have a config.php file yet, it can be generated as normal from the Moodle installer.
6. Restart or start your web server. If Moodle still cannot communicate with the database server, please turn display_startup_errors to "On" in your /PHP/php.ini file, then restart the web server and check for any errors that may indicate incorrect DLL versions or missing dependencies. These error reports, turned off by default in PHP, can be vital in locating a problem with new extension installations.
7. Install Moodle as usual. Good luck!

Troubleshooting
If you encounter some problems you can try:
check that you have DotNet framework 1.1 installed (later version are installed on Vista, but you could need this specific one)
enable TCP/IP for MSSQL: SQL Server 2005 Network Configuration -> Protocols for MSSQLSERVER -> TCP/IP (Enable) -> Properties -> Ip Addresses -> 127.0.0.1 (Active+Enable)
if you are using SQL Server 2005 and you have the error 4004: Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier, try the ODBTP method (next chapter). The SQL Server complaining that it doesn't support pure Unicode via TDS or older versions of ODBC. Microsoft has deprecated DB-Library a long ago, in favor of ODBC, OLE DB, or SQL Native Client. Many new features of SQL 2005 aren't accessible via DB-Library so if you need them, you could have to switch away from tools based on TDS and DB-Library :(

Using ODBTP on Unix or Windows
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:
Create a directory on the Windows host where the service program files will reside, i.e., md odbtp.
Copy the files odbtpctl.exe, odbtpsrv.exe and odbtpsrv.ini files from the winservice directory into the directory created in step 1.
Edit the file odbtpsrv.ini of the previous step and this line: MaxRequestSize=20971520
Open a command prompt (cmd) window on the Windows host.
Change to the directory to which the service program files were copied, i.e., cd odbtp.
Run the following commands to install and start the service:
odbtpctl install (NOTE: at the date this note is written -23/06/08- the ODBTP service can only be installed on Windows NT 4.0, 2000 or XP Pro. The service cannot be installed on Microsoft Windows Vista)
odbtpctl start
With these steps you should have one new service running in your host called "odbtp". Verify it's present and running in the "Services" control panel.
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 you have built 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 Win32 box running the ODBTP service)
type = mssql
unicode sql = yes
use row cache = yes
right trim text = yes
var data size = 20971520
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.
Once ODBTP is working, Moodle config.php should include lines like these: $CFG->dbtype = 'mssql_n'; // Required
$CFG->dbhost = 'localhost'; // assuming MS SQL is on the same server, otherwise use an IP
$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.
If you don't have a config.php file yet, it can be generated as normal from the Moodle installer.

Using ODBC on Windows
This functionality is EXPERIMENTAL and must not be used on production environments!
1. Go to the Administrative Tools control panel, then the Data Sources (ODBC) panel.
2. Configure one new System/User DSN (call it, for example "moodle"). Dont forget to enable these options if the driver asks for them:
ANSI NULLS Enabled = true
Quoted Identifiers Enabled = true
3. Your Moodle config.php should include lines like these: $CFG->dbtype = 'odbc_mssql'; // Note this is different to all the other configs on this page!
$CFG->dbhost = 'moodle'; // Where this matches the Data source name you chose above
$CFG->dbname = ''; // Keep it blank!!
$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.
4. Install Moodle as usual. Good luck!

Related links
Installing Oracle for PHP
Retrieved from "
http://docs.moodle.org/en/Installing_MSSQL_for_PHP"
Categories: Moodle 1.7 Experimental Features Installation Developer XMLDB

No comments: