Oct 14 2015

Getting Started With DocumentDB

I’ve put this off for too long. It’s time to get my feet wet with some new tech.

Step 1 is easy. Go to the Azure portal and start the process for creating a DocumentDB:


While that’s running, let’s see what’s on the interwebs about getting started in DocumentDB…

Nice. I know I’m going to have write a little code to exercise this thing. Here’s a great run-through on exactly how to do it. Actually, the first hit when I searched on “Getting Started With DocumentDB.” Microsoft has a start page on DocumentDB, but it was clearly put together by someone from marketing. Scroll down to the bottom. There are a couple of interesting links including SQL Query Within DocumentDB. Now we’re talking. Here’s a Curah! or Docs.com (which is it? do I care?) on DocumentDB with some of the above links and a few others. I spot a theme on this one. Here’s a more thorough how-to on querying DocumentDB.

Plenty to read, lots to do. And look, I have a DocumentDB database ready and waiting:


I’ll report back as I get things going.

Oct 05 2015

Trace Flags in Azure SQL Database

One of the ways that you take more direct control over your SQL Server instances is through the use of trace flags. There are a number that people recommend you enable by default. Prior to Extended Events for example, I’d say you should turn on trace flag 1222 in order to capture deadlock information on your server (now I just recommend you use the system_health session). I absolutely think you should turn on trace flag 2371 to get better behavior out of your automated statistics updates. There are others that I’ll leave to all the systems experts to advise you on.

What about Azure SQL Database?

I doubt you’ll be shocked, but if I try this:

DBCC TRACEON (2371,-1);

I get the following error:

Msg 2571, Level 14, State 3, Line 1
User ‘xxx’ does not have permission to run DBCC TRACEON.


This error makes sense right? We’re talking about a Platform as a Service (PaaS). You’re only managing the database, not the server, so you don’t have access to control underlying server behavior.

How about if we want to just modify the behavior of a query? You can use the query hint QUERYTRACEON to adjust behavior. For example, the new statistics cardinality estimation engine in 2014 and better is just marvelous. It’s in use in Azure SQL Database. However, there are edge cases where the old way can work better for certain queries. If you want to go to the old cardinality estimation engine in SQL Server 2014/2016, you use traceflag 9481 in a query hint like this:

FROM    Sales.SalesOrderHeader AS soh
JOIN    Sales.SalesOrderDetail AS sod
        ON sod.SalesOrderID = soh.SalesOrderID
WHERE   sod.OrderQty > 30
AND sod.ProductID = 867

Bad news. The error message is the same.

Working within Azure SQL Database, trace flags are not a part of your tool set.

Apr 16 2015

Azure SQL Database v12 and SQL Magazine

I spend many of my evenings researching and writing. Sometimes it’s writing new books. Sometimes it’s fixing and rewriting old books. Occasionally it’s a blog post like this one. Lately, it’s been a series of articles for SQL Magazine that are all about the new functionality available in Azure SQL Database v12 that was released in February for most data centers in Azure. It’s a whole new ball game. Check out my introductory article for v12 and the one on DMVs in Azure. I have more coming up on CLR in Azure, getting started, PowerShell, DBCC, T-SQL enhancements, Premium Tier and more. I’ll also get into “old” functionality like backup and restore. I’ll also explore new functionality, Azure is nothing if not dynamic, as becomes widely available.

I know a lot of you have been putting off exploring Azure, especially Azure SQL Database until it was “ready.” Guess what? It’s that time. Feel free to lean on me here, and over at SQL Mag, for questions, suggestions, thoughts, concerns, anything you have about Azure.

Apr 13 2015

Azure SQL Database Firewall Settings

The new portal for managing Azure is pretty. I’m not sure I’m in love with it, but it’s pretty.

However, one thing that I have to access regularly is the firewall settings for my Azure SQL Database. I do demos from all over the place. I never know what my IP address is going to be. Rather than expose everything, I just set up whatever IP address I’m on and then remove it later. The old portal made this easy. The new one… not so much.

So, let’s get this down real quick. Assuming you connect to the new portal and go straight to your database, you’ll see this image showing you the database and the server it’s on:


