• Article

Section 1: Creating SQL Azure Database

Overview

In this lab you will create a new SQL Azure database or use an existing one and ensure it is configured correctly. This database will then be populated with a schema and sample data used throughout this lab.

Objectives

In this lab, you will:

  • Learn to create a SQL Azure database.
  • Learn to create a schema for a SQL Azure database.
  • Learn to create sample data for a SQL Azure database.

System Requirements

You must have the following items to complete this lab:

Setup

The Windows Azure SDK (included in Windows Azure Tools for Visual Studio) installs a simulation environment on your development machine for testing Azure applications locally before deploying them to the cloud. The simulation environment consists of the development fabric to host web and worker roles, and the development storage which simulates cloud blob, table and queue storage locally.

Development storage uses SQL Server as its underlying storage mechanism, and by default the SDK will attempt to configure it to use SQL Server Express. If you do not have SQL Server Express installed before installing the SDK, or you wish to simply use an existing SQL Server instance to host the development storage database, you must run the dsinit command to select the SQL Server instance where the database will be created.

Please see instructions below for how to run dsinit.

Using dsinit to Configure Development Storage

  1. Open a command prompt.
  2. Edit the following command line as appropriate for your environment, where [AzureSDKInstallDrive] is the drive where you installed the Azure SDK (or Windows Azure Tools for Visual Studio), and [YourSqlInstance] is the SqlServer where you want to create the development storage database.[AzureSDKInstallDrive]\ Program Files\Windows Azure SDK\v1.3\bin\devstore\dsinit.exe /sqlinstance:[YourSqlInstance]Example Command Line:“C:\Program Files\Windows Azure SDK\v1.3\bin\devstore\dsinit.exe” /sqlinstance:.
  3. Note that the sample command line above uses the value “.” for the sqlinstance argument, which specifies that the local default SQL instance will be used for development storage.

Exercises

This Hands-On Lab is comprised of the following exercises:

  1. Create or configure a SQL Azure database
  2. Create a database schema for the SQL Azure database
  3. Load sample data into the SQL Azure database schema

Estimated Time to complete this lab: 30 minutes

Exercise 1: Create/Configure a SQL Azure Database

In this exercise, you will create a new SQL Azure database or use an existing one created in a previous lab. You will then modify the database firewall to ensure you can easily connect and interact with the data in the database.

Note:
If you already created a SQL Azure database server in a previous lab, you can skip Task 1 and jump to Task 2 and simply reuse the existing database server from the previous lab.

Task 1 – Creating a SQL Azure Database Server

In this task you will create a SQL Azure database server. You will need the login details for a valid Windows Azure subscription to complete this task.

  1. Login to the Windows Azure portal.
    1. Navigate to the Windows Azure site and login by selecting the Sign in to the Management Portal link the top navigation.
    2. Sign in using valid Windows Azure subscription credentials.
  2. Create a new SQL Azure Database Server.

    1. In the Windows Azure Platform management portal, click Database in the lower-left navigation:

      Figure 1

      SQL Azure Database Navigation

    2. The left pane contains a Subscriptions folder. If that folder is empty, create a new subscription using the New button in the top navigation. If it contains a subscription, proceed to the next step.

    3. Expand the Subscriptions navigation tree and select the existing subscription or the one created in the previous step.

      Figure 2

      Windows Azure Subscription

    4. Select the subscription and click the Create button in the Server group of the top navigation:

      Figure 3

      Create new SQL Azure Database Server

      1. When prompted, select either of the regions in the US and click Next.

      2. Enter the login credentials for an administrator account (this is like the sa account in SQL Server):

        Administrator Login: SPAzureAdmin

        Password: Pass@word1

        Repeat Password: Pass@word1

        Figure 4

        Create new SQL Azure Database Administrator Login

Task 2 – Configuring the SQL Azure Database Firewall

In this task, you ensure that the firewall configuration of the SQL Azure database is setup so that the custom application will be able to connect to it.

  1. Add a single SQL Azure firewall rule by clicking Add.

  2. Enter the IP of the workstation where you are doing this lab to grant this machine access to the SQL Azure server and click OK.

    Figure 5

    Create new SQL Azure Database Administrator Login

    1. Add a SQL Azure firewall rule by checking the box that grants Windows Azure services access to the database.
    2. Check the box Allow other Windows Azure services to access this server.
    3. Click Finish to create the SQL Azure server
      Note:
      Write down the name of the new SQL Azure Database Server as you will need this later when creating a connection string.

