Using PHP, Zend Framework, PDO and FreeTDS in Windows Azure

Earlier I wrote about IPv6-connectivity with MS SQL server into Linux / PHP with FreeTDS.

This time my quest with FreeTDS continued, I put together the minimal possible CentOS 6.4 Linux with enough parts to produce a Nginx / PHP-FPM / Windows Azure SQL Database -based web application. The acronym could be not LAMP, but NPFWASD. No idea how to pronounce "npf-wasd", though.

I packaged a Hyper-V -based Linux .vhd into Azure virtual machine IaaS-image and created couple of load-balanced HTTP-ports into it. The problem was to lure PHP's PDO to connect into Azure SQL via FreeTDS dblib. I spent a good while banging my head and kicking it, before it stopped resisting and started to obey my commands.

Everything would have gone much better, if only I had the proper version of FreeTDS installed into the Linux. When I realized that the TDS-protocol version is hyper-important in Azure SQL, I realised that my FreeTDS-version was not the one it was supposed to be. My own-package would have been the correct one (see the earlier post). My tsql -C says:

Compile-time settings (established with the "configure" script)
                            Version: freetds v0.92.dev.20130721
             freetds.conf directory: /etc
     MS db-lib source compatibility: yes
        Sybase binary compatibility: yes
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 7.1
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: yes

The default TDS version of 7.1 is really, really important there. With that I can do:

tsql -H -my-designated-instance-in-Azure-.database.windows.net \
 -p 1433 \
 -U -the-application-SQL-user-without-admin-rights- \
 -D -my-own-database-in-the-SQL-box-

It simply works, displays the prompt and everything works as it should be. In my Zend Framework application configuration I say:

resources.db.adapter = "Pdo_Mssql"
resources.db.params.host = "-my-designated-instance-in-Azure-.database.windows.net"
resources.db.params.dbname = "-my-own-database-in-the-SQL-box-"
resources.db.params.username = "-the-application-SQL-user-without-admin-rights-"
resources.db.params.password = "-oh-the-top-secret-passwrod-"
resources.db.params.version = "7.1"
resources.db.params.charset = "utf8"
resources.db.params.pdoType = "dblib"

No issues there. Everything works.

I received couple of comments from other people when I announced that I would try such a feat. It appeared that most people are running their own SQL-instances of various kinds because of performance reasons. The Azure SQL -service is definitely not the fastest there is. But what if you're not in a hurry. The service is there, easily available, cheap and functional, even from Linux/PHP.