Manage SQL Azure Databases with the Web Interface 'Houston'

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

 

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.

        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.

        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:


Books to Consider

comments powered by Disqus
X

An Introduction to 3D Printing

Explore the future of manufacturing and design  - read our guide to 3d printing for free