SQL Azure: A First Contact

SQL Azure… Yep, that is SQL Server on the Cloud!

A few days ago I received my SQL Azure (CTP) invitation code. The first thing I naturally did right after this was to create and access a database on the Cloud! I was very curious to see how I could interact with a database hosted on the cloud.

After getting access to the SQL Azure Service (you can register for the CTP here), you can easily create a database by clicking on the “Create Database” button.

The default database is the well-known “master” database.
I called the database I created “sqlazure”.

So, an empty database is created, what next? One of the options is to connect to the database using SQL Server 2008 Management Studio (SSMS)!

I will provide the procedure I followed (with screenshots) in steps:

Step 1
Select the database hosted on the cloud in SQL Azure and click on the “Connection Strings” button:

When clicking on that button you will get three connection strings for the following:

• ADO .NET
• ODBC
• OLE DB

In all the connection strings, you can find the same server value in the form of Server=tcp:[full_server_name]. Note down the full server name or just copy it to your Windows clipboard.

Step 2
Start SQL Server 2008 Management Studio (SSMS).
On the connection dialog that appears when starting SSMS, click on the “Cancel” button.

Step 3
In SSMS click on “New Query”. The connection dialog appears again.

On the connection dialog enter the following information:

• Server Name: The full server name as provided by the SQL Azure connection string.
• Authentication: SQL Server Authentication.
• Login: The SQL Azure server admin username.
• Password: The SQL Azure server admin password.

Also, before clicking the “Connect” button, click on “Options” and under the “Connection Properties” tab, in the “Connect to database” field, enter the database name which is hosted on the cloud and you want to access*.

* In this case you have to note that if you do not specify the database in the connection properties, you will be automatically connected to the “master” database. Though, once connected to a database on SQL Azure, it is not yet possible to use the “USE” command for switching to another database. The only way for switching to another database, is to establish a new connection on that database.

OK, after selecting the database on which you want to connect (in this example sqlazure) click on the “Connect” button.

By the way, you will get an error message about ANSI_NULLS but you can ignore this and continue.

That’s it! You are now connected to your database hosted on the cloud and you can issue queries using SSMS query window!

Step 4
Finally, let’s run some queries against our database:

-- Create a table
CREATE TABLE CLOUD_MSGS(
msgID int,
msgDescr varchar(100)
)
GO

-- Create unique clustered index
CREATE UNIQUE CLUSTERED INDEX msgID_ind
ON CLOUD_MSGS (msgID)
GO

-- Populate table with data ... using Row Constructors! :)
INSERT INTO CLOUD_MSGS(msgID,msgDescr)
VALUES (1,'Welcome to SQL Azure'), (2,'Hello World!'), (3,'SQL is on the Cloud!')
GO

-- Display the contents of the "CLOUD_MSGS" table
SELECT *
FROM CLOUD_MSGS

Resources

Below you can find some useful resources regarding SQL Azure. Note that this is pre-release documentation and is subject to change in future releases:

• SQL Azure MSDN Dev Center
• SQL Azure Help Documentation on MSDN DevCenter