Build new applications in the cloud - or use interoperable services that run on Microsoft infrastructure to extend and enhance your existing applications. You choose what's right for you.

Table of Contents

 

Lab 3 — SQL Azure

 

In this lab, you will learn how to use SQL Azure.

 

 

Task 0 — Prerequisites

 

Before doing this lab, if you not done so:

 

  1. Complete install of Windows Azure for Eclipse
  2. Complete introduction lab Getting Started

  3. This lab requires that you register for a developer account in order perform tasks pertaining to Windows Azure Platform account.

     

    To register, please visit Windows Azure Platform — Account Information.

  4. Once the Windows Azure Platform account is created, you will need to create one SQL Azure server by using the SQL Azure portal.
  5. Get SQL Azure Lab:   Download
 Note:

 

It is important to note that this lab requires you to estabilish a SQL Azure account within Windows Azure to perform this lab.

 

Unlike Blob and Table storage labs, which can be performed using locally hosted Development Storage, SQL Azure can only be hosted within Windows Azure.

 

 

 

Task 1 — Microsoft SQL Azure Database

 

What is SQL Azure?

In short, SQL Azure is simply a repackaging of SQL Server for the Windows Azure Cloud enviroment. SQL Services and SQL Data Services are now known as Microsoft SQL Azure and SQL Azure Database. There are a few changes, but fundamentally Microsoft’s plans to extend SQL server capabilities in cloud as web-based services remain intact. SQL Azure will continue to deliver an integrated set of services for relational databases. The reporting, analytics and data synchronization with end-users and partners also remains unchanged. This makes it most appealing to current users of SQL Server.

 

   Read more: MSDN: Introducing SQL Azure Database

 

SQL Azure vs. SQL Server

SQL Server database technologies were used to build SQL Azure. Specifically, the technologies used in critical enterprise and web applications are included. The extensive data platform of SQL Server is capable of handling all data types and the SQL Azure platform introduces many associated capabilities. New relational functionalities are included and extended as services in the cloud.

 

Highly Availability (HA), self-management and ease-of-use are the highlights of the new service. While only the core RDBMS capabilities of the full SQL Server data platform are presently included, more services are expected to be introduced in the future. Reporting, analytics and ETL will no doubt be available over time. As SQL Server and SQL Azure share the same technologies, bilateral innovation can also be anticipated.

 

SQL Azure Database

The SQL Azure database service offers a scalable and distributed database hosted in the cloud, and therefore highly available. As highly available, backup and recovery, geo-distribution and disaster recovery are built-in, developers do not need to manage any software, but in the case of a dedicated hosted database, they will still be responsible for database software, i.e. for the installation and tasks related to OS and database software.

 

   Read more: MSDN: SQL Azure Overview

 

 

Task 2 — SQL Server for PHP

Overview

First, it is important to note that Windows Azure SDK for PHP has nothing to do with SQL Server for PHP. Windows Azure SDK for PHP is exclusively for handling Windows Azure Storage Services.

 

The SQL Server Driver for PHP relies on the Microsoft SQL Server Native Client to communicate with SQL Server.

 

   Read more: MSDN: SQL Server Native Client

 

 Note:

When creating a Windows Azure PHP Project using data storage option of SQL Azure, SQL Server Driver for PHP is included with project.

 

SQL Server Driver for PHP 1.0

The SQL Server Driver for PHP 1.0 is designed to enable reliable, scalable integration with SQL Server for PHP applications deployed on the Windows platform. The Driver for PHP is a PHP 5 extension that allows the reading and writing of SQL Server data from within PHP scripts.

 

It provides a procedural interface for accessing data in all Editions of SQL Server 2005 and SQL Server 2008 (including Express Editions), and makes use of PHP features, including PHP streams to read and write large objects.

 

   SQL Server Driver is available at: Codeplex: Microsoft SQL Server Driver for PHP

 

SQL Server Driver for PHP 1.1