Task 3 – Configuring the SQL Azure Database

In this task, you create a new SQL Azure database for use in the remainder of this lab.

Note:
If you already created a SQL Azure database in a previous lab, you can skip Task 3 and simply reuse the existing database server from the previous lab.
  1. Create new SQL Azure Database.
    1. Expand the tree navigation on the left to show the new SQL Azure database server and master database.

    2. Select the database server and click Create in the Database section of the top navigation:

      Figure 6

      Create new SQL Azure Database

    3. When prompted for the database details, use the following:
      • Database Name: SPAzureKitDb
      • Edition: Web
      • Maximum Size: 1GB
      Note:
      Write down the name of the new SQL Azure Database as you will need this later when creating a connection string.

Exercise 2: Create a Database Schema

In this exercise, you will create a schema in your database that will store information about African Cichlids, a type of tropical fish.

Task 1 – Connecting to the SQL Azure Database

In this task, you will connect ot the SQL Azure Database

  1. Login to the Windows Azure portal.
    1. Navigate to the Windows Azure site and login by selecting the Sign in to the Management Portal link the top navigation.
    2. Sign in using valid Windows Azure subscription credentials.
  2. Connect to the SQL Azure Database.

    1. In the Windows Azure Platform management portal, click Database in the lower-left navigation:

      Figure 7

      SQL Azure Database Navigation

    2. The left pane contains a Subscriptions folder. If that folder is empty, create a new subscription using the New button in the top navigation. If it contains a subscription, proceed to the next step.

    3. Expand the Subscriptions navigation tree until you see the name of your database. Select this database.

      Figure 8

      Windows SQL Azure Database

  3. After selecting the database, in the top navigation click the Manage button to launch the database manager.

    Figure 9

    Connect to Windows SQL Azure Database

  4. When the Database Manager login prompt appears, enter the credentials you created in the previous lab to connect to the database.

    Figure 10

    Connect to SQL Azure Database

Task 2 – Creating Database Schema

In this task, you will create a simple schema that will contain the data used in the reminder of this lab.

  1. Click the New Query button in the top navigation.

    Figure 11

    Creating a SQL Azure Datbase Query

  2. Enter the following T-SQL script in and press the Execute button in the top navigation:

    T-SQL

    CREATE TABLE [dbo].[AfricanCichlids]( [ID] [int] IDENTITY(1,1) NOT NULL, [SpeciesName] [nvarchar](50) NOT NULL, [CommonName] [nvarchar](50) NULL, [OriginLake] [nvarchar](10) NOT NULL, [CichlidType] [nvarchar](25) NOT NULL, [Diet] [nvarchar](20) NULL, [Temperament] [nvarchar](50) NULL, [pH] [nvarchar](10) NULL, [Difficulty] [tinyint] NULL, CONSTRAINT [PrimaryKey_01] PRIMARY KEY CLUSTERED ([ID] ASC) ) GO

  3. Verify the table was created the by clicking the Database tab at the top of the browser and click the Refresh button in the top navigation. You should see the new table in Tables selection of the left navigation.

    Figure 12

    Refreshing the SQL Azure Database Management Tool

Exercise 3: Load Sample Data into the SQL Azure Database

In this exercise, create and load some sample data into the SQL Azure database that will be used within the application created in a later lab.

Task 1 – Connecting to the SQL Azure Database

In this task, you will connect ot the SQL Azure Database

  1. Login to the Windows Azure portal.
    1. Navigate to the Windows Azure site and login by selecting the Sign in to the Management Portal link the top navigation.
    2. Sign in using valid Windows Azure subscription credentials.
  2. Connect to the SQL Azure Database.

    1. In the Windows Azure Platform management portal, click Database in the lower-left navigation:

      Figure 13

      SQL Azure Database Navigation

    2. The left pane contains a Subscriptions folder. If that folder is empty, create a new subscription using the New button in the top navigation. If it contains a subscription, proceed to the next step.

    3. Expand the Subscriptions navigation tree until you see the name of your database. Select this database.

      Figure 14

      Windows SQL Azure Database

  3. After selecting the database, in the top navigation click the Manage button to launch the database manager.

    Figure 15

    Connect to Windows SQL Azure Database

  4. When the Database Manager login prompt appears, enter the credentials you created in the previous lab to connect to the database.

    Figure 16

    Connect to SQL Azure Database

