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

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

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

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

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

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

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

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:

Msg 40508, Level 16, State 1, Line 1
USE statement is not supported to switch between databases. Use a new connection to connect to a different Database.

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

If you try fully qualifying their names like this:

SELECT * FROM master.sys.firewall_rules

You get an error:

Msg 40515, Level 16, State 1, Line 1
Reference to database and/or server name in 'master.sys.firewall_rules' is not supported in this version of SQL Server.

Or this one:

Msg 10054, 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 existing connection was forcibly closed by the remote host.)

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

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:

EXEC dbo.sp_who

And you get this:

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.sp_who'.

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):

DBCC INPUTBUFFER(157)

And you get this:

Msg 40518, Level 16, State 1, Line 1
DBCC command 'INPUTBUFFER' is not supported in this version of SQL Server.

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.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts

Here’s my bookmarked links for September 25th through October 2nd:

SQL Server, Cloud, and Tech Links

• Viva la Famiglia! – Brad Schulz shows how to use T-SQL to determine family relations. This is freakin’ awesome.
• PASS Board 2009 Slate of Candidates – Candidates are Brian Moran, Jeremiah Peschka, Matt Morollo and Thomas LaRock.
• PASS Elections and UStream | SQLRockstar – Tom LaRock is on the ballot again this year for the PASS Board of Directors, and you can ask him questions live in a video chat.
• Vote for Mr. LaRock – Grant Fritchey, author of SQL Server 2008 Query Performance Distilled, endorses Tom LaRock for PASS Board.
• Off-Hours Work: A Guide For Managers – If you have to rally the troops to work after hours, here’s how to do it with minimal morale loss.
• Interview with Kevin Kline – Tom LaRock interviews the former president of PASS.
• One Million Pageviews – StackOverflow hits the one-million-pageviews-per-day milestone. And yes, the back end is just one single SQL Server on relatively cheap commodity hardware with local disks, not SAN.
• HOW TO: Get Started with Google Wave – I think I’m the only guy who’s not jumping up and down clapping about Wave. I think it’s technically awesome, but it doesn’t solve challenges that I can’t solve right now with other methods. I’m going to ride this one out (get it – ride – wave – okay, maybe not) and wait a couple of months to see if it has staying power. (This from a guy who signed up for Twitter the instant he saw it years ago.)
• ROI from MCM: A look back 6 months later. – A Microsoft Certified Master talks about the changes it’s had on his billing rates and negotiations.
• YES WE CAN!!! – Jen McCown’s announcement about winning the SQLServerPedia Dream Trip contest. Very cool.
• DPM 2010 Beta is available now – Microsoft System Center Data Protection Manager is a very different way of backing up your databases (among many other things) and I’m interested to see what they brought in this new release. Says “can backup SQL servers with ~700-800 DBs” – makes me wonder what that bottleneck is.
• The ABCs of Management Studio Shortcuts – List of keyboard shortcuts for SSMS. Control+I is a neat trick.
• The old INNER JOIN syntax vs. the new INNER JOIN syntax – I got lucky – I started with the “new” syntax years ago and never looked back. It’s way better.
• Sessions for new DBAs at PASS – Bill Graziano suggests which sessions make sense for first-time attendees and new DBAs.
• Free Training in October at the Quest Connect vConference – Kevin Kline and I will be doing presentations and taking questions in our booth.
• Pre-Compiled Stored Procedures: Fact or Myth – Grant Fritchey plays Mythbuster.
• Inside Amazon’s Cloud: Just How Many Customer Projects? – An analyst estimated customers are launching 50,000 virtual servers a day on Amazon EC2.
• SQL Management Pack Survey – Live on Connect – Use Microsoft System Center Operations Manager (the artist formerly known as MOM)? Now’s your chance to influence development of the SQL Server management pack.
• Google Docs OCR – Another example of how cloud computing changes everything. The more we move services like this into the cloud, the more we can find creative uses for the data.
• Strong Password Generator – Uses T-SQL code to generate strong passwords. Interesting example.
• Consolidation Planning – Mike Hillwig is looking for feedback on his server setups.
• 10 Useful Usability Findings and Guidelines – Lots of “ah-ha” moments about how to make your web site look better and attract more permanent visitors.
• SQL Server Best Practices: Guard the Backup Files – You know the deal: the only reason we do backups is so that we can do restores. Buck Woody talks more about it.
• First Look: Zipcar app for iPhone hits the road – Find nearby Zipcars with your iPhone, then unlock them. I gotta play with this now that I’m in a city with Zipcars.
• Windows 7 coming to netbooks in all its myriad forms – YES! We won’t have to put up with the bullshit 3-app “Starter Edition” on netbooks – you can get ‘em with any version of Windows 7.
• Random Gradient Wallpaper Generator – Does just what it says on the box. Windows only though.
• Run Everything Virtualized and Deduplicated : aka Chuck Anti-FUD – Talk about cutting edge – people are talking about running their virtual servers on SANs that are being deduped. That would reduce the storage footprint required for a lot of Windows machines, for example, that all share a lot of the same binaries.
• How do checkpoints work and what gets logged – By Paul Randal.
• No, I will not do your homework for you – When posting forum & StackOverflow questions, think about phrasing them in a way to make sure nobody thinks you’re posting homework assignments.
• SQLIOSim Parser by Jens Suessmeyer & Yours Truly – SQLIOSim pushes load through your SAN storage and can simulate SQL Server IO patterns. However, like SQLIO, the problem is interpreting the results, which get dumped out to text or XML. Now there’s a utility to make it easier.
• Mississippi leads the way: 3 MCM’s a requirement in State IT RFP – Mississippi is requiring Microsoft Certified Masters if a consulting company wants to compete for a particular project. Wow. Just wow.
• Windows Server 2008 Enterprise edition and Virtualisation – When you use Enterprise Edition, you can install 4 OS guests for free. Andrew Fryer explains.
• Heard it from a friend who… heard it from a friend who… – Yet another hilarious blog posting from Colin, aka @BenchmarkIT. This guy’s on a roll. This time, he’s talking about a daisy chain of purity check info.
• Toys and Tools – Mike Hillwig’s favorite SQL Server utilities. (And yes, there’s a lot of Quest stuff in there, and no, I didn’t put him up to that.)

