Note: You are currently viewing documentation for Moodle 3.6. Up-to-date documentation for the latest stable version of Moodle is likely available here: Installing MSSQL for PHP.

Installing MSSQL for PHP: Difference between revisions

From MoodleDocs
Main page ► Installation ► Installing MSSQL for PHP
Installation
(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)
 
(197 intermediate revisions by 32 users not shown)
Line 1: Line 1:
=== Introduction ===
{{Installing Moodle}}== 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.
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.


First of all, minimum required version of MSSQL has been stabilised to MSSQL 2000 (v.8), although it's '''highly recommended to use MSSQL 2005 (v.9) or newer''', mainly because all the development process has been performed using this release and it could be some '''unknown problems''' with previous releases.
Some of this may also apply if you wish to access an MSSQL server for external database authentication/enrollment.  


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 this problems [[Development:XMLDB problems#MSSQL, PHP, UTF-8 and UCS-2|here]].
The minimum required version of Microsoft SQL Server has been stabilised to SQL Server 2008.


So, in order to allow PHP (i.e. Moodle) to access to MSSQL DBs properly we have to install some '''mssql extension alternative''' to save us from the problems related above. Lucky, we have, at least, two of them:
== Installation overview ==


* '''[http://www.freetds.org/ FreeDTS]''': This a 100% client product that it's based in the [[Wikipedia:Tabular_Data_Stream|Tabular Data Protocol]] supported both SQL*Server and Sybase.  
1. Get MSSQL Server installed and running. ([http://www.microsoft.com/sql/editions/express/default.mspx A free limited version, SQL Server Express Edition] is available for testing.)
* '''[http://odbtp.sourceforge.net/ ODBTP]''': This is another protocol, Open Database Transport Protocol, that allows to connect virtually to any Win32 DB server.
: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).


=== Installation process ===
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'''


# You must have one MSSQL Server running and accepting incoming TCP/IP connections on port 1433 (the standard one).
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.
# You must have one empty DB created in your MSSQL server and you must know the username and password of its owner.
# You must have the '''mssql extension alternative''' installed and running properly in your PHP box (see detailed info about this in the next sections).
# You must configure your Moodle config.php with this DB related info:
  $CFG->dbtype    = 'mssql';
  $CFG->dbhost    = 'xxx.xxx.xxx.xxx'; //IP of hostname of the machine running MSSQL
  $CFG->dbname    = 'dbaname'; //Name of the empty DB created in previous point
  $CFG->dbuser    = 'dbuser'; //DB owner username
  $CFG->dbpass    = 'dbpass'; //DB password
  $CFG->dbpersist =  false;
  $CFG->prefix    = 'mdl_'; //Prefix, you can change it, but '''never''' leave it blank.
# With all this properly configured, you can continue with [[Installing Moodle|standard Moodle installation]].


=== Installing FreeTDS ===
<span id="Configuration"></span>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.


You can download FreeTDS from http://www.freetds.org. Also you will access to the documentation at http://www.freetds.org/docs.html.
5. 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.


Note that the download link above is one '''source download''', so you will need to install and compile it properly (at the time of writing this, it has been successfully compiled and used under Unix-like servers only, so all the instructions below are exclusively for those systems). If you compile it under Windows and it works, feel free to document your experience here!
6. 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]].


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:
7. With all this properly configured, you can continue with a [[Installing Moodle|standard Moodle installation]].
* /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:
== Microsoft Drivers for SQL Server for PHP ==


  [global]
In July 2008 Microsoft [http://social.msdn.microsoft.com/forums/en-US/sqldriverforphp/thread/a10e5202-9e41-4ff8-a33e-fbcc7b951be2/ 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.
      host = xxx.xxx.xxx (ip or hostname of the MSSQL server)
      port = 1433
      client charset = UTF-8
      tds version = 7.0
      text size = -1


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:
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.


  tsql -S anything -U dbowner -P dbpassword
For Windows servers with [[Apache]] see https://www.microsoft.com/en-us/download/details.aspx?id=55642.


If everything is ok, you'll get this output:
To know more about how to run Moodle with these drivers go to [[Using the Microsoft SQL Server Driver for PHP]].


  locale is "es_ES.UTF-8"
== See also ==
  locale charset is "UTF-8"
* [[Errors FAQ]]
  1>
* Using Moodle [http://moodle.org/mod/forum/view.php?id=28 Installation problems forum]


just type, for example:
[[Category:XMLDB]]
 
[[Category:DB]]
  sp_help sysobjects
[[Category:SQL databases]]
 
and you will 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 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-mssql=/usr/local/
 
then, after the standard "make and make install" steps, your PHP server will be built with MSSQL support provided by FreeTDS.
 
Note that all the steps above assume that we have to build everything from source code (freetds and PHP). If you find the freetds exacutables 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 freetds.conf edition step above will be necessary). Of course, it will be really welcome to have all those binary alternatives documented here.

Latest revision as of 18:20, 28 February 2018

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

Retrieved from "https://docs.moodle.org/36/en/index.php?title=Installing_MSSQL_for_PHP&oldid=130224"
Powered by MediaWiki