Task 2 – Inserting Sample Data into SQL Azure Database

In this task you will create the T-SQL script that will be used to create some sample data & execute it to add the data to the database.

  1. Click the New Query button in the top navigation.

    Figure 17

    Creating a SQL Azure Datbase Query

  2. Enter the following T-SQL script in and press the Execute button in the top navigation:

    T-SQL

    INSERT INTO [AfricanCichlids] ([SpeciesName],[CommonName],[OriginLake],[CichlidType],[Diet],[Temperament],[pH],[Difficulty]) VALUES ('Metriaclima callainos','Cobalt Blue Zebra','Malawi','Mbuna','Herbivore','Mildly Aggressive','7.8-8.6',1) INSERT INTO [AfricanCichlids] ([SpeciesName],[CommonName],[OriginLake],[CichlidType],[Diet],[Temperament],[pH],[Difficulty]) VALUES ('Aulonocara stuartgranti','Cobue','Malawi','Peacock','Carnivore','Peaceful','7.8-8.6',1) INSERT INTO [AfricanCichlids] ([SpeciesName],[CommonName],[OriginLake],[CichlidType],[Diet],[Temperament],[pH],[Difficulty]) VALUES ('Aristochromis christyi','Malawi Hawk','Malawi','Haplochromines','Carnivore','Peaceful','7.8-8.6',2) INSERT INTO [AfricanCichlids] ([SpeciesName],[CommonName],[OriginLake],[CichlidType],[Diet],[Temperament],[pH],[Difficulty]) VALUES ('Copadichromis azureus','Haplochromis chrysonotus','Malawi','Haplochromines','Carnivore','Mildly Aggressive','7.8-8.6',2) INSERT INTO [AfricanCichlids] ([SpeciesName],[CommonName],[OriginLake],[CichlidType],[Diet],[Temperament],[pH],[Difficulty]) VALUES ('Placidochromis','Electra Blue','Malawi','Haplochromines','Carnivore','Peaceful','7.8-8.6',2) INSERT INTO [AfricanCichlids] ([SpeciesName],[CommonName],[OriginLake],[CichlidType],[Diet],[Temperament],[pH],[Difficulty]) VALUES ('Pseudotropheus','Acei','Malawi','Mbuna','Herbivore','Mildly Aggressive','7.8-8.6',1) INSERT INTO [AfricanCichlids] ([SpeciesName],[CommonName],[OriginLake],[CichlidType],[Diet],[Temperament],[pH],[Difficulty]) VALUES ('Labidochromis caeruleus','Yellow Lab','Malawi','Mbuna','Omnivore','Peaceful','7.8-8.6',1) INSERT INTO [AfricanCichlids] ([SpeciesName],[CommonName],[OriginLake],[CichlidType],[Diet],[Temperament],[pH],[Difficulty]) VALUES ('Labeotropheus trewavasae','Likoma','Malawi','Mbuna','Herbivore','Aggressive','7.8-8.6',1) INSERT INTO [AfricanCichlids] ([SpeciesName],[CommonName],[OriginLake],[CichlidType],[Diet],[Temperament],[pH],[Difficulty]) VALUES ('Nimbochromis venustus','Giraffe Cichlid','Malawi','Haplochromines','Carnivore','Aggressive','7.8-8.6',2) INSERT INTO [AfricanCichlids] ([SpeciesName],[CommonName],[OriginLake],[CichlidType],[Diet],[Temperament],[pH],[Difficulty]) VALUES ('Cyphotilapia frontosa','7-Stripe Fontosa','Tanganyika','Frontosa','Carnivore','Mildly Aggressive','8.6',2)

  3. Verify the data was created by selecting the table dbo.AfricanCichlids in the left-navigation, select it and then click the Data button in the top navigation. The main window will refresh to display the data from the database table.

    Figure 18

    Viewing sample data

Summary

SQL Azure allows developers to create a hosted and scalabe database that can be asscess via HTTP or HTTPS from any application. Developers can configure the firewall settings for the database so only Windows Azure hosted applications can access the application or how other applications residing outside of Windows Azure can access the SQL Azure database. This lab demonstrated how to create a SQL Azure database as well as populate with a schema and some sample data.

next >

Downloads

Download The Offline Training Kit
Download Hands-on Lab Document (DOCX)
Download Hands-on Lab Source Files

Contents