Jul 08 2014

Worst Day of a DBAs Life

Red Gate Software is running a campaign around coping with the worst day of a DBAs life. We’ve been posting some really fun stories with, I hope, a kernel of useful information inside each. Chances are, if your DBA career has been like mine, your worst days don’t involve explosions and car chases. But now they’re asking for people to write up stories, what was the worst day in your life as a DBA. I know, I know, first world problems, right? Regardless, I have yet to put a company out of business or kill anyone with any errors I’ve made, although I’ve worked at places where either was possible. But the one day that just stands out, well it started about three weeks ahead of the bad day.

I was working for an internet startup. I was very much still learning the ropes as a DBA, although, I had helped design a system on SQL Server 7.0 that was collecting about 1gb of data a day. Back in those days, that was big data. But, frankly, I wasn’t doing the monitoring correctly. I was doing a lot of manual checks and manual maintenance, stuff I should have taken the time to automate. Live & learn, right? Anyway, because I was taking a lot of time out of each day to do maintenance and run checks on the systems, I wasn’t spending lots of time supporting the development teams. One day, one of the managers came in and said, “No more maintenance. Things should be fine. Spend time on development.” And he was serious. I argued and lost. So I started spending a lot of time doing development and let the maintenance slide. Fast forward three weeks, things had largely been stable, but, I didn’t have monitoring in place, so I wasn’t noticing that we were running a little hot on transactions. The transaction log was bigger than it had been. And then disaster struck. The backup drive filled. I didn’t notice. Transaction log backups started failing. I didn’t have alerts. The log drive filled, all the way, and our 24/7, zero downtime web site went kablooey.

It was at 2 in the afternoon or something, so I, and my fellow DBAs were right there. We immediately tried to backup the log, but it wouldn’t backup. We tried adding a log file. Didn’t work. Then we started getting creative. We tried every possible thing we could think of. Some of them failed quick, so we tried something else. Some of them took hours to fail, making us think we had fixed the problem. It took us 48 hours of failed attempts before we finally admitted defeat, went to the last good backup and restored the database, losing about 12 hours worth of transactions. It was a nightmare.

The good news was, our directive to stop doing maintenance was cleared. We immediately went about setting up alerts and other things so that we wouldn’t get surprised like that ever again. It wasn’t fun, but it was a great learning experience. Plus, all the troubleshooting for 48 hours straight provided excellent camaraderie within the team. That said, I’d rather have avoided the whole thing, and could have with proper monitoring and alerting.

Jun 30 2014

The Curse of Working With A DBA

noI no more than finished my rant from last week than I started thinking about all the reasons why a healthy chunk of the reasons that developers want to bypass relational database is not the horror of the relational database itself, although, that’s there. No, a very large reason why is the DBA.

We’re on a blog called The Scary DBA. I earned that title, well sometimes. Sometimes I got it and I wasn’t sure why. However, it’s perfectly in keeping with how many people view their database administrators; grumpy, obstructionist, slow, difficult, control freak, etc.. There are even jokes about it, “What’s the DBAs favorite word? No!” And for those answering “It depends” that’s two words.

I understand why. In large part it’s that phone in your pocket (used to be a pager on your belt, I’m old). That darned thing can go off any time night or day. It tends to make you very gun-shy. You start doing anything you can to keep that thing from going off. And developers, holy moly, they want to change things. They want to introduce new tables and new queries and they want to do it all really fast, faster than you can possibly review all that code, and all, ALL, AAAAALLLLLL, that code needs to be reviewed before we can let it unsettle our production servers. No.

And developers get crazy ideas in their heads sometimes. Maybe it would be easier to put the queries in the code rather than in stored procedures? What? How the heck can I review all the code too? No.

Developers also start thinking to themselves, you know, most of this T-SQL code could be generated using other code. Wait, that means even more T-SQL generated even faster, and generated by a program, and I can’t review that program, or it’s code and you want to put it into my production server? Are you smoking something over there? No.

CLR? Ha! No.

ORM tools? Have you seen that T-SQL? Hell no!

How about other tool sets? Maybe an object database would work here. We may be better off using unstructured storage for data collection in this situation. ID/Value pairs might work well for this application. No, no, no and no again. Just in case you think of something else, no.

Gee, I’m sure if I were a developer I’d be perfectly happy with this approach. I’ve no doubt as developers introduce even harder subjects like agile development, devops, and other things in the future the responses will be just as nuanced. In short, I’d be doing anything I could to bypass the DBAs too.

So, what do we do about it as DBAs? Change. Use the word “Yes.”

We need to recognize that the business is changing, fast. That means that the applications are going to change, faster. We, DBAs, must become enablers. We must create processes and methods that smooth and speed the deployment process in order to provide lots of opportunities for automated testing because you’re not going to review this code and you can’t just stand in the way. We need to adopt to and adapt the development and deployment paradigms used by the developers. We have to start treating databases, as much as possible, like code. We need to have our code in source control along side the application code. We need to be at the stand ups. In short, we need to change what we do and the way we do it. We can’t just say no. We need to say yes. The goal is to get in with the developers and influence through assistance, not just stand in the way.

Is it more work? Yes. Is it going to be hard? Yes. Will we have to go quite a long ways to convince them that we’re not just going to say “No” again? Yes. Are there damned good reasons for us to make these efforts so that someone who loves and protects the data and will be able to provide special skills not developed by most programmers? Yes again.

See, it’s easy. Try it.

Jun 18 2014

The Curse of Relational Databases

Let’s face it, none of Information Technology is easy. Oh yeah, there are those few geniuses that have an absolute grasp over some small aspect of the stack, or those other geniuses that have a very shallow knowledge level, but understand the entire stack. But the stack itself, it’s vast, deep, wide, utterly unfathomable. So what do you do? You cheat. You take shortcuts. You ignore things you don’t like/understand/appreciate. And then there’s all the things you just don’t know. Or, you cheat another way, you get experts that have drilled down on a particular technology so that they’ll provide you with the knowledge you need. Ah, but then you have to listen to them and what happens when your local genius (deep or wide) doesn’t agree with your hired gun? Do you override your local person for the hired gun (I’ve seen this happen a ton where consultants were favored over in-house), or do you go with your local person (I’ve also seen this where the local person who has solved all the problems before may be over their heads now, but they’ve always been right and are therefore trusted)?

I just read (and I mean I finished about 90 seconds ago) this really interesting article on The Curse of the Excluded Middle. I won’t even pretend to you that I understood all of it. But, I did get a pretty fundamental concept out of it, this programming stuff is very hard, we’re going to take shortcuts to get through it, and those shortcuts come with a cost. The argument being put forward isn’t to somehow find a magic solution. It’s simply to acknowledge that there really is a cost, maybe even a cost you don’t completely understand. Further, that cost, and especially your lack of understanding of it, will come up and bite you on the behind.

Which brings me around finally to developers and databases. Relational databases are a pain the bottom. They really are. Speaking just of SQL Server (where I spend most of my time) you have to work with a ridiculous, archaic, language, T-SQL, in order to manipulate the data. And the rules of normalization, yeah, we can all learn them, but applying them makes every single aspect of coding harder. Plus the language lets us do things that it then interprets in horrendous fashion. Oh, and don’t forget all the obscure and weird maintenance and configurations that you have to go through to keep the silly servers online and functioning correctly. Then there’s the whole object/relational impedance mismatch thing to chew on our behinds even further. In short, I completely understand why developers would like to burn the entire edifice to the ground (come see one of my presentations when I talk about the “data persistence layer” that a particular dev team wanted to build). And all that is just the technical side of this mess. I’m not even going to address the personnel issues that come with the different focuses of responsibility between a developer and a DBA.

