This post will be very direct. It will get a database running in the Azure cloud as quickly as possible. There is plenty of material about why SQL Azure might be of interest and how it works. Go to Bing and type SQL Azure and you’ll get all the reasons that SQL Azure is the right solution in many scenarios. www.microsoftpdc.com is a great place to start.

Brief Goals

  • Build a simple data driven ASP.NET Page using the graphical controls in Visual Studio 2008
  • Develop against a local SQL Server/SQL Express database before connecting to SQL Azure

Steps in this post

  1. Create the northwind database
  2. Add northwind to sql server
  3. Create a cloud project with 1 web role
  4. Add a grid and attach grid’s data source our data (Northwind database)
  5. Run our cloud application against local data
  6. Create a SQL Azure database
  7. Download and install the SQL Server Migration Wizard
  8. Upload the customers table to SQL Azure
  9. Change our connection string to point our app to the cloud
  10. Run our app and verify everything works using the data from SQL Azure (NorthwindInCloud)

The Northwind database – where to download

Do a search on Bing for “Northwind and pubs Sample Databases for SQL Server 2000” and you should find this link:

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&displaylang=en

image 

The Northwind database – building the database

The file to pay attention to in the SQL Server 2000 Sample databases is instnwnd.sql.

image

This is only a fraction of the .sql code.

