Manage SQL Azure Databases with the Web Interface 'Houston'

Exclusive offer: get 50% off this eBook here
Microsoft Azure: Enterprise Application Development

Microsoft Azure: Enterprise Application Development — Save 50%

Straight talking advice on how to design and build enterprise applications for the cloud using Microsoft Azure with this book and eBook

$26.99    $13.50
by Jayaram Krishnaswamy | January 2011 | Microsoft

In the wake of Microsoft Windows Azure platform many programs both Microsoft and third party were developed to manage SQL Azure. SQL Server Management Studio (SSMS) is a program that can manage SQL Azure although SSMS works well with SQL Azure, the preferred version of SQL Server 2008 is R2.

This article by Jayaram Krishnaswamy, is based on a project named 'Houston' which is a web based SQL Azure management tool that has not gone into production but can be tested using the SQL Azure Labs portal at http://www.SQLAzureLabs.com. In this article we look at some of the features of this web based tool and carry out a few tasks. Houston provides the following basic database management tasks:

  • Authoring and executing queries
  • Designing and editing database schema
  • Editing table data

 

Microsoft SQL Azure Enterprise Application Development

Microsoft SQL Azure Enterprise Application Development

Build enterprise-ready applications and projects with SQL Azure

  • Develop large scale enterprise applications using Microsoft SQL Azure
  • Understand how to use the various third party programs such as DB Artisan, RedGate, ToadSoft etc developed for SQL Azure
  • Master the exhaustive Data migration and Data Synchronization aspects of SQL Azure.
  • Includes SQL Azure projects in incubation and more recent developments including all 2010 updates

Appendix

        Read more about this book      

(For more resources on Azure, see here.)

In order to use this program and follow the article you should have an account on the Windows Azure Platform on which preferably an SQL Azure server has been provisioned. This would also imply that you have a Windows Live ID to access the portal.

As mentioned, in this article we look at some of the features of this web based tool and carry out a few tasks.

Click the Launch Houston button in the Project Houston CTP1 page shown here on the SQLAzureLabs portal page.

Manage SQL Azure Databases with the Web Interface Houston

This brings up a world map displaying the current Windows Azure Data Centers available and you have to choose the data center on which you have an account. For the present article we will use the Southeast Asia data center and sometimes the North Central US data center.

Manage SQL Azure Databases with the Web Interface Houston

Click on Southeast Asia location. The Silverlight application gets launched from the URL: https://manage-sgp.cloudapp.net/ displaying the license information that you need to agree to before going forward.

Manage SQL Azure Databases with the Web Interface Houston

When you click OK, the Login in page is displayed as shown.

Manage SQL Azure Databases with the Web Interface Houston

You need to enter the server information at the Southeast Asia data center as shown. Click Connect.

The connection gets established to the above SQL Azure server as shown in the next image.

Manage SQL Azure Databases with the Web Interface Houston

This is much better looking than the somewhat ‘drab’ looking SSMS interface (albeit fully mature)shown here for comparison.

Manage SQL Azure Databases with the Web Interface Houston

Changing the database

If you need to work with a different database, click on Connect DB at the top left of 'Houston' user interface, as shown in the next image.

Manage SQL Azure Databases with the Web Interface Houston

The conneciton interface comes up again where you indicate the name of database as shown.

Manage SQL Azure Databases with the Web Interface Houston

Here the database has been changed to master. Click Connect now connects you to the master database as shown.

Microsoft Azure: Enterprise Application Development Straight talking advice on how to design and build enterprise applications for the cloud using Microsoft Azure with this book and eBook
Published: December 2010
eBook Price: $26.99
Book Price: $44.99
See more
Select your format and quantity:
        Read more about this book      

(For more resources on Azure, see here.)

Manage SQL Azure Databases with the Web Interface Houston

If there is an error in the entry, the page spawns a link to the Error as shown and when you click on the link the window rotates to display the error message as shown.

Manage SQL Azure Databases with the Web Interface Houston

And after rotation displays the messaage:

'Failed to retrieve connection information. Try again'.

However, you cannot have two connections open at the same time in the same browser but using two browsers you can be working on two databases as shown here (Super imposed view from two browsers).

