SMO vs Invoke-Sqlcmd in Azure

Azure, PowerShell
I'm working on a series of Powershell scripts for the pre-conference seminars on Windows Azure SQL Database that I'm going to be giving at TechEd and the PASS Summit. One of the things I want to do is script out a full backup process (one that's consistent with transactions) which means I need to create a database copy. There's a nice neat T-SQL command that does this: CREATE DATABASE ADW_COPY AS COPY OF nmhdhskcew.AdventureWorks2012; So, being the lazy Powershell freak that I am, I figured I'd just pass it in with Invoke-sqlcmd as a single line like this: Invoke-Sqlcmd -ServerInstance $server -Database $db -Username $user -Password $password -Query $sql Which resulted in an unexpected error: Invoke-Sqlcmd : The CREATE DATABASE statement must be the only statement in the batch. Well, now I'm curious.…
Read More

A Little Azure Fun on Friday

Azure, T-SQL
You know how we've always heard that sp_msforeachtable and other similar undocumented functions may not be supported in future versions of SQL Server? Have you tried running something like this on any of your Windows Azure SQL Databases yet: EXEC sp_msforeachtable 'select ''?'', count(*) from ?' Try it out.
Read More

PASS Summit 2013 is Looking Cloudy

Azure, PASS
No, this isn't some complaint about PASS or the Summit. This is an announcement that not only will I be speaking at the PASS Summit, but I'm speaking about Azure... a lot. First up, I'm going to be doing an all-day pre-conference seminar specifically aimed at getting you into Azure. No, I don't want you to drop your on-premise databases and infrastructure. Are you nuts? No, wait, you think I am. OK, fair point. But what I actually want you to realize is that some pieces of your work are better done in the cloud. There are all kinds of terribly fun and cool things you can get done there, as an addition to your existing infrastructure. There are way, way too many things that are better done locally to ever think you'd move…
Read More

Azure does Powershell too

Azure, PowerShell
Or, I guess it might be more appropriate to say that Powershell does Azure. Regardless, there are a set of commandlets for Azure and Azure SQL Database. Here's the link to get you started and the basic documentation. After that, it's a little tricky to know for sure what's required. Follow the instructions on the link to get the basic set up done, and then I'll show you just a little bit about how you can manage your Windows Azure SQL Database through PowerShell. First up, you need to set up a context, which is basically a connection to your Azure server. This requires very specific objects. The code below outlines what you need: $SQLLogin = new-object System.Management.Automation.PSCredential("UserName", ('12345' | ConvertTo-SecureString -asPlainText -Force)) $context = New-AzureSqlDatabaseServerContext –ServerName 'MyAzureServer' -Credential $SQLLogin…
Read More

How to Tell Your Windows Azure SQL Database Moved

Azure
The very concept of the Windows Azure SQL Database (WASD) is predicated on the up-time created by having three active copies of your database. Should there be a hardware or software failure that would cause the primary replica to go down, your database gets moved to one of the secondary replicas which has been maintaining a copy of the data from the primary replica. In theory, the worst thing that happens is that you have to retry a query. In fact, building that sort of resilience into your code is a pretty fundamental aspect of working with WASD. I was asked the question, how do you tell if your database has been moved between replicas. I didn't have a complete answer, so I set out to find one. The first, more obvious…
Read More

Execution Plans in Azure SQL Database

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

New DMO in Azure SQL Database: sys.dm_db_wait_stats

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

Azure SQL Database Execution Plan Differences

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

Saving Execution Plans on Azure SQL Database

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