Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

If you like to migrate your Web tier or the ASP.NET Application to Windows Azure, check my new posts Part I and Part II

SQL Azure is the latest buzz around Cloud Computing and the ability to take relational database to the cloud as is, is something most would welcome compared to the earlier model of ACE (Authority, Container, Entity) that was there when SQL Azure was SSDS.   Well, like others, I also got interested and wanted to explore the SQL Azure.  SQL Azure is currently in CTP and offers token based access.  I would briefly outline the steps here on getting the SQL Azure Token, although Jim O’Neil has a nice post on this

Visit SQL Azure Registration at Microsoft Connect

Sign in with your Live ID (Passport, Hotmail, MSN, Live)

Fill in the details and submit the form

You should receive the invitation code in an email from an alias “SQL Azure Talk” or something similar (note this may take from a day to a week until the CTP is available)

Visit https://sql.azure.com

Fill in the Invitation Code received in the email

You will be directed a page similar to below

image

Click on the “Manage” link in the right. It will take you to a page as below:-

This page is where you can create / delete databases as well as get the connection string etc.,

image

In the top of this page you will also see “Connection Strings” , “Reset Password” icons and also the Server Name, User Name that you chose, and the location where the database is hosted.

You can click on “Create Database” to specify a new Database table name (that’s all you can do, you cannot create tables from here, just the database)

As you can see, I have created a few databases except master which is the default database.  You can chose an individual database and then click on the “Connection Strings” icon in the bottom (currently disabled) to get the specific connection string for the database you created.

Now, this is the first step towards getting access to SQL Azure Services.

Next, the big question you might have is how do I create tables, procedures etc.,  The MSDN Sample documentation provides you the instructions on creating a basic database with a single table.  However, you might want to create / import a relational database such as Northwind with more tables etc.,  The trick here is that not all data formats are supported, particularly the deprecated ones such as ntext, image etc., at least at the moment and you can’t just go ahead and run the DB Scripts against SQL Azure to create the database and tables.  Lets examine one by one on the steps to access SQL Azure Tables in SQL Server Management Studio, Getting a script that can be executed on SQL Azure, Uploading data to SQL Azure and finally binding it to GridView

Accessing SQL Azure Tables in SQL Server Management Studio

SQL Server Management Studio (SSMO) is probably the most favourite tool used for querying, running scripts etc.,  You can download SSMO for SQL Server 2008 or the free SQL Server Express edition from here

Once you installed, it would prompt you with a login dialog with server details etc., (if you have used it already, you would be familiar with it).  Now, the trick is, to be able to use SSMO with SQL Azure, you need to dismiss the initial login screen and click on “New Query” icon in the top left and specify the server name which is typically like <YOURSERVERNAME>.ctp.database.windows.net (you can find the server name in the screens explained above).  Then specify the username, password and click on “Options” to specify the database that you created above.  Click “Connect” and you should be able to connect to the SQL Azure Server database.  A more detailed step by step instruction is available at Ramaprasanna’s Blog

The above trick of dimissing the initial login screen and again bring it up to enter the azure database credentials is no longer required with SQL Server 2008 R2 November CTP

If you had created the sample database by following the MSDN Sample documentation you can specify that database while connecting (explained in the step above) and then query the tables (select * from T1) to get the results.

Migrating Northwind to the SQL Azure Server

Like mentioned earlier, currently, you cannot take the DB script of say Northwind and run it in the SSMO to be able to create the database in SQL Azure.  There are a few changes required and also few modifications in the script.  Now, doing it for a simple database is quite easy.  However, if you want to migrate a database such as Northwind or Adventure Works, its going to get very complicated and a probable nightmare for you.

Thankfully, there is a SQL Azure Migration Wizard (beta) being developed in CodePlex which is free, open source and under development.  You can view and download it from CodePlex  (Note that it is in Beta and has limited capability in terms of performance, features etc.,)

You can run the SQLAzureMW.exe that gets downloaded once you install the above.  It opens up a screen for connecting to your SQL Server Database

image

Click on “Connect to Server” and specify the servername, credentials etc., and it would list all the databases available in your SQL Server instance.  Click on “Northwind” (this post assumes you already installed the Northwind sample database for SQL Server. If not you can download it from here ) and chose the “Next” step.  It shows the “Script Options” screen where you can change settings.  Clicking on “Next” brings up the screen to chose Object types (SPs, Views, Tables).  I chose, “Script all Object types”

 

image

The next screen provides you to Script it to Window / SQL Azure or to a File.   Chose the “Window / SQL Azure” (default) and click “Next”.  The next screen provides an option to review the configurations made so far.   Click on “Script” and it would parse the scripts and provide the Results Summary.  What this tool essentially does is to make your regular DB Script compatible with a format that is supported in SQL Azure.   Once again since this is in Beta, it may not be near to 100% perfection in this process.