You won’t see anything else that suggests FIREWALL. But, you can click on the server. When you do, you’ll see another panel open up to the right of the one you’re on. It’ll have an image like this:


Still nothing that screams FIREWALL, but if you click on the little gear icon that says SETTINGS you’ll get yet another panel opening to the right that resembles this image:


There she blows, Firewall. Click on that and you’ll see the standard screen for editing your IP address access into the firewall:


Just remember that after adding a new IP address to your firewall you must hit the save icon at the top of the screen, or you still won’t be able to access your database.

Sep 05 2014

Speaker of the Month: September 2014

This month I’m very grateful because I was given the opportunity to present at DevLink in Chattanooga. I got to meet a lot of new people and see presentations by people that just don’t hang around SQL Server specific events. It was great. I’m going to apply next year (depending on scheduling of course) and I’m applying for a lot more development conferences. I still get to see friends present, Louis Davidson, Kevin Boles and Kevin Kline were all there. But I get to see new people. Speaking of which, speaker of the month for September 2014 is Josh Lane (b|t) and his presentation AWS vs. Azure, Which One Is Right for You.

The thing that I found the most amazing about this presentation was how even-handed it was. Josh Lane really went out of his way to find positive (and negative) things to say about both platforms. Because of this, I think anyone really trying to understand which platform to pick, and, more importantly, why they should pick it, walked away with good data. I’m working with Azure regularly and I picked up a few things that I didn’t know. I also liked how he identified lots of commonality between the two platforms. The slides were very simple and clean and well structured. Mr. Lane’s delivery was excellent. He was engaging, handled questions well, and kept things moving. I was really impressed.

The one issue I have with the presentation is that there was really way too much material. He didn’t just rush through the last of it. He skipped right by sections. And those sections looked interesting. I’m pretty sure this was put together for a venue that had more than an hour of time, but I don’t think he would have made it through on a 75 minute schedule either. So, as much as I hate to say it, I’d suggest trimming some material. Going quickly through some doesn’t usually make people too angry, but skipping stuff entirely makes them feel ripped off. But, what a great problem to have. Too much awesome, well delivered material.

Thanks for the session Mr. Lane. I’ll keep an eye out for other material from you in the future.

May 13 2014

Add an Instance to SQL Server Azure Virtual Machine

How do you add an instance to your local SQL Server installation? You run the executable that you probably downloaded from MSDN or maybe from a CD. Works the same on an Azure VM right? Sure… but wait. Do I have to go and download the software to my VM instance? Let’s assume that you’re running one of the VMs from the Gallery, then, the answer is “No.” Just navigate to C:\SQLServer_12.0_Full. There you’ll find the full installation setup for SQL Server. And you’re off and running… Until you realize that you don’t have the Product Key for this thing. What happens when you get to this screen:


You can look around all you want and you won’t see a product key anywhere. At least no where that I could find. So what do you do? Same question was asked and answered over on this forum at SQL Server Central. The trick is to get the product key from SQL Server. I tried several different methods, the ones you’ll find if you search for how to get the product key from an existing copy of SQL Server. But finally, as was posted on the forum, a method that worked was found. I tested it out and I was able to add an instance to a VM from the Gallery.

Which brings up the next question. Did I just violate some type of licensing with Microsoft? Lordy I hope not. But I did some research. This definition of the support policy at Microsoft says that anything that is not explicitly denied in that documentation, that is normally supported is still supported. There’s nothing in there about multiple instances. There’s nothing in the basic Azure Licensing FAQ. There’s nothing against this in the Pricing details either. And since the standard iron version of SQL Server allows you to have as many instances running on a given server that you want, from what I can tell, this still applies here.

Personally, I don’t think I’d want to run multiple instances on a single Azure VM. I wouldn’t really want to run multiple instances on a VM or, in some cases, even on iron. Multiple instances frequently have difficulty playing nice. I can’t see that getting any better inside Azure. However, there’s nothing to keep you from doing it except tracking down that Product Key. Get that, and you’re golden.

Apr 23 2014

Azure Automation

I introduced Azure Automation in a previous post. I’ve spent some more time exploring it.