So when the developers bring in an Object Relational Mapping (ORM) tool or they explicitly attempt to slap out at DBAs by going after a NosQL database (and no, despite the new twist, it means NO F’ING ESSQUEELL, instead of Not Only SQL as many are saying now), I understand why they would do this. It short circuits all the issues. We get around the problem. We speed development by eliminating that thing that we didn’t completely understand and certainly didn’t like and…. Hang on… Isn’t there a darn good chance we’re digging a hole here?

Yes.

Don’t get me wrong. I see the need for unstructured data stores, ID/Value pairs, speed over consistency, speed over durability, the need to move fast because your competition is sure as heck trying to move fast. So NoSQL databases serve an absolutely valuable purpose and used correctly fix unique and difficult problems. A well structured ORM properly applied absolutely saves development time. But there’s this nasty little surprise hidden behind the need, the sometimes seemingly desperate need, to completely get rid of relational storage. That surprise? Relational storage actually works and works well when applied to the appropriate problems in the appropriate ways. It provides a means of collecting information fairly quickly (although not as fast as many NoSQL databases), storing it efficiently (although, maybe not as efficient as some object databases), and returning it to the users on demand (and here relational does stick out again). And does it all in on place, not one for collection, another for reporting, or some of the other strange perambulations I’ve seen people going through with some NoSQL implementations (again, not all, some are awesome, but many are horrific).

About twice a year I get to read a “death of the DBA” article that points to a technology or process or tool that’s going to eliminate the need for those nasty, ugly, difficult, relational databases and those freaks who try to keep them online and available. And about twice a year I see lists of the most needed workers in IT and guess what’s almost always there, yep DBAs. The fact is, relational storage does work. And instead of trying to eliminate it, or the DBA, or the code necessary to interface with it, embrace the stuff and learn to use it, or hire someone who actually knows how to use it and then listen to them. I’ve just seen too many places where the need to eliminate relational storage and DBAs is driven by one of two things, I have a shiny new hammer and everything is a nail, or, databases and DBAs are a pain because they make us do stuff we don’t want to, so let’s bypass them. Those are almost precisely the wrong reasons to go about moving to a NoSQL implementation, because you’re going to be ignoring stuff, as the Curse of the Excluded Middle talks about (and I know, it didn’t talk about databases, I’m extrapolating, hang with me here), and the things you ignore, or worse yet, don’t know about, are going to hurt and may hurt badly.

Apr 22 2014

I Am Better Than You

That is a patently false statement and total BS. It sure does crawl up your spine though doesn’t it? Why then do we need to do this?

I read an article, “How DevOps is Killing the Developer,” and, frankly, was a little put off by this:

Good developers are smart people. I know I’m going to get a ton of hate mail, but there is a hierarchy of usefulness of technology roles in an organization. Developer is at the top, followed by sysadmin and DBA. QA teams, “operations” people, release coordinators and the like are at the bottom of the totem pole. Why is it arranged like this?

Because each role can do the job of all roles below it if necessary.

Nice to know I’m almost as good as a developer. Now, I could go off on a “bash the developers” rant, but I think that would be seriously silly and counter-productive, not to mention completely against my beliefs. I really do like, admire, respect, developers. I also respect system administrators and SAN admins and QA people, hell, project managers. You can go find a web site or a blog specializing in any of these IT disciplines and locate the “DBAs are better than developers” article or the “QA people save the universe” article or “Thank the gods project managers keep all you poo flinging monkeys in line” article. They’re out there. And every single one of them is wrong.

My actual job title these days is Product Evangelist, but I’ve spend the last 15 years as a DBA, database developer and data architect. I feel like I have a handle on that job. I’ve specialized around the Microsoft stack, not because of any sort of religious beliefs, but because it pays the bills. Before that I worked as an application developer. Before that I was in tech support. And you know what, at no point in my career was any of the jobs I did literally more important than the others. You know why? They’re all in support of the same thing: the company succeeds.

