Linux - PHP 7.0 and MSSQL (Microsoft SQL)

Microsoft has PHP Linux Drivers for SQL Server for PHP 7 and above on PECL. These are production ready. To download them, follow these steps:

Ubuntu 16.04:

sudo su curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.listexitsudo apt-get updatesudo ACCEPT_EULA=Y apt-get install -y msodbcsql mssql-tools unixodbc-devsudo pecl install sqlsrvsudo pecl install pdo_sqlsrvecho "extension=sqlsrv" >> `php --ini | grep "Loaded Configuration" | sed -e "s|.*:\s*||"`echo "extension=pdo_sqlsrv" >> `php --ini | grep "Loaded Configuration" | sed -e "s|.*:\s*||"`

CentOS 7:

sudo sucurl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repoexitsudo yum updatesudo ACCEPT_EULA=Y yum install -y msodbcsql mssql-tools unixODBC-devel sudo yum groupinstall "Development Tools"sudo pecl install sqlsrvsudo pecl install pdo_sqlsrvecho "extension=sqlsrv" >> `php --ini | grep "Loaded Configuration" | sed -e "s|.*:\s*||"`echo "extension=pdo_sqlsrv" >> `php --ini | grep "Loaded Configuration" | sed -e "s|.*:\s*||"`

This will install the PHP SQL Server Drivers and register them in the php.ini folder.

Verify that it works by using the following sample

<?php$serverName = "localhost";$connectionOptions = array(    "Database" => "SampleDB",    "Uid" => "sa",    "PWD" => "your_password");//Establishes the connection$conn = sqlsrv_connect($serverName, $connectionOptions);if($conn)    echo "Connected!"?>

Links for reference:

• https://pecl.php.net/package/sqlsrv

• https://pecl.php.net/package/pdo_sqlsrv

• https://github.com/Microsoft/msphpsql

The sybase of PHP7 contains the pdo_dblib module.

sudo apt install php7.0-sybase

tldr; sqlsrv and pdo_sqlsrv php extentions were very slow with large queries with lots of parameters, but installing and using pdo-dblib resolved the issue for me.

Running on php framework laravel 5.1 and 5.6 (on php 7.1 and 7.2) on Ubunutu 16.04. I found that the packages sqlsrv and pdo_sqlsrv did not work well for large queries. I had a large query with 30 bound variables. Sql Server 2008 converted all of the bound variables to nvarchar(4000) causing the db to do loads of casting taking forever.

I then disable the sqlsrv.so and pdo_sqlsrv.so extentions and installed pdo-dblib extension with:

sudo apt-get install php7.2-pdo-dblib

Then the query processed much quicker.

For more information:

Under the hood laravel uses a PDOStatement like this:

$conn = new PDO( "dblib:host=$host:1433;dbname=$db;", $uid, $pwd);$stmt = $conn->prepare( $query );$stmt->execute($param);

where a direct query like

$conn = new PDO( "dblib:host=$host:1433;dbname=$db;", $uid, $pwd);$results = $conn->query( $query_with_parameter_already_bound );

would work fine.