There’s a set of documentation available as I noted before. Unfortunately, reading through the full set of documentation, I have some criticisms to offer. The layout of the documentation goes through “Common runbook tasks” actually more or less laying things out as I did, inadvertently, I assure you, in my previous blog post. The problem with that, as I found in that post is, the administration of the runbooks seems fairly straightforward from the screens. But, you can’t do a darned thing with any of it until you have a runbook . Further, you can’t have a runbook until that thing has some code in it. And, the documentation doesn’t include documentation about code. Instead, we just get a page with a list of samples, but no links to that code, nor an indication of where it might be. The scripts are located here. But man, that ought to be in the documentation. There’s also no clearly documented method for how to start doing the development. It’s not really necessary since the GUI leads you inevitably to the Draft screen we saw in my other post. But, documentation is generally supposed to let you know what to do, where to look, etc.

There is another set of documentation just on authoring runbooks. Lots and lots more meat there. I’ll go through it and follow up further.

Enough criticism, let’s play with some code.

I’m going to start with the “Hello World” code set. It’s supposed to be an introduction to how everything works. You can’t open it from the Azure Portal. Instead you have to download it to your machine and then either upload it into a new runbook or copy and paste it into the Draft editor window. Presumably this is so you can do the coding locally using the PowerShell ISE or other tools. Documentation for the script is clear. It’s description:

If you are brand new to Automation in Azure, you can use this runbook to explore testing and publishing capabilities.


Well, let’s just say that’s a little grandiose for what is, literally, a “Hello $Name” example. But, it’ll get your feet wet. I took the script, pasted it into my “RunningScare” runbook. From there, I have the  capacity to Save, Test, or Publish. Being a good paranoid type, I ran test first. It popped up a window to input the parameter and then showed the output in the Output Pane (which I hadn’t actually noticed):


I can’t tell you why it output multiple times, but it did from one test of the script. To see the rest of the functionality, scheduling, etc., I went ahead and hit Publish. That moved it from Draft to Published where all I can see is a faded outline of the actual script and a Start button at the bottom of the screen. I went ahead and ran it from there. It actually takes a surprisingly long time for such a silly small script to complete.  There’s event the ability to view the Job as it’s running:


So that works. Next up, scheduling. It’s pretty straight forward to walk through the GUI in the Portal (although, now I want to see if I can programmatically control the Automation interface, more to explore). I’m going to try to run this script once an hour. So, I’ll give the schedule the name, unique to my account, Hourly (imagination knows no bounds). And then things get weird. I can only schedule this for a “One Time” run or “Daily.” No other options available:


Nothing in the core documentation about the details of scheduling. Checking the authoring doc (which has tons of stuff in it) there is a PowerShell command for directly controlling this (oh yes, much more to explore), Set-SmaSchedule. But, it’s not clear if the command has more variables other than a day interval. I’ll have to test it out to see. The Portal recognized that parameters were necessary, so I put one in and scheduled my runbook. Worked great.

With that, I have my first run book set up, tested and scheduled. So far, this is looking really interesting.




Apr 16 2014

Microsoft Azure Automation

AutomationMicrosoft just announced a new mechanism for managing your Azure resources, Automation. You can check out the documentation on it here. It’s a mechanism to create runbooks using PowerShell that you can then combine with other runbooks inside a runbook, etc. Let’s check it out. I’m doing everything you see here without consulting the documentation. I want to see how easy it is to put this stuff together. First, because it’s still in preview, you have to sign up. Once you’re accepted in the program, you get a new icon in your Management Portal.

Next, you’ll have to create an automation account. That’s pretty straight forward. It’s just a name, your selected region and the subscription you’re putting it under. No immediate guidance on where, when or if the region matters that much:


When you get into Account, nothing is there. No default runbooks or anything. Now, I know you can go and get sample runbooks and I suspect there will even be a clearing house through GitHub or somewhere for runbooks. But right now, we’re flying by the seat of our pants, so let’s just create our own runbook. It’s Azure, so the New button is right there in the lower left. Clicking on it, we can do a quick create for runbooks, code to be complete later:


Nice and easy so far, although, so far, this thing doesn’t do anything. So now, I have a runbook. If I open it up, it shows a dashboard with several tabs, Jobs, Author, Schedule, Configure. Clicking on Jobs, I don’t see anything interesting displayed. Obviously I should click on Author next, but where is the fun in that? So I click on Schedule. That brings up a message that “You must publish this runbook before you can add a schedule. Click AUTHOR to author and publish this runbook.” So I go ahead and click on Configure (yeah, I’m that guy). It’s not that interesting. So, let’s go back to where we should have started, Author. Clicking there, I get this:


OK, fine. Let’s click on Draft. Which brings me to, well, I think it’s a fascinating screen:


It’s a script. And that’s PowerShell it expects me to type in there, but I really don’t know what kind of commands I should be using, so… Here endeth the beginning of my exploration of Azure Automation. I need to actually go and read the docs. More to come.


Mar 25 2014

Save Money On Your Training Server

Save MoneyYou can spend less money. Some of us are lucky. We work for very large corporations who can easily set aside a spare desktop or even space on a rack for a server on which we can train. Others of us are not as lucky. We work for smaller organizations that have to be more careful with their money. Not only do we not get the extra machine to train on, but our laptops could be weak things that can’t run two or more VMs. In this case, how can you go about learning stuff? Spend your own money? Sure, it’s an option.

There are some very cheap servers available out there that won’t cost you even $1000 dollars to set up. And for pretty cheap you can buy some network attached storage to have your own little SAN-style setup. That’s very doable. Let’s break it down a little:

HP Proliant MicroServer G8: $549
Added Memory to 16GB: $209
24oGB SSD: $129
Lenovo/Iomega 1TB of storage NAS: $878

We’ve just spent $1765 for a decent little set up. So now you could run 3-5 VMs on this machine and you’re good to go. Of course, now you’ve got to maintain that system, patching, upgrades. What happens when it gets old? You’ve got to replace it. What if you’re not using it? That was a lot of money spent then.

Ah, but wait. Software. We need to get Windows server licensed and SQL Server. Let’s see:

Windows Server 2012 R2 Fundamentals: $501
SQL Server Developer Edition: $44

We’re now up to $2310. But… oh, yeah, the licenses for the servers, that doesn’t include VM licensing, so let’s buy… 4. That’s enough for one server and 3 VMs. That’s an additional $1500, so now we’re up to $3810. Cool though, right. That’s not much money and we’re off and running.

Here’s a suggestion, even if you have to spend your own money, how about Azure? Currently, I’ve left three servers running on my account (not something I recommend, but I’ve been doing this as an experiment), plus the storage they use, plus the SQL Databases I have, I’m racking up a bill of about $80/month. That’s $960 in a year. Which means in about 3.9 years, I’ll have spent as much as you just did on that server that’s sitting under your desk.

Yeah, I know. It runs somewhat faster, except when I burn a little cash and bump my servers up to 8 core and 56gb of ram for a test, then turn it back down, or even, turn it off or deallocate it. Because, you’re only going to pay for what you use. So if you just throw the VMs away between tests, you’re saving tons of money, way above and beyond what that hunk of iron under your desk cost. You can even estimate exactly what things are going to cost using the engine Microsoft provides.

But did I say pay? Not quite. You see, I have an MSDN account. That includes Azure credit. Anywhere from $50 to $150 per month. So, for $1199/yr, I can get $50 a month of Azure credit. That means, just buying an MSDN account, it’ll take me three years to equal what I spent on that box under the desk.

Oh, and that’s before we get to the electricity you paid.

Look, there’s a reason to buy iron. I believe in it. But, there are also reasons not to buy iron. Testing, training, personal use… maybe iron. Or, maybe it’s time to step into the 21st Century.

Mar 21 2014

PASS DBA Virtual Chapter Talk

I almost forgot to tell you about the Database Administration Virtual Chapter meeting next week, March 26th, 2014. I’ll be doing a talk about query tuning in Windows Azure SQL Database. It’s a talk I’ve given before (it was in the top 10 at the PASS Summit last year). Come find out why you’ll need to tune queries in WASD, the tools you get, and the glorious fact that you’ll actually be actively saving your business money by tuning queries! Click here now to register.