Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Getting started with SQL Azure

By Jacob Sebastian,

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:

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.

Total article views: 9089 | Views in the last 30 days: 7
 
Related Articles
FORUM

SQL Server Client Connectivity Tools

SQL Server Client Connectivity Tools

FORUM

SQL Server Client Connectivity Tools

SQL Server Client Connectivity Tools

FORUM

Problem in client computer connection to SQL server

SQL server client tcp/ip connection problem

FORUM

How Sql Server 2005 counts clients

If we have a Sql Server with 0 client licenses and if we use adodb connection object

FORUM

Error = [Microsoft][SQL Native Client][SQL Server]Invalid object name 'dbo.auditrec'

Error = [Microsoft][SQL Native Client][SQL Server]Invalid object name 'dbo.auditrec'

Tags
azure    
cloud computing    
ssds    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones