TSQL Tuesday: Why Are DBA Skills Necessary

November 2, 2010 at 8:00 am (Misc, SQLServerPedia Syndication) (, , , , )

 

Quote: “Database stuff, all this programming stuff, is easy. Anyone can do it. That’s why everyone in the company has sa privileges.”

For nine months, I worked in an environment where everyone, from developers to QA to the sales people to the receptionist, had SA privileges. You know what? DBA skills are necessary.

I speak from the point of view of someone that has had to recover a server after a salse person helpfully “cleaned up the temporary stuff on the server” by dropping tempdb, causing a late deployment for a client. I speak from the point of view of the guy who kept a window open on his desk with the database restore script ready to run, all day long, because of “accidents” that stopped development until I could get the database restored. I speak from the point of view of someone who had to explain, multiple times, “No, you can’t hit the undo button to get that data back” in client systems. And yes, I could go on and on about this nine month gig, but I won’t. You might consider it an extreme case, mainly because, it was.

Instead, I’ll talk about another  job. It was a free-wheeling dot com. We were doing some amazing stuff. We were running  well over 300gb of data into SQL Server 7.0 in a 24/7 environment… until the day one of the managers of the organization said “You database guys are spending too much time working on maintenance of the server. SQL Server manages itself. You will stop all maintenance, immediately, and don’t work on it any more.” It only took two weeks for the server to crash. It took us three days (72 straight hours)  to rebuild the system and recover the data, with no web site available for our thousands of clients during that entire time. Funny enough, a memo came down from management, not thanking us for putting in all the effort, no, but instructing us that we should spend adequate time performaning maintenance routines on the server.

And before you ask, yes, I can keep going. I have tons of examples. How about the application development team (different company) that built the entire database out of multi-statement table valued UDFs that called other UDFs that called other UDFs that called other UDFs… which led to a delayed production roll-out.

What I’m saying is, these are not isolated or extreme examples (OK, the first one was extreme, but only a little). This is real world stuff occurring on regular basis, every day, all over the world that negatively impact the businesses that we support. It’s not that people are stupid. They’re absolutely not. It’s just that databases and database servers, and data for that matter, are still pernicious and difficult. It’s not rocket science, not by any stretch of the imagination. Why do I say that? Because I can do it. In fact, I’ve been quite successful as a DBA and Database Developer. If I can do it, it’s not that hard. But it is a specialized set of knowledge, and one that is quite extensive. There’s just so much to learn about how data is stored, retrieved, and managed within SQL Server, it’s crazy to assume you wouldn’t need a specialist as you move deeper and deeper into managing more and more information. The lack of that knowledge, can, and does, have an impact on the bottom line.

If you’re a business person or a developer (and by developers, I’m not talking about the exceptional Brainiac that can really do it all, I’m talking a normal human being), ask yourself, do you know how to restore a database to a point in time? Just in case you don’t know, this means recover the database from backups and log backups to a point, usually just before a failure or a bad update or some other problem, again, that will cost your company money.  Ask yourself, do you know how to set up backups so you can do that point in time recovery? Do you know how to tell why a query is running really slow and keeping customers from buying your stuff? Do you know how to tell why you’re getting deadlocks and losing transactions? Do you know how to tell if a process is blocked or just taking a long time? I’m clearly belaboring the point, and this is still fairly basic information. I haven’t even started delving into configuring systems for mirroring, or gathering metrics for a performance tuning effort, or any other of hundreds of tasks that need to be performed to build large scale data management systems that keep your business in business. If you don’t know what all this stuff is, or you don’t know why it’s needed, you might just need someone in your organization who can help out.

And yeah, there are tools out there now, like Object Relational Mapping (ORM) tools, that take away vast amounts of the labor that used to go into building databases. But, having worked with teams building tools with an ORM, while they’ve successfully removed the database from their thoughts & designs by using the ORM tool, they’re still storing data on a database system. Guess what? The problems, such as deadlocks, index scans, blocking, still exist. Sometimes, these problems are even exacerbated by the ORM tool. Now what? You need a specialist.

Fine, what if you toss all the mess, get rid of relational storage, go for one of the NOSQL database systems. Yeah, that might work, a bit. But guess what. You’re still storing data. It’s still going to, in most circumstances, outlive the application that built it. It’s still going to need to be reported against, backed up, recovered, protected, performance tuned… In other words, you’re going to run into situations where, despite the fact that you’ve eliminated the things that irritate you, like SQL, like constraints, you’re still dealing with pernicious data and you’re going to need someone who has been working out how to deal with that.

