Installing MSSQL for PHP

Revision as of 18:20, 28 February 2018 by Leon Stringer (talk | contribs) (FreeTDS was removed in PHP 7.0, Moodle requires PHP 7.0+ so the FreeTDS sections have been removed. Also updated Apache link to v4.3 driver)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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 database authentication/enrollment.

The minimum required version of Microsoft SQL Server has been stabilised to SQL Server 2008.

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)
  • Is Read Committed transaction ON = True (ALTER DATABASE xxxx SET READ_COMMITTED_SNAPSHOT ON)
    • In older versions 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. Download the PHP extension for SQL Server access following the instructions under Microsoft Drivers for SQL Server for PHP.

7. 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 https://www.microsoft.com/en-us/download/details.aspx?id=55642.

To know more about how to run Moodle with these drivers go to Using the Microsoft SQL Server Driver for PHP.

See also