Exercise 2: Creating a SQL Azure Database

In this exercise, you will create a SQL Azure database. In this scenario, the Windows Phone 7 application queries the SQL Azure database and passes in the product make and model. The SQL Azure database stores the different warehouses that currently have the replacement parts in stock and how many replacement parts are available in each warehouse.

Task 1 – Creating a SQL Azure Storage Account

In this task, you will open the lab solution in Visual Studio 2010.

1. Open Internet Explorer, and go to https://windows.azure.com.

2. Login using your Windows Azure credentials. If you do not have a Windows Azure Account, sign-up for a new one. Create a new Storage Account by clicking the New Storage Account from the ribbon menu.

   Figure 5

   New Azure Storage account

3. In the Choose a subscription dropdown list, select the name of your Azure subscription. (You would have provided this information while signing up for the Azure account.)

4. In the Enter a URL textbox, provide a URL for the storage account.

   Example: spwp7dev

5. In the Choose a region or affinity group dropdown list, select a value close to you.
6. Click Create.

Task 2 – Creating a SQL Azure Database Server

In this task, you will create a SQL Azure database server.

1. Open Internet Explorer, and go to https://windows.azure.com.
2. Login using your Windows Azure credentials. If you do not have a Windows Azure Account, sign-up for a new one.

3. In the left pane, click Database.

   Figure 6

   Select database

4. Select the appropriate subscription.

5. In the main menu, in the Server section, click Create.

   Figure 7

   Create new database

6. In the Create Server dialog, select a Region.
7. Click Next.
8. Enter an Administrator Login and password.
9. Click Next.
10. Check the Allow other Windows Azure services to access this server checkbox.
11. Click Add.
12. In the Rule name textbox enter Everything.
13. In the IP range start textbox, enter 0.0.0.0.
14. In the IP range end textbox, enter 255.255.255.0.

15. Click OK.

    Figure 8

    New server created

16. Click Finish.

    Figure 9

    Server details

17. Notice the Fully Qualified DNS Name. This is used to connect to the SQL Azure database.

Task 3 – Creating a SQL Azure Database

1. In this task, you will create a SQL Azure database.
2. Open Microsoft SQL Server 2008 R2 RTM - Management Studio Express.

3. In the Connect to Server dialog, enter the Server name for your SQL Azure database server.

   Note:

   The Server name is the Fully Qualified DNS Name for the SQL Azure database server you created in task 2.

4. In the Authentication dropdown list, select SQL Server Authentication.
5. Enter the Login and Password for your SQL Azure account.
6. Click Connect.
7. In the Object Explorer pane, highlight the Databases folder.
8. Click the New Query button on the toolbar.

9. Paste the following command into the query window:

   TSQL

   CREATE DATABASE PartsInventoryEnhanced (MAXSIZE=1GB)

10. Click Execute on the toolbar.
11. In the Object Explorer pane, expand the Databases folder and highlight the PartsInventoryEnhanced database. If you cannot see the PartsInventoryEnhanced database, highlight the Databases folder and click the refresh button in the Object Explorer toolbar.
12. With the PartsInventoryEnhanced database highlighted, click the New Query button in the toolbar.

13. Paste the following SQL commands into the query window. These commands create the Parts table.

    TSQL

    USE [PartsInventoryEnhanced] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Parts]( [ID] [int] IDENTITY(1,1) NOT NULL, [Warehouse] [varchar](256) NOT NULL, [Make] [varchar](256) NOT NULL, [Model] [varchar](256) NOT NULL, [NumberInStock] [varchar](256) NOT NULL, [OrderPending] [varchar](3), CONSTRAINT [PK_ Parts] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ) GO SET ANSI_PADDING OFF GO

14. Click Execute on the toolbar.
15. With the PartsInventoryEnhanced database highlighted, click the New Query button in the toolbar.

16. Paste the following SQL commands into the query window. These commands create the sample data in the Parts table.

    TSQL

    INSERT INTO [PartsInventoryEnhanced].[dbo].[Parts] VALUES ('Denver', 'Fabrikam', 'BLR15000-S', 0, 'No') GO INSERT INTO [PartsInventoryEnhanced].[dbo].[Parts] VALUES ('Cincinnati', 'Litware', 'MP-4200', 2, 'No') GO INSERT INTO [PartsInventoryEnhanced].[dbo].[Parts] VALUES ('Springfield', 'Fabrikam', 'BLR15000-S', 11, 'No') GO INSERT INTO [PartsInventoryEnhanced].[dbo].[Parts] VALUES ('San Diego', 'Fabrikam', 'SD-1', 1, 'No') GO INSERT INTO [PartsInventoryEnhanced].[dbo].[Parts] VALUES ('Breckenridge', 'Fabrikam', 'SD-1', 3, 'No') GO INSERT INTO [PartsInventoryEnhanced].[dbo].[Parts] VALUES ('Denver', 'Litware', 'MP-4200', 6, 'No') GO INSERT INTO [PartsInventoryEnhanced].[dbo].[Parts] VALUES ('Philadelphia', 'Litware', 'MP-4200', 3, 'No') GO

17. Click Execute on the toolbar.
18. With the PartsInventoryEnhanced highlighted, click the New Query button in the toolbar.

19. Paste the following SQL commands into the query window. These commands create the sample data in the Parts table.

    TSQL

    SELECT * FROM [PartsInventoryEnhanced].[dbo].[Parts] GO

20. Click Execute on the toolbar.

21. Verify the sample data exists in the Parts table.

    Figure 10

    Sample data table