This extension to SQL Server Driver for PHP provides a procedural interface for accessing data in all editions of SQL Server 2005, 2008, and 2008 R2 (CTP).

 

   SQL Server Driver is available at: SQL Server Driver for PHP 1.1 - October 2009

 

 

Task 3 — Create Windows Azure PHP Project for SQL Azure

 

In this task, you will create a Windows Azure PHP Project that uses SQL Azure as Data storage option.

 

  1. Start Eclipse with Windows Azure PHP Projects plug-in installed.
  2. Assign or Create workspace folder as Eclipse starts.
  3. Change Eclipse perspective from PHP to PHP Windows Azure.
    1. In Eclipse menu-bar, select Window menu.
    2. In Window menu, select Open Perspective menu-item.
    3. In Open Perspective menu-item, select Other... cascading menu-item.
    4. In Open Perspective panel, select PHP Windows Azure
  4. In the PHP Windows Azure perspective, create a new Windows Azure Web Project.
    1. In Eclipse menu-bar, select File menu.
    2. In File menu, select New menu-item.
    3. In New menu-item, select Windows Azure Web Project cascading menu-item.
    4. In new PHP Azure Project panel:
      1. Provide project name SQLAzureStorage
      2. In Data Storage Options, select SQL Azure
      3. Enable JavaScript support.
      4. Select Finish button

  5. Note that in the PHP Explorer, examine the structure of the created solution.

    Two PHP projects have been created:

    • SQLAzureStorage
    • SQLAzureStorage_WebRole

 

Task 4 — Sample Files

 

In this task, your will examine the default files that are provided when Windows Azure PHP Project that uses SQL Azure as Data storage option is created.

 

  1. Select and Expand PHP project SQLAzureStorage_WebRole, and note its contents:
  2. The default sample files for SQL Azure PHP project are:
    1. index.php – Essentially a simple phpinfo() page and provides a link to perform Windows Azure blob storage operations by loading boiler-plate PHP sample file SQLAzureSample.php.
    2. SQLAzureSample.php – This sample will connect to provided SQL Server host and create Database TEST.
    3. To be created connect.php – SQL Server connection properties.
 Note:

 

It is important to note that SQLAzureSample.php is dependent upon the user in providing connection information to the SQL Server host.

 

SQLAzureSample.php references connect.php which is created when providing SQL Server connection properties to this Windows Azure PHP project. (To be discussed later).

 

If these SQL Server connection properties are not provided, then SQLAzureSample.php will fail.

 

 

 

Task 5 — Setup to use SQL Azure

The web application default document index.php can run Development Fabric or Windows Azure Cloud.

However, to run SQLAzureSample.php requires SQL Azure connection withing Windows Azure Cloud.

 

Once you have create a SQL Azure account, then you may proceed to gather necessary connection infomation when providing SQL Azure properties to your WebRole PHP project.

 

Let's get started an open your created SQL Azure account...

SQL Azure portal within Windows Azure Cloud

SQL Azure Database Connection Settings

 

  1. Within your SQL Azure account:
    1. Select tab labeled

      Databases

    2. Select database labeled master
    3. Select now enabled bottom button labeled Connection Strings
    SQL Azure Databases

  2. Within Connection Strings information for master database are two items that you need to record under section labeled ADO.NET:

    • Server value (excluding tcp:): foobar.database.windows.net
    • User ID value
      • : myAdmin@foobar

     

    Do not record Password value myPassword from this page. It is a filler value to keep the actual password for User ID myAdmin@foobar private. You must know the real password that was provided to your User ID.

     

    SQL Azure Master Database Connection Strings
    3.  Note:

     

    For this example, the values for Server foobar and User ID myAdmin@foobar are not from an actual account. This information must come from your SQL Azure account.

     

     

SQL Azure Firewall Settings

 

Next, for SQLAzureSample.php to access you SQL Azure account, Firewall setting must be configured to permit access from your IP address.

   Read more: SQL Azure Firewall

 

