Kicking the tires on SQL Azure

Last week I got my token to activate my SQL Azure service and took a few minutes to take it for a test drive.  If you haven’t heard about SQL Azure it’s the re-launch of SQL Data Services (SDS).  SDS used to be a much more restrictive model that really didn’t have any hope for sharing code between a traditional SQL Server and the cloud.  At Mix09 it was announced that SDS was going to go through some major changes for the best and move towards allowing more fuller relational and a powerful subset of TSQL support.  Recently in July, SDS was renamed to SQL Azure to better align the name.

Like other Azure services once I got the token, I just went to the portal and there’s now a SQL Azure section – after registering my token it allowed me to create a SQL Azure Instance and then I proceeded to create a new database via the web interface – this all happened really fast and painless. Now with a new database, I set out to figure out how to connect using SQL Studio. From the portal you can get your connection information but there’s a few quirks with the CTP you have to deal with.  Zach owns blog post on using SQL Azure is the best place to start to figure out how to connect.  Like I said it’s a little quirky, and you have to be patient with the timeouts that kick you off if you leave it idle, but the fact that I can use SQL Studio to query the data in the cloud was a huge plus!

Next, I wanted to move some table definitions from my local SQL server to my SQL Azure database.  There’s not a copy /upload database option yet – lets hope that gets added but in the mean time I found a couple of things that worked.  First, I tried the create script from within SQL Studio.  Using that approach I copied it into my query window for the new database and gave it a try.  You have to tweak the SQL some because things like Using database isn’t supported in the CTP  There’s some other keywords that aren’t supported either I just kept deleting them till I got my table create to run!

After some experimenting I actually found using Red Gates compare was the best approach because it would script multiple tables at once and the SQL seemed to be cleaner for what SQL Azure wanted (cleaner, not perfect I still had to do some tweaking).  The way I tried this was to create another empty DB locally that I would do the compare against to create my script.  That also worked as I modified and made changes to the tables I would just do the compare to that local copy and run the change script (slightly tweaked) on both the local copy and my SQL Azure db.  Who knows, maybe Red Gate will come out with a version specifically for doing this as I bet they could get something out quicker than MS tweak SQL Studio.

The database I moved to SQL Azure had a few tables that supported an application that used Entity Framework.  This is the part where I emphasize the improvements between the prior SDS and SQL Azure.  I simply took my connection string, pointed it to the cloud  and I was off and running.  Now I’m not saying you will find SQL in the cloud a seamless transition for every application and clearly there are some inherit things that are different with SQL Azure that you need to learn.  But compared to the prior SDS, I’m impressed and like where we are heading.  After almost a week of running this small database in the cloud with an application I use daily, so far so good!

Speaking of learning, there’s a lot more info on the SQL Azure dev center on MSDN you can find that here.

Comments

[Mario]

I'm still not sure, if I should use Table Storage or SQL Azure. All my needs are met with Table Storage, it's fast and scalable, except that my clients will feel much more deeply tied into Windows Azure, if ever they want to go back.

[Gaurav Mantri]

Hi David,

We just released a utility to manage your SQL Azure databases using a browser based interface.

To some extent, we have tried to provide all the functionality that SQL Azure currently offers. Using this utility, you can:

- Create/Alter/Drop Schemas
- Create/Alter/Drop Tables
- Create/Alter/Drop Views
- Create/Alter/Drop Stored Procedures
- Create/Alter/Drop User Defined Functions
- Create/Alter/Drop Roles (Please note that SQL Azure does not support Application roles yet)
- Create and execute some simple queries
- Do metadata search i.e. you can find out which tables or view contains a particular column by searching for that column.

To read more about it and try it out, please visit:
http://www.cerebrata.com/Blog/post/Browser-based-SQL-Azure-Explorer.aspx

Hope this helps!!!

Thanks

Gaurav Mantri
Cerebrata Software