Now, fine, developers are smart people. No question, no argument. Developers are capable of learning other jobs (I’m living proof). But you know what, so are people in all those other positions. How do I know this? Because I’ve worked with the QA person turned developer. She was a GREAT QA person. She was so great because she learned the full stack. She developed an understanding of databases and systems and code in support of doing a better and better job at QA. Then, she finally decided she wanted to fix the code a little earlier and switched over to development full time. I’ve also met the QA person turned sysadmin. I’ve met the developer turned SAN admin. The system admin turned DBA, the DBA turned developer, a million and one support desk people turned developer/dba/admin. Every one of these people followed similar trajectories. They started learning the full stack and found areas where they could specialize while using their knowledge of the full stack to make each position they were in better.

But all these jobs and all these people are all, or all should be, focused on one thing, helping the business to do what the business does, for most businesses, support the customer.  Regardless, the focus needs to be on the goals of the organization, not on the purity of a job, a process, a software stack, or a system. Purity and perfection are dangerous concepts within IT. We need to keep our focus where it belongs, not on MY code or on MY database or on MY servers, but on OUR BUSINESS.

And DevOps (I wish the term didn’t have such bad connotations), is about breaking down communication barriers, not just putting all the work on one person/team. Again, focus back on the business and what the business does.

So no, I am absolutely not better than you (the title is just click-bait). I’m not. But, you’re not better than me either. If my saying that makes you angry, maybe you need to reexamine your assumptions.

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.

Oct 09 2013

Do Be a Gatekeeper

batman-the-dark-knight-1I read this fascinating blog post called “Don’t Be a Gatekeeper” by Julie Zhuo. Please read that first.

It really resonated for me in a lot of ways. Everything she said is 100% applicable to our jobs as data professionals. Work to make things more robust. Create processes and structures and an environment where you don’t have to be the hero all day every day. Yes, absolutely. But… ah, there’s this nagging little voice at the back of my head. Let’s ignore it for a moment.

Are you a gatekeeper for your developers? Why? Get out of their way. Listen to what Ms. Zhuo has to say. Your development team doesn’t need you squatting on their servers preventing them from moving as fast as they can. In fact, they need you to help them out. Create mechanisms and deployment best practices that assist them. Get your database into source control. Implement continuous integration. Explore the concept of continuous deployment (ABCD, Always Be Continuously Deploying, Coffee is for deployers).

Are you a gatekeeper for your testing teams? They don’t want you to be either. They need the ability to quickly, and easily, on their schedule, reset from a set of tests. Preferably without having to wait on you. Again, listen to Ms. Zhuo. She is right. You’re not the hero because you’re the only one who has access to do fundamental processing that can, fairly easily, be automated and then released to the teams that need it.

Are you a gatekeeper for your production environment? No? Well, here I say, you better be. I’m now going to let that voice in the back of my head out to play.

First, those data professionals among us who are tasked with being DBAs (or some equivalent) are expected to be on call for production problems. That means we’re responsible for what is there in production. Which means… it’s not a question of whether or not there should be gates or gatekeepers. It’s a question of WHERE the gate is. Because, in order for me to sleep at night, I need to do the things listed in the previous article, make things robust where I don’t have to be a hero. And that means controlling what goes in there as much as I reasonably can (have to use weasel words here). This environment, the one the business depends on, can’t be out of control and crazy.

Second, there are regulatory requirements about who can have what kind of access to certain information. Legally you can’t just open the production environment up to the entire company. Again, WHERE the gate goes is the question, not whether or not there is one. If the government tells you to put a gate up, you usually put a gate up.

