[Updated November 9th, 2011]
In this post, I’ll show you how to get started with PHP and SQL Azure. I’ll walk you through building a very simple Web page in the same way I did in a recent post: Getting Started with the SQL Server Driver for PHP. That blog entry is very relevant because I used a SQL Server Express database as my data store, and in the development stage of an application, there is almost no difference between a PHP/SQL Server Express application and a PHP/SQL Azure application. There are some differences and limitations, which I’ll highlight, but not many that I encountered. (Note: I won’t go into the benefits of cloud computing in this post, but if you are interested, I would suggest starting here: Cloud Computing.) For more thorough information about SQL Azure, see the SQL Azure documentation and the Windows Azure SDK for PHP.
Updates as of November 9th, 2011:
- The latest version of the SQL Server Drivers for PHP (which includes the sqlsrv and pdo_sqlsrv drivers, both of which provide connectivity to SQL Azure) can be downloaded here: http://www.microsoft.com/download/en/details.aspx?id=20098.
- The latest version of SQL Server Express can be downloaded here: http://www.microsoft.com/download/en/details.aspx?id=26729. I suggest installing SQLEXPADV_x86 or SQLEXPADV_x64 option (depending on your architecture) as the advanced installation provides advanced functionality and tooling.
In a Nutshell
The only difference between the application I built in this post (the post I mentioned earlier) and the one I’ll build here is in the connection code:
Notice three things:
- In the $serverName variable, the server name is prefixed with tcp:, and…
- the connection port is specified (1433).
- The format of the UID element in the connection options array is Username@ServerID.
Note: I’ll show you where I’m getting the value of ServerID and how to set up a username and password in the sections below.
Those are the only changes I needed to make to the application code. So, in the development stage of your application, you can build your application against SQL Server Express. For details about moving the database to the cloud, read on.
PDO connection information: If you want to use PDO, here’s the connection code. Otherwise, using PDO with SQL Azure should be the same as using it with SQL Server.
Create a SQL Azure Server
To create a SQL Azure server, you need a Windows Azure subscription. You can sign up for a free trial here: http://www.microsoft.com/windowsazure/free-trial/. (You’ll need a Windows Live ID to sign up.) For information about other options and pricing details, see http://www.microsoft.com/windowsazure/offers/ and http://www.microsoft.com/windowsazure/pricing/.
After creating your subscription, go to the Windows Azure Management Portal to get started: https://windows.azure.com/. To create your server, click on Database in the left-hand pane…
Next, click on Create a new SQL Azure Server…
Next, you will be taken through the steps to create your server.
Select the subscription with which your server will be associated:
Select the region in which your server will be deployed:
Create the administrator name and password. Note that this is the username and password I referred to in the connection code above:
In the next step, click Add to add a firewall rule that enables access to your server. Note that I’m only allowing access from my current IP address (which you may or may not want to do):
Lastly, check the Allow other Windows Azure services to access this server box and click Finish.
Now, in the left-hand pane of the portal, under your subscription, you should see a server icon with your server ID:
Note that if you click on the server icon, it’s properties will appear in the right-hand pane (making them easy to copy). The server ID you see here is the ServerID I mentioned in the connection code at the start.
Now, on to creating a database…
Create a SQL Azure Database
To create a database, click the Create icon near the top of the portal…
…then fill in the database name, select an edition, and select a size:
You should now see your newly created database among the databases listed for your server:
Install the SQL Server Driver for PHP
The easiest way to access SQL Azure databases from PHP is by using the SQL Server Driver for PHP. I described how to install and configure the driver in this post: Getting Started with the SQL Server Driver for PHP. If you need instructions for installing PHP and configuring your Web server, see Getting Started with PHP on Windows.
Test Connectivity from PHP
To test connectivity to the SQL Azure database we created earlier (ExampleDB), simply execute the connection code above (in the In a Nutshell section), replacing ServerID with your generated server ID, and, of course, Username and Password with your username and password. If you are trying to connect from behind a corporate firewall, you may need to install the Microsoft Firewall Client. You may also need to change the identity of the application pool under which PHP is running to a domain account. For troubleshooting other connection problems, see Using SQL Server Client APIs with SQL Azure 1.0.
Add Tables to the Database
You can create tables in your database by using the database management UI in the Windows Azure Management Portal (just click on the Manage icon near the top of the portal and login). However, you can also execute SQL commands just like you would with SQL Server to create a database, which is what I’ll do here.
To add tables (one table, actually) to the ExampleDB database, execute the following PHP script (after filling in your server ID, username, and password):
If you look closely at the SQL for creating the table, you’ll notice that I am explicitly creating a clustered index on the table, a requirement for all tables in SQL Azure.
Run the Application
Now we can run our application with a SQL Azure database. Copy the following script to a file in your root directory (for more detail about this simple application, see Getting Started with the SQL Server Driver for PHP):
Differences Between SQL Azure and SQL Server
As I mentioned earlier, the only code I needed to change in the code above when changing to a SQL Azure database was the connection code. However, there are some other differences that you might run into when developing a more complex application. I’ll list the two that got me, but for a complete list, see the SQL Azure documentation: Guidelines and Limitations.
- You cannot change databases with the Transact-SQL USE command. To change databases, you must close your open connection and open a new one to the target database.
- Table partitioning is not supported.
That’s it for now. Hopefully, this is enough to get you started with PHP and SQL Azure. Look for more in-depth posts soon about using PHP with the Azure platform. And, as always, please let me know what information I’ve missed (or just what other information you would like to see).