Playing Around with SQL Azure and SSMS

The latest build of SQL Server Management Studio for SQL 2008 R2 includes built-in support for SQL Azure.  The November CTP is still only a feature-complete preview, not a release candidate, so don’t go installing it willy-nilly on your production desktop.

To use this, you’ll need a SQL Azure account.  You can register for an invitation code, and while you’re waiting, check out the SQL Azure Team Blog and the Microsoft SQL Azure site.

Create an Azure database and get the connection string.

Go to sql.azure.com and sign in with your Live account.  By default, your database list will just show master:

Azure Database List

Azure Database List

Click the Create Database button at the bottom and enter a new database name.  You can either pick 1GB max size or 10GB, and when Azure goes live, you’ll either be billed $10 per month or $100 per month.  Your database list will refresh.  Click on the new database name, and click the Connection String button to get the full string.  All you really need is the server name.

Azure Connection Strings

Azure Connection Strings

Open the SQL Azure Firewall

Just like SQL Server 2008 ships with remote connections disabled, so does Azure.  To open ‘er up, click the Firewall Settings tab in the SQL Azure web control panel and click Add Rule.

Adding a SQL Azure Firewall Rule

Adding a SQL Azure Firewall Rule

Enter a name and an IP address range and submit.  SUBMIT, I TELL YOU!  (Guys, seriously, it’s 2009.  Stop using S&M verbiage on your web forms.)

Open SQL Server Management Studio and Connect to SQL Azure

Open SSMS and start a new Database Engine connection.  Copy/paste the server name from the connection string you generated earlier.  Use SQL Authentication (not Windows trusted authentication) and enter the username and password you set up for SQL Azure.  (This is not your Microsoft Live account.)  Click Connect, and you get a normal Object Explorer browser:

SQL Azure in SSMS

SQL Azure in SSMS

Click View, Object Explorer Details, and you can see the compatibility level, recovery model, and collations – in my case, 100, full, and case-insensitive:

Object Explorer Details

Object Explorer Details

If your firewall settings haven’t taken effect yet, or if you didn’t set up a rule for your current IP address, you’ll get an error like, “Cannot connect to gr8clozgxt.database.windows.net.  Cannot open server ‘gr8clozgxt’ requested by the login. Client with IP address ‘85.18.245.70’ is not allowed to access the server. Log in failed for user ‘brento’. (Microsoft SQL Server, Error: 40615).”

Azure Connection Fail

Azure Connection Fail

Go back into the SQL Azure control panel and double-check your firewall rules.

Poking Around in SQL Azure

Now the fun starts.  First off, you can’t right-click on the server itself.  You can right-click on a user database, but there’s no Properties or Tasks.  If you right-click in the Tables list and click New Table, you don’t get a GUI – you get a table creation script.  Put the mouse away, speedy, because you’ve got some scripting to do.  Speaking of scripting, there’s a new option in SSMS for the type of database engine you’d like to script for, either “Standalone SQL Server” or “SQL Azure Database.”

Scripting Options

Scripting Options

You’ll find the master database in the System Databases tree, but no model or tempdb database.  There are no system tables visible in either your master database or your user databases, but there ARE system views like INFORMATION_SCHEMA.

Cross-database querying is a little odd.  In Object Explorer, click on the master database and click New Query.  The database dropdown is simply grayed out, and you can’t change databases once a connection has started.  Try to change it with a USE statement, and you get the following error:

You can’t specify the master database name, either.  You can query master system views like sys.bandwidth_usage, sys.database_usage, sys.firewall_rules, and sys.sql_logins only from the master database.

Querying sys.firewall_rules

Querying sys.firewall_rules

If you try fully qualifying their names like this:

You get an error:

Or this one:

Innnteresting.  Not everything’s quite hooked up in the Object Explorer either – try drilling into the Replication Options, and you’re told that “Operation not supported on version 10.25. (Microsoft.SqlServer.SqlEnum)”.

Not So Fast

Not So Fast

All in all, it’s an exciting step.  So let’s poke around the way a production DBA would:

Things That Don’t Work in Azure

Try this:

And you get this:

And same with our old buddy sp_configure – he’s not found either.

Try this (I picked spid 157 because that was my current logged-on one, and I found that high number rather interesting, but that’s a story for another post):

And you get this:

Same thing with CHECKDB.  All my good friends in the DMVs are MIA, too.

Which brings me to the final thing that doesn’t work in Azure – the production DBA.  In short, anything a production DBA would rely on isn’t there – and that’s because in theory, at least, Azure doesn’t need production DBAs.  Azure’s $100/mo 10GB pricing only seems expensive until you compare it to the cost of a full-time DBA for a small business.  I, for one, welcome our new Azure overlords, but only if they knock performance, availability, and scalability out of the park.

Is there anything you would like to see tested and documented about SQL Azure?  Let me know and I’ll take my best shot.  I plan on running some ballpark performance numbers over the coming weeks for starters.

Previous Post
PASS Recap: Discussions with PASS Board Members
Next Post
Why Dedupe is a Bad Idea for SQL Server Backups

11 Comments. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}