Free trial *Internet Service Required

How to Access Windows Azure SQL Database from PHP

This guide will show you the basics of using Windows Azure SQL Database from PHP. The samples are written in PHP. The scenarios covered include creating a SQL Database and connecting to a SQL Database. This guide covers creating a SQL Database from the Management Portal. For information about performing these tasks from the production portal, see Getting Started with PHP and SQL Database. For more information, see the Next Steps section.

What is Windows Azure SQL Database

Windows Azure SQL Database provides a relational database management system for Windows Azure, and is based on SQL Server technology. With SQL Database, you can easily provision and deploy relational database solutions to the cloud, and take advantage of a distributed data center that provides enterprise-class availability, scalability, and security with the benefits of built-in data protection and self-healing.

Table of contents

Concepts

Because Windows Azure SQL Database is built on SQL Server technologies, accessing SQL Database from PHP is very similar to accessing SQL Server from PHP. You can develop an application locally (using SQL Server) and then connect to SQL Database by changing only the connection string. However, there are some differences between SQL Database and SQL Server that could affect your application. For more information, see Guidelines and Limitations (SQL Database).

The recommended approach for accessing SQL Database from PHP is to use the Microsoft Drivers for PHP for SQL Server. (The examples in this article will use these drivers.) The Microsoft Drivers for PHP for SQL Server work on Windows only.

How to: Setup your environment

The recommended way to set up your development environment is to use the Microsoft Web Platform Installer. The Web Platform Installer will allow you to choose elements of your web development platform and automatically install and configure them. By downloading the Web Platform Installer and choosing to install WebMatrix, PHP for WebMatrix, and SQL Server Express, a complete development environment will be set up for you.

Alternatively, you can set up your environment manually:

How to: Create a SQL Database

Follow these steps to create a Windows Azure SQL Database:

  1. Login to the Management Portal.

  2. Click the + NEW icon on the bottom left of the portal.

    Create New Windows Azure Web Site

  3. Click DATA SERVICES, SQL DATABASE then CUSTOM CREATE.

    Custom Create a new SQL Database

  4. Enter a value for the NAME of your database, select the EDITION (WEB or BUSINESS), select the MAX SIZE for your database, choose the COLLATION, and select NEW SQL Database Server. Click the arrow at the bottom of the dialog. (Note that if you have created a SQL Database before, you can choose an existing server from the Choose a server dropdown.)

    Fill in SQL Database settings

  5. Enter an administrator name and password (and confirm the password), choose the region in which your new SQL Database will be created, and check the Allow Windows Azure Services to access the server box.

    Create new SQL Database server

To see server and database information, click SQL Databases in the Management Portal. You can then click on DATABASES or SERVERS to see relevant information.

View server and database information

How to: Get SQL Database connection information

To get SQL Database connection information, click on SQL DATABASES in the portal, then click on the name of the database.

View database information

Then, click on View SQL Database connection strings for ADO.NET, ODBC, PHP, and JDBC.

Show connection strings

In the PHP section of the resulting window, make note of the values for SERVER, DATABASE, and USERNAME. Your password will be the password you used when creating the SQL Database.

How to: Connect to a SQL Database instance

The following examples show how to use the SQLSRV and PDO_SQLSRV extensions to connect to a SQL Database called testdb. You will need information obtained from the section above. Replace SERVER_ID with your 10-digit server ID (which is the fist 10 characters from the SERVER value obtained in the section above), and assign the correct values (your user name and password) to the $user and $pwd variables.

SQLSRV
$server = "tcp:<value of SERVER from section above>";
$user = "<value of USERNAME from section above>"@SERVER_ID;
$pwd = "password";
$db = "testdb";

$conn = sqlsrv_connect($server, array("UID"=>$user, "PWD"=>$pwd, "Database"=>$db));

if($conn === false){
    die(print_r(sqlsrv_errors()));
}
PDO_SQLSRV
$server = "tcp:<value of SERVER from section above>";
$user = "<value of USERNAME from section above>"@SERVER_ID;
$pwd = "password";
$db = "testdb";

try{
    $conn = new PDO( "sqlsrv:Server= $server ; Database = $db ", $user, $pwd);
    $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
}
catch(Exception $e){
    die(print_r($e));
}

Next steps

As mentioned earlier, using SQL Database is very similar to using SQL Server. Once you have established a connection to a SQL Database (as shown above), you can then use the SQLSRV or PDO_SQLSRV APIs for inserting, retrieving, updating, and deleting data. For information about the SQLSRV and PDO_SQLSRV APIs, see the Microsoft Drivers for PHP for SQL Server documentation. There are, however, some differences between SQL Database and SQL Server that could affect your application. For more information, see Guidelines and Limitations (SQL Database).

Rss Newsletter