Execution Plans on Azure SQL Database Portal

Azure
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…
Read More

Surface Problems With Azure

Surface
I've been posting about the Surface over the last couple of weeks and I've tried really hard to be positive about it. But there are some serious problems with the device. I've already made a stink about the apps, but this time, I want to talk about the Microsoft ecosystem. I'm working more and more with online services. These vary from storage, such as SkyDrive to Amazon Servers to Azure. And Azure is my problem. I sit here, typing into a device that is really, really close to being fully productive despite being extremely small and light. Really close. And, it's supposed to be a major player within the Microsoft world. It's what the new OS was designed for, or so I thought. But, I might have just found my…
Read More

Another Execution Plan… In the Cloud!

Azure
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…
Read More

SQL Azure Query Tuning

Azure
SQL Azure is still SQL Server at the end of the day. This means it is entirely possible to write queries against SQL Azure that really… what’s a good word… stink. So what do you do? It’s all in the cloud. You couldn’t possibly tune the queries, right? Wrong. Many of the same tools that you have available to you, such as execution plans and dynamic management objects, are still available in SQL Azure. Let’s talk DMOs for a second. First off, don’t make the mistake I did of trying to run these outside the context of a specific database on SQL Azure. You’ll get extremely inconsistent results, trust me on this. Anyway, I did a quick run-down on some of the most used DMOs for performance tuning, the sys.dm_exec_*…
Read More

SQL Azure Migration Wizard

Azure
There’s a project over on code plex to come up with a mechanism for validating databases and generating the necessary scripts to allow you to move those databases, and data, over to SQL Azure. It’s called the SQL Azure Migration Wizard. There’s no real install yet since it’s just a beta. You can download the executable and run it. It’s pretty straight forward stuff. It’s primarily focused on validating that there is a database, script or series of calls (from a trace event file) that will be properly compatible with SQL Azure, but it can also generate a deployment script from the database. Here’s the opening screen: Not much to it. I’ll walk you through the Analyze and Migrate path which covers most of the functionality of the app. Clearly,…
Read More

SQL Azure and the DAC Pac

Azure
When last we left our intrepid hero he had successfully deployed to SQL Azure using the Data-Tier Application Package, a DAC pac. It was easy and I had a database in place in nothing flat. There really weren’t any issues worth mentioning. I wasn’t crazy about the fact that unless I had Visual Studio 2010 I couldn’t edit the Data-Tier apps or get them into source control, but there you. So, assuming this is a real production application, I’ve just realized that I need to get a new procedure into my database. If I just got and run the script to create the procedure then I’ll be breaking the link between my database and the DAC pac (and yes, I still enjoy saying that knowing that blood pressure is rising…
Read More

SQL University: SQL Azure & PowerShell

Azure, PowerShell
Welcome once more to the Miskatonic branch of SQL University. I see that most off you survived out last encounter… uh, class. Most of you even appear somewhat sane… somewhat. Today we’re going to talk about PowerShell and it’s use with SQL Azure. Which sounds a something like an eldritch horror breaking in from some outer dimension… hmmm… that could explain certain things… So, back to Powershell and SQL Azure. You can’t run Powershell from SQL Azure. Thank you for attending and enjoy your trip home. Still here? I did try. Let’s clarify. You can’t run PowerShell from SQL Azure, but I didn’t say that you couldn’t use SQL Azure as a target for PowerShell. It is possible to connect to your SQL Azure databases running PowerShell from a local…
Read More

SQL Azure Diagnostic Tool

Azure
One of the CSS Engineers from Microsoft has released a diagnostic tool for SQL Azure. It's worth a look. It's just using standard DMO queries to pull back data, but they're put them together inside a nice UI to help out. If you're already a DMO super-star, this might not be useful, but if you're looking for an easy way to gather data from your SQL Azure instance, this is a good choice. Just remember, although it wasn't mentioned on the MS web site, I'm fairly certain you get charged for running these queries. It's always something to keep in mind when working with SQL Azure.
Read More

SQL Azure Deployments

Azure
You’ve set up your access to a SQL Azure site so you’re ready to go. Now how do you get your database up there? Well, TSQL works just fine, so you can script it all out to a file or set of files and apply them. Because you can connect with SSMS, you could use the GUI to build out your database. You can also use the Azure development platform and it’s web based GUI to create your database structures. Personally, the scripting method doesn’t seem too bad to me because you can get your code into source control that way. But, Microsoft has something additional in mind. It’s called Data-tier Applications or DAC for short (and yes, there are other things called DAC in SQL Server, what can I…
Read More

SQL Azure Cost

Azure
We’ve all heard the scary stories. A developer starts testing Azure and then suddenly gets a thousand dollar bill on their credit card. The whole idea behind Azure works of the premise that you are not paying for the infrastructure. Instead of buying servers and disks and racks and switches and routers, you just pay for access and storage. It’s a great model. That model is  especially applicable to small businesses that just can’t  afford the outlay, or to a larger company that wants to perform cheap experiments, or any number of other places where purchasing and maintaining hardware just doesn’t make sense. But what are the costs? That’s a little tricky to answer, truth be told. SQL Azure is charged a monthly fee based on the size and number…
Read More