Third, Uncle Ben (by way of Stan Lee,), while educating young Peter Parker/Spiderman, said it best, “With great power comes great responsibility.” You are the data pro. You are the one. When push comes to shove, when the server goes offline, when the database is corrupted, when the query is running too slowly… It’s you. You’re the hero. Time to step up. You have production access and now you have the responsibility that comes with it. Thank the gods we don’t have to deal with the Joker in our little corner of the universe. But if we did, would you want the local cops to track him down, or Batman? I’m putting my money on Batman. He has a unique set of skills that makes him qualified for just such a task, as do you data pro, as do you. You’re Batman. But, if you’re being Batman for your databases and servers 2-3 times a week, you’re doing it wrong.

Largely, I think Julie Zhuo is dead on accurate with her approach. But, I do think there are places where gates are necessary. So, the important point, for me, is not whether or not you have gates and gatekeepers. The important point is, where do you put them. Do be a gatekeeper. But, you must very carefully consider where you place the gates.

Jun 04 2013

Helping Database Administrators

sql-dba-bundleLet’s be honest. Database administration is not all that tough. Set up your backups. Test them. Get consistency checks on line. Some maintenance routines for statistics and maybe for fragmentation are also helpful. Set up security. In most of the important ways, you’re done. Sit back, monitor the whole thing and collect your paycheck.

BWA-HA-HA!

Yeah, well, it’s a nice dream.

The reality is that you’re dealing with constant change that throw this simple set of maintenance monitoring tasks into the garbage. No, you’ve got new databases under development. Sometimes by development teams that are absolute rocket scientists and you sit at their feet learning how they did some really cool piece of code. And sometimes by development teams that more resemble crazed monkeys throwing poo at the walls to see what sticks. There are upgrades to the third party tools your company uses to manage it’s finances/CRM/whatever. OS upgrades, SQL Server upgrades. You have changes to your data which introduces new performance problems. Parameter sniffing. What do you mean I have to put a WHERE clause on the DELETE statement? On, and on, and on. I’m not even scratching the surface for all the things that are constantly shifting under your feet or going wrong. No, this job is hard.

You have the tools you need to do everything. There’s the Transact Structured Query Language (T-SQL) that lets you manipulate data and objects. You’ve got the SQL Server Management Studio (SSMS) a graphical user interface to manipulate server, the databases, the objects inside the database, security, scheduling, some monitoring, performance metrics and others. You’ve got a powerful scripting language, PowerShell, to automate a lot of your work. Everything you need is already in your hands except one, time.

That’s where Red Gate Software can help. We recently modified our Database Administrator Bundle. Some of our toolset was only available through either the entire software set in the Toolbelt or through the Database Developer Bundle. But, these tools are needed by DBAs. Yes, we’re still including vital tools that are absolutely at the foundation of what you do as a DBA; SQL Backup Pro to get your backups in hand, SQL Monitor to keep an eye on your servers and databases, and SQL Multi-Script which lets you run distributed scripts across multiple machines & multiple databases. But now you’ve got what were considered “developer” centric tools. Let’s face it, yes, these are tools used when developing databases, but, as a DBA, you write as much T-SQL as any developer, probably more, so having a copy of SQL Prompt is a must. You’re maintaining production databases, yes, but you’ve also got multiple test, development and other environments. So SQL Compare and SQL Data Compare are a major piece of a well-appointed toolset.

In short, Red Gate reoriented the DBA Bundle in ways that are going to provide a positive impact in helping you do the hard work of database administration.

Aug 28 2012

These People Are Exceptional

I should know. I was one of the judges of this year’s Exceptional DBA of the Year award. I had to read through tons and tons of submissions and help to arrive at a final list of people who truly personify the concept of both the DBA and the exceptional person. They are all worthy of your votes, but you can only pick one. Today is the final day to make your voice heard and get your influence in. Please, let us know who you think the Exceptional DBA of the Year is by clicking on the link, now, and voting.

Mar 21 2011

Communication

