[ Send this article ]
Your message has been sent.
How would you like to send this article:
[ Save this article ]
Save this article to your account for easy access.
This article has been saved to your account.
Go to my account
This article has been emailed to your Kindle.
Send this article
Complete the form below to send this article, Manage SQL Azure Databases with the Web Interface 'Houston', to a friend (or to yourself). We will never share your details (or your friend's) with anyone. For more information, read our Privacy Policy.
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
| 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.
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.
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.
When you click OK, the Login in page is displayed as shown.
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.
This is much better looking than the somewhat ‘drab’ looking SSMS interface (albeit fully mature)shown here for comparison.
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.
The conneciton interface comes up again where you indicate the name of database as shown.
Here the database has been changed to master. Click Connect now connects you to the master database as shown.
| Read more about this book |
(For more resources on Azure, see here.)
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.
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).
Working with queries
The New Query menu item can be used to create a new query against the connected database as shown.
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.
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.
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.
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,
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.
| Read more about this book |
(For more resources on Azure, see here.)
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.
Now you can verify the data with the select statement as shown.
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
Now you can see that you have created BlueskyMumbai, a snapshot copy of BluseskyBengaluru.
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.
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.
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.
We could also find the column information from the following query:
You want to find constraints, no problem, run the following query:
Of course you can also create Schemas of your own using the create schema statement in T-SQL as shown here.
Followed by Select * from Sys.schemas
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:
- SSIS Applications using SQL Azure [article]
- A Guide for Migrating Data to the Cloud [article]
- Microsoft SQL Azure Tools [article]
- Web Services in Microsoft Azure [article]
- Microsoft LightSwitch Application using SQL Azure Database [article]
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.
Books From Packt
|
|
|
Post new comment