Zach Skyles Owens

So, now that SQL Azure CTP1 has been released there are a few things that most people will probably run into when trying to connect to SQL Azure with SQL Server Management Studio (SSMS), SQLCMD and OSQL.

SQL Server Management Studio

Update March 2010: With the release of the SQL Server 2008 R2 Tools November CTP working with SQL Azure from SSMS is much better.  Please read my new post if you want to use the updated tools.

One of the best parts about working with SQL Azure is that you can use the tools that you already know and love.  There are some differences though with the System Views, System Stored Procedures, System Tables and SMO that break some of those tools.  The current version of SSMS 2008 is able to connect to SQL Azure but the Object Browser doesn’t work.  There is also a trick to getting connected.

When you fire up SSMS and are presented with connection dialog box, if you enter your credentials you will get the following error.

TITLE: Connect to Server
——————————
Cannot connect to MY_SERVER_NAME.ctp.database.windows.net.
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Invalid object name ‘sys.configurations’. (Microsoft SQL Server, Error: 208)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.9006&EvtSrc=MSSQLServer&EvtID=208&LinkId=20476

The key to getting around this is to click on the Cancel button on the initial dialogue box.

Then click on the New Query button.

You will be presented with a new connection dialogue that looks the same as the initial one.

Enter your credentials, if you are trying to connect to a specific database click on the Options and enter a database name because USE <database> is not supported.

Once you connect you will get a error

TITLE: Microsoft SQL Server Management Studio
——————————
Unable to apply connection settings. The detailed error message is: ‘ANSI_NULLS’ is not a recognized SET option.

You can just click OK.

Now you are good to go!  You can issue queries, visualize explain plans, etc.

SQLCMD and OSQL

For those of us who like to use command line tools, SQLCMD and OSQL are also supported.  The key when connecting with these tools is that you must use the <username>@<servername> in the connection string.

sqlcmd -S MY_SERVER_NAME.ctp.database.windows.net -U MY_USERNAME@MY_SERVER_NAME -d DATABASE_NAME

The key here is that you must use MY_USERNAME@MY_SERVER_NAME…  For example, if your server name is h38ssfjeiwh201, your username is admin and connecting to sample_db your connection would be.

sqlcmd -S h38ssfjeiwh201.ctp.database.windows.net -U admin@h38ssfjeiwh201 -d sample_db

Since SQL Azure doesn’t currently support the USE <database> you need to specify a database name.

More Information

• SQL Azure MSDN Docs – http://msdn.microsoft.com/en-us/library/ee336282.aspx 
• Connecting to SQL Azure Demo in the Windows Azure Platform Training Kit – http://www.microsoft.com/downloads/details.aspx?FamilyID=413E88F8-5966-4A83-B309-53B7B77EDF78

It’s a really exciting day for everyone involved in SQL Azure as today we’ve announced that CTP1 is available to customers.

Over the next couple of weeks everyone who has already requested an Invitation Code should be receiving one in an email. If you haven’t already requested on please do so here. Once we get through the list of developers who have already requested access, new requested will be processed in a day or two.

We’ve delivered a bunch of new SQL Azure content in the August release of the Windows Azure Platform Training Kit including:

Presentations

• Introduction to SQL Azure
• Building Applications using SQL Azure
• Scaling Out with SQL Azure

Demos

• Preparing your SQL Azure Account
• Connecting to SQL Azure
• Managing Logins & Security in SQL Azure
• Creating Objects in SQL Azure
• Migrating Database Schemas to SQL Azure
• Moving Data into and out of SQL Azure using SSIS
• Building a Simple SQL Azure App
• Scaling Out SQL Azure with Database Sharding

Hands on Labs

• Introduction to SQL Azure
• Migrating Databases to SQL Azure
• Building Your First SQL Azure App

The SQL Azure documentation on MSDN is also a great learning resource.

As the SQL Azure CTP comes closer we’ve released the documentation which can be found here
http://msdn.microsoft.com/en-us/library/ee336279.aspx

This should help developers who are waiting on their invitation token to get started learning about the details of SQL Azure.  Make sure you’ve signed up for access here
http://go.microsoft.com/fwlink/?LinkID=149681&clcid=0×09

Enjoy!

I was listening to an awesome speaker yesterday who said…  “Apparently, names have betas too.” which I think was a great way to put it.  A couple of weeks ago at WPC we announced a branding change to our RDMS in the cloud.  It was originally called SQL Server Data Services (SSDS) when it was originally released.  We rebranded it to SQL Data Services (SDS) as of PDC last year.  It is now called SQL Azure.  SQL Azure is the umbrella term for the database platform components that we will be launch into the Windows Azure platform, the first of which is SQL Azure Database (SADb).  David Robinson wrote a great post here.

Stayed tuned…  Within the next week or so the SDS team will be publishing more details on what TSQL features will be supported.  There are three basic categories:

• Supported
• Partially Supported
• Unsupported

As you might expect, most DML is Support, a lot of Manageability stuff is Unsupported and certain DDL clauses fall into the Partially Supported category.

I’ll follow-up once the details are released.