Pinal Dave had a helpful post a few days ago about how to setup and configure SQL Azure. You will need a Windows Live ID, and an Invitation Code before you can configure SQL Azure.

Once you have configured SQL Azure and created a database, you can register your SQL Azure “instance” in SQL Server 2008 R2 November CTP using the Registered Servers tab in SSMS. Then you will be able to browse your database(s) in Object Explorer, which makes them a little easier to work with. One thing you will notice is that you have fewer folders under the database in Object Explorer with SQL Azure than you do with a regular, on premise version of SQL Server 2008. You only have Tables, Views, Synonyms, Programmability, and Security folders.

You will also notice that many of the graphical utilities that you may be used to in SSMS are not present when working with a SQL Azure database. These include the Table Designer, the Index Creation dialog, the ability to right-click on a table and view or edit records, etc. You are going to have to write some T-SQL yourself to create things like tables and indexes (which is not such a bad thing).

You also have to be aware of a few subtle differences when you create objects (such as the fact that you don’t have to worry about file groups, you cannot specify many options, etc.).  My samples below show a few of these differences.  You will have to get used to having SQL Azure disconnect you if you are idle for a few minutes, so you will see the error message below quite a bit. All you have to do is repeat the command, and SQL Azure will reconnect.

Msg 10053, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine.)

-- Fun with SQL Azure
-- Glenn Berry
-- 12-2-2009
-- http://glennberrysqlperformance.spaces.live.com
-- Twitter: GlennAlanBerry
-- SQL Azure will drop your connection after a few minutes of inactivity
--Drop table if it exists
IF OBJECT_ID('dbo.UserAccount', 'U') IS NOT NULL
  DROP TABLE dbo.UserAccount;
GO
-- Notice no file group or other options allowed in CREATE TABLE for SQL Azure
CREATE TABLE [dbo].[UserAccount](
    [UserID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar](256) NOT NULL,
    [Password] [nvarchar](50) NOT NULL,
    [CreateDate] [datetime] NOT NULL,
    [ExpireDate] [datetime] NULL,
    [FName] [nvarchar](50) NOT NULL,
    [LName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_UserAccount] PRIMARY KEY CLUSTERED 
([UserID] ASC));
-- On Premise version of CREATE TABLE
-- Does not work with SQL Azure
CREATE TABLE [dbo].[UserAccountLocal](
    [UserID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar](256) NOT NULL,
    [Password] [nvarchar](50) NOT NULL,
    [CreateDate] [datetime] NOT NULL,
    [ExpireDate] [datetime] NULL,
    [FName] [nvarchar](50) NOT NULL,
    [LName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_UserAccountLocal] PRIMARY KEY CLUSTERED 
(
    [UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
-- Drop index if it exists
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[UserAccount]') 
            AND name = N'IX_UserAccount_UserName')
DROP INDEX [IX_UserAccount_UserName] ON [dbo].[UserAccount];
GO
-- Notice no options allowed in CREATE INDEX for SQL Azure
CREATE NONCLUSTERED INDEX [IX_UserAccount_UserName] ON [dbo].[UserAccount] 
(
    [UserName] ASC
);
-- On Premise version of CREATE INDEX
-- Does not work with SQL Azure
CREATE NONCLUSTERED INDEX [IX_UserAccount_UserName1] ON [dbo].[UserAccount] 
(
    [UserName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
       DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
GO
-- This works in SQL Azure
TRUNCATE TABLE dbo.UserAccount;
-- Insert a few rows
INSERT INTO dbo.UserAccount(UserName, [Password], CreateDate, [ExpireDate], FName, LName)
VALUES('GlennBerry', 'testpassword', GETDATE(), '12/31/2010', 'Glenn', 'Berry');
INSERT INTO dbo.UserAccount(UserName, [Password], CreateDate, [ExpireDate], FName, LName)
VALUES('JessicaAlba', 'testpassword', GETDATE(), '12/31/2010', 'Jessica', 'Alba');
INSERT INTO dbo.UserAccount(UserName, [Password], CreateDate, [ExpireDate], FName, LName)
VALUES('MirandaKerr', 'testpassword', GETDATE(), '12/31/2010', 'Miranda', 'Kerr');
-- See what we have in the table (notice GETDATE() actually returned a UTC Date)
SELECT UserID, UserName, [Password], CreateDate, [ExpireDate], FName, LName
FROM dbo.UserAccount;
-- Get the UTC Date
SELECT GETUTCDATE();
-- This works in SQL Azure
SET STATISTICS IO ON;
-- Create a stored procedure
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetUserInfoByID]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetUserInfoByID]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetUserInfoByID]
(
    @UserID int
)
AS
    BEGIN
        
        SET NOCOUNT ON;
        SELECT UserID, UserName, [Password], CreateDate, [ExpireDate], FName, LName
        FROM dbo.UserAccount
        WHERE UserID = @UserID;
        
        RETURN;
        
    END
GO
-- Call a stored procedure
EXEC dbo.GetUserInfoByID 1;
EXEC dbo.GetUserInfoByID 2;