Installing MSSQL for PHP: Difference between revisions

Revision as of 07:35, 29 November 2013

Introduction

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

Some of this may also apply if you wish to access an MSSQL server for external db authentication/enrollment.

First of all, minimum required version of MSSQL has been stabilised to MSSQL 2005 (v.9).

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: Configure these settings in your created (and still empty) database:

Use a case sensitive collation, such as Latin1_General_CS_AS.
ANSI NULLS Enabled = true (ALTER DATABASE xxxx SET ANSI_NULLS ON)
Quoted Identifiers Enabled = true (ALTER DATABASE xxxx SET QUOTED_IDENTIFIER ON)
Row Versioning Enabled (ALTER DATABASE xxxx SET READ_COMMITTED_SNAPSHOT ON)
- This is not settable via the DB properties. To set READ_COMMITTED_SNAPSHOT, there must be no active connections to the database except for the connection executing the ALTER command. If you are viewing the DB in the Server Management Studio, disconnect from any servers in the "Object Explorer" (right-click > Disconnect), then create a "New Query" and run the ALTER command. See http://msdn.microsoft.com/en-us/library/bb522682.aspx for details.
- If your DB name starts with a number, you may need to put quotes around the DB name in the query.

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

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

Microsoft Drivers for SQL Server for PHP

In July 2008 Microsoft released a new SQL Server Driver for PHP. This is a PHP extension that allows PHP scripts to read and write data on Microsoft SQL Server databases and it overcomes the problems with the native SQL Server extension that was previously bundled with PHP.

When using IIS it is strongly recommended to use the official Microsoft PHP installer from http://php.iis.net/, it should include the latest version of necessary drivers and it also simplifies future upgrades and configuration.

For Windows servers with Apache see http://www.microsoft.com/en-us/download/details.aspx?id=20098.

FreeTDS on Linux (on Ubuntu by compiling an mssql.so extension)

This is a good read to building a FreeTDS based mssql extension for apache on Ubuntu. Do note that freeTDS 0.91 was recently released, you can find latest versions here.

Note: the freetds.conf file you use should have "text size = 20971520" as mentioned in the FreeTDS on Windows section otherwise you might see sessions logging out or worse apache segmentation faults. Also see FreeTDS.

Note2: Please note that updating php tends to remove the freetds configurations. You might need to reconfigure freetds after an upgrade

Using FreeTDS on Debian Lenny

I found the following solution using:

PHP Version 5.2.6-1+lenny9
Microsoft SQL Server Enterprise Edition, version: 9.00.4053.00

apt-get install libsybdb5 freetds-common php5-sybase
/etc/init.d/apache2 restart

At the end of the process, if all goes fine, you will find in the mssql section of phpinfo();

MSSQL Support	enabled
Library version	FreeTDS

Once FreeTDS is correctly installed, don not forget to set it up following explanations in https://docs.moodle.org/en/FreeTDS

@@ Line 26: / Line 26: @@
 :*ANSI NULLS Enabled = true (ALTER DATABASE xxxx SET ANSI_NULLS ON)
 :*Quoted Identifiers Enabled = true (ALTER DATABASE xxxx SET QUOTED_IDENTIFIER ON)
-:* (Moodle 2.x only) Row Versioning Enabled (ALTER DATABASE xxxx SET READ_COMMITTED_SNAPSHOT ON)
+:*Row Versioning Enabled (ALTER DATABASE xxxx SET READ_COMMITTED_SNAPSHOT ON)
 :** This is not settable via the DB properties. To set READ_COMMITTED_SNAPSHOT, there must be no active connections to the database except for the connection executing the ALTER command. If you are viewing the DB in the Server Management Studio, disconnect from any servers in the "Object Explorer" (right-click > Disconnect), then create a "New Query" and run the ALTER command. See http://msdn.microsoft.com/en-us/library/bb522682.aspx for details.
 :** If your DB name starts with a number, you may need to put quotes around the DB name in the query.

Documentation