Project Cool

In the previous post we had examined on getting access to SQL Azure, creating your first database, accessing it with SQL Server Management Studio and then migrating the Northwind database schema to SQL Azure using the SQL Azure Migration Wizard Beta.

As explained earlier, the SQL Azure Migration Wizard migrates the schema of your database after tuning it for working with SQL Azure.  However, we would still need to migrate the Data to our SQL Azure Server.  At the moment, the step I took was to open the instnwnd.sql script in SQL Server Management Studio (SSMO) and copy the Insert statements alone to execute.  Note that, you cannot run all the scripts directly onto the SQL Azure portal like I explained earlier due to the limitations / formats supported currently in SQL Azure. 

To begin with, open “C:\SQL Server 2000 Sample Databases” folder (the default location where Northwind and pubs sample database gets installed) and double click on the instnwnd.sql script.  It opens up in SSMO.   Examine the scripts and navigate to the portion where the INSERT statements begin.  I took the portion and put it up as a single insert script.  You can download the script  from the link below:-

Running the scripts from SQL Server Management Studio

As explained in my previous post, open SSMO and cancel the initial login prompt.  Click on “New Query” and specify the server name as the fully qualified Azure Server name i.e. <YOURSERVERNAME>.ctp.database.windows.net, specify the user name, password.  Click on “Options” and specify “Northwind” at “Connect to database” option.  Click “Connect”.  It should be able to connect provided you have specified the path, username, password etc., correctly and created the “Northwind” database as per the previous post.  There will be a small error prompt that comes before opening the Query window, as below.  You can ignore that and click “ok”

Once you are in the SqlQuery1.sql screen you can check if the database is created and the tables are there by running  a bunch of few queries as below:-

SELECT * FROM Products

SELECT * FROM Orders etc.,

You will get blank result sets since we haven't migrated the data yet.

Double lick to open the insertnwnddata.sql script downloaded in the first step.  It would open in a new sql window.  Copy paste the script into the first window we got once we connected to the Azure Database.  If you get a connection error.  Try reconnecting using the “New Query” option as explained above.  If all is good, you should be able to execute the scripts.  Once the scripts are successful, you can test if the data is migrated, by again running the SELECT statements above.  They should now list data.   If there is an error, try running the individual INSERT statements for each table to verify which table has the issue.

Once this is done, we are all set to bind this data to our ASP.NET Page.

Binding the SQL Azure database to ASP.NET Page

Open Visual Studio 2008 and create a web application project.  In the Default.aspx page, drag and drop a GridView control and use the GridView’s wizard to connect to your local Northwind database.  Pick the Products table and chose the columns you want to display and complete the steps.  You may want to enable paging to avoid all data shown in the same page.

The above steps should add a GridView, a SQL Data Source to the page.  Once you run the website, it should display the Products table record with the columns you chose to display.  So far, we have bound the local database.  We however, intended to bind it to SQL Azure database that we have created.

Binding the GridView to the SQL Azure Database Table

If you thought this is another big process, you might be in for a surprise.  Visit the SQL Azure portal (https://sql.azure.com) sign-in with your credentials that you used to create the token/database etc., Click on the “Manage” link that comes in the right of the Configuration Page that you get once you sign-in.  The next screen lists all the databases created.  Click on the “Northwind” database radio button to select it.   Click on “Connection Strings” in the bottom (the one in the top gives the connection string for master, so click on the “Connection Strings” button below the grid that displays the databases.   You should get a popup with 3 different connection strings targeting different data access methodologies.  Chose the first one (Click on “Copy to Clipboard”) corresponding to the entry for ADO.NET (the first one). 

In Visual Studio, open the web.config file of the site that we created earlier and visit the connectionstrings setting.  If you had followed the wizard steps, it would have entered a connection string over here, that corresponds to your local database server.

Replace the connection string with the one copied here.  Note, you need to update the password that you used instead of “mypassword”.  Also, remove the trailing semicolon at the end after password. 

If you have done all the steps properly, you should be able to run the solution now and without any other change, the data that is bound to your grid view should display the same as it did when you initially bound it to the local database.  You can try paging / sorting etc., to see if all works fine. 

So, practically, we just had to change the connection string to be able to access the database on the SQL Azure Server.  From a developer’s perspective, the data access technique, binding, control and configuration remains unchanged.

That’s it for this series.  There are quite a lot of steps that I had listed over the last 2 posts.  You may want to check every point if you are facing any issue at any stage.

Cheers !!!

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

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.,

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

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

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”

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.

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.    

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.

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. 

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

Cheers !!!