image

The next screen is where you specify your SQL Azure Server Details. Note that the default Server URL that comes is little outdated.  You can get the correct URL from the SQL Azure Portal page (explained in screen shot 2 above) by clicking on the “Connection Strings” icon in the top of the page.  It provides the Server URL as mentioned earlier <YOURSERVERNAME>.ctp.database.windows.net.  You just need to add tcp.  So the fully qualified URL that you need here would look like tcp:<YOURSERVERNAME>.ctp.database.windows.net.  Also, specify the username and password (the one you chose when creating the SQL Azure Database in the first step) and then click on “Test Connection” to check the details.  If you receive an error it might just be wrongly specified server name or missing parts of the URL.   Once you succeed, you can see that it also lists the existing databases (if you haven’t created anything, only “master” would be listed here) and provides the option to create a new database.  You can chose the “Northwind” if you already created it in the SQL Azure portal or type “Northwind” to create it newly.    

image

Next, you can click on “Script” and it does a migration of the scripts to SQL Azure Portal.  It lists down the results in the final screen.

image

Note that this has just migrated the Schema and not the actual Data.  But to take up an on-premise database, generate the scripts and deploy it on SQL Azure manually would be next to impossible, particularly if you have a large database with complex schema.  This tool would help to a great extent in automating the process. 

UPDATE: The latest version of SQL Azure Migration Wizard migrates both Data and Schema.  You can download the same from http://sqlazuremw.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=32334

Finally, we need to upload the Data.  Lets examine it in the next post.  Read Next Post

Cheers !!!

Print | posted on Tuesday, September 29, 2009 2:58 AM

Comments on this post

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
This is very cool. Eagerly awaiting the "upload the Data" post.
Left by Michael Washington on Sep 29, 2009 4:51 AM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
Can data be synchronized between local and cloud
Left by Len Yabloko on Sep 29, 2009 9:09 AM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
this is what i want currently, thanks.
Left by wind-snail on Sep 29, 2009 2:07 PM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
Do they always send invitation? No matter what?
Of course i understand this is until CTP is available....
Left by ITmeze on Sep 29, 2009 8:45 PM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
Can ne1 explain me what is this Azure? I got back with ASP after a yr.......Provide links to related topics if possible.keep posting. Will be visiting back soon.
Left by nintendo r4 on Oct 11, 2009 3:54 PM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
i didnt know this is possible at the beginning..Azure with ASP .. good
Left by randalf on Oct 30, 2009 11:05 PM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
Useful post.

and as i am evaluating SQL Azure with dynamically changing IPs (due to Internet Service Provider), currently allowing all public IPs as
0.0.0.0 - 255.255.255.255

..
Left by Muruganantham Durairaj on Nov 05, 2009 6:13 AM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
sorry about previous comment. It it related to different post.

http://geekswithblogs.net/ranganh/archive/2009/11/04/sql-azure-connection-error-ldquocannot-connect-to-ltservernamegtquot-client-with.aspx
Left by Muruganantham Durairaj on Nov 05, 2009 6:17 AM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
I agree with your conclusions and will eagerly look forward to your next updates. Saying thanks will not just be sufficient, for the wonderful clarity in your writing. I will immediately grab your rss feed to stay privy of any updates.
Left by weight loss pill on Jan 08, 2010 10:21 AM

# weight loss pills

Requesting Gravatar...
Thanks for such a great post and the review, I am totally impressed! Keep stuff like

this coming.
Left by kanchan on Feb 10, 2010 11:09 PM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
Thanks for sharing. i really appreciate it that you shared with us such a informative post..!!!
Nutritional Health Science degree | Law and legal studies school
Left by health science degree on Mar 18, 2010 8:35 PM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
I am looking forward for your next post, I will try to get the hang of it!
biology degree
Left by Online Natural science degree on Mar 18, 2010 8:36 PM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
I find gridview's in general to be a bit clunky, but improvements are being made.

Computer Freezes
Left by Increase Computer Speed on Mar 19, 2010 2:03 AM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
i havent tried SQl Azure till now.. i m still in the old ones..
Left by Resveratrol Supplements on Apr 08, 2010 6:00 PM

# Re:

Requesting Gravatar...
Well i feel SQL Azure was a fail compared to the other versions.. its my opinion .
Left by Online Paralegal Certificate on Apr 13, 2010 2:57 PM

# RE

Requesting Gravatar...
what are your opinions regarding SQL Azure ?
Left by Free Energy Generator on Apr 16, 2010 4:25 AM