Manage SQL Azure Databases with the Web Interface Houston

Working with queries

The New Query menu item can be used to create a new query against the connected database as shown.

Manage SQL Azure Databases with the Web Interface Houston

Click New Query and in the opened Query Editor type in your query and click the Execute menu item. The Result/Message tabbed pages should display as shown.

Manage SQL Azure Databases with the Web Interface Houston

This is an empty table as there are no data rows. Now if you click dbo.fromMySql on the left, the column inform ation is displayed as shown here.

Manage SQL Azure Databases with the Web Interface Houston

Here is something new that people have been looking for in a SQL Azure tool. Using SSMS you can only use T-SQL for creating or looking at objects (with the exception of Delete which brings up a GUI). However as you can see above you can have the table in a design view. What is more, you can even edit the table in design view)

Let us try to populate the table with a few values using the following query,

Insert into fromMySql (id, Month,Temperature, RecordHigh) values (

1,’January’, 32,40)

You will receive the following error message.

Manage SQL Azure Databases with the Web Interface Houston

This is exactly what would happen when you run a query is SSMS. In SQL Azure, tables without a clustered index are not supported and a clusetered index is necessary when you want to insert data into the table.

Let us modify this table so that the Id column has a clusetered index by modifying the table design as shown here,

Manage SQL Azure Databases with the Web Interface Houston

Manage SQL Azure Databases with the Web Interface Houston

The modification made to the ID column shown above should be saved to apply changes to the table using the Save menu item shown here.

Applied Architecture Patterns on the Microsoft Platform An in-depth scenario-driven approach to architecting systems using Microsoft technologies with this Applied Architecture Patterns book and eBook
Published: September 2010
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:
        Read more about this book      

(For more resources on Azure, see here.)

Manage SQL Azure Databases with the Web Interface Houston

Click Save and the changes will be saved.

With this change to the ID column now you will be able to insert data as shown in the next window.

Manage SQL Azure Databases with the Web Interface Houston

Now you can verify the data with the select statement as shown.

Manage SQL Azure Databases with the Web Interface Houston

Copying a database

The lastest update SU4 which was released on Aug 24 allows us to make a copy of a database in the same server or on a different server in the same sub-region. Here let try to make a copy of BlueskyBengaluru to BlueskyMumbai. In order to do this we must be connected to the master database on this server. We have seen earlier how to connect to a different database. Now connect to master and execute the following statement:

Create datebase BlueskyMumbai as copy of BlueskyBengaluru

Manage SQL Azure Databases with the Web Interface Houston

Now you can see that you have created BlueskyMumbai, a snapshot copy of BluseskyBengaluru.

Manage SQL Azure Databases with the Web Interface Houston

SQL Azure and Schemas

Schemas dates back to SQL Server 2005 and are containers of obejcts (Tables, Views etc). Using Information Schema Views it is easy to look up and filter Table and Cloumn schemas. Schemas can be created independent of users and what is important for a schema is a schema name and a schema owner which can be set up independent of who owns the objects.

Let us take a look at the Table schemas in the BlueSync database on the SQL Azure server in North Central US. Since Houston is not SSMS there is no GUI to look up or create the schema. For now it will be T-SQL only.

Let us first of all find all schemas in the database. You could expand the Security node in SSMS for the particular database and get at the Schemas nodes, but here let us run a query in Houston to find the same as shown here.

Manage SQL Azure Databases with the Web Interface Houston

Can we look at table schemas? Of course you can. You will have to use the information-schema views in the database as shown here.

Manage SQL Azure Databases with the Web Interface Houston

Let us query some of the items from this list for our BlueSync database on SQL Azure in the Houston project page.

Here is the Tables schema. You could use SQL to further filter from the rows returned.

Manage SQL Azure Databases with the Web Interface Houston

We could also find the column information from the following query:

Manage SQL Azure Databases with the Web Interface Houston

You want to find constraints, no problem, run the following query:

Manage SQL Azure Databases with the Web Interface Houston

Of course you can also create Schemas of your own using the create schema statement in T-SQL as shown here.

Manage SQL Azure Databases with the Web Interface Houston

Followed by Select * from Sys.schemas