Does every single company with any installation of SQLExpress need to hire a DBA? No, of course not. But as soon as your business starts to rely on having data around, all the time, you just might need the skills of someone who has been working with this stuff for a while.

Advertisements

Permalink 11 Comments

Links From Twitter

September 7, 2010 at 12:34 pm (Misc) (, , , )

Normally, I try to stick to posting technical info or community stuff on the blog, but there were a couple of links from Twitter today that are too good not to share.

First, an interesting take from Tom LaRock on the issue around the lack of quality DBA’s. He suggests that it’s actually a lack of quality managers. Go read it & comment there.

Second, this is Not Safe For Work (NSFW). Please, please please understand that before you click on this link. It’s a hilarious discussion about NoSql. Put on headphones & give it a listen.

Back to your regularly scheduled blog posts…

Permalink 1 Comment

Seacoast SQL Users Group

April 12, 2010 at 1:03 pm (Misc) (, , , )

Tomorrow is the Seacost SQL Server User’s Group inaugural meeting. I’ll be presenting a session called “Understanding Execution Plans.” It’s my introductory session on execution plans and I love giving it. Please come by and support this new user group. Mike Walsh (blog | twitter), the new user group president, is under the impression I draw a big crowd. While that isn’t true, please show up anyway just so that Mike has a successful first meeting. I promise a bit of free stuff. Remember, interactivity is bribed encouraged.

Permalink 5 Comments

Renewed as an MVP

April 1, 2010 at 11:26 am (Misc) (, , )

I received my renewal notice and, after checking the message header, it is April 1st, I got very happy. I’d try to wax poetic about this, but I just read Paul Randal’s (blog | twitter) comments on the fact that he and Kimberly Tripp (blog) were just renewed. As in lots of things, he did a better job than I can in describing exactly what this means.

Permalink 5 Comments

Blog Anniversary

March 9, 2010 at 8:26 am (Misc) (, )

Two years old. In March of 2008 I received a whopping 96 visits. I’m up to 1900 so far this month. I’d call that a positive growth trend. Thanks for stopping by, especially if you’ve been here more than once.

Permalink 4 Comments

MacGyver?

March 8, 2010 at 8:00 am (Misc) (, , )

Who the heck is MacGyver? Television program you say? Hang on. I need to visit imdb.com

Oh, the late eighties… Yeah, I wasn’t watching TV in the late eighties. Actually I didn’t own a TV in the late eighties. But reading a few of the plot lines (you guys watched this?) I get the idea.

Nuts. I don’t think I’m MacGyver. Can’t we just buy something to fix the problem?

Honestly, the only thing that comes to mind was the time when I needed to get alerts when jobs failed, but I couldn’t install DBMail on the server because our admin types didn’t want mail clients on our machines. What to do? Use event forwarding. Instead of setting up the mail client on a server, I got it installed on a different machine, then forwarded events to it and let it take care of sending emails. Yeah, I know, almost as exciting as a MacGyver episode (based on reading a couple of synopsis’, synopsi, whatever).

So, I don’t have a good MacGyver story. But, what I do have is a ready set of skills, just in case I need to be MacGyver. What skills you may ask? Let me ask you, do you run backups from TSQL or from Management Studio. I don’t mean every time, but most of the time. For that matter, how much do you run CREATE or ALTER or DROP through the TSQL window and how much do you know the right-click pop-up menu of most of the database objects by heart? If you’re only using the SSMS window to administer your databases, then your MacGyver skills are going to be missing when the time comes to put them to work. I came into the database world through programming. My skills are rusty, almost to the point of immobility, but I can actually write functional (mostly) C# code and I’m working on my PowerShell skills. Is the only language you know T-SQL? Best get started on, at least, PowerShell so when your MacGyver moment comes along, you’ll be ready. Slightly harder to measure, but are you open to new ideas, especially if they seem a bit crazy or weird or MacGyverish? Assuming we’re not putting our production data at risk, I’ll try any scheme or methodology you want to take a swing at. I’ve done Agile, SCRUM, Feature-Driven Development, Test Driven Development, all on the database. I’m willing to try things. I call it being a bit of a cowboy, but you could call it being a little bit MacGyver (and I will in this case, just to keep with the theme). In addition to all this, I read, a lot, and study, a lot, to try to learn new things and keep my brain facile (as much as possible). Do you have twenty years of experience or one year of experience repeated twenty times?

