Author Archives: Greg Moore

Unknown's avatar

About Greg Moore

Founder and owner of Green Mountain Software, a consulting firm based in the Capital District of New York focusing on SQL Server. Formerly, a consulting DBA ("and other duties as assigned") by day, and sometimes night, and caver by night (and sometimes day). Now, a PA student working to add PA-C after my name so I can work as a Physician Assistant. When I'm not in front of a computer or with my family I'm often out hiking, biking, caving or teaching cave rescue skills.

Never run out of a plan

Posted on by
1

I’ve actually been meaning to blog about this for awhile, but have been putting it off, so here goes.

I’ve mentioned in the past my analogy of “flying the plan”. Lately I’ve been spending a lot of time on a site called Quora. It’s quite a fun site and I’ve learned quite a bit.

But this particular question I think is a great one for life in general.

Scrolling down, you’ll see a post from Jim Mantle. I want to take a quote from his answer:

There have been many air crashes where a problem was being worked by both pilots, neither was flying the aircraft, and they had a Very Bad Day.

If you read about the L1011 Crash you’ll see the real mistake was failing to actually fly the plane. The crew was so engrossed in solving the problem of a burnt-out landing gear light that they missed the fact that the plane was flying into the ground.  A simple burned out bulb and 101 people died.

Compare that to the Miracle on the Hudson where the pilots had a MUCH worse problem (lack of power in either engine) and managed to bring the plane down safely without any loss of life.

He also has good advice that he repeats often “Keep calm”.

I also want to quote Dirk Van Der Walk who later says:

You can run out of height, you can run out of engine, but one thing you can never run out of, is a plan. You must always have a Plan B.

I had a client a few years ago that had called me in to implement a specific change in their infrastructure.  There was also a fairly specific timetable by which it had to be done.

I met with CTO about once a month to go over the status of the project.  At one point it became clear that due to certain corporate policies, it would take about 12 weeks to get to a certain milestone in the project.  Unfortunately the schedule demanded we be there in about 8 weeks.

He asked me what we could do.  I explained I had no control over the corporate policies and that we should start to consider a Plan B.  I’m quite proud that I kept my jaw from hitting the floor when he uttered his next sentence.

This is no plan B and there can’t be a plan B.

This is an example of taking the mantra “Failure is not an option” to a whole new level.

Ironically I was there about a month later when the CTO was basically called out on the carpet for the status of the project and when it was clear he had no plan B, the corporate folks spent the next 24 hours designing a plan B.

In part this wasn’t too hard because the internal people on the project had already had several plan B’s in their mind.

It was only because others did have a plan B that we were able to save any real semblance of the original goal.

Moral of the story: always have a backup plan.  And start thinking about a backup plan to the backup plan.

Rolling in the Deep

Posted on by
Reply

I was at SQL Saturday in Boston this past weekend and I sat in on a session given by Paresh Motiwala: “Why do we shun using tools for DBA job?”

It’s a decent question and sometimes rolling your own (hence the title here and yes I’m listening to Adele right now) is the right answer.

But often, it’s not.

A case in point are DBAs who avoid using the built-in SQL Server maintenance plans, especially for simple tasks such as Backups.

Now, I’ll start out by saying straight up, sometimes they’re not the optimal solution. (You may want to backup certain partitions on a different a rotation schedule and the like as one example.)

Below is a recent situation I came across.

But, if you do decide to roll your own, please do NOT do the following:

  1. First write a sproc that has to be placed into each database as it’s created in order to run the backup. (If you DO decide to go this route, please make this a scripted part of your release procedure so it’s not missed.)
  2. If you DO in fact ignore suggestion #1, I will point out that it doesn’t do much good to make the first step of your sproc to check to see if the database exists. BIG HINT: If the database doesn’t exist, the sproc won’t run in the first place! Yes, I know you’re saying, “Obviously” but this is the situation I just came across.
  3. If you DO in fact ignore suggestion #2, you probably don’t need to call your own special “alert sproc”.
  4. But if you DO in fact ignore suggestion #3, make sure your “alert sproc” does more than call a “email sproc” and pass it a few parameters.
  5. And if you DO in fact ignore suggestion #4, please make sure your “email sproc” does a bit more than build a message and call the built-in SQL Server stored proc to send an email.