Within your SQL Azure account:

  1. Select tab labeled Firewall Settings

    SQL Azure Firewall Settings
  2. Select bottom button Add Rule

  3. In panel labeled Add Firewall Rule, notice contents within Your IP address: is 131.107.0.73

     

    Since IP address changes, provide a reasonable range for your Firewall settings will allow SQL Azure access by your project.

     

    For example, from 131.107.0.1 to 131.107.0.255

     

    Adding SQL Azure Firewall Setting Rule

Setting SQL Azure Properties in Windows Azure Project

 

Now that your SQL Azure account is available and you have recorded the Connection Strings settings for Server and User ID, you may begin to set the SQL Azure connection values to Windows Azure WebRole PHP project.

 

  1. Right click you WebRole PHP project and select Properties menu item.

     

    Selecting WebRole Properties

  2. Within Properties panel for WebRole PHP project, scroll down and find property tree labeled Windows Azure, expand it and select SQL Azure.

     

    This will present SQL Azure panel whereby you provide the previously recorded SQL Azure connection settings.

     

    SQL Azure properties panel
  3. In this example, insert the values into SQL Azure panel as follows from top to bottom:
    • Host: Server value foobar.database.windows.net
    • Database: master
    • Username: User ID value myAdmin@foobar
    • Password: Private password to access User ID myAdmin@foobar

  4. In SQL Azure properties panel, select button labeled Test Connection.

     

    Test connection to SQL Azure successful

  5. If testing connection is successful, then within SQL Azure properties panel select button labeled Apply.

     

    If testing connection is not successful, then read details in displayed error dialog and validate all settings are correct.

     

  6. To complete, within SQL Azure properties panel select button labeled OK.

     

  7. If testing connection with your SQL Azure account was successful, then a new PHP file is added to your SQL Azure PHP project labeled connect.php

     

    SQL Azure PHP Project with new connect.php

     

    PHP file connect.php contains all the SQL Azure connection information that you had added to SQL Azure properties panel previously:

     

    <?php
// ===Created by Azure PDT===
$host = "foobar.database.windows.net";
$dbname = "master";
$dbuser = "myAdmin@foobar";
$dbpwd = "myPassword";
$driver = "{SQL Server Native Client 10.0}";

     

  8. Now that PHP file connect.php has been generated, then can PHP file SQLAzureSample.php can run because it now has the required SQL Azure connection settings to perform its routines.

    9.  

 

 

Task 6 — Running SQL Azure Project in Dev Fabric

 

Assuming that you successfully completed the last section, whereby you were able to establish a connection to your SQL Azure account and PHP file connect.php was created, then launch your Windows Azure project in Dev Fabric.

  1. Select your Windows Azure project.

  2. From menu Windows Azure, select menu-item Run in Development Fabric

    Run SQL Azure PHP Project in Development Fabric

  3. Development Fabric will deploy you SQL Azure PHP project into the next available port of the localhost address http://127.0.0.1/.

     

    In this example, the highest port taken by localhost address is 82, http://127.0.0.1:82/.

     

    Thereby, Development fabric will deploy this project to port 83 of localhost address, http://127.0.0.1:83/.

     

    Default page index.php of deployed SQL Azure project.

     

  4. In default page index.php, select link at Click here.

     

    This will open PHP file SQLAzureSample.php and if connection to SQL Azure is successful will query master database and created TestDB.

     

    SQL Azure sample output.

 

Summary

 

In this lab, you have learned how to...

  1. Created a Windows Azure PHP project using SQL Azure storage.

     

  2. With your SQL Azure account within Windows Azure cloud:

    • Gathered SQL Azure connection settings.
    • Added Firewall Settings in order for your Windows Azure PHP project to access SQL Azure.

     

  3. Supplied SQL Azure connection settings to your Windows Azure PHP project's properties' SQL Azure.

     

  4. Tested Windows Azure PHP project could succesfully connect ot SQL Azure.

     

  5. Ran this project within Development Fabric and validated that SQL Azure sample code, SQLAzureSample.php, accessed SQL Azure within Windows Azure cloud.

     

 

 

Good job!! You are done with this Lab.