Difference between revisions of "Using the Microsoft SQL Server Driver for PHP"

Jump to: navigation, search
m (Typos)
m (Installation overview)
 
(13 intermediate revisions by 7 users not shown)
Line 1: Line 1:
{{Moodle 2.0}}
+
{{Installing Moodle}}
 
== Using the SQL Server Driver for PHP from Microsoft ==
 
== Using the SQL Server Driver for PHP from Microsoft ==
  
 
== Introduction ==
 
== Introduction ==
This short manual is for running Moodle 2.0 (and upwards) using Microsoft SQL Server (MSSQL) RDBMS. Steps detailed below must be performed '''before''' installing Moodle itself.
+
This short manual is for running Moodle 2.0 (and upwards) using the Microsoft SQL Server (MSSQL) RDBMS. The steps detailed below must be performed '''before''' installing Moodle itself.
 +
 
 +
First of all, the minimum required version of MSSQL has been stabilized to MSSQL 2005 (v.9).
  
First of all, minimum required version of MSSQL has been stabilized to MSSQL 2005 (v.9).
 
 
== Installation overview ==
 
== Installation overview ==
 
1. Install Microsoft SQL Server including SQL Server Management Studio. ([http://www.microsoft.com/sql/editions/express/default.mspx A free version, SQL Server Express Edition] is available for testing.)
 
1. Install Microsoft SQL Server including SQL Server Management Studio. ([http://www.microsoft.com/sql/editions/express/default.mspx A free version, SQL Server Express Edition] is available for testing.)
Line 13: Line 14:
 
:By default, MSSQL listens to port 1433 for incoming TCP/IP connections and this port needs to be opened in the firewall. This is explicitly configured in the firewall installed (either Windows Firewall in the Control Panel or the configuration interface for other firewalls). If the port was changed when MSSQL was installed, then specify the correct port number to open in the firewall.
 
:By default, MSSQL listens to port 1433 for incoming TCP/IP connections and this port needs to be opened in the firewall. This is explicitly configured in the firewall installed (either Windows Firewall in the Control Panel or the configuration interface for other firewalls). If the port was changed when MSSQL was installed, then specify the correct port number to open in the firewall.
 
:Confirm the TCP/IP protocol is enabled in: '''SQL Server Configuration Manager''' -> '''Network Configuration''' -> '''Protocols''' -> '''TCP/IP enabled'''
 
:Confirm the TCP/IP protocol is enabled in: '''SQL Server Configuration Manager''' -> '''Network Configuration''' -> '''Protocols''' -> '''TCP/IP enabled'''
*MSSQL is usually installed with port 1433 as the default.
 
  
 
3. Create and configure a new database.
 
3. Create and configure a new database.
Line 31: Line 31:
  
 
5. Install the SQL Server Driver for PHP.
 
5. Install the SQL Server Driver for PHP.
:On the web server, install [http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=ccdf728b-1ea0-48a8-a84a-5052214caad9 SQL Server Driver for PHP] including all the pre-requisites listed on the download page. Note: it is critical to install the SQL Server Native Access Client version documented on the download page of the SQL Server Driver for PHP.
+
:On the web server, install [http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=20098 SQL Server Driver for PHP] including all the pre-requisites listed on the download page. Note: it is critical to install the SQL Server Native Access Client version documented on the download page of the SQL Server Driver for PHP.
 
:Configure PHP to use the appropriate SQLSRV driver. In php.ini, set the following:
 
:Configure PHP to use the appropriate SQLSRV driver. In php.ini, set the following:
  
Line 46: Line 46:
 
  [PHP_SQLSRV]
 
  [PHP_SQLSRV]
 
  extension=php_sqlsrv_53_nts_vc9.dll
 
  extension=php_sqlsrv_53_nts_vc9.dll
 +
</code>
 +
 +
For PHP 5.6.24 (or later)
 +
 +
<code>
 +
[PHP_SQLSRV]
 +
extension=php_sqlsrv.dll
 
</code>
 
</code>
  
Line 56: Line 63:
 
<code php>
 
<code php>
 
$CFG->dbtype    = 'sqlsrv';        // Required
 
$CFG->dbtype    = 'sqlsrv';        // Required
$CFG->dbhost    = 'localhost';      // Assuming MSSQL is on the same server, otherwise use an IP address
+
$CFG->dbhost    = 'localhost';      // Assuming that MSSQL is on the same server, otherwise  
 +
                                    // use the actual name or IP address of your database server
 
$CFG->dbname    = 'moodle';        // The name of the newly created Moodle database
 
$CFG->dbname    = 'moodle';        // The name of the newly created Moodle database
$CFG->dbuser    = 'yourusername';  // Usually the 'sa' account (dbowner perms are enough)
+
$CFG->dbuser    = 'yourusername';  // your database username
$CFG->dbpass    = 'yourpassword';
+
$CFG->dbpass    = 'yourpassword';   // your database password
 
$CFG->dbpersist =  true;
 
$CFG->dbpersist =  true;
$CFG->prefix    = 'mdl_';          // The prefix can be changed per individual preferences, but NEVER leave this blank.
+
$CFG->prefix    = 'mdl_';          // The prefix can be changed per individual preferences,  
 +
                                    // but NEVER leave this blank!
 
</code>
 
</code>
  
Line 74: Line 83:
 
<code php>
 
<code php>
 
<?php
 
<?php
$link = sqlsrv_connect($this->dbhost, array('UID' => 'db_user', 'PWD' => 'db_password'));
+
$link = sqlsrv_connect($this->dbhost, array('DATABASE'=>'db_name', 'UID' => 'db_user', 'PWD' => 'db_password'));
 
if($link === FALSE) {
 
if($link === FALSE) {
 
     echo 'Could not connect';
 
     echo 'Could not connect';
Line 87: Line 96:
  
 
== See Also ==
 
== See Also ==
* [[Installing Moodle]]
 
 
* [[Errors FAQ]]
 
* [[Errors FAQ]]
 +
* [[Installing MSSQL for PHP]], about how to install Moodle on SQL*Server using the FreeTDS drivers.
 
* Using Moodle [http://moodle.org/mod/forum/view.php?id=28 Installation problems forum]
 
* Using Moodle [http://moodle.org/mod/forum/view.php?id=28 Installation problems forum]
* [[Beginning Administration FAQ]]
+
* Microsoft SQL Server Driver for PHP solves major Moodle performance problems. See: [http://moodle.org/mod/forum/discuss.php?d=183987 Hardware and Performance Forum]
* [[Installing Oracle for PHP]]
 
  
[[Category:FAQ]]
 
[[Category:Installation]]
 
[[Category:Developer]]
 
 
[[Category:XMLDB]]
 
[[Category:XMLDB]]
[[Category:DB]]
 

Latest revision as of 12:34, 20 December 2016

Using the SQL Server Driver for PHP from Microsoft

Introduction

This short manual is for running Moodle 2.0 (and upwards) using the Microsoft SQL Server (MSSQL) RDBMS. The steps detailed below must be performed before installing Moodle itself.

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

Installation overview

1. Install Microsoft SQL Server including SQL Server Management Studio. (A free version, SQL Server Express Edition is available for testing.)

Make sure to choose mixed authentication (Windows and local accounts) to keep things simpler later. Define the "sa" account password when requested (it's the default System Administrator account which has full access to all databases by default).

2. Configure Windows for MSSQL.

By default, MSSQL listens to port 1433 for incoming TCP/IP connections and this port needs to be opened in the firewall. This is explicitly configured in the firewall installed (either Windows Firewall in the Control Panel or the configuration interface for other firewalls). If the port was changed when MSSQL was installed, then specify the correct port number to open in the firewall.
Confirm the TCP/IP protocol is enabled in: SQL Server Configuration Manager -> Network Configuration -> Protocols -> TCP/IP enabled

3. Create and configure a new database.

Open "SQL Server Management Studio" and create a new empty database.
  • Execute the following command to enable Row Versioning:
 USE MASTER
 GO
 ALTER DATABASE &lt;your-database-name&gt; SET READ_COMMITTED_SNAPSHOT ON
 GO

4. Install PHP and a web server.

5. Install the SQL Server Driver for PHP.

On the web server, install SQL Server Driver for PHP including all the pre-requisites listed on the download page. Note: it is critical to install the SQL Server Native Access Client version documented on the download page of the SQL Server Driver for PHP.
Configure PHP to use the appropriate SQLSRV driver. In php.ini, set the following:

For PHP 5.2.4 (or later)

 [PHP_SQLSRV]
 extension=php_sqlsrv_52_nts_vc9.dll

For PHP 5.3.2 (or later)

 [PHP_SQLSRV]
 extension=php_sqlsrv_53_nts_vc9.dll

For PHP 5.6.24 (or later)

[PHP_SQLSRV]
extension=php_sqlsrv.dll

The Microsoft documentation for the SQL Server Driver for PHP is available at: http://msdn.microsoft.com/en-us/library/ee229548(v=SQL.10).aspx

6. Install and configure Moodle.

Continue with the standard Moodle installation.

7. The Moodle config.php should include lines like these:

$CFG->dbtype    = 'sqlsrv';         // Required
$CFG->dbhost    = 'localhost';      // Assuming that MSSQL is on the same server, otherwise 
                                    // use the actual name or IP address of your database server
$CFG->dbname    = 'moodle';         // The name of the newly created Moodle database
$CFG->dbuser    = 'yourusername';   // your database username
$CFG->dbpass    = 'yourpassword';   // your database password
$CFG->dbpersist =  true;
$CFG->prefix    = 'mdl_';           // The prefix can be changed per individual preferences, 
                                    // but NEVER leave this blank!
If the config.php file doesn't exist, it will be generated as normal from the Moodle installer. Alternatively, use the config-dist.php file that comes with the Moodle package to create a custom config.php file.

8. Restart or start your web server.

If Moodle still cannot communicate with the database server, turn display_startup_errors to "On" in the /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.

9. Test the database connection.

Try this PHP script, just add a text file called test.php from the example below and change ('localhost', 'db_user', 'db_password') to align with the config.php settings, and load from local host (http://localhost/test.php).
<?php
$link = sqlsrv_connect($this->dbhost, array('DATABASE'=>'db_name', 'UID' => 'db_user', 'PWD' => 'db_password'));
if($link === FALSE) {
    echo 'Could not connect';
    die('Could not connect: ' . sqlsrv_errors(SQLSRV_ERR_ALL));
}
echo 'Successful connection';
sqlsrv_close($link);
?>

10. Complete the rest of the Moodle installation as usual.

See Also