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.


Apr 15 2014

SQL Server First Aid

10884793236_16744ca395_mIf you take basic first aid, say a CPR course, you’ll learn a handy mnemonic for the primary assessment you have to make, A-B-C. That breaks down as Airway, Breathing, Circulation. Is there an open airway so they can breathe? Are they breathing? Do they have circulation, a pulse, are they alive in short. I recently took a two day course on wilderness first aid (on top of CPR training and first responder training and basic and advanced first aid training and Scout training and Scout first-aid training and I’m sure I’m forgetting some) that added to that, D-E. We now have Disability and Environment. In short, just how responsive is the person or do they have the possibility of spinal issues? What’s the environmental situation, lieing on cold ground, in the rain, on a boulder, in a crevasse, etc. And that’s just for the primary assessment. Then there’s the secondary assessment.

I’m not going to attempt to regurgitate the class here (go here if you’re interested). But it got me thinking, could we come up with a primary assessment for SQL Server that is also predicated on simple concepts that we could use to focus our assessment when dealing with SQL Server issues? The obvious answer is yes, but then, how well will it work? I’m pretty sure the shockingly simple, while simultaneously complex, ABCDE for assessing patients went through a lot of rounds to arrive at the current version. So, here’s my idea. I’m going to do a pass at this. I’ll lay out my first thoughts on coming up with a primary assessment, and then, I’d love to see others take it and run with it. Maybe we can turn it into a WIKI or something. But I can see it as a foundation for a more systematic approach.

Let me lay a few ground rules. First, this is the primary assessment. This is the “let’s see if we can keep this person alive” moment. They may have tons of cuts and scrapes and messed up hair and horrible taste in clothing. We can get to all that later. Right now they’re laying on the ground and we need to determine if they’re napping, getting ready to start screaming, unconscious or just dead. So, let’s keep that concept in the front of our brains as we think through what we need to get the patient/server away from FTD (“fixin to die” I love the black humor that EMTs and police have) to either completely alive again or at least limping along. No query tuning (as much as I love it) or questioning people’s database design choices (as silly as they may be) or even finger pointing (yet).

Second, let’s try to stick within the mnemonic as much as possible. So ABCDE. If we determine a need for an F & G as part of the initial checks, fine. And, we have the whole secondary checks to do so there can be any number of fun mnemonics or other memory devices or whatever. But we’ll leave that for the nonce. Primary checks.

Here’s my first pass (I’m skipping survey the scene, the real first step, maybe that’s wrong, let me know):

A: Availability – Can you ping the thing? Is there a network around it that is online such that you can get to it at all? If you can’t, chances are no one else can either.

B: Basic Connectivity (yeah, it sucks, see above) – Can you log in? Can you use the DAC? You can see the server, yes, but can you get to it?

C: Circulation (sucks even worse) – Are there blocked processes (yeah, it should be “B” but that doesn’t feel like the right order to me)? Are you able to query the system and get a response?

D:  Database – Are the databases online? Are there issues around databases? Did you run out of space on the log or data file?

E: Execution – Are we looking at some sort of query issues? Permissions, performance, etc., that is affecting general availability.

Yeah, I know. This is pretty weak stuff. I’m absolutely just putting thoughts into words and letting you see the process as it unfolds. Remember, as a DBA, chances are very good that you’re a first responder. You need to know what to do. Show me where I’ve gone wrong. Feel free to write your own blog post in response. Create a presentation with this idea. I don’t care, just let me know what you’re doing with it so I can try to update and expand on the idea. Could we incorporate existing resource such as this awesome book? Anything and everything. This is an open idea that I’m passing on to see if it has legs. I think it might, but then again, I couldn’t believe that everyone didn’t own an Axim.

Apr 14 2014

Sharing a Good Idea

I posted earlier about my experiments with Microsoft Curah!. (yes, technically the period should follow the exclamation since the exclamation is part of the name, not the end of the sentence) Evidently people actually read this blog because it inspired Stephen Bennet to start putting together his own curations and collect them on his blog. I think that’s a pretty interesting idea. I might try it myself (after I get back from SQL Intersection). Stephen’s Curah! so far.

Oh, and I kind of dislike the name. Curah! Just typing it I feel like I should be excited except I’m not. Anyhooo…

Apr 10 2014

I’m a Traveling Man

We are coming into quite a busy time for my speaking schedule. I’m hitting the road. It does one thing for me that I truly love, I get to talk to people. So, if you have questions, want to chat, need to call me a pompous know-it-all to my face, I’ve got some opportunities for you.

Next week, April 13-16, is SQL Intersection. You can register by clicking here. The following week, I’ve got two events. First, on Friday April 25th, Red Gate Software is hosting a free half day SQL in the City Seminar in the Chicago area. We’ll be talking database deployment all day. Go here to register, but don’t wait, seats absolutely are limited. And, since this is a Red Gate event, at the end of the day, I’ll buy you a beverage or two while we exchange war stories. The next day, Saturday April 26th, is SQL Saturday Chicago I’ll be presenting a session. Check out the lineup and get yourself registered. That’s it for April.

May gets really fun. Saturday May 3 is SQL Saturday Atlanta. This is one of those “I was there” events for the Atlanta area. I’ll be there. Saturday May 17 is SQL Saturday Detroit. This one, at the moment, looks pretty intimate, but that means you get to hang out with Jeff Moden, Ginger Ford, Allen White, Tim Ford and ask questions until you run out of questions. I wouldn’t miss it if I lived in that area. Heck, I don’t live in that area and I’ll be there too. Then I get to go on my Carolina Cruise. I’m visiting three user groups in three days in the Carolinas. First up is Raleigh at the Triangle SQL Server User Group on the 20th. Then I get to Charlotte (and that was a great city for hosting the PASS Summit) on the 21st. Finally I’m off to Columbia and the Midland PASS Chapter on the 22nd. That’s going to be a blast. And we’re not done with May. On the 27th and 28th I’m going to hop the pond to speak at TechoRama in Belgium. I’m terribly excited about this event. Maybe it’s just because I like Belgian beer, but it really does look pretty cool. Go here to get registered. And I love the count-down clock on the web page. That’s exactly how I feel.

In June I come back over to my side of the pond. There are some events we’re still setting up. But the ones I know I’m going to are SQL Saturday Louisville on the 21st of May. But, the day before, on the 20th, I have an all day seminar on query tuning. Click here to register. We should have another SQL in the City Seminar set up for June as well as a couple of more SQL Saturday events. I’ll post once I learn more.

July is still pretty open (please, please, please, OH, PLEASE, I want to got to SQL Bits), but I do have another all day seminar on query tuning set up for Albany. You can go here to register. That’s the day before SQL Saturday Albany. It’s going to be their first event, so let’s help make it a great one.

As the schedule for June and July solidifies I’ll publish another listing. Let’s get together and talk.

Apr 08 2014

Curation and Performance

5314917023_2af11d592e_mI’m trying out a new web site from Microsoft called Curah! that is all about curation. Curation is basically what blogging started out as. Blogs, short for Web Log, was really just a collection of links you’d visited recently and what you thought about them. But it’s grown into all manner of things, the least of which is a collection of links and what I thought about them. However, the concept of a useful set of links, why they might be useful, what you’ll find there, these concepts still have value. Hence the rise of curation. As a concept, I get it. I don’t think it deviates radically from what we do with our blogs, our resources pages (see the links above), and other similar functions. But, it is rather focused and it might lead to differences in use, derivation, direction… heck, I don’t know. It’s shiny.

My passion is of course performance and performance optimization, query tuning, execution plans, monitoring and all the rest. So, in keeping with that, I’ve started putting together some curations (is that a word, well, it is now). I’m actually using my Reference slides from some upcoming presentations as a foundation for a collection of interesting links. I’ve put together three so far. The first is all about execution plans. I think that’s a seriously decent collection of links to resources you really should be using. Yes, some of those links are to my book, but there are other resources there too. The second is to a collection of links on how to monitor and troubleshoot the relational storage engines we have available to use through Azure. No links to any of my work in that one. In fact, it’s almost all Microsoft links because they have some of the best material still. Finally, my initial experiment with Curah! was to gather together the best links I could find on sys.dm_exec_query_profiles. Don’t know what that is? Well then, this page is going to be very useful for you.

I’m not convinced this is a viable mechanism for the delivery of content superior to what we’re already delivering through our blogs and other mechanisms. For example, I’d rather direct everyone and their brother to my blog so that you see my brand and brand material (the rest of the blog) as much as humanly possible. Sending you over to Microsoft strikes me as less than effective. However, the experimentation just takes a little time and I have nothing against trying stuff out. If you can, let me know what you think. I’m curious. For example, the little pyramid up there, I think that’s bogus. Curation is a step above commenting, certainly, but it’s not above producing. Am I right?

Speaking of performance tuning, execution plans and monitoring, I’m going to be on the road a bunch in the near term. Next week is SQL Intersection where I’ll be talking execution plans and Azure monitoring. It’s not too late to register by clicking here. In May I’m going to be putting on an all day seminar on query tuning in Louisville, KY. Click here now to register. Seats are disappearing for this one. I’m also taking that same seminar to Albany NY in July. There’s an early-bird special in effect through June. Don’t wait to register. Click here.

Apr 04 2014

Speaker of the Month, April 2014

I’m really enjoying picking a speaker of the month. It forces me to sit through a lot more sessions at the events I attend. I had been getting rather slack about attending sessions. It’s easy to get caught up in networking so much that you’re not taking advantage of the learning opportunities. This month we’re on to the East Coast to pick a speaker from the Boston SQL Saturday event. The talk was called, “What I Wish I Knew Before Becoming a DBA.” The speaker of the month is Mike Walsh (b|t).

Mike’s session was just a general discussion about the job of being a DBA. He didn’t get into a lot of technical detail. Instead it was like a conversation with your friends talking about personality traits, work/life balance, restore plans, all good stuff. I especially enjoyed the emphasis he placed on practicing our skills. It’s not enough to know how to do a point in time restore, you need to actually run through it a few times so that when the CEO is standing in your cube, you get it right. I also enjoyed the concept Mike put out that DBAs are advocates for the data. He suggests that a DBA should think that “developers want to mangle the data, vendors want to steal it and managers want to lose it.” I think that’s probably unnecessarily harsh, but it gets the point across. I enjoyed when Mike put little faux SQL statements on the board for a topic. It communicated the point while still being fun. Many of his slides were bare, almost Spartan, but they worked well with the general discussion format that he took.

In fact, I enjoyed the spare slides so much, that when he introduced some pictures, it felt kind of jarring and I thought it took away from the approach he had been building on pretty well. Also, maybe a few extra slides would help the situation. Sometimes Mike would get on a topic and stay there for a while, but his talk would stray a little to far afield from the topic listed on the screen. Having a few more slides might help there. Also, some of the topics tended to meander a tad. While I really did enjoy the conversational style, I think Mike would only benefit from a little tighter focus on the subjects at hand.

It was a session well worth attending. The people in there seemed to enjoy it as well. I’ve no idea where else Mike is speaking. I don’t see anything on Lanyrd or on his blog. Keep an eye out for him and check out the session, especially if you’re just getting started as a DBA.

Apr 03 2014

Thanks For Stopping By

Yesterday I passed 300,000 views on the blog. Recently I went over 1,000 comments. My little joke on Tuesday garnered over 2,000 views alone. I just wanted to say thank you for stopping by and reading about my struggles to understand SQL Server, Azure, our amazing community and whatever else it is that I’ve posted here. I appreciate your time. On to the next 300,000.

Apr 01 2014

SQL Server 2014 New Defaults

CelebratingToday, April 1st, 2014, marks the release of SQL Server 2014. There are tons and tons of great new methods and functions and processes within the product. We’re all going to be learning about them for quite a while to come. One of the most exciting though is one of the changes to the defaults. In the past there’s been a lot of debate around how best to configure your databases. What cost threshold should be set for parallelism, the max degree of parallelism, memory settings, auto growth, and all sorts of other settings affect how your databases work. But, Microsoft has finally done something smart. They’ve bowed to the pressure of hundreds and hundreds of DBAs, Database Developers and Developers around the world. They’ve finally done the one thing that will improve everyone’s code once and for all. Expect to see massive performance improvements in SQL Server 2014 thanks to this one default change.

What have they done you ask? What miracle is this that is going to result in both better code and better performance? Simple, by default, all connections to the database are now using the transaction isolation level of READ_UNCOMMITTED. In a single stroke, we no longer are forced to put with WITH NOLOCK on every single table reference in every single query. All the pain and suffering caused by blocks from locking has been removed from the product. We can look forward to a much cleaner code base and better query performance. Thanks Microsoft.

Please, note the date carefully.

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.