Tuesday, March 27, 2012

Creating an SQL Azure Database

Log in to your Windows Azure Platform at https://windows.azure.com/ and clicking on Portal. At the bottom, press New and select Data Services | SQL Database | Quick Create. Specify a name of “HR” and login credentials for the database. Press Create SQL Database.

Creating a new SQL Database.

When the database is created, click on the name in the list of all items, and then press Manage on the bottom toolbar.

Managing the database.

Accept the prompt to add a firewall exception. A new tab will open the management screen. Enter your user credentials and press Log on.

You will be taken to a summary screen that shows database statistics.

Administration page of Microsoft SQL Azure database management web app

Click on the Design button in the bottom left corner. This will take you to a list of all tables in the database. As this database has just been created, the list of tables will be currently empty. Let’s add a table by pressing the New table button.

New Table button in Microsoft SQL Azure database management web app

Add the following fields, starting with the Column name and then selecting type:

Column Select type Is Identity? Is Required?
JobID int Yes Yes
Title nvarchar(50)    
Description ntext    

Jobs table created in HR database using SQL Azure database management web app

In the Table Name field above the columns, enter “Jobs”. Save the table by pressing Save on the ribbon at the top.

Click on Design again. Create another table, name it “Employees”, and set up the following field information:

Column Select type Is Identity? Is Required?
EmployeeID int Yes Yes
LastName nvarchar(30)   Yes
FirstName nvarchar(50)   Yes
JobID int    
Salary money    
Notes ntext    
Photo image    
HireDate datetime    

Employees table created in HR database using SQL Azure database management web app

Save the table. Let’s add a foreign key reference for the JobID field between Employees and Jobs. Above the list of columns, click on Indexes and Keys.

Click on the Add a foreign key relationship header on the right side. Check the JobID field, choose the table dbo.Jobs, and select column JobID.

Foreign key relationship between Jobs and Employees table created in HR database using SQL Azure database management web app

Press Save to save the foreign key.

Now it’s time to create a web application from the database and deploy this application to the cloud. For detailed instructions on Azure Factory project creation and deployment, please refer to this article.

Run Code On Time generator and create a new Azure Factory project. Give this project a name, and specify the connection string of the database using the Fully Qualified DNS Name of the server. Enable ASP.NET Membership and Session State Management. Use the Grapello theme. Generate the application, and you will have a full featured web application running locally on your computer and connecting to your SQL Azure database.

To publish, go back to the web application generator, click on the project name, and press Publish. A Windows Explorer window will open with two files necessary for deployment. Go back to the Windows Azure Platform, and open Hosted Services folder. Create a new Hosted Service, and specify the settings. The package and configuration file are in the opened Explorer window. Press OK to create the deployment. It may take a while for the service to start.

Once complete, you will be able to access the website using the link under Properties of the deployment. Click on this link, and you will be taken to the fully functional website. 

Code On Time Azure Factory web application running in Azure staging deployment

If you look at the URL, you will notice that it uses the ID of the deployment, not the requested DNS Name. This is because the deployment is a staging deployment. You can go back to the management portal, and choose to Swap to change it into a production deployment. This will use the URL you specified during creation of the hosted service.

Code On Time Azure Factory web application running in Azure production deployment

No comments:

You can find more about Code OnTime Generator, Data Aquarium Framework, and other great products here.

© 2010 Code OnTime LLC. Intelligent code generation software for ASP.NET. Visit us at http://codeontime.com