There’s a self-help saying, control the mind and the body will follow (in Kenpo, we say control the head and the body will follow, same thing, just outwardly directed). You can’t be MacGyver if you don’t have that MacGyver mind-set and the skills it takes to use it. So, while I’m waiting for my great MacGyver story, I’ll keep practicing my skills.

Tagging… I’m going with the one person that I suspect may have never seen the show before, Gail Shaw.

Permalink 2 Comments

What three events brought you here?

January 18, 2010 at 2:18 pm (Misc) (, , , )

Another one of the DBA bloggers games of tag is occurring. I’ve been asked by Tom LaRock to answer Paul Randal’s question; What three events brought you here. Well, mom was a cheerleader and dad was a football player, so… Oh, wait. I guess I misunderstood the question. He means what three events lead me to becoming a data geek. Well, that’s completely different. Luckily, no (further) cheerleaders will be harmed in making this (part of the) story.

Geek

When I was 16 years old and Jimmy Carter was President, Radio Shack was still considered to be the place for aspiring geek wannabe’s. It just so happened that I hit $500 in my bank account about the same time they started pushing this radical thing called a “personal computer.” I bought the base model TRS-80 with a whopping 4k of memory, an OS, a keyboard and a monitor. I supplied the cassette tape player (I’m providing links to some of the more arcane bits of technology since you young punks won’t know what I’m talking about) for storage. I also got one game, Space Warp. I started learning basic. I wrote up a random encounter generator for the Traveler role playing game that used all 4k of memory. I was hooked.

I want to Direct

But, instead of going to MIT & pursuing computers, as I should have, and unlike Paul Randal, I joined the Navy. While there I became great friends with a guy that was sold on getting out & going to film school. That sounded like great fun. So I did that. I went to film school and I started doing indie work in NYC. But, unfortunately for my film career and fortunately for my database career, NYC was not the place to be for indie film or to really break into the film business in the late 80’s. I should have been in LA or maybe up in Canada or down in the Carolina’s, but NYC was largely over as place to get started (very strong community, but few opportunities for up & comers). I supplemented my film jobs with temp work, mainly typing letters & doing data entry. One day I was asked what I knew about databases. The correct answer was nothing, but what I said was, “What do you need?” They needed a Paradox database that could store names for a mailing list. “I can do that.” I ran out & bought a Paradox book and stayed about two chapters ahead of what they needed until I had a fully (mostly) functional system up & running. I shudder to think what the thing must have looked like now, but at the time I was a hero. I got a lot more work from these guys and stopped worrying about my film career, because, I still loved computers.

You need to do something

I had been working in IT full time for about 10 years. I’d spent most of that time doing development, first in Paradox, later in VB. I was working for my first dot com. Our DBA had quit, but we’d just kept going, but after a while, we were really feeling the pain of not having someone spend all day, every day, looking at the database. Finally, I went into the bosses office and went on a total rant. “We need backups. We need consistency checks. We need someone to vet the design and validate the code.”  Blah, blah, blah. He waited until I ran out of steam and then said, “OK, what are you going to start with first?” A DBA was born.

That’s about it. You could pick any number of events, but these are the ones that kind of stand out for me.

Tagging:
Gail Shaw (because I always do, and she’s interesting)
Tim Mitchell (because he’s interesting)
TJay Belt (because he’s interesting too)

Permalink 4 Comments

Database Design Process

January 18, 2010 at 11:35 am (Misc, Tools) (, )

Buck Woody recently asked a question; how do you design a database. He outlined the process he followed and asked four questions about how each of us do our work:

  1. What process do you follow?
  2. How important are the business requirements?
  3. What tool do you use to create the design, do you need it to diagram, do you even care about diagrams?
  4. What’s your biggest pain-point about designing?

