Windows Azure SQL Database Backups
You can get backups of your WASD databases. But, if you want them to be transactionally consistent, you need to do a little extra work. This video (with bad lighting at the start) shows how to get the job done.
Execution Plans in Azure SQL Database
Microsoft has stated pretty clearly that they're putting code on Azure first, ahead of the desktop. Which makes one wonder when we're going to start to see some of this cool stuff within SSMS. What cool stuff you ask? Well, let me explain. Let's start with a query: SELECT m.MovieName, msd.MovieStageName, st.StageTypeDesc FROM dbo.Movie AS m JOIN dbo.MovieStage AS ms ON m.MovieId = ms.MovieID JOIN dbo.MovieStageDefinition AS msd ON ms.MovieStageDefinitionId = msd.MovieStageDefinitionId JOIN dbo.StageType AS st ON msd.StageTypeId = st.StageTypeId WHERE m.MovieId = 42; When I run this on Windows Azure SQL Database (WASD) I get the following execution plan: Kind of weird, kind of useful, right? First thing new that I can do is zoom in using that slider bar you seen in the lower left and then graphical…
New DMO in Azure SQL Database: sys.dm_db_wait_stats
I just did a series of Boogle searches and when that didn't find anything I tried Ging. Neither listed sys.dm_db_wait_stats. Nothing in a search directly against MSDN either. So, let me introduce you to a new DMO, sys.dm_db_wait_stats. It's a dynamic management view since it doesn't require any input. The output is about what you would expect if you thought about it for a second: In short, what we have is the sys.dm_os_wait_stats moved internally into your SQL Database so, even though you cannot get at any of the OS counters from with an a SQL Database normally. In short, thanks Microsoft. Now we can see the wait statistics on our Azure SQL Database in order to better understand where things are problematic. Without documentation I don't know for…
Azure SQL Database Execution Plan Differences
I've been exploring execution plans in Azure SQL Databases a lot lately. I'm getting a presentation together for  some upcoming SQL Saturday events (first one is SQL Saturday #177, Silicon Valley). If you scroll to the bottom of this previous post, I mentioned that there were clearly differences in the optimizer because queries against empty databases were generating different plans. I've loaded up the data in my database, both SQL Server and SQL Database, so I can compare real behaviors. Doing so, I found a fun difference, even though I was running the query and generating the plan from SQL Server Management Studio. Here's the property sheet from the SELECT operator for the query run against SQL Server: And here's the property sheet from the SELECT operator for the query…
Saving Execution Plans on Azure SQL Database
In my previous post showing how to get to execution plans in the Database Management Portal, I showed that it's pretty easy to put a query in a query window and get the execution plan. This allows you to understand query behavior in order to tune your T-SQL or your data structures, all through the Azure interface. But, what happens if you want to share an execution plan with a friend, post it to an online forum, save it for later comparisons as part of troubleshooting bad parameter sniffing, track behaviors over time as statistics change, other purposes that I can't think of at the moment? To first answer this question, let me tell you how you would do these things in SQL Server Management Studio (SSMS). First, and most…
Pro SQL Server 2012 Practices: Chapter 12
I was very privileged to get the opportunity to write a chapter in a book with some of my friends and SQL Family, Pro SQL Server 2012 Practices . Just as each of us took a chapter to write, each of us going to take a chapter to review. But, being the greedy sort, I'm going to review two. First up, Chapter 12, "Windows Azure SQL Database for DBAs", by Herve Roggero (b|t) Personally, I love Azure. And I love Azure SQL Databases. But, I get the fear and trepidation they might cause. I also get the urge to write about them, but I never really felt like I should approach them from a book. Everything changes so much, so quickly in Azure and books just take a while to…
Execution Plans on Azure SQL Database Portal
If you've been working with the Azure SQL Database portal, you know that you have a query window. Within the query window you can run queries against your server. You'll get back results, timing, everything you need to observe behavior within the system. What's that? You never heard of this? Let's figure it out real quick. Here's my portal to my Azure SQL Databases: [caption id="attachment_2296" align="alignnone" width="150"] Azure SQL Database Portal showing usage overview[/caption] Yes, all sorts of things you can do from here, but we're focused on the bottom of the screen at the Manage button. Clicking this button will open up the Management portal window, part of which you can see below: [caption id="attachment_2298" align="alignnone" width="150"] Part of the Azure SQL Management Portal showing some of what…
HDInsight, Finally
See this: That's right. The install worked. All I had to do was get a completely clean server set up. No domain controller. Now to get my learn on. Microsoft has a web site with a number of introductory samples. I'll start there and work through them. The very first example gets me set up with some data that it builds by running a Powershell script, importdata.ps1. But I'm not going to just blindly follow along. I want to see what the heck is happening so I can start understanding this stuff. By the way, thank you Microsoft for making the samples in PowerShell and not forcing me to relearn Python or something else. That would have been frustrating. The script is really simple. It has two scenarios you can…
HDInsight: First Pass
I installed HDInsight with no errors. The install is so brain-dead, I couldn't see how there could be errors. But, I decided to fire up the service to start playing with it. That's when I hit the errors. First up, I was getting logon errors for the Hadoop service itself. I tried resolving them manually, but was digging a hole. So, I uninstalled, and reinstalled, figuring I had missed an error message somewhere along the line. After this install, I didn't get logon errors. Instead I got internal errors. Some more investigation and it appears that the service was marked for deletion. The uninstall worked, but somehow wasn't complete. Yay! So, I did another uninstall after rebooting the server. Install #4 coming up. Â And we're back to logon errors. Specifically:…
Another Execution Plan… In the Cloud!
A couple of weeks ago I posted about how different execution plans look when using the Azure Management Portal. I promised to look at a more complex plan and here we are. The query looks like this: SELECT * FROM dbo.Agent AS a JOIN dbo.Resource AS r ON a.AgentId = r.AgentId JOIN dbo.MovieStageResource AS msr ON r.ResourceId = msr.ResourceId JOIN dbo.MovieStage AS ms ON msr.Movieid = ms.MovieID AND msr.MovieStageDefinitionId = ms.MovieStageDefinitionId JOIN dbo.Movie AS m ON ms.MovieID = m.MovieId WHERE a.AgentId = 42; I didn't go nuts in creating a crazy complex query. I just wanted to see a standard set of different operators to see how things changed. As before, I'm working off a controlled database that has identical structures and data in both the Azure database and my…