← ServerPilot Docs
To install a PECL extension for multiple PHP versions, repeat the PECL extension installation for each PHP version.
The SQLSRV extension provides functions for accessing Microsoft SQL Server databases.
In late 2016, after more than four years of this extension not being updated, Microsoft began releasing updates to greatly improve this extension and add support for PHP 7. As of early 2017, this extension is now considered stable again.
This extension only supports PHP 7.0, 7.1, and 7.2. PHP 7.3 is not yet supported.
Additionally, this extension only supports Ubuntu 16.04 and 18.04. You will not be able to use this extension on Ubuntu 12.04 or 14.04.
You can follow Microsoft's work on this extension through the PHP SQL Server Team blog.
Installing the SQLSRV Extension on PHP 7.X
In the examples shown, replace "7.X" with your app's PHP version (for example, "7.0").
To install the PHP SQLSRV extension, SSH in to your server as root and run the following commands to install prerequisites:
On Ubuntu 16.04
curl -s //packages.microsoft.com/keys/microsoft.asc | sudo apt-key add - sudo bash -c "curl -s //packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list" sudo apt-get update sudo ACCEPT_EULA=Y apt-get -y install msodbcsql17 mssql-tools sudo apt-get -y install unixodbc-devOn Ubuntu 18.04
curl -s //packages.microsoft.com/keys/microsoft.asc | sudo apt-key add - sudo bash -c "curl -s //packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list" sudo apt-get update sudo ACCEPT_EULA=Y apt-get -y install msodbcsql17 mssql-tools sudo apt-get -y install unixodbc-devNext, run the following commands to install the extension:
sudo apt-get -y install gcc g++ make autoconf libc-dev pkg-config sudo pecl7.X-sp install sqlsrv sudo pecl7.X-sp install pdo_sqlsrvOnce installed, create configuration files for the extension and restart PHP by running the following commands as root:
Possible Errors
If you get the following error while installing prerequisites using the instructions above:
Installation Failed, ODBC Driver 13 for SQL Server Detected!this may be caused by a previous installation of Microsoft's msodbcsql package while the new SQLSRV driver was still in development and not yet released. In this case, the solution is to start over with a clean Ubuntu 16.04 server, connect it to ServerPilot, and follow the PHP SQLSRV extension installation steps again on the new server.
Installing the SQLSRV Extension on PHP 5.X
This extension no longer supports PHP 5.X.
Verifying the Installation
You can verify the SQLSRV extension is installed with the command:
php7.X-sp -i | grep -i sqlsrvIf SQLSRV is installed correctly, you will see the following:
/etc/php7.1-sp/conf.d/pdo_sqlsrv.ini, /etc/php7.1-sp/conf.d/sqlsrv.ini, Registered PHP Streams => https, ftps, compress.zlib, compress.bzip2, php, file, glob, data, http, ftp, zip, phar, sqlsrv PDO drivers => dblib, mysql, odbc, pgsql, sqlite, sqlsrv pdo_sqlsrv pdo_sqlsrv support => enabled pdo_sqlsrv.client_buffer_max_kb_size => 10240 => 10240 pdo_sqlsrv.log_severity => 0 => 0 sqlsrv sqlsrv support => enabled sqlsrv.ClientBufferMaxKBSize => 10240 => 10240 sqlsrv.LogSeverity => 0 => 0 sqlsrv.LogSubsystems => 0 => 0 sqlsrv.WarningsReturnAsErrors => On => OnUninstalling the SQLSRV Extension
To uninstall this extension, as root run the commands:
sudo rm /etc/phpX.Y-sp/conf.d/sqlsrv.ini sudo rm /etc/phpX.Y-sp/conf.d/pdo_sqlsrv.ini sudo peclX.Y-sp uninstall sqlsrv sudo peclX.Y-sp uninstall pdo_sqlsrvNext, restart PHP-FPM with the command:
sudo service phpX.Y-fpm-sp restartUsing the PHP SQLSRV Extension
See the extension's GitHub page for an example of how to use the extension.
Note that the MSDN Online Documentation for this extension has not yet been updated for PHP 7.
Last updated: October 8, 2018
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 enabled3. 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:
4. Install PHP and a web server.
- PHP can be downloaded from www.php.net
- If IIS is used as the web server, IIS 7.0 or later is recommended with FastCGI and WinCache.
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.dllFor PHP 5.3.2 (or later)
[PHP_SQLSRV] extension=php_sqlsrv_53_nts_vc9.dllThe Microsoft documentation for the SQL Server Driver for PHP is available at: //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'; // Usually the 'sa' account (dbowner perms are enough) $CFG->dbpass = 'yourpassword'; $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 (//localhost/test.php).<?php $link = sqlsrv_connect($this->dbhost, array('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
- Errors FAQ
- Installing MSSQL for PHP, about how to install Moodle on SQL*Server using the FreeTDS drivers.
- Using Moodle Installation problems forum
- Microsoft SQL Server Driver for PHP solves major Moodle performance problems. See: Hardware and Performance Forum