Funny enough, I haven’t done a full on database design in over a year. My company just finished about 6 years of very hard-core engineering work, designing and building or redesigning and building, the majority of our data input and collection systems. Then, I was doing lots of design work. Now, we’re either largely in maintenance mode for most of those systems, or the few new major projects we’re working on are using CRM, no database design, or hoping that database design will never, ever matter and using nHibernate to build the database on the fly, based on the object model (this, by the way, is still in super-double-secret probation development mode. I haven’t seen what they’re producing). All that means we’re doing very little design work now. That will change.

Process

The process I follow isn’t radically different from Buck Woody’s. I get the business requirements, which are hopefully written on something more substantial than a cocktail napkin, and with more detail than will fit on one, and I read them. Or I meet with the business people and ask lots and lots of questions, which I’ll probably do even if I have written requirements. Sometimes, especially at my current company, I’ll get a full logical diagram from another team. I’ll still basically do the same design work, even if I have a logical model, but I’ll use it as a reference point to double-check my understanding of the requirements. From there:

  1.  Identify the nouns. Figure out what kinds of objects, or things, or widgets that we’re talking about needing to store.
  2. Figure out the relationships between the widgets. What’s a parent and what’s a child and which of the widgets is related to many other widgets or to one other widget, etc.
  3. Lay out the attributes for the widget, meaning the columns in the table. Big points here include getting the data type correctly identified and figuring out which of the attributes are required and which are not. Further, which of the attributes come from pick lists, which means look-up tables.
  4. Identify the natural key. I’m with Buck, most of the time I use an alternate, artificial key (yeah, frequently an identity column), but I want to know the natural key so that I can put a unique constraint on the table, in addition to the primary key. This is a vital, but often missed step, in terms of the business processes being modeled.
  5. Figure out where I’m going to put the clustered index. Usually, but not always, this will be the primary key, but I do it as a fundamental part of the design. That means, as a fundamental part of the design, I think about the most common access path for the data. That’s going to be where the cluster needs to be.

How Important Are the Business Requirements

Buck, I respect you and I like you and I hate to do this, but you’re kidding with this question, right? No requirements, no database. The requirements are all. The most important reason you want the requirements written down is because that means that business at least thought them through, all the way, one time. It’s your best bet that you’re going to deliver something that slightly resembles what the business wants and needs. I live and breathe by the requirements. When someone from the business corrects my design, “Oh, that widget is related this thingie, not that watchamacallit,” I get them to change the requirements to reflect that new information. This way, when someone wonders why I did what I did, they’ll always be able to see the requirements the way I saw them.

Tools

I use Embarcadero’s ERStudio. I know there are other ER tools on the market, but I fell in love with this one on the day I needed to change the data type on a column that was in about 30 different tables and I did it in a couple of minutes using their scripting tool. I’ve been using it ever since, and we’re talking well more than ten years now. It’s just great. I only use it for the initial design and for documentation after the design. Once the design is done, the first time, and a physical database is constructed, I don’t work from the ER diagram to do builds and deployments, I work from source control. Do I have to do it like this? No, but I really enjoy the power of an ER tool while I’m doing the design because it lets you do a lot of changes, quickly and easily without having to rebuild a database over & over.

Biggest Pain Point

The largest pain point has to be changing requirements. Change happens. I embrace it. I’ve worked on agile projects and I like the general approach and mind set. And yet, changing databases is hard. It’s not so bad when you’re in the strict, isolated, ER diagram only stage, but as soon as you’ve built the database, even one time, change gets difficult. It’s not so bad if you work closely with the design, test & development teams and get their buy-in, early, that test data must be tossed & rebuilt with each fundamental design change. But that’s a hard agreement to get and so you end up spending a lot of time trying to retain the test data AND fundamentally redesign the data structure. This is not an enjoyable combination.

That’s about it. I would say the one thing I try to do, and it’s not easy, is be open to doing silly stuff. I try, and I don’t always succeed, to let the business or developers or logical modelling team make silly choices after I carefully tell them why they’re silly and the likely outcome. I do this because fighting them on every single silly decision is a losing proposition. Plus, it saves you for the fights against the stupid, as opposed to silly, things that come along occasionally.

Permalink 6 Comments

Horror… sort of

January 11, 2010 at 11:18 am (Misc) ()

You must read this post from Tim Ford to understand why I might do this on my technical blog. But Tim’s a friend and the chance to branch out, at least a little, could not be avoided. I hope I don’t lose too many of my eight readers from this….

The sign read “Live Nude Cats,” so of course, I had to go and take a look. It wasn’t the best part of town and I certainly didn’t have any reason to be there, but the sign read “Live Nude Cats.” How could I resist? How could anyone?

I parked and locked the car, scanning the parking lot. Not good. There must be 50 different ways a person could get jumped in here, and did I mention this part of town is less than savory? “But,” I thought to myself, “I’ve started, so I’ll finish!”

I walked up to the entrance. The obligatory muscle at places like this was sitting on a stool outside the door, despite the cold. Why do these guys always look slightly overweight and on steroids. “No cover he says.” I’m shocked. No cover with “Live Nude Cats.” I step in to the dark room, out of the light, momentarily blind. The sound & smell hit first, thumping music like, well, one of those places, the smell of old beer and feces… this is not going to be good.

My eyes adjusted and then I saw it, animals, all types of animals, but cats everywhere. Don’t get me wrong, nothing unnatural was occuring, but there were animals all over the place and guys, all guys, drinking between them, just watching. “Live Nude Cats” indeed. Live Nude Animals. What the heck is this place?

I notice one guy put his beer on the floor for a moment, but as soon as he looks down he’s going to be very sad about the pony, because the silly thing kicked it over. He notices. H jumps to his feet screaming, “What the heck is the point? Just ask George “Let’s Have Padme Die Of A Broken Heart Instead Of Anakin Crushing Her To Death” Lucas. It’s all pointless and meaningliness.”

Maybe that was the message behind “Live Nude Cats.” That there wasn’t a message at all. That it was all as much drivel as the last three Star Wars movies. Let’s face it the script must have been written by simply using some magical tool, library, or bong to bash against a keyboard over & over again.

At that point, my nerve left me and I ran, as fast as I could out of… of… whatever the heck that place was, exploding out the door bursting into the sun, blinded as much as I was when I went in…

Permalink Leave a Comment

Plagiarism

January 1, 2010 at 9:36 am (Misc) (, , , , )

UPDATE: Lulu has removed, not just my stolen material, but all offending material. That means some of the links in this post will no longer work. Back to the post…

And really bad plagiarism at that.

I received an email from someone suggesting I check out a book on Lulu.com, that it might be a copy of my book. Sure enough, this other guy, William Miller, had posted my book, with the original cover (that had my name on it) and the original description on his own “author” page. He also offered a decent little discount on the price. Nice guy. I tried to get an image of his copy of my book, but I can’t find anything on any of the internet archives, which is just as well. His work does show up in a Google search and you can see the cached page from a Bing search.

I contacted Lulu.com and they promptly took my book down. Thanks guys.

But, as I type this, Mr. Miller still has an account. Further, works published under his name are copies of older works, although again, he offers a discount. At least he took the extra effort to provide a different cover graphic. I’ve been in touch with other authors that were on his page (one of them was the original contact that got this started) and it appears that some of them have had their work removed. I appreciate Lulu’s fast response to this issue, but from what I can see, at this point, they’ve only done about a 1/4 of what they need to do. They should pull down all of Mr. Miller’s work and shut down his access to the system. Then, they need to do at least a little bit of oversight to ensure that people can’t do this in the future.

Just so we’re clear, Simple-Talk and Red Gate set up that Lulu page. I wrote the book on spec for them (and thanks, again) and they have publishing rights (although I have copyright). I’m not fighting over this for money because I won’t make a dime if an extra book sells on Lulu, or anywhere else for that matter. I just really and truly hate being ripped off, even though, in this case, money is not involved. I’m especially peeved because of the amount of work that goes into writing a book, even an obscure technical manual that’s only 200 pages long.

I also want to emphasize another point. I think Lulu is providing a great service and they seem to be doing things in an above the board, if somewhat inadequate, fashion. Based on the experiences that I’ve had to date, I’d recommend them for people who wanted to self-publish. I don’t mean for this to be a hit piece on Lulu. I want to get the guy who did this smacked around a bit more and make sure he, or others of his ilk, can’t do it again.

And one other point, kind of a side point, I started posting about this when I found out about it on Twitter. My first response from Lulu was through Twitter. I’m actually wondering if they first saw my complaint on Twitter instead of in the email I sent. I’m going to chalk it up tot he power of Twitter.

If you do want a print copy of the book in question, you can get it on Lulu, or a slightly newer version on Amazon.

Permalink 9 Comments

Next page »