It sure seems like there’s a lot of miscommunication between developers and database specialists. In fact, the communication can become so poor that outright hostility between the groups is common. At the end of the day we are all working towards a common goal, to add value to whatever organization we are working for. It’s a shame that we all lose sight of this commonality and create such a false dichotomy between the groups. I think there are some ways that we, as database specialists, can use to attempt to cross that gap.

Prior to being suborned to the dark side, I was a developer. I had a little over 10 years experience working in VB, Java & C#. I remember, distinctly, cursing our database team for being so problematic about how they did things. They slowed me down. They got in the way. When I had problems they were slow to respond, unless the problems were on production. I know I even instigated a few fights with them in an attempt to get them to move the way I wanted (hard to believe, I know). Then came the day when I shifted over to all database work.

Suddenly, I’m responsible for making sure the production system stays online and that the data is readily available to the business. Now I’m slowing down development, because I want a chance to review their design and validate their code to ensure it’ll work well and not affect production. Now I’m acting as a gatekeeper to prevent unauthorized access to the systems or at least keep people from making any of the 10,001 simple errors that could impact production. Now when a developer wants something fixed in dev, I’m the guy telling them they have to wait because something in production is wonky. And yeah, I’ve instigated fights from this side as I tried to get devs to understand that simply delivering code is not enough and that data persistence is there for a reason (again, shocking I’m sure).

Remember, both of these groups are more right than wrong, and both are working towards that common goal, value for the business. But they really don’t get along. What’s more, what they work on and how they work with it is frequently at odds. Ever heard of the object-relational impedance mismatch? How about the concept that you don’t have a database, but a persistence layer? What about managing data integrity within the application (one of my abiding favorites)? Never heard of those terms or concepts? Then you’re probably a database specialist and you’re probably not talking to your developers. If they haven’t already, they’ll soon be introducing an Object Relational Mapping tool to your enterprise. Best of luck.

A lot of these communication issues probably can’t be solved, but I know of one place where most database specialists are not communicating well with their dev teams, and database guys, it’s your fault. Source Control. Do you think of the structures and procedures within your database as code? You should, because, to a large degree, it is. The Data Definition Language (DDL) calls that make up your tables, views and procedures are code. That code needs to be checked into a source control management system. There, the individual objects can be versioned and managed. There you can create labeled releases of your code. There you can branch your code to create alternate development or support streams that contain variations of your database. There you can merge changes from multiple users and branches into a single main source for deployment to production. There you can keep your database directly in sync with application developers code.

Did you catch that last one? You can become more tightly coupled with your development team. Best of all, you can do this using their tools and their language. This is the communication problem I want you, the database professional to solve. Very few of us database types are using source control these days. This, despite the fact that there are fantastic tools and methods under development from different vendors that directly address the issue of getting and keeping database code within a source control system.

Years ago, when I first made the jump to databases, I was appalled that I couldn’t keep my code in source control. Then, as I worked more and more with databases, despite the problems, I abandoned the idea of managing the code in source because, frankly, it was way too hard. But several years ago new tools appeared on the market to make it possible (if still somewhat painful) to get the database into source control. I’ve been working that way for years now. It has completely eliminated one of the many problems I used to have with developers. They now know that my code is stored with theirs. That my versions are their versions. That their labels are my labels. That we branch the code together. It’s taken completely for granted, and we share a common language about change and deployment.

This has not solved every problem or conflict with database teams I’ve worked with. It has eliminated a source of friction. It has increased communication. It’s something that I could do, and you can do, to get a little closer to your development team. Not to mention the fact that you will now have your databases in a known state, all the time, that you’ll be deploying from a single location, that you can manage access to your code, and all the other things that having your databases in source control will bring.

For more details on the concept of putting your database in source control, and working better within teams in general, I’d recommend reading the SQL Server Team-based Development book. It’s a free download.

Addendum (3/27/2011): If you got this link through an email, could you post a comment below as to which distribution list it’s from? Thanks.

Nov 02 2010

TSQL Tuesday: Why Are DBA Skills Necessary

 

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.