Writing, Blogging and Networking Links

• Books or Blogs: My Perspective – Scott Lowe, a published author, talks about whether or not writing books still make sense. I’d add that books force you to a higher level of technical accuracy due to the number of editors you have to get through.
• Tips for Networking at PASS – Great tips on how to meet fellow database administrators and why it can pay off.
• Shirts for Speakers? – Of everything in my closet, my very favorite shirt is my PASSCamp Germany polo with my name monogrammed on it. You could have knocked me over with a feather when Christoph and Oliver gave that to me. It made a huge impression, and I’m still really proud to wear it.
• 9 Ways to Make Your Writing More Compelling – Does what it says on the label.
• Tweet 2001: A Social Media Odyssey – OCDQ – Obsessive-Compulsive Data Quality – A ton of links about how LinkedIn, Twitter, and other social networks can work for data people.

The Junk Drawer

• The 50 most interesting articles on Wikipedia « Copybot – Good way to blow an hour and learn stuff.
• Nine Workspaces Where Famous Folks Get Stuff Done [Workspaces] – Most of these are publicity shots, but they’re still useful to see how people like Steve Ballmer set up their offices.
• Penny Arcade! – The Distinction – What if football was an RPG? Wait – it is! Final Fantasy Football….
• Tokyo Preview: Honda EV-N concept is retro-adorable, has unicycle storage – Please, please build this car. Let the world have an electric car that has some semblance of personality.
• 9 Secrets of Truly Happy People – You don’t become happy by saying “Yes” to people in an effort to make them happy. You have to pay attention to yourself and your own needs too.
• Me, Darth Vader’s brother, Guy Kawasaki and the hijacker from The Usual Suspects. Just, you know, hanging out. – In which The Bloggess comes up with alternate versions of The Little Engine That Could, like “The little engine that can’t take a hint.”
• Eff You, Penguin – Not safe for work – uses funny animal photos with NSFW captions.
• Penny Arcade! – Love Bombs From On High – Jesus loves me, this I know, because he gave me Gyromancer and…

