Installing MSSQL for PHP: Difference between revisions

Latest revision as of 11:54, 18 July 2024

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:

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.

@@ Line 3: / Line 3: @@
 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.
+Some of this may also apply if you wish to access an MSSQL server for external database authentication/enrollment.
-First of all, minimum required version of MSSQL has been stabilised to MSSQL 2005 (v.9).
+The minimum required version of Microsoft SQL Server has been stabilised to SQL Server 2008.
-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 [[Development:XMLDB problems#MSSQL, PHP, UTF-8 and UCS-2|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 ==
@@ Line 22: / Line 18: @@
 . 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.
-:*Use a case sensitive collation, such as Latin1_General_CS_AS.
+*ANSI NULLS Enabled = True (ALTER DATABASE xxxx SET ANSI_NULLS ON)
-:*ANSI NULLS Enabled = true (ALTER DATABASE xxxx SET ANSI_NULLS ON)
+*Quoted Identifiers Enabled = True (ALTER DATABASE xxxx SET QUOTED_IDENTIFIER 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)
-:* (Moodle 2.x only) Row Versioning Enabled (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.
-:** 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.
-:** If your DB name starts with a number, you may need to put quotes around the DB name in the query.
 . Get PHP installed with a web server.   Unless you want to do it under IIS or some other way, the packages on the [http://download.moodle.org Moodle download page] are a good solution.
-. Choose one of the following specific sections for your server to install the '''mssql extension alternative''' installed and running properly on your PHP box.
+. Download the PHP extension for SQL Server access following the instructions under [[#Microsoft_Drivers_for_SQL_Server_for_PHP|Microsoft Drivers for SQL Server for PHP]].
-. Set the following settings in your php.ini file
+. With all this properly configured, you can continue with a [[Installing Moodle|standard Moodle installation]].
-:* mssql.textlimit = 20971520
-:* mssql.textsize = 20971520
-. With all this properly configured, you can continue with a [[Installing Moodle|standard Moodle installation]].
 == Microsoft Drivers for SQL Server for PHP ==
@@ Line 47: / Line 37: @@
 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.
+For Windows servers with [[Apache]] see https://www.microsoft.com/en-us/download/details.aspx?id=55642.
-== FreeTDS on Linux (on Ubuntu by compiling an mssql.so extension) ==
-This is a good read to [http://www.robert-gonzalez.com/2009/02/18/building-the-php-ms-sql-server-extension-from-source-on-ubuntu-810/ building a FreeTDS based mssql extension for apache on Ubuntu]. Do note that [http://www.freetds.org/news.html freeTDS] 0.91 was recently released, you can find latest versions [http://freetds.sourceforge.net/ 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
+To know more about how to run Moodle with these drivers go to [[Using the Microsoft SQL Server Driver for PHP]].
-== 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
-<pre>apt-get install libsybdb5 freetds-common php5-sybase
-/etc/init.d/apache2 restart</pre>
-At the end of the process, if all goes fine, you will find in the mssql section of phpinfo();
-{| class="nicetable"
-|-
-! 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
 == See also ==
-* [http://moodle.org/mod/forum/discuss.php?d=183987 Hardware and Performance Forum]
 * [[Errors FAQ]]
 * Using Moodle [http://moodle.org/mod/forum/view.php?id=28 Installation problems forum]
-* [[Using the Microsoft SQL Server Driver for PHP]]
 [[Category:XMLDB]]
 [[Category:DB]]
 [[Category:SQL databases]]

Documentation

Installing MSSQL for PHP: Difference between revisions

Latest revision as of 11:54, 18 July 2024

Contents

Introduction

Installation overview

Microsoft Drivers for SQL Server for PHP

See also