The cloud is the newest buzzword in all things development, and there are many different options for utilizing the nebulous cloud. Amazon Web Services, Microsoft Azure, and Digital Ocean (just to name a few) are some providers that give you various levels of cloud computing. While all three give you Virtual Private Hosting, Microsoft has stepped up their offering.
For those unfamiliar, Azure encompasses everything “cloud”. It allows you to do virtual hosting at various levels, but they also have split up most services a server can offer into individual products. You need a web server? Spin one up. You need a database? Done. You need a back end for a mobile app? They have you covered.
One of the advantages of using their cloud services is the uptime and scalability. With this power, anyone can have an enterprise level data center without having to pay to run one. For one, being able to utilize a SQL cluster for any application is invaluable, especially if you don’t have to pay the set up or maintenance costs.
With this in mind, let’s explore connecting a PHP application to an Azure Microsoft SQL database.
Getting Started
The thing to keep in mind when using cloud services is that you access the resources just like you would if they were in your data center – the only difference is the addresses that are used. With that in mind, you need to have the proper drivers for PHP to connect to the Microsoft SQL database. If you are using a windows based server, this is pretty easy because Microsoft provides a PHP driver for you (which is very well documented, and comes with lots of examples on how to connect to Microsoft SQL servers both local and in Azure). The tricky part is utilizing a Linux based server to do the same thing. There are some open drivers out there, but using them to access Azure gets tricky.
The Driver
To get your PHP application talking to a Microsoft SQL database, you need to use the FreeTDS driver. The best bet is to just get the newest version of the driver (trust me, save yourself some pain). Most modern distributions have the FreeTDS driver neatly packaged, so just use that.
Configuring the driver is a bit different depending on the server to which you are connecting. To give some background, the FreeTDS driver is set up to communicate to all flavors of Microsoft SQL Server. By default (as of 2014) most distributions of Linux have it set to compatibility mode. If you want to connect to a SQL Server 2008 database (This is what Azure runs currently) we need to tweak a few things.
The Configuration File
When you edit the FreeTDS config file, you will notice a “tds version” line. Most of the time, this will be set to 4.2, but to connect to SQL Server 2008, this needs to be set to 7.2. You can do this either in the [global] section, or create individual server blocks:
[global]
tds version = 4.2
[myserver]
host = my.server.com
instance = foo
tds version = 7.2 At this point, if your app were connecting to a local SQL Server you could use the standard PHP MSSQL functions. However, things are a bit different with Azure.
Azure Specifics
One thing about Azure databases is that the connection string you are given ONLY works for your database. With that in mind, certain commands are disabled – like USE. This becomes a problem only because the standard MSSQL functions in PHP create a connection, then execute a USE statement to switch to the database you want to work with – which gloriously fails in Azure.
To get around this limitation, you have to use something that allows you to specify a database in the connection string, such as PDO. When PDO connects to the Azure database server, it connects directly to the database and is ready to go. Just use standard PDO functions to get your data.
Summary
In summary, to access Azure databases in PHP from a Linux machine, you need to make sure you do the following:
Skipping either of these steps will result in hours of frustration and bizarre error messages… trust me.