These bookmarks are automatically imported from my bookmarks at Delicious.com. If you’d like to get up-to-the-minute updates on what I’m bookmarking, you can subscribe to my bookmark RSS feed.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts

I’m going to show how to use a new feature in SQL Server 2008 Management Studio: the ability to query groups of servers simultaneously.

In Registered Servers, right-click on a group of servers and click New Query. What comes up will look like a normal query window, but pay close attention to the very bottom of the screen after we run a query:

SQL Server 2008 Management Studio Group Execute

“Group L” shows the name of my registered server group. I keep my lab servers in Group L for Lab.

“LAB\Administrator” is the login that was used for authentication. (Okay, you caught me, I’m logging in as the domain admin. Thank goodness this isn’t a blog post about security.)

“Master” is the database, of course, and it would make sense to use Master because it’s one of the few databases we know exist on every server.

“6 rows” is obviously the number of rows the query returned, but let’s take a look at the query and the number of rows:

SSMS 2008 Querying Multiple Servers At Once

I ran a “SELECT GETDATE()” against this group of servers, and SQL Server Management Studio did the hard work for me: it connected to every SQL Server in the group, ran that query, and then combined the results back into a single results grid. It automatically added a “Server Name” column at the beginning of my results to identify which server (and which instance) the results came from. If we ran a query that returned multiple rows per server, that would work fine too.

This query would be useful if we wanted to check the dates on all of our servers, but let’s be honest – that’s not a big problem for us database administrators. We’re geniuses.

Taking a SQL Server Inventory with SSMS 2008

Let’s go tackle a harder problem, like taking an inventory:

Getting Multiple SQL Server Versions

Run SP_Configure on Multiple Servers

In the above example, I’ve grabbed the SQL Server version for each of my instances. Now we’re starting to get somewhere, but what we really want is the kind of detailed information we can get from sp_configure, so let’s see how that looks:

Now we’re cookin’ with gas – but wait. Look at that first column. We’re getting lots of data back, multiple rows for each server, and it’s hard to compare this data back and forth. For example, maybe I want to see whether “allow updates” is turned on for all of my servers – so I should probably sort by “name”, right?

Can’t Really Order By with SSMS 2008 Group Execute

Uh oh – turns out that doesn’t work. Remember, SQL Server Management Studio simply runs this same query on every single instance, then dumps the data into the SSMS results window. It doesn’t process the query results together. You can’t “join” between tables on different servers – well, you can, but you have to set up linked servers, and that doesn’t really have anything to do with the Group Execute functionality.

Another thing you’ll want to do is take this data and insert it into a table. Again, no dice there – if you try to insert this into a temp table like this:

SELECT @@SERVERNAME INTO #MyServerList

SSMS will execute that query on every single server, so every server will end up with its own temporary table with one record in it. Not exactly what you wanted.

Next up on our feature list: the ability to schedule these multi-server queries and take action on the results. I might want to run a query every night checking for failed jobs or sp_configure changes. Again, not going to happen – this can’t be automated with a SQL Server Agent job. This functionality only lives in SQL Server Management Studio.

Be Aware of Case Sensitive Collations

One more thing you want to be aware of: if any of your servers are case-sensitive, then you need to write all of your group execute queries in the proper case. I recommend that if you’ve got any case-sensitive instances, then use a case-sensitive instance as your personal testbed server like on your workstation. That way, as you’re writing new utility queries, you’ll know for sure that they’ll succeed on your case-sensitive instances. It’s a real pain to bang out a hundred-line utility query only to find out you’ve got case errors all over the place when you try to execute it.

It might sound like I’m down on this feature, but I’m not: it’s really useful. I love using it to quickly find out a piece of information across lots of servers, like find out if there’s any locking or deadlocks going on. It’s also really useful if you’ve implemented a Central Management Server as a centralized list of your instances. But it’s not a solution for automated reporting or proactive reporting.

Worry not – SQL Server 2008 has a different new feature specifically aimed at automated, proactive management: Policy-Based Management.  More on that later.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts