A Wandering Mind

As part of my trying to diversify my writing on the blog, I thought I might start commenting on books that I read. So, without further ado, here’s the first! (Note: I’m actually on the fourth book of the year but am playing catch-up.)

Atlas Shrugged is, in my opinion, probably one of the most iconic (and controversial) books out there. From what I’ve seen, people read it and generally either consider it gospel or blasphemy. Many tout it as a warning of how interventionism and central planning will be the downfall of our modern economy, and perhaps society in general. I actually found myself in the middle of the road, albeit with a decided lean.

One the one hand, I see some logic in Rand’s obvious belief in free capitalism. Markets can only work when the values of goods and services are allowed to set themselves according to demand, not by the whims of planners. Governments bailing out failed businesses (“Too Big To Fail”?) at the expense of taxpayers and more successful ones seems to encourage mediocrity and rampant speculation. Look no further than our current situation, where those who patiently save and practice frugality are punished by record low interest rates, while the traders who throw piles of money at the market reap huge profits. And when they make poor choices (can anyone honestly say that investing in mortgages where the holders’ incomes weren’t even verified is a prudent decision?) and suffer, here comes the almighty Fed and the U.S. government to help them out. As a result we have a ballooning debt and a stagnant economy. I’m not an economist so I certainly can’t pretend to say all this is exactly what Rand foretold, but the similarities are striking.

Taking the opposite side, I believe firmly that what Rand describes is really more of an ideal than reality. If all corporations were honest, and all workers fairly treated (and by that I mean paid what their work is worth, and not made to work under dangerous conditions – at least not unwillingly), then sure, we could have a totally free market with no regulation. That not being the case, there has to be some allowance for governments or regulatory bodies to oversee things and ensure that people are not defrauded or placed in danger. Without unions, coal miners or high rise construction workers might still be working under horrific conditions (Lunch atop a Skyscraper anyone?). Having only read one of her books I can’t say for sure, but I have a suspicion Rand might well (at least partially) agree with me. In the famous “John Galt speech” (which yes, those of you who know what I mean, I read the whole thing), there’s this little tidbit:

The only proper purpose of government is to protect rights; a government’s only proper functions are: the police; the armed forces; and the courts, to settle disputes by objective law.

If there are laws prohibiting children under a certain age from working, or requiring that workers are protected with basic safety measures, and a corporation fails to follow those, then the government has every right to lay sanctions upon them. Or if a company turns out to have defrauded folks out of their money (though in many cases I’d argue that defrauding should be defined quite narrowly – if you foolishly invest your money without understanding where you’re putting it, shame on you), they should be made to give it back.

Naturally things like this are all part of a larger continuum, where conditions swing from one end to another in a cyclical fashion. Things go too far down one path, and we are forced to make hard course corrections. I think history has generally shown that human kind is fairly clueless at recognizing when our path has drifted to far to one side, and as a result we generally defer dealing with reality until it hits us squarely in the jaw. My psychology background has also taught me that there are a lot of reasons why we generally make poor decisions, most of which we are completely unaware of. Hopefully Rand’s tale turns out to be more of a cautionary one, rather than a stark look at the future of our society.

When I was a DBA (and, to a lesser extent, now as well) I frequently got into a scenario where I’d have multiple groups clamoring at me to accomplish a request from them on short notice. This sometimes became a problem, such as when there was only x hours in the day and I needed y hours to finish everything that folks were requesting. Or, when I had multiple people demanding immediate service (that never happens, right? Oh wait, it just happened today, that’s why I’m writing this. #Facepalm). Earlier in my career I would tend to get worked up, let my blood pressure go up, and perhaps engage in some creatively worded conversations with the people competing for my time. But after realizing that (a) this really wasn’t helping things, since people still want what they want; (b) all the yelling wasn’t helping my health, I came up with a couple rules on how to handle these, let’s say, explosive situations. In both cases, they’re remarkably effective at both alleviating the confusion over relative priority and lowering my stress level.

First: don’t even try to work out on your own which request is more important. Sure, you can ask questions and try and rank things in your mind according to things like which client things are for, how long things take, etc. Ultimately though, you’re going to be wrong in someone’s eyes and that someone is going to be unhappy with it. And moreover, unless you’re a manager, you’re not paid to determine things like that. You’re paid to get things done and write code, period.

Second: let the competing groups do just that – compete! Send an e-mail to the various requestors stating something like this:

Hi there folks, this is your friendly neighborhood (DBA | Architect | Database Developer) speaking. I thank you all for putting in your requests to me, and I acknowledge that each of you has requested that I handle them immediately. Unfortunately I can only work on one task at a time (unless someone’s developed cloning technology, which would be super!), so I will be doing these in the order which they were received. Before you begin thinking of arguments to get me on your side, I will warn you that this decision is not mine to make, so please do not send me justification of why you need to come first. If you feel that your request should take priority, please work with the owner(s) of those requests in front of yours to adjust their priority. If you all can amicably agree to shuffle things around I’m happy to oblige. I’ve listed the relevant work items below in order along with their owners. If I do not hear back I will assume you are all fine with this and will proceed in the order shown. Thanks!

Let the various people battle among themselves for the lead. Yes, this can sometimes get a little bloody, so you should probably check with your boss to make sure this is allowed before doing it. Which brings us to our third point…

Third: let your boss handle it. Managers are paid to, well, manage! If people cannot agree peaceably (or otherwise) on a pecking order, let your boss hear each of their cases and then tell you what to do. This will take the pressure off you to make a decision that is clearly one at a level above yours. Certainly your boss may (and perhaps should) ask for your input in the matter, but the decision should rest with them.

Since I’ve put the above rules in place I’ve never been more than slightly annoyed when these situations inevitably come along. It may not stop (or even lower) the volume of whining coming your way, but it will let you deflect and delegate all the stressful aspects of the problem.

Hello there blogosphere… been awhile since I posted!

What’s that you say? Did I lose my motivation? Why no, actually, I’ve been extremely motivated lately. Just not to blog.

You see, I came to a realization shortly after my last post: I’ve only got so many hours in a day, so I’d better use them as best I can. I love blogging; really, I do! But I love spending time with my family more. I love working on our drafty old house, tilling the garden, seeing some tangible results of my work. For some reason, I am finding myself really hung up on the whole idea of producing things with physical presence. For example, for Christmas I spent hours building the simplest little wooden toy for my son. I’m not a natural and not very experienced at that sort of thing, so it was not easy work. But I found immense enjoyment in it, and the look on his face when he saw it come out of the box was nothing short of priceless.

Perhaps this is why I haven’t been blogging much. Writing is wonderful, but if I’m going to be brutally honest, I’d much rather write science fiction than SQL Server. Please don’t misunderstand me; I love my work and learning about SQL Server. When I’m on the job I eat, sleep, and breathe it. I’d say you’d be pressed to find someone more passionate and focused than I am during those 8-10 hours on Monday through Friday. But when work is done, I want it to be done.

The above picture is something that really had some profound impact on my perspective. For a while I felt like I was floundering in my personal life. Things never seemed to really get done. I had my lists, and I was reviewing them, but I lacked focus. I never felt like I had much time to get things done. So, I decided to see just how much time I really had.

Using a simple Excel sheet, I laid out my week, then blocked out sections in color for things I knew I had to do. Green is sleeping, yellow is work, and blue is meals. And when I saw what was left, I was shocked. My feeling that I never had enough time to get stuff done? Boy was I right. Comparatively speaking, my “discretionary” time (that is, the time during which I pretty much choose what I do) was quite small.

Obviously, short of quitting my job or getting less sleep (and believe me, you don’t want to see me in zombie mode), there was no way to increase the time available to me. So instead, I chose to be more deliberate with how I spend what time I have. I sat down and thought long and hard about what is important to me. After awhile, I came up with a list, and while work and SQL Server were definitely on there, they weren’t as high up as I’d have thought. So, I made the conscious choice not to write on this blog, while doing other things.

That word, “conscious”, is a very important one. Whereas in previous hiatuses, the lack of postings was more about my lack of motivation, this one was more about doing what was important to me. And I hope that in sharing this, I might encourage others to do the same. J.R.R. Tolkein famously wrote “All we have to decide is what to do with the time that is given us.” So, if you find yourself mindlessly wandering through your personal life, as I did, stop wandering, and start deciding.

Now don’t fret, dear reader, for I am not abandoning this blog. I’m still going to write, even about SQL Server! But at the same time, I’m going to spend a good bit of time on other things, like writing that novel that’s been sitting at one chapter for over a year now, or tending to my garden (because c’mon, who doesn’t love fresh-from-the-ground veggies?). I hope you’ll stay with me, because I do think I’m still going to write interesting stuff; just a little more diverse than previously. I’m going to work on getting category-specific feeds up, so if you like, you can just subscribe to those that interest you. I also plan on trying to write a few articles for the likes of SQL Server Central or Simple Talk.

Lately I’ve been doing a lot of work re-writing some reporting code that was performing terribly. You know the old joke “Start it, then go get a cup of coffee, then come back”? Well in this case, that would be more like “Start it, get a cup of coffee, go for a run, read the morning news, eat lunch, take a nap, eat dinner, watch an hour of TV, then come back.” Ugh…

Obviously it’s important when modifying and tuning code to ensure that you don’t affect the results that come out of the procedure. Normally I like to do this using unit tests, but in this case the logic of the procedure was so complex (and relied on a bunch of underlying vendor code) that to create tests for it would have taken weeks. So instead, I opted for the strategy of simply comparing the old and new output, given a set of standard parameters. At first I did this manually, but after comparing 200+ columns a few times, I said to myself, “Self! This is silly, why not use Powershell to compare the results automatically?”

The result is the Compare-SQLResultSet function. It’s rough and currently doesn’t handle differently shaped results sets at all, but it has already been a huge time saver. I hope to improve it as time goes on, but wanted to get it out so others in the community could use it. Because if you’re doing this kind of comparison work manually… well, perhaps you should find a different line of work, because clearly you’re not getting it.

One of the first goals I have in my new role as a SQL Server developer is to learn how to write unit tests for SQL Server database code. I’ve started using the open source TSQLT framework, and I thought as I go I’d record my thoughts in an ongoing series.

What have I learned so far (in about a week’s time)?

Retro-fitting Unit Testing Is A Lot Of Work

This isn’t TSQLT specific, but I think it’s a valid point. One of my first projects is to add units tests to a number of existing stored procedures. It’s a lot of work, for a number of reasons. At least several of them actually don’t have much to do with unit testing, per say, but are just indicative of other issues. For example, creating unit tests is very difficult without solid and documented business requirements. In their absence, all you can do is try and reverse-engineer the code. This is even more difficult if the code is complex and not well commented.

In addition, if the stored procedures involve multiple tables, the necessary setup code can be lengthy. Just making sure all the tables have correct values is a significant amount of trial and error work. This alone accounted for hours of getting my first unit test up and running. When you do this, make a list of all the tables and go through each one methodically. Depending on the framework you are using, and how you are organizing your tests, you may want to do this work in “setup” code (meaning, code that executes once at the beginning of testing). TSQLT has this functionality; you just include a procedure called “SetUp” in your test suite.

FakeTable Is Your Friend

TSQLT has a stored procedure called “tsqlt.FakeTable”, whose function is to “[create] an empty version of the table without the constraints in place of the specified table.” This makes isolated testing very easy, without dealing with foreign keys or constraints. Naturally, if you’re actually testing those constraints (which, apparently is also helped by more TSQLT functionality) this is a bad thing. But if you’re just testing data returns or calculations, this can be immensely helpful.

Perseverance Is Key

This is tiring work, especially when having to create all the tests after the fact. But the feeling you get when you get one working is well worth it. It’s amazingly calming to know that you can make any change you want to a piece of code and instantly know if it breaks key functionality. It’s taken a lot of the anxiety out of working on some very critical code, especially as a relative newcomer to the field of database development. And once I’m done, I’m thoroughly convinced that the amount of time this will save in the future is well worth the effort.

That’s all for now. I’ll continue to record my thoughts in this tag as I get further down the road. In the mean time, if anyone has tips for a newbie, please feel free to share!

As a development DBA I often got requests from developers to elevate their rights to sysadmin for the purpose of “trying out some things for a proof of concept.” Examples would include things like Service Broker, replication, and CLR. And every time my answer was the same: No.

Now, before you stop reading and go off muttering “Man, what a typical DBA-hole”, you should understand why I took this view. The systems where the developers wanted this access were shared enterprise development and QA systems, which were often used by many different teams. If I had caved and given the developers this access, and they’d somehow managed to take the instance down or otherwise affect its stability, I would have had scores of people standing at my desk screaming about lost productivity. And rightfully so, because as a DBA it’s my job to make sure their systems are working.

On the other hand, there is clearly a need to allow folks to experiment with methods and architectures. Otherwise, how would we ever learn how to do new things, or know if a particular approach is going to work? But the enterprise environment isn’t the place for that. Instead, this is where one of a developer’s most important tools comes into play: a sandbox environment.

The basic concept is this: you have an area where you can play to your heart’s content without fear of affecting others. You can tear things down and rebuild them as you please (and hopefully in automated fashion, because good developers are always lazy). Personally I like to have this setup on my personal machine, using a series of virtual machines. With hard drives and memory being pretty cheap, SQL Server Evaluation Edition, and great free virtualization products like VirtualBox readily available, there really isn’t an excuse not to have something like this. Using some scripting and products like the excellent SQLSPADE automated SQL installation framework, you could spin up a couple SQL servers in a matter of an hour.

Once you’ve proven out your idea, and gotten the setup process streamlined (because no DBA ever wants to get a multi-page document full of screenshots of clicking “Next” buttons), that’s when you can move on to the shared environments, to make sure things are going to work properly in a leveraged setup.

Since moving into a full time development role, I’ve become even more convinced this is an essential tool for being a successful database developer. It makes my life so much easier not having to constantly go through channels when I need something done like setting up a login on a server. Mind you, I still agree with those processes, because they protect the shared environment from renegades like me.

Several months back at SQL Saturday 121 in Philadelphia, I had a great conversation in the speaker’s room with Mike Hillwig (blog | twitter) about career paths. One point that he made which really stuck with me was (and I’m paraphrasing roughly here) that the best way to find the job you truly love is simply to figure out what it is you like about your current one, then find a job where that’s all you do. I remember he attributed the advice to someone else, but for the life of me I can’t remember who (Mike please feel free to jump in and jog my woeful memory if you read this); in any case, the words really resonated with me.

For the last three years and change I’ve been in some form of a DBA / support role. And while overall I really enjoy my job, there’s a lot of aspects of it that I don’t find terribly fun. For instance:

• Doing routine administrative work, such as creating logins or databases on servers, fixing broken backups, etc.
• Having an endless barrage of “this server is broke, please fix it” e-mails, when in nine out of ten cases, the problem is their code, not my server.
• Troubleshooting hard to trace infrastructure issues. Don’t even get me started on my frustration with Kerberos and Network Load Balancing.

But, let’s face it; those tasks are part of a DBA’s role.

After Mike’s words had rattled around in my brain for a few weeks, I began thinking: “Self! What are the things that you really enjoy about your job (outside of the people on your team, who are great)?” So I pulled out my thinking notepad, and started scribbling. The top three that I came up with, in no particular order, are:

• Solving problems – not to be confused with “troubleshooting”; perhaps a more apt description would be “determining solutions for business and technical problems”.
• Designing data structures – I’m by no means a data modeler, but I do enjoy the process of normalizing data, and creating elegant, simple structures to store information.
• Performance Tuning – In an ideal world this skill wouldn’t be necessary, since all code would be written properly from the start and scale well. Fortunately for me, that’s not the case. There’s just something incredibly satisfying about taking some god-awful Gordian knot of nested subqueries and views, untangling it, and seeing an application’s performance just soar through the roof.

After this exercise I began thinking about what kind of job would let me focus on these things. After some thought, I came to a startling conclusion: I had to join the dark side. I had to become a developer.

But not just any developer, mind you. I could never be one of those heads down, coding machines that are just handed specs and go off on their merry way. No, I needed to find a job where I would have a fair bit of interaction with people, while still writing the code and getting my hands dirty.

Then, almost as if by magic, an amazing offer came my way. I was given a chance to join an elite group of database architects / developers within my company. This team is essentially the SQL Server equivalent of the A-Team; If you have a problem with SQL Server, and no one else can help, you bring these guys in. They do not own any specific applications or databases themselves. Rather, they come in, do a targeted assessment of the situation, then perform surgical work to get things back on track. Need help determining how best to store your database in source control (because you are doing that, right?)? They’ll deliver tools to help script out and organize your code, plus make deploying new releases a painless process. Have a query that is running horribly and can’t figure out why? They’ll rip apart the code and rewire it behind the scenes, and show you how to do it yourself going forward. In many ways, they are a lot like an internal consulting group.

At the time the offer came down the team only had two members, but combined they have almost twice my lifetime in experience working with databases. With their workload getting larger and larger by the day, it became apparent some additional help was needed. And I’m truly honored to say I’ve been given the chance to join them.

It’s bittersweet, for sure. I have loved my time in production support, and will miss the people and fun times I’ve had there. But I’m also incredibly excited about this new opportunity and the challenges it will bring.

And to Mike (and whomever originally passed the advice down to him): thanks for the push.

Recently I began experimenting in earnest with Powershell runspaces as an alternative to background jobs. My interest was mostly keyed by an excellent blog post on the subject by Boe Prox (blog | twitter). I’ve found that, in general, runspaces work really well for multi-threaded workloads, and, once you get past some initial hiccups, are easier to use than background jobs.

During my experimentation I had a bit of a realization around how runspaces are generally used. I found that I was generally doing the same things over and over again:

1. Creating a script block to do the background work, which had one or more parameters.
2. Creating a series of parameter sets containing the information about the individual “entities” to be processed.
3. Instantiating various objects and settings for the runspace configuration, such as the number of concurrent runspaces.
4. Starting the background threads and waiting for them to finish.
5. Getting the data back from the runspaces, and returning warnings for any errors that occurred during processing.

After copying and slightly modifying code a few times, I said to myself, “Self! This is not good practice copying and pasting code all over the place. Why not write a reusable and generic function implementing this work and simply re-use it?” Thus Execute-RunspaceJob was born.

The function basically encapsulates and makes generic the work required to setup and execute a parameterized script block within background runspaces. It also handles tricky items such as error handling and parsing return data. Let’s look at a quick example of how to use it.

Let’s say that you have a function called Get-DiskspaceInfo, which retrieves, well, information about disk space on remote machines using WMI calls. You have a series of servers, say, twenty, that you want to collect this information from. You could certainly simply get this list of servers and pipe them into the function (because you are writing functions that accept pipeline input, right?), but that approach would not scale very well since it operates in a one-at-a-time mode. Instead, using Execute-RunspaceJob, you could have any number (up to overwhelming your computer, naturally) of concurrent background threads collecting this information, then have it return the resulting data to you after it was finished.

First, you need to construct the script block which actually executes the work:

Next, we need to construct a hashtable of parameter values. The function expects a hashtable where the key is some unique identifier for the row to be processed (like a server name, database name, file name, etc), and the value is a nested hashtable of “parameter name”=”parameter value” pairs.

Finally, we execute the function, letting the results be placed into an array variable. We use the “-ThrottleLimit” parameter to specify the number of concurrent operations that are allowed.

If any of the background operations fail, a warning message will be printed out on the screen. Once all the data is collected you can treat the array just like you would the set of data returned by a normal pipleine-style operation.

I’ve started using this all over the place, and found that it greatly increases performance of most “collect and return” type operations. For example, it will be used in my forthcoming adaptation of Alan Renouf’s excellent vCheck framework for SQL Server.

To download the latest version of the function, go here. And please, tell me if you see something wrong or have suggestions for enhancements!

A few days ago I read a post over on Simple Dollar titled “How Much Are Family Friendly Benefits Worth“. It made me think about the non monetary things I appreciate in my job. I’m fortunate to work for a great company with a very open mindset. If you were to visit my offices, you’d probably think we were some kind of art studio, as opposed to a financial technology company. All our buildings are wide open spaces, with everyone mingling with each other. No one has an office; not even the CEO. It’s a wonderfully creative and collaborative workspace.

I love that I can work from home when needed, and that I’m given the freedom to innovate and be creative when it comes to problem solving. There would be nothing more frustrating than being stuck in antiquated or rigid rules, simply because that’s the way things have always been done. That’s not to say we are careless, far from it! We operate very cautiously and hold ourselves to very high standards. But that doesn’t come at the expense of an entrepreneurial spirit.

Most of all, I love the people I work with. We’re a nutty, humorous bunch of geeks for sure. We rib each other constantly, but we also push each other to be better. And we always support one another when we need it. The value of a good team cannot be underestimated. I, for one, have probably stayed in my position longer than I shold have, solely because I feel a great sense of loyalty to those around me. Some might tell me that decision has hampered my advancement. So be it; I’d go to war with these folks any day.

This month’s T-SQL Tuesday is being hosted by Aaron Nelson (blog | twitter), and is all about logging. As it happens my recent presentation at SQL Saturday 121 included a discussion around this subject in the context of keeping track of administrative activities. As a result, the topic is fairly fresh in my mind.

Rather than re-hash the whole thing, I’ll simply restate what I consider the most important point from that section of the presentation.

You can be logging everything and anything under the sun, but if you’re not reviewing and being alerted as needed, there’s really no point. How you do so is really up to you; use whatever tool you are most comfortable with. For some folks that might be SSRS, others Powershell (shameless T-SQL2sDay host brown-nose: this is my preference for collecting logs from multiple sources and displaying them all in one place), or even SSIS. It’s the act, not the method, that matters.

Personally, I like to review everything first thing in the morning, after I’ve filled my coffee cup but before I start reading over my e-mail. That way, any issues are immediately noticed and can be dealt with before I get distracted. Taking a tip I once heard from Sean McCown of MidnightDBA fame, I collect everything in one place and then send a single summary of problems, rather than dealing with individual e-mails. This makes the task seem much less daunting.

How do you collect and review your logs (of all sorts)? Because, of course, you are reviewing them. Right?