Now granted, there may be reasons to do some of the above. Perhaps your alert sproc also calls some sort of 3rd party monitoring tool. Or your email needs are very specific.

In the case I just rectified, none of that was true.  So there was a lot of additional complexity (which really didn’t work anyway) for no good reason.

Another problem this roll your own backup setup had was that it used the same filename every time for its backups. i.e. On Monday the backup name was M:\Backups\FOO_FULL.BAK.  On Tuesday it was M:\Backups\FOO_FULL.BAK, etc.

In theory (and generally in practice) each of these would be backed-up to a 3rd party so it was in theory possible to find the backup for a specific day, but that was an added complexity; and probably not one you want in an actual DR situation.  Also, if for some reason the backup to the the 3rd party failed (but the local backups continued) they’d definitely lose the ability to restore specific days of backups.

In addition, the person who built this procedure setup differential backups for MOST databases to run every 15 minutes.  Now, I wouldn’t necessarily call that a terrible idea, but in this case, almost certainly not the best approach in my opinion.  However, again, the same file name was used each time.

This means that in a DR event, the company could restore the previous nights backup and if they wanted, the most recent Diff backup and that was it.  If they wanted to restore to a point in time in between, that was impossible. And in my experience this is far more common than most other restore needs.

Finally, the developer who wrote all this clearly did not understand what the CHECKPOINT command did. He had scheduled a checkpoint job to run every 30 minutes.  Again, in general, not only not necessary, but probably a bad idea. However in this case it not only was not necessary, the reason given in the job comments was completely wrong.  He seemed to think it would keep the transaction logs from growing.

This is of course NOT what it does and sure enough on the one database still with FULL RECOVERY enabled the transaction log was far larger than the actual database. (Fortunately it was a lightly used database or the disk might have filled up years ago.)

Since discovering all this, I’ve gone and replaced all this complexity with a set of maintenance jobs.  These will guarantee each system database is backed up weekly (for their needs this should be fine) with unique names.  User databases will be backed up nightly and retained for 4 nights (and perhaps extended once we determine fully how much disk space we want to set aside for this.)  Transaction logs will be performed every 15 minutes. These too will have unique names.

Now the customer can restore to any point in time in the last 4 days (if they go to their 3rd party backup, even further back) up to 15 minutes before a failure (and in some cases if the log is still available and they can backup the tail of the log, up to the instant before the failure).

If they add additional databases, they don’t have to worry about remembering to put in 3 separate sprocs for each database added and adding new jobs to the SQL Server Agent.

Now they not only have a far more robust backup plan, they have one that is far easier to maintain.  Oh and one that will actually send an email if there’s a problem with the backup.

The morale is: Don’t make things more complex unless you absolutely need to and if you do, make sure you actually achieve the goals you’re trying to achieve.

Getting the right answer by suggesting the wrong one

Posted on by
Reply

I’m a participant on a CMC called Lily It is based out of my alma mater, RPI.  At some point, someone created a rule (which I’ve seen elsewhere so it’s hardly unique) that sometimes the fastest way to get the right answer to a question is to post the wrong answer.

There is truth to that.  I think in part it can be summed up with this XKCD cartoon.  Many of us who are involved in technology seem to have an incessant need to be “right”.  So when we see something wrong, we’re compelled to correct the mistake.

But, to be wrong, it has to be clearly wrong.  To go back to my cave rescue experience, if I recommend a 3:1 haul system and you recommend a 2:1, neither of us is necessarily wrong. We might be optimizing for different factors.  On the other hand, if you recommend we use 11mm rope for the haul line and I whip out some clothesline I’ve had in my car for a few years and suggest it should be good enough, after all it’s only Bill we’re rescuing, I’m clearly going to be wrong and need to be corrected.

