SQL Azure – Relational Database as a Service – Soup to Nuts – The Whole Enchilada

  • Article

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:

https://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 databasesnap0113How 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"snap0109How To Get This Screen: : Available within Visual Studio

Adding a connection to Northwind

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

Northwind

Purpose of Screen: Add a database connectionsnap0111How 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 stringsnap0112How 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 Projectsnap0114How To Get This Screen: : File / New /Project

To create a cloud service application

Purpose of Screen: To create a cloud service applicationsnap0119How 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 Rolesnap0117How To Get This Screen: : See previous step

Northwind_WebRole

See previous step about click the pencil.

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

Adding a GridView control

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

Select <New data source..>

Purpose of Screen: To bind a data source to our gridsnap0122How 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 typesnap0123How 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 Connectionsnap0124How To Get This Screen: : Select Northwind

Save the connection string

Purpose of Screen: Save the connection stringsnap0125How 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 statementsnap0126How 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 statementsnap0127How 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 querysnap0128How 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 databasesnap0130How 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 cloudsnap0134How 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 https://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 Azuresnap0136How 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 databaseMyImage 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 sizesnap0140How 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 settingsimage 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 settingsimage 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 continuesnap0143How 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 addresssnap0146How 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 businesssnap0147How 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"snap0148How 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 Wizardsnap0151How 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 filessnap0152How 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 Serversnap0154How 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 cloudsnap0155How 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 Azuresnap0160How To Get This Screen: : We are selecting "Customers"

Summary Screen

This allows us to verify our migration.

Purpose of Screen: Summary Screensnap0161How 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)snap0163How 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 datasnap0164How 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 uploadedMyImage 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 datasnap0167How To Get This Screen: : Click "Next" to continue

It is time to execute

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

Execution in progress

Purpose of Screen: The migration in progresssnap0169How To Get This Screen: : Click "Next" to continue

Success !

Purpose of Screen: Successsnap0170How 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 cloudsnap0174How To Get This Screen: : Open and modify

Make changes to reflect your connection string

Purpose of Screen: Note we commented out the old oneMyImage 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 applicationsnap0171How 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 successsnap0172How To Get This Screen: : We have completed this post. Our goals have been met.

Done !