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
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
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
- 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
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
- 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"
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)
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.