Manage SQL Azure Databases with the Web Interface Houston

Note that Hodentek and db_owner share the same principal_id.

Summary

The Houston project on the SQL Azure developer’s portal is described in some detail. The web based database management tool is extremely useful to work with SQL Azure spanning a number of data centers across the globe. The author found that sometimes the queries and connections work faster with Houston than using SSMS. Perhaps this needs corroboration from other users.


Further resources on this subject:


About the Author :


Jayaram Krishnaswamy

Jayaram Krishnaswamy studied at the Indian Institute of Science in Bangalore India and Madras University in India and taught at the Indian Institute of Technology in Madras. He went to Japan on a Japanese Ministry of Education Research scholarship to complete his PhD in Electrical Engineering from Nagoya University. He was a Post-Doctoral Fellow at Sydney University in Australia; a Government of India Senior Scientific Officer at the Indian Institute of Science in Bangalore and Indian Institute of Technology at Kanpur; a Visiting Scientist at the Eindhoven Institute of Technology in the Netherlands; a visiting Professor of Physics at the Federal University in Brazil; an Associate Research Scientist at a government laboratory in São Jose dos Campos in São Paulo, Brazil; a visiting scientist at the National Research Council in Ottawa, Canada before coming to USA in 1985. He has also taught and worked at the Colorado State University in Fort Collins and North Carolina State University in Raleigh, North Carolina. He worked with Northrop Grumman Corporation on a number of projects related to high energy electron accelerators and Free Electron Lasers. These projects were undertaken at the Brookhaven National Laboratory in Long Island and in the Physics Department at Princeton University. He has over 80 publications in refereed and non-refereed publications and 8 issued patents. He is fluent in Japanese and Portuguese and lives in Honolulu, Hawaii, USA.

He has been working in IT-related fields since 1997. He was once a Microsoft Certified Trainer in Networking and a Siebel Certified developer. He has worked with several IT related companies, such as the Butler International in their Siebel practice, with several IBM sub-contractors and smaller companies. Presently he is active in writing technical articles in the IT field to many online sites such as http://CodeProject.com, http://APSFree.com, http://DevShed.com, http://DevArticles.com, http://OfficeUsers.org, http://ASPAlliance.com, Egghead Café, http://SSWUG.org, Packt Article Network, http://databasedev.co.uk, http://cimaware.com, and many others. Between 2006 and 2010 he wrote more than 400 articles mostly related to database and web related technologies covering Microsoft, Oracle, Sybase, ColdFusion, Sun, and other vendor products.

He has written four books all published by Packt related to Microsoft Database and Application Development: SQL Server Integration Services Using Visual Studio 2005, Learning SQL Server Reporting Services 2008, Microsoft SQL Azure; Enterprise Application Development, and Microsoft Visual Studio Lightswitch Business Application Development. He regularly writes for his four blogs on Blogger; http://Hodentek.blogspot.com, http://HodentekHelp.blogspot.com, http://HodentekMobile.blogspot.com, and http://HodentekMSSS.blogspot.com. He received the 2011 Microsoft Community Contributor award.

Contact Jayaram Krishnaswamy

Books From Packt


.NET Compact Framework 3.5 Data Driven Applications
.NET Compact Framework 3.5 Data Driven Applications

Microsoft Forefront UAG 2010 Administrator's Handbook
Microsoft Forefront UAG 2010 Administrator's Handbook

NHibernate 3.0 Cookbook
NHibernate 3.0 Cookbook

Microsoft Dynamics GP 2010 Implementation
Microsoft Dynamics GP 2010 Implementation

Microsoft Windows Workflow Foundation 4.0 Cookbook
Microsoft Windows Workflow Foundation 4.0 Cookbook

WCF 4.0 Multi-tier Services Development with LINQ to Entities
WCF 4.0 Multi-tier Services Development with LINQ to Entities

Software Testing using Visual Studio 2010
Software Testing using Visual Studio 2010

Microsoft Windows Communication Foundation 4.0 Cookbook for Developing SOA Applications
Microsoft Windows Communication Foundation 4.0 Cookbook for Developing SOA Applications


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
9
z
u
r
F
d
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software