# # re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
Thankfully, there is a SQL Azure Migration Wizard (beta) being developed in CodePlex which is free, open source and under development.
Left by Hampaiden Valkaisu Kotona on May 02, 2010 4:11 AM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
I agree with your conclusions and will eagerly look forward to your next updates. Saying thanks will not just be sufficient, for the wonderful clarity in your writing. I will immediately grab your rss feed to stay privy of any updates.
Left by Acne Scar Treatment on May 03, 2010 3:03 AM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
I am so impressed w/evernote so far. really pro stuff. thx folks
Left by free cna test Questions on May 05, 2010 3:20 AM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
UPDATE: The latest version of SQL Azure Migration Wizard migrates both Data and Schema.

This was very helpful to me. Thanks!
Left by Phone Sex on May 28, 2010 3:22 AM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...


I keep finding videos and other resources from Microsoft that mention the plan to have 'automatic partitioning'. Where can we learn more?

The strategy suggested sounds possible. We have a multi-tenant database where every table has a CustomerID field. It would be fantastic if SQL Azure could know to partition data based on that. Especially if we can write normal queries without knowledge of the partitions.
Left by rn to bsn online on Jun 07, 2010 11:10 AM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
Sending an email is a fundamental requirement of any cloud hosting solution. Yet both Azure and Amazon seem to be missing it. In the mean time we are using Elastic Email, which is cheap and good
Left by backlink building on Jun 07, 2010 11:15 AM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
SQL is more complicated)
Left by Apotheke on Aug 04, 2010 5:19 PM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
i learned more about sql and asp. thank you.
Left by motivational tools on Aug 12, 2010 3:22 AM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
The MSDN Sample documentation provides you the instructions on creating a basic database with a single table.
Left by High heels for men on Aug 31, 2010 11:36 PM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
This have so many valuable things to learn. If possible, as you gain information, please add to this blog with new information. I'm sure some people will really like this information. it was quite interesting nice work. Thank you.
Food Recipe
Left by Sarah on Feb 07, 2011 11:21 AM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
interesting one
Left by Isopropyl Alcohol on Mar 04, 2011 1:42 AM

# Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
The MSDN Sample documentation provides you the instructions on creating a basic database with a single table. However, you might want to create / import a relational database such as Northwind with more tables etc.
Left by Hvac Programs on Apr 13, 2011 1:25 PM

# Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
SQL Server Management Studio (SSMO) is probably the most favourite tool used for querying, running scripts etc., You can download SSMO for SQL Server 2008 or the free SQL Server Express edition from here.
Left by Master Degrees Online on May 06, 2011 6:04 PM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
This is an informative blog for computer personals
Left by little giraffe blanky on May 14, 2011 11:57 PM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
well never worked with northwind ?
Left by bowtrol on Jun 13, 2011 8:09 PM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
Thanks for explaining in details the ins and out of this tutorial.
Left by prostate supplements on Jul 03, 2011 6:45 AM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...

Your writing is really opening my knowledge of SQL. Thank's so much..
kissy kissy baby clothes | baby headbands
Left by layette on Jul 24, 2011 8:39 PM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
I would like to consider the chance of saying thanks to you for that professional suggestions I have continually enjoyed viewing your site. I will be looking forward to the commencement of my college research and the complete planning would never have been complete without browsing your blog.... 866-826-4101
Left by umair on Aug 31, 2011 12:40 AM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
Hello, I love reading through your blog, I wanted to leave a little comment to support you and wish you a good continuation. Wishing you the best of luck for all your blogging efforts... wall stickers for kids | kids wall stickers
Left by khang on Sep 26, 2011 9:09 PM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
really a knowledgeful site a great one
Left by full service real estate los ang on Nov 05, 2011 12:44 PM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
Well I came across numerous beneficial material in your own blog primarily this web page. From the so many responses on your articles, I guess I am not the only one having all the excitement here!!!! latest news today || News
Left by umair malikg on Nov 16, 2011 8:29 PM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
This was really very helpful was looking for something like this for quite sometime.
Left by Online Nutrition Schools on Nov 28, 2011 5:52 PM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
The strategy suggested sounds possible. We have a multi-tenant database where every table has a CustomerID field. It would be fantastic if SQL Azure could know to partition data based on that.
Left by net grid on Jul 21, 2012 5:55 PM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
We need to wire up additional code for using Canvas, SVG and other HTML5 features. But, definitely, this is a good starting point
Left by grama sintetica on Dec 20, 2012 7:22 PM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
This step created the Model1.Context (not required for us though Smile) and the Model1.tt template which groups the
Left by oxyelite pro on May 04, 2013 2:29 AM

# re: Taking your Northwind Database to SQL Azure and binding it to an ASP.NET Grid View – Part I

Requesting Gravatar...
Since the code was ported from another machine
Left by ganhar dinheiro na internet on May 09, 2013 5:05 AM

Your comment:

 (will show your gravatar)