Code Snippet
  1. SET NOCOUNT ON
  2. GO
  3.  
  4. USE master
  5. GO
  6. if exists (select * from sysdatabases where name='Northwind')
  7.         drop database Northwind
  8. go
  9.  
  10. DECLARE @device_directory NVARCHAR(520)
  11. SELECT @device_directory = SUBSTRING(filename, 1, CHARINDEX(N'master.mdf', LOWER(filename)) - 1)
  12. FROM master.dbo.sysaltfiles WHERE dbid = 1 AND fileid = 1
  13.  
  14. EXECUTE (N'CREATE DATABASE Northwind
  15.   ON PRIMARY (NAME = N''Northwind'', FILENAME = N''' + @device_directory + N'northwnd.mdf'')
  16.   LOG ON (NAME = N''Northwind_log'',  FILENAME = N''' + @device_directory + N'northwnd.ldf'')')
  17. go
  18.  
  19. exec sp_dboption 'Northwind','trunc. log on chkpt.','true'
  20. exec sp_dboption 'Northwind','select into/bulkcopy','true'
  21. GO
  22.  
  23. set quoted_identifier on
  24. GO
  25.  
  26. /* Set DATEFORMAT so that the date strings are interpreted correctly regardless of
  27.    the default DATEFORMAT on the server.
  28. */
  29. SET DATEFORMAT mdy
  30. GO
  31. use "Northwind"
  32. go
  33. if exists (select * from sysobjects where id = object_id('dbo.Employee Sales by Country') and sysstat & 0xf = 4)
  34.     drop procedure "dbo"."Employee Sales by Country"
  35. GO
  36. if exists (select * from sysobjects where id = object_id('dbo.Sales by Year') and sysstat & 0xf = 4)
  37.     drop procedure "dbo"."Sales by Year"
  38. GO
  39. if exists (select * from sysobjects where id = object_id('dbo.Ten Most Expensive Products') and sysstat & 0xf = 4)
  40.     drop procedure "dbo"."Ten Most Expensive Products"
  41. GO

Microsoft SQL Server Management Studio

Purpose of Screen: To create a new database
snap0113
How To Get This Screen: : Start Microsoft SQL Server Management Studio

Start Microsoft SQL Server Management Studio and choose, “File, Open, File”

image

Execute the Query to build the database

Open instnwnd.sql  and go to SQL Server Management Studio and hit the “f5” key or go to the menu and choose “Query, Execute.”

image

Notice that we have a Northwind database

We will work with some of the tables here in our sample application.

image

NorthWind Database – Attach to “Server Explorer”

One of the first tasks we need to get done is get a database created and ready to code against within Visual Studio 2010.

Purpose of Screen: Open "Server Explorer"
snap0109
How To Get This Screen: : Available within Visual Studio

Adding a connection to Northwind

Purpose of Screen: Add Northwind to Server Explorer
snap0110
How To Get This Screen: : Right mouse click and "Add Connection"

Northwind

Purpose of Screen: Add a database connection
snap0111
How To Get This Screen: : We previously added Northwind to our "(local)" instance of SQL Server

Advanced connection properties

Purpose of Screen: Demonstrate the local connection string
snap0112
How To Get This Screen: : Click on the "Advanced" button

Task – Creating the Visual Studio Project

In this task you will create a new Visual Studio project for the Windows Azure Web Site:

Open Microsoft Visual Studio 2008/2010 elevated as Administrator, from Start | All Programs | Microsoft Visual Studio 2008/2010 right-click Microsoft Visual Studio 2008/2010 and choose Run as Administrator.

If the User Account Control dialog appears, click Continue.

From the File menu, choose New and then Project.

Create a new Web Cloud Service

Rename the WebRole to NorthWind_WebRole by clicking the pencil icon.

Click the OK button to create the solution.

When the project template has finished creating items you should be presented with the Default.aspx page. If not open the Default.aspx page.

Ensure that you are viewing the Default.aspx page in Design View by clicking the Design button

Drag and drop a GridView control from the Data section of the Toolbox onto the design canvas.

The steps in detail follow below

Creating a new cloud project

Purpose of Screen: Create a New Cloud Project
snap0114
How To Get This Screen: : File / New /Project

To create a cloud service application

Purpose of Screen: To create a cloud service application
snap0119
How To Get This Screen: : Select the "Cloud" template

Creating a new web role

You will need to click on the pencil to rename the web role.

Purpose of Screen: Add an ASP.NET Web Role
snap0117
How To Get This Screen: : See previous step

Northwind_WebRole

See previous step about click the pencil.

Purpose of Screen: Naming our Web Role
snap0118
How To Get This Screen: : Click on the image of the pincil to edit

Adding a GridView control

Purpose of Screen: To add a GridView control
snap0121
How To Get This Screen: : Drag the GridView to Default.aspx

Select <New data source..>

Purpose of Screen: To bind a data source to our grid
snap0122
How To Get This Screen: : Click

Choosing a data source type

We are selecting our Northwind SQL Server database.

Purpose of Screen: Choosing a data source type
snap0123
How To Get This Screen: : Select "SQL Database" and "Specify an ID for the data source"

Choose Your Data Connection

Purpose of Screen: Choose Your Data Connection
snap0124
How To Get This Screen: : Select Northwind

Save the connection string

Purpose of Screen: Save the connection string
snap0125
How To Get This Screen: : Call the connection string NorthwindConnectionString

Create a "select" SQL statement

This is the data that will show up in our GridView control.

Purpose of Screen: Create a "select" SQL statement
snap0126
How To Get This Screen: : Select the customers table and some columns

Build the select statement

It will be used to populate our GridView.

Purpose of Screen: Build the select statement
snap0127
How To Get This Screen: : Click next once you've selected your columns.

The test query

You can test the query before even running it.

Purpose of Screen: The test query
snap0128
How To Get This Screen: : Verify the results

Run the application to test the grid and the database

We will now run our application.

Purpose of Screen: Run the application to test the grid and the database
snap0130
How To Get This Screen: : From the main VS menu, select "Debug / Start Debugging"

Our cloud application running on the local developer cloud

The next step is run the application in the cloud.

Purpose of Screen: Our cloud application running on the local developer cloud
snap0134
How To Get This Screen: : Simply run the application (it is running on my local machine, not in the cloud)

So now we are moving to the cloud and our goal is to deploy our database in SQL Azure. We have a few steps to get this done.

Login into http://windows.azure.com

Note that when you create a server (from the SQL Azure tab), you will need to specify:

  • Administrator UserName
  • Administrator Password
  • Where you want your data geolocated

You will need these later when you modify web.config to support connectivity to SQL Azure from your Windows Azure ASP.NET web application.

Purpose of Screen: Creating our first SQL Azure
snap0136
How To Get This Screen: : Login to the Windows Azure Portal

Once you’ve created your server, you will need to create a database. Our database will be called “NorthwindInCloud.” The database will be empty initially, until we add our tables and data.

Server Name established

Note that in this case, the server name has been created as

Purpose of Screen: Click "Create Database" to create our database
MyImage
How To Get This Screen: : You will need to have logged in to Windows Azure. Click “Create Database”

Naming and Sizing for the database

You will now provide:

  • Name of your database
  • Size for your database (1gb versus 10gb)
    • Affects your monthly charges
Purpose of Screen: Provide a name and size
snap0140
How To Get This Screen: : The size can be 1GB versus 10GB

Recap – Where we are so far

At this point we’ve accomplished quite a bit. We have:

  • A server name to connect to
  • A database name
  • A location
  • A size

Purpose of Screen: The next step is the configure firewall settings
image
How To Get This Screen: : Click on the "Connection Settings"

The “Connection Strings” button

I found this button to be “almost true,” in that I got an error for “Encrypt=True.” More on that later.

Purpose of Screen: You will edit these settings
image
How To Get This Screen: : See previous screen

Unless you modify Firewall settings, you will get connnection failures.

Configuring the Firewall

Purpose of Screen: You will need to configure the firewall to continue
snap0143
How To Get This Screen: : Navigate to the screen above and follow the directions

You will need to add a firewall rule. The good news is that you are told about the IP address, “Your IP address: 205.248.102.81”

Add Firewall Rule

I’m working in Peets Coffee today when I’m writing this post. And I’ve had to add additional firewall rules based on where I’m connecting from.

Purpose of Screen: Make sure you are providing the correct IP address
snap0146
How To Get This Screen: : See previous screen (Add Firewall Rule)

Example of a Firewall rule

Depending on your network scenario, you may need to add additional rules.

Purpose of Screen: You may need to add additonal address per your business
snap0147
How To Get This Screen: : See previous steps

Testing Connectivity

You may need to wait a few minutes for the firewall rules to take hold. Nothing beats a green “Successfully connected to the database” message.

Purpose of Screen: You will need to "Test Connectivity"
snap0148
How To Get This Screen: : See previous screens

 

Migrating our data

Now that we have the on-premise database running correctly, we are now ready to deploy to the cloud. To help with this effort we will use the SQL Azure Migration Wizard available on CodePlex.

Purpose of Screen: Download the SQL Azure Migration Wizard
snap0151
How To Get This Screen: : Go to CodePlex and download

WinRar to install our SQL Azure Migration Wizard

There’s nothing complex about the installation. Just extract to a holder and you are good to go.

Purpose of Screen: You will need to unzip the files
snap0152
How To Get This Screen: : Use whatever "unzipping tool" you wish. I am using WinRar here.

Starting the migration wizard

I will select “Analyze and Migrate” to start the process.

Logging in to our local instance of SQL Server

Purpose of Screen: This is to log into your local, on-premise SQL Server
snap0154
How To Get This Screen: : Start the SQL Azure Migration Wizard and connect

Selecting the database we wish to upload to SQL Azure

We wish to upload “Northwind”

Purpose of Screen: Select the database that you wish to migrate to the cloud
snap0155
How To Get This Screen: : Select the database that your wish to deploy to the cloud and select "Next"

Customers Table

We are interested just in the Customer table.

Purpose of Screen: Select the tables you wish to migrate to SQL Azure
snap0160
How To Get This Screen: : We are selecting "Customers"

Summary Screen

This allows us to verify our migration.

Purpose of Screen: Summary Screen
snap0161
How To Get This Screen: : Just click "Next"

Results Summary

Purpose of Screen: The migration tool is building the scripts to create the SQL objects (tables, stored procedures, etc)
snap0163
How To Get This Screen: : The upcoming step is to physically create the table in the cloud and upload data.

Note the DMO queries that will build the customers table

Purpose of Screen: Code to create the tables and bulk upload data
snap0164
How To Get This Screen: : Click "next" to continue

Logging in to SQL Azure

Purpose of Screen: Provide credentials so tables can be created in the cloud and data can be uploaded
MyImage
How To Get This Screen: : Fill in the appropriate information as seen above (your information will be different)

Our database up in the cloud (previously created)

Purpose of Screen: NorthwindInCloud is the SQL Azure database where we wish to create the customers table and upload data
snap0167
How To Get This Screen: : Click "Next" to continue

It is time to execute

Purpose of Screen: The magic moment where the migration will happen
snap0168
How To Get This Screen: : Click "Yes"

Execution in progress

Purpose of Screen: The migration in progress
snap0169
How To Get This Screen: : Click "Next" to continue

Success !

Purpose of Screen: Success
snap0170
How To Get This Screen: : You are done migrating

Modify web.config

Purpose of Screen: Modify Web.config and change the connection string to point to the cloud
snap0174
How To Get This Screen: : Open and modify

Make changes to reflect your connection string

Purpose of Screen: Note we commented out the old one
MyImage
How To Get This Screen: : Add the new connection string as seen above (specific to your SQL Azure, of course)

Running our application as a final test of success

Purpose of Screen: Test the database by running our ASP.NET Cloud application
snap0171
How To Get This Screen: : Debug / Start Debugging

Don Quixote would say, “The proof of the pudding is the eating."

Purpose of Screen: The data below is from SQL Azure. Our steps were a success
snap0172
How To Get This Screen: : We have completed this post. Our goals have been met.

Done !