PHP7 migration: The SQL Server case

Currently testing a migration to PHP7 (from PHP5.5), I noticed that the management of connections to SQL Server via the native functions (mssql_) disappeared. Using connections to MySQL and SQL Server through our PHP applications, I therefore had to look into the question to see what were the possible solutions to replace these native functions.

PHP7 migration

PHP7 migration

PHP7 has been released for several months now, and is integrated by default for example in Ubuntu 16.04. So we decided to test a migration from Ubuntu 14.04 to Ubuntu 16.04, with a migration to PHP7. We were convinced, among other things, by the very flattering benchmarks for PHP7  and also by the very interesting new features of PHP7 in terms of typing possibilities .
Another point also, the migration to PHP7 is normally quite simple. The main development concerns the overhaul of the engine in depth to improve performance.

Disappearance of native SQL Server functions

Few things change with this migration to PHP7, but looking at the official documentation we see that a number of extensions are disappearing, including the mssql functions. For the others, like ereg and mysql, they were already deprecated for several versions.
These mssql functions allow you to connect and launch queries or stored procedures on a SQL Server. In our case, it is very useful because we have MySQL databases and also SQL Server databases. It is therefore a point which can be blocking for us if ever we do not find an equivalent.

Replacement tracks

If we look at the official documentation, for example that of the mssql_connect function , we see that 3 options are possible:

  • Go through PDO
  • Use the sqlsrv_connect function
  • Go through ODBC

In any case, it is more restrictive than native functions. Because with PDO it will be necessary to pass by an ODBC pilot (thus that joins the solution 3). Finally,   sqlsrv_connect is installed in Windows environment (DLL under IIS).

The conclusion is therefore that whatever solution is chosen, it will in any case be more restrictive than a purely native solution. Afterwards, the idea now is to make it as transparent as possible. And as stable as possible too, because it’s a production environment.

Continuing my research, I came across a package, maintained by Meet Bhagdev , which provides an all-in-one that looks interesting. It’s available on a github .
The installation is not very complicated. On a functional installation, with Apache and PHP already, it comes down to getting the right packages, the ODBC manager, and the extensions for PHP.
In summary, here are the commands I had to run (on Ubuntu 16.04 therefore):

sudo sh

sudo apt-get install php libapache2-mod-php

sudo apt-get install php-pear php-dev

sudo pecl search sqlsrv

sudo pecl install sqlsrv-4.0.5

sudo pecl install pdo_sqlsrv-4.0.5

This has the effect of setting up a PHP extension, with everything needed to communicate with a SQL Server.

The last thing to do was to activate the extension in the php.ini by adding these lines:

[SQL Server]

And the trick was done.

This then gives access, under Linux, to the various sqlsrv_ functions which are described in the documentation . It will work the same as on an IIS.

For example, for a connection to a SQL Server server:

$connectionInfo = array( "Database"=>$this->dbname, "UID"=>$this->dbuser, "PWD"=>$this->dbpass);
$this->sqlLink = sqlsrv_connect( $this->dbhost, $connectionInfo);

Continuation of the tests

For now, I’ve gone with this solution.
It seems very good, but I haven’t finished my various tests yet. In any case, I was able to call queries, update data, launch stored procedures without problems. I have not yet tested, for example, queries with OpenQuery to see what it looks like.
I will update this post according to the result of my tests.

On your side, if you have other ideas or better leads, do not hesitate to react in the comments below.

Leave a Reply