These thoughts about being wrong and trying to find the right answer were prompted by a coding problem that has consumed far too much of my time. I finally came up with an answer that worked, but not one that I liked.

Essentially I’m building a Combobox (loading it from a datatable) in vb.net

It has key,value pairs, let’s call them (“Test1”, “A”), (“Test2”, “B”) and so forth.
(note VB.net appears to call these a DisplayMember,ValueMember pair and they can be loaded with a dictionary type, so in my mind it’s what they call the “valuemember” is what I’d consider the lookup key and that illustrate my misunderstanding of the issue.)

However, once I load the record in question, I want the selected value in the dropdown to reflect the value in the record (which of course is stored as “A” or “B” etc.)

There appears to be no way in VB.Net to easily say something like:

cbxResource.SelectedValue = Itemrecord.Value

Then I tried:

cbxResource.SelectedItem = Itemrecord.Item just to see if it would work. It doesn’t.

Googling suggests something like:

cbxResource.SelectedIndex = cbxResource.FindString(Itemrecord.Item)

That does indeed work, if I know the DisplayMember name. But that’s I want to display, not what I store in Itemrecord and as such means I don’t know it.

It strangely seems I can not set the index based on the ValueMember, just the DisplayMember.  To me this is strange since coming from a DB world, it appears the value member would be the key I’d want to look  up to select the Displaymember to be displayed.

I finally settled on a hack.  What if I switched the two?

cbxResources.DisplayMember = “Resource”
cbxResources.ValueMember = “Description”

cbxResources.SelectedIndex = cbxResources.FindStringExact(Itemrecord.Item)

cbxResources.DisplayMember = “Description”
cbxResources.ValueMember = “Resource”

I’m not sure I like this answer. It seems to me it should be far simpler. Or that I’m fundamentally misunderstanding how the control should be setup and used.  But for now it’s the hack that’s going into my code.

So why publish here?  Well either it’s a great work-around and I can save other folks the hours of fruitless searching I experienced, or someone can say, “It’s on the Internet and it’s wrong; I have to correct it!”

I’ll take either answer.

Moral: Sometimes being wrong is the right thing to do.

Practicing for Disaster

Posted on by
Reply

I’ve had this post by Wayne Hale in my queue for awhile since I’ve wanted to comment on it for awhile and until lately have been to busy to do so.

One of my current contracts requires them to do an annual DR test.  Since the end of the year is approaching, they’re trying to get the test in. Part of the test requires an “official” test observed by an outside auditor.

So, being smart, and since a lot has changed in the past year, we decide to schedule a dry-run or two before hand.

Well let’s just say those have not gone as expected.

Some might consider the dry-runs failures.

I don’t. I consider them successes. We are finding out now, in a controlled environment with no real time pressures, where we are weak and need to fix things.

It’s far better to do this now than during the audited test or even better than during an actual disaster event! So the dry-runs are serving their purpose, they’re helping us find the holes before it’s too late.

That said, I have to claim the part that I’m most involved with, the SQL Log-Shipping has been working well.  The only issue this week with that was a human error made by another DBA that was completely unrelated to the DR test and within minutes of him discovering his error he executed the proper procedure to begin fixing it.  The total fix on his end took no more than 5 minutes and other than monitoring on my end, the effort on my end took no more than 5 minutes.  That’s an excellent example of a robust design and set of procedures.

Today’s moral is don’t just have a DR plan, practice it. And not every failure is really a failure.

Shiny Things

Posted on by
Reply

It’s not uncommon for fads or “shiny things” to rise to the top.  In my work in cave rescue for example, there’s a lot of really cool equipment that comes out every year.  And often a particular piece or type of equipment will grab peoples attention and folks will start to use it all over the place. A good example of this are various “rope grabs” such as a Rescucender. Suddenly everyone is teaching, “Oh, you should use this tool, it’s so much better.” In part people fall into the trap of thinking it’s better because it’s newer.

Another example are ascending systems.  For climbing ropes in caves there are Frogs, Ropewalkers, Texas and more.  They all have their advantages. And quite honestly, for the most part, they are far better than what they replaced, the traditional “3-knot” system.  Unfortunately, as an instructor I’ve come across a large number of experienced vertical cavers who have NO idea how to climb on a 3-knot system.

Rescucenders have their place and they had their peak of prominence. For awhile it seemed they were used and taught all over the place. A lot of teams I know are going back to focusing on a good old Pruisk Knot for a rope grab.  This of course is the most common knot used in 3-knot climbing systems.

In one case, the newer, better, shinier thing was found not necessarily to be all that better.  In the other case, the newer equipment really is better. But the basics shouldn’t be forgotten. You never know when you’ll need to fashion Prusik’s out of shoelaces.

I bring this up because of an article brought to my attention today by , discussing SQL vs. NoSQL. Many have told me that NoSQL will replace a traditional SQL RDBMS. Of course I was told the same thing about OODBMS and other database systems. So far many of the replacements have withered on the vine and are long gone.  Some still thrive in niche applications.  I don’t think NoSQL will wither and die; nor is it simply a small niche.  Yet neither will it completely replace SQL databases either.  Both have incredible powers and are very good at what they do.  Both I think are here to stay for the foreseeable future.

But don’t fall into the trap of thinking you have a data problem and assuming the newer solution, NoSQL is the better solution.  It might be.  It might not. If I have to climb rope, I’ll use the “newer” (it’s decades old now) technology of my Frog climbing system. If I need to put a rope-grab on a rope, I’ll almost certainly go back to the “older” (it’s all relative) technology of a Prusik knot.

The actual needs of the application drive the solution. Don’t allow the solution to drive the design.

A good rule of thumb is: Anytime anyone sets up a debate, especially about technology as an X vs. Y debate, they’re probably framing it wrong from the get-go. It’s rarely X xor Y, but almost always X and/or Y, or even possibly Z.

All set to think

Posted on by
Reply

A short post today.

This past weekend I attended another SQL Saturday event. This one in Washington DC.  For anyone interested in SQL Server, I highly recommend these events. Typically the cost is just $10 to attend for the day.

I had put in a bid to present but unfortunately was declined.  That’s fine, there were plenty of other seminars worth my time to attend.

One of them, Common Coding Mistakes and how to Mitigate Them by William Wolf was a good example of that. @SQLWareWolf did a great job of illustrating a number of fairly common mistakes.  That itself was worth the price of admission. But what I really enjoyed was an observation of his, one that I’ve had in the past.  As a DBA, I can often spot stored procs written by a programmer with a non-SQL background.  The tell-tale is that many (certainly not all) programmers who come to SQL from another background often think in terms of rows, not sets.

An example of this would be the case of a programmer opening up a cursor and looping through the cursor to set the date on a number of records.  Of course for any person with a SQL background, we understand that can be a single statement which will execute far faster.

Now, there are certainly times when row by row is the only way to do it, but if you see that in a sproc or script, I’ll bet you 5:1 it was written by a programmer who didn’t know better.

Now, to be fair as a DBA who learned to program when “object oriented” wasn’t a buzz-word, I’ve been doing a lot of VB.net programming lately and I have to admit often I’ll find myself down a hole of twisty paths before I’ll realize I’m writing bad code and if I simply think of the data I’m trying to modify as an object, suddenly things get FAR more clear and easier.  I would not be surprised if 5 years from now the next programmer comes along, looks at my code and thinks, “What was he thinking? He obviously understood objects, but didn’t do everything he should have using objects.”

But in both cases, over time the non-SQL programmer coming to SQL will learn and I hope my OO code is slowly getting better.

No real moral here other than common observations and that hopefully overtime, we all improve.

Survivor Bias

Posted on by
Reply

I’ve been so busy lately I haven’t had a chance to write anything.

Of course part of the problem isn’t having ideas to write about, but time to write about them.

I think perhaps I should focus more on writing SOMETHING, even if it’s just a short post, than trying to write the Great American Blog post.

In this case I’m going to actually post a link to a great article on survivorship bias.  This is the sort of article I wish I had written myself.  As I’ve mentioned part of my point here is to get one to think about HOW we think.

The story of the bomber survivors was first related to be by a good friend in college, but without a source. Now at least I have a source for it.

In a similar vein, and the article touches upon it, people will talk about how great the older homes in weather prone areas were built because they’re standing decades after they’re built despite hurricanes or floods or blizzards. These folks completely miss the other 90% of homes from those eras that didn’t survive.

Years ago, my father bought and rehabilitated what we believe to have been the oldest house in town (in fact technically it was older than the town and probably where the town charter was signed.)

There really wasn’t anything about the construction that stood out that made it survive. Just luck at this point.  A single fire at any point in time could have made the second oldest house in town the oldest.

In closing, this article doesn’t represent most of my thoughts over the past 6 months only the ones that survived to the publishing stage.

Newspapers and paradigm shifts

Posted on by
Reply

When I was fairly young, I learned a detail about newspaper advertising.  The space on the lower-outside right-hand page was worth more than lower left inside page (i.e. along the fold).

If you think about how folks read and flip threw newspapers, this makes sense.  It’s an area more likely to be seen than others.

With news, there’s the term “above the fold” and “below the fold”  Obviously, you want the big news article on the front page, above the fold where it’s most likely to be seen.

When laying out a newspaper, there is over a century of experience in how to do things.  You don’t jump a front page news article to a page in the middle of the sports section; for the most part, you don’t run box-scores on the front page (unless perhaps it’s an upset at the Super Bowl or something else that will garner eyeballs); you don’t scatter sections of your newspaper across didn’t pages.

Years ago, I was proud to be part of one of the first newspaper web application service providers, “PowerAdz” (which later become PowerOne Media, and then later most of it was bought by TownNews.)

Even back then, I realized much of what was known about newspaper layout was going to have to change. There was no longer a physical fold in the newspaper.  There was a bottom edge to a browser window, and that still meant you needed the important news at the top.  But, how long should it run down the “page”.  How many pixels did the viewer have before the bottom edge of the window?  What was the width of your front page?

You also weren’t limited by a physical size to a page.  Articles could run on as long as readers were willing to scroll.  Or was having a reasonable sized page with links to following pages better?

Much of this is still in flux. And I suspect will continue to be for years to come.  Heck, just the fact that articles can have hyperlinks to other articles, or background information makes news on web pages very different from the traditional print medium.

What reminded me of this today was seeing yet another comment on a CNN fluff piece that was linked off of the front page.  The commentator was complaining that “this is news?”

Someone replied it was under the Entertainment section. Another rebutted “yeah, but it’s on the the front news page.”

That reminded me of these thoughts. What is the front page any more? Even though you can click to different sections of CNN, it’s not like a traditional newspaper where you have physically separate section, each with its own front page.  Now it’s all virtual and a front page is simply as you define it.

I think ultimately we have to let go of our definition of the front page of a news site and accept that links to news, fluff pieces and the like will all end up there.  Sure, there will be sections within the page, but to complain there’s sports, or entertainment, or other non-traditional news links off the front page will be like complaining you don’t have to unscroll the papyrus in the correct direction to read it: a sign of an older time.

Times change, but more importantly the medium changes, even if the message doesn’t.

 

Git ‘r Done (part 2)

Posted on by
Reply

Someone recently forwarded the following article to me: “Get Shit Done: The Worst Startup Culture Ever”.  Before reading it I was a bit ready to disagree. (see my previous post on getting stuff done.)

But after reading this article, I have to agree with the premise of the article; and point out I think there’s two different ways of looking at what “Get Stuff Done” can mean.

At my current assignment, a coworker and I were joking about how some people had some many letters after their name like PMP or CAPM, PMI-SP and the like.

So we joked we needed some letters and we settled on GSD – Get stuff done.  At times on this particular project we seemed to be the only ones accomplishing much or caring about accomplishing much. We had one person who was more concerned with the agenda of the meeting every day (yes, daily meetings to see why the project wasn’t getting done.  With 5-6 people in that room, that’s 25 or more person-hours per week of discussing why things weren’t getting done.)

So in that context, “decide what your goal is, and actually GETTING IT DONE” I think “Get ‘r Done” is an important concept.

On the other hand, I have seen (and fallen prey to myself, both as a manager and as a employee) of the “Get ‘r Done” attitude in the above article.

The project above I was working on never got done.  It wasn’t for lack of effort on the part of myself and several others that it didn’t get done.. It was though for the lack of effort on the part of management that it never got done.  At one point they asked me what could be done to make sure the project could be completed on time. I gave them several examples of areas where they could put some pressure on another group to streamline some procedures.

I was basically told that wasn’t going to happen, and that I had to work harder and “get ‘r done”.  At this phase of the project, I needed 4-5 items from another group and the other group had a policy that each item needed a separate ticket.  Each ticket had to be done sequentially and could only be submitted when the previous ticket was closed out.  Oh, and their policy was 2 weeks per ticket.  Period.

So, by my math, that’s 8-10 weeks. That assumes every ticket goes smoothly, which had not been our experience with this other group.

The project due date was in 6 weeks.

So, I was being told to get things done, in an impossible fashion.  Talk about demotivating.

In general, I’ve been my best as a manager, when I’ve been given the tools to let my team get the job done. It may be buying them dinner one night as morale boost. It may be making sure no extra work gets thrust upon them, or keeping certain other managers from trying to add to their work queue. In one case, it was buying a new NAS so we had enough storage space that we weren’t getting paged every night about diskspace issues. When properly motivated, people can move mountains and better yet, can often do it in a normal work week.

So, if you want to get it done, make sure your team has the tools to do their job, aren’t being distracted, and aren’t being given reasons to have low morale.  They’ll move mountains for you. But ask them to work harder without any of the above, and sooner or later you’ll find yourself without a team, and your boss simply asking you to work harder!

By the way, on that NAS, I think that $5K investment probably helped keep a key employee of mine from jumping ship for greener pastures.  That NAS was probably a better investment than if we had tried to offer him another $5K to keep him happy despite the lack of sleep from all the pages and other issues.

Moral: You want them to “get ‘r done”, give them the tools they need, remove barriers and keep morale up.  They’ll get it done.

Link

As a middle manager in several start-ups I’ve had to deal with being short of resources of all kinds.  But, at the height of the first dot-com bubble, I had a great team.  No, not all of them were equals, but each pulled their weight and each could be relied on to perform well, in their area of expertise.

One guy, was a great troubleshooter.  He’d leave no stone unturned and you could tell if a problem was bothering him since he’d fixate on it until he understood it AND had solved the root cause.  It wasn’t good enough for him to fix the current problem. He wanted to make sure it couldn’t happen again.  However, what he wasn’t good at, was the rote, boring procedures. “Install this package in exactly this way, following these steps.” He’d tend to go off script and sometimes that caused problems.

On the other hand, I had another guy who was about 2 decades older and not from an IT background.  Troubleshooting wasn’t his forte and he honestly didn’t have the skill set to do a great job at it.

However, he excelled at the mundane, routine, rote tasks. Now this may sound like a slight, but far from it. The truth is in most cases in IT, you’re dealing with the routine, rote tasks. In an ideal world, you might ever have emergencies.

Now, this wasn’t to say he couldn’t solve most problems as they came up.  Simply if it was overly complex or rather obscure, it wasn’t his forte.

I learned when I wanted to get stuff done, that assigning the routine stuff to him worked far better than assigning it to the first guy.  And just the reverse.  If I had some weird problem I needed debugged that wasn’t easy to solve, the first guy was the guy to through at the problem.

Each excelled in their own way and the team did best when I remembered how to best utilize their talents.