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.
- 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
- Create the northwind database
- Add northwind to sql server
- Create a cloud project with 1 web role
- Add a grid and attach grid’s data source our data (Northwind database)
- Run our cloud application against local data
- Create a SQL Azure database
- Download and install the SQL Server Migration Wizard
- Upload the customers table to SQL Azure
- Change our connection string to point our app to the cloud
- 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:
The Northwind database – building the database
The file to pay attention to in the SQL Server 2000 Sample databases is instnwnd.sql.
This is only a fraction of the .sql code.
Microsoft SQL Server Management Studio
|Purpose of Screen: To create a new database
How To Get This Screen: : Start Microsoft SQL Server Management Studio
Start Microsoft SQL Server Management Studio and choose, “File, Open, File”
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.”
Notice that we have a Northwind database
We will work with some of the tables here in our sample application.
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"
How To Get This Screen: : Available within Visual Studio
Adding a connection to Northwind
|Purpose of Screen: Add Northwind to Server Explorer
How To Get This Screen: : Right mouse click and "Add Connection"
|Purpose of Screen: Add a database connection
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
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
How To Get This Screen: : File / New /Project
To create a cloud service application
|Purpose of Screen: To create a cloud service application
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
How To Get This Screen: : See previous step
See previous step about click the pencil.
|Purpose of Screen: Naming our Web Role
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
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
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
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
How To Get This Screen: : Select Northwind
Save the connection string
|Purpose of Screen: Save the connection string
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
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
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
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
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
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
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
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
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
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
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
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: 188.8.131.52”
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
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
How To Get This Screen: : See previous steps
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"
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
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
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
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
How To Get This Screen: : Select the database that your wish to deploy to the cloud and select "Next"
We are interested just in the Customer table.
|Purpose of Screen: Select the tables you wish to migrate to SQL Azure
How To Get This Screen: : We are selecting "Customers"
This allows us to verify our migration.
|Purpose of Screen: Summary Screen
How To Get This Screen: : Just click "Next"
|Purpose of Screen: The migration tool is building the scripts to create the SQL objects (tables, stored procedures, etc)
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
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
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
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
How To Get This Screen: : Click "Yes"
Execution in progress
|Purpose of Screen: The migration in progress
How To Get This Screen: : Click "Next" to continue
|Purpose of Screen: Success
How To Get This Screen: : You are done migrating
|Purpose of Screen: Modify Web.config and change the connection string to point to the cloud
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
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
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
How To Get This Screen: : We have completed this post. Our goals have been met.