SQL Azure CTP 1

Back in March, the SQL Data Services team announced the revamping of the ‘database in the cloud’ offering to include a fully-relational capability versus the Entity-Attribute-Value (EAV) model that was unveiled as part of the Azure Services Platform at PDC.  More recently, at the Worldwide Partner Conference, we also learned that SQL Data Services is now to be known as SQL Azure, and the Azure Services Platform has been rebranded as the Windows Azure Platform.

Today marks another major step toward the official launch of Windows Azure, the start of the first SQL Azure Community Technology Preview (CTP).  In order to participate in the CTP, you first need to procure a SQL Azure token.  When your request is fulfilled – be patient, as the team is rolling this out in a controlled fashion – you’ll receive an invitation code that you can then redeem via the SQL Azure portal.  On the portal, you can create a database and provide the SQL Server administration account required to access the database (at this point integrated security is not supported).  Here for instance, is a snapshot of my portal after creating a new database called azureDB:

 

 

The server name is generated for you by the SQL Azure service, and note the "Connection Strings” buttons.  Pressing the button at the bottom of the screen (with my azureDB instance selected), yields the following pop-up, which provides the connection strings needed by clients connecting via ADO.NET and the SQL Native Client ODBC driver, the very same mechanisms you’re used to using with on-premises applications!

While you can also access SQL Azure via ADO.NET Data Services, which was the only option to communicate with SQL Azure’s predecessor (SQL Data Services), support of the native protocol of Tabular Data Stream (TDS) enables the tooling and code constructs that you’ve been accustomed to using with standard SQL Server. 

In the example below, I access my SQL Azure database via sqlcmd, create a table, and insert some data.  It’s important to note here that since SQL Azure doesn’t support heap tables, a clustered index needs to be created before any inserts can be carried out.

Although not officially supported, you can also use SQL Server Management Studio to poke around at your SQL Azure database. 

• The server name is what was provided to you when you redeemed your token and is of the form <server>.ctp.database.windows.net.
• Your login (recall, only SQL Server Authentication is supported) should be followed by @<servername>.
   
• You must specify the database on the Connection Properties tab that you access via the Options>> button on the initial dialog.  SQL Azure does not yet support the USE statement, so you must specify the database as part of the connection string, and you cannot change the database within a connection.
  

If you try to connect, you may be greeted with the rather final-sounding message:

 

This occurs because the default setting of SQL Server Management Studio is to open the Object Explorer, which is not supported for SQL Azure at this point.  You can circumvent the startup error by modifying the Environment settings (via Tools>Option) to not open Object Explorer.  Or you can instead open a new Query window via the Query>Connection>Connect… dialog (or simply click the New Query button on the toolbar), and then provide the connection options outlined above.

Now when you connect, you’ll still get an error message indicating lack of support for the ANSI_NULLS setting, but you can dismiss the message box and proceed.  (Check here for more information on Transact-SQL constructs not supported in SQL Azure).

 

Now, you can issue a standard SQL query, such as the following that selects the data I added in the sqlcmd session above.

 

That should get you started, and or more information on SQL Azure, including what aspects of ‘on-premises’ SQL Server are and are not supported in this initial release, check out the following links:

SQL Azure on the Windows Azure Platform site

SQL Azure Documentation on MSDN

SQL Azure Developer Center

Comments

• Anonymous
  August 19, 2009
  Jim, Thanks for the great post on SQL Azure.  I'm glad that Microsoft has taken this approach as opposed to sticking with a purely EAV model.  I think this will greatly increase developer adoption, and make it easier to migrate existing applications to Azure. Best regards, John R. Pattison

• Anonymous
  August 19, 2009
  You're welcome (and thanks as well for your kind words).  The relational approach was always an ultimate goal, but I agree that accelerating it was the "right thing."  It makes the move to the cloud so much easier for the majority of current applications, and for those needing that extreme scale that EAV provides, there's still some options there involving Azure storage.  Keep the feedback coming!

• Anonymous
  September 01, 2009
  Thanks for your post. I had kind-a figured out some of it but knowing WHY it's  not working is a lot better than 'just cancel and connect with New Query'.. LOL. I think I will also use sqlcmd instead of SSMS.  What we really need is a patch so VS 2008 can connect to it. Dave

• Anonymous
  September 01, 2009
  I know they're working on compatibility with SSMS (maybe for the launch at PDC?), hopefully that will just parlay into VS connecting to it as well.

• Anonymous
  October 13, 2009
  Does CTP 1 support the asp.net membership & roles provider install yet?

• Anonymous
  October 13, 2009
  Well the CTP is a stake in the ground, there aren't rolling public updates.  I did find a forum thread http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/82e666e7-05df-4e83-b95c-106671420d18 where an MVP had cleaned up the scripts to install and run them successfully on Azure.