What is SQL Azure?

SQL Azure is a cloud-based relational database platform built on SQL Server.

Some of the major advantages you could get from a cloud based database platform

is High Availability and Scalability. By creating a SQL Azure

Database, you are free from the administrative head-aches and the SQL Azure

platform will make sure that your database is available with almost no down

time. The database may be replicated to different data centers across the world

and in case of a problem with one data center, the requests will be automatically redirected to an

alternate location where an exact copy of the database is available. See SQL Azure Web site for more

information.

Getting started with SQL Azure

Since SQL Azure is a cloud based platform, you need to obtain a SQL Azure

account to get started. The current version of SQL Azure is CTP (Community

Technology Preview - A pre release version) and if you don't have a SQL Azure

account yet, you can obtain one by visiting this

web page and by clicking the "Register for the CTP" link. Once registered, you

will get an invitation code by e-mail. Don't give up if you experience a delay in getting the e-mail

with an invitation code. It may take a few days (it took me weeks)

before the invitation code is mailed to you. After waiting for a few days, if

you feel little frustrated, you could visit the SQL

Azure forum which is highly monitored by the SQL Azure Product Team and they

may quickly help you to get an invitation code.

I have created a SQL Azure Account, Now what?

After obtaining a SQL Azure account, the next action you might want to do is

to create a database, add tables and data, create stored procedures, views and

functions etc. There are a number of ways to connect to your SQL Azure database

and perform DDL/DML operations. As you would expect, you need a server address,

username and password to connect to a SQL Azure database. You can find this out

by logging into your SQL Azure account and from the "Server Administration" page.

Click on the "Connection Strings" button and it will show you valid

connection strings for ADO.NET, ODBC and OLEDB clients. For example, the OLEDB

connection string to my test database looks like the

following:
"Provider=SQLNCLI10;Server=tcp:abcxxxxxkse.ctp.database.windows.net;Database=master;Uid=jacob;Pwd=myPassword;"

There are 4 pieces of information that you might often need while working

with SQL Azure. You can identify them all from your connection string.

• Server Address: The address of the server. In the above example, it

  is "tcp:abcxxxxxkse.ctp.database.windows.net". This is what you need to

  enter in the server address field while trying to connect to the SQL Azure

  database using SSMS or any other client tool.

• Server Name:This is the identifier of the server. In the above

  example, the server name is "abcxxxxxkse". Some times you might need to

  enter your username in the format of "servername@username" in order to login

  to your SQL Azure database. I am able to login using my username only.

  However, I have seen many people are able to login only by entering

  "servername@username".

• Username:Well, this is your login name. In the above example, the

  login name is "jacob". You need to enter this value in the "Login Name"

  field of your client application to connect to your SQL Azure database. If you

  are not able to login with the username alone, try providing it in the form of

  "servername@username" which in this example will be "abcxxxxxkse@jacob"

• Password:Your password to access the SQL Azure database. In the

  above example, it is "myPassword"

Connecting to SQL Azure database using SQLCMD

As mentioned earlier, there are a number of ways you can connect to your SQL

Azure Database. One of the quick options is to try to connect to your SQL Azure

database using SQLCMD. The following code shows how to connect to SQL Azure from

SQLCMD command line.

-- username: jacob
-- password: jacob123
-- server : tcp:urrg21kkk.ctp.database.windows.net
sqlcmd -Ujacob@urrg21kkk -Pjacob123 -Stcp:urrg21kkk.ctp.database.windows.net -dmaster

Connecting to SQL Azure database using SQL Server Management

Studio

SQL Server Management Studio is the favorite tool for most SQL Server people.

You can connect to SQL Azure database using SQL Server Management Studio. Zach Skyles from the SQL Azure

product team has written a blog post that walks you through the steps to connect

to SQL Azure using SSMS. Please refer his article for a detailed

explanation. I will briefly summarize the steps here.

• Start SSMS
• Click "Cancel" in the connection dialog
• Click on the "New Query" button of SSMS
• In the connection dialog enter "Server Address", "Username" and

  "Password".

• Click on Connect
• If the information you provide is correct, a connection will be

  established to the SQL Azure database. SSMS will display the following error message that you can safely ignore:

  "Unable to apply connection settings. The detailed error message is

  'ANSI_NULLS' is not a recognized SET option".

• Click "OK" and SSMS will open a new query window that is connected to the

  SQL Azure database.

If SSMS is already open and if you are connected to one or more databases, if

you click on the "new query" button, it will create a new query window that is

connected to your current database. You wont be able to connect to a SQL Azure

database using the "Connect to Database Engine" button on the top of object

explorer or the "Connect Object Explorer" menu item from the "File" menu. To do

it, you should select "database engine query" from the "New" submenu under "File"

menu.

You can also specify the database that you want to connect to, from the "connection properties" tab of the SSMS connection dialog.

Where is Object Explorer?

Though SSMS is my favorite database client tool, it is not up to the mark for working with SQL Azure. I guess the next version of SSMS will be equipped with enough features and support for working with SQL Azure. One of the features that I personally would like to have in SSMS is the object explorer for SQL Azure database.

The object explorer is especially very useful when we have to alter a stored procedure or view. It allows to generate the alter script for the current object definition and then we could modify the script and run it again.

Enter SQL Azure Manager!

If you are unhappy with SSMS, SQL Azure manager might make you happy. It is a light weight client application that allows you to connect to a SQL Azure database and perform basic operations. You can download SQL Azure Manager from here.

I found the User Interface features of SQL Azure Manager much better than the current version of SSMS.

Though SQL Azure Manager has a much better UI than SSMS 2008, it is still lacking a lot of features in the object explorer. For example, it shows the views in object explorer, but does not allow to create the alter script for a view. It does not display stored procedures or functions in the object explorer.

Omega Web Client for SQL Azure

Omega Web Client has a great set of functionality. It has a much better object explorer. I have seen many people in the forums, having trouble with opening the Port (1433) in their corporate fire wall for accessing SQL Azure. Omega Client will be the ideal choice for them as well.

Makes sure that you check the 'SDS' check box in the connection dialog.

Use this dialog to select the database to connect to.

I am sure you will like the object explorer.

The Query builder page is very interesting. It has almost every functionality that you would expect from a query builder page.

I never expected such a feature, but it is cool to have!

Other SQL Azure Tools

There are a number of other SQL Azure tools available, that make your life easier, while working with SQL Azure.

• SQL Azure Verifier - A web page that allows you to verify your SQL Azure code. Note that SQL Azure supports only a limited subset of TSQL Feature. See this page for more details.
• SQL Server Migration Wizard - helps you migrate your local SQL Server 2005 / 2008 databases into SQL Azure. The wizard walks you through the selection of your SQL objects, creates SQL scripts suitable for SQL Azure, and allows you to edit / deploy to SQL Azure

SQL Azure Resources

There are a number of resources available on Internet, but they are pretty much scattered. I have been collecting some of them and putting into a SQL Azure Resource Center wiki page for easy reference. You might be interested in bookmarking, referring or contributing to those pages. Here is a quick reference of the resources available:

• Connecting to SQL Azure database
• SQL Azure Tools
• SQL Azure Articles, Tutorials and Blogs
• SQL Azure Training
• SQL Azure Videos
• SQL Azure - Migrating databases
• SQL Azure Samples
• SQL Azure Bloggers
• SQL Azure and SQL Server Integration Services (SSIS)

Conclusions

This article aims to help you to get started with SQL Azure. It does not cover the SQL Azure concepts or the technical details of SQL Azure platform. The focus of this article is to introduce some of the client tools that you might find to be interesting while working with SQL Azure. I may have missed some of the interesting points of one or more of these client tools, but I am sure some of you with more experience with those tools will come forward with your opinion in the discussion forum.

About the author

Jacob Sebastian is a SQL Server MVP, MSDN/Technet Moderator, Regional Mentor for PASS Asia, Author, Speaker and Trainer. See Jacob's Blog|Profile.