Kissing the Job Goodbye (tags #SQLServer #dbadmin)
Originally published at Ramblings from the Flip Side (Site under construction). You can comment here or there.
Today I want to talk about my job, the day job. As a Jack-of-All-Trades DBA, I am responsible for backups, security, production releases, data fixes, architecture, schema approvals, development (both T-SQL and BI), mentoring & training new employees, job maintenance, etc. Everything you can think of as a SQL Server DBA job is the responsibility of our very small DBA team. So I like to take shortcuts. Sometimes that’s a good thing, such as automating tasks to free me up to do other things.
Then there’s the bad.
Shorcuts are the easiest way kissing one’s job goodbye (a.k.a. getting fired). Taking the wrong shortcut at the wrong time can be deadly to one’s career.
A few years back, I was in the middle of a monster project (two projects, actually, that were related to each other) that took 2 years to complete successfully and had 46 scope changes. It had more than 46, actually, but we didn’t allow them to make any more after that. We insisted they treat the other changes as new projects. For 2 years, I fought with a vended solution, creating reports off that solution, pulling the data as an XML file into our database, building tables and procs for the data, creating SSIS packages to massage and manipulate the data and send it off somewhere else (ETL), updating our EDS data flow and tables to include this new data, and fixing problems every time something with the process broke.
It is at the point where I know the tables, procs, and data so well that I was the one tasked with making all future changes. When I have to make a change, I touch no less than 6 stored procedures, 5 SSIS packages, 2 XML configuration files, 1 XSD file, and usually 3-7 tables (depending on the project changes). Every time I fiddle with these feeds, I have to make a list of everything I’m changing so I can pass the word to the release coordinator and make sure nothing gets forgotten in the move.
But it wasn’t always this organized. About a year into the project, I decided I was the Golden Child. I knew the data (vendor side and our side) so well, I didn’t need to think about silly things like formula consequences or JOIN code. I could just write it and it would all work.
Unit testing? Who needs that? The test team would find any errors if they existed (which I was certain they didn’t). Besides, the data was coming from a vended solution that didn’t have a development DB / interface I could use, and I couldn’t run the front end process to generate the report needed to load my data into SSIS without encroaching on the Test / QA environments and having to do a lot of data entry, so unit testing was not feasible for me. I’d use stale data from Production to make sure the code and packages would run and when they completed successfully, I knew things would work.
So when the testers came back to me with error after error, I would go through the stages of grief. Denial. It couldn’t be my code. It was a data issue. I’d seen that before (and sometimes I was even right about it). Blame. It was bad requirements. The business unit had no clue what they were talking about or what they wanted (well, that happened too, but not as frequently as I wanted. Mostly they interpreted certain terminology to mean something different from what I knew it to be).
Acceptance. I’d rewrite the code to fix the error, throw the fix into Test and saunter off to do Something Important with my time while the testers did their thing. But then they’d come back with another error, or sometimes the same error, and the cycle would start all over again. The more they came back to me, the more angry I’d get. Defensive even. This code is my baby, my gem, it’s perfect, DON’T THEY UNDERSTAND? It should work! (Hey, authors, does this sound familiar to you? It’s amazing how often my two jobs dovetail. @=)
I didn’t understand the process, see. I didn’t get why unit testing was so important. Truth be told, I wasn’t even trying to unit test beyond “does it run?” And it reflected in my performance review that year and in the way I interacted with my coworkers. My temper got the best of me at times and I’d tense up the moment I saw an email or phone call from one of the testers.
Then I got a clue. I needed to unit test. Since I couldn’t generate the report from a dev database, and since I didn’t want to take the time to mock setups and stuff in an interface (even if I could have had access to a dev DB), I’d create my own fake file. I grabbed an old production source report, copied about 10-20 lines from it (including header and footer), wrote up my scenarios based on the requirements, then altered columns and added new ones with the correct dummy data. Then I’d run it against my dev database to see if what came out the end was the expected data. It took me a few tries to be able to recreate the files this way. Once I figured out the formula, suddenly I was finding errors well before my code was inflicted upon the hapless (and very patient) test team. In my last release, the major of errors were data related with only one being my issue (and even that was expected given the vagueness of the requirements).
Bugs get into Production all the time, no matter where we work. This is a fact of life. But when they can be avoided, they should be. Unit testing is worth the effort because all the additional time put into it saves three or four times the effort fixing the bugs during the time-crunched QA process. Or, heaven forfend, fixing the problem after it has offended customers and lost business.
If a query is passed to a release with obvious issues (like an ORDER BY clause in a subquery), I can tell right away the coder didn’t even bother performing an initial execution pass. As I almost learned the hard way, careless coding is one significant method of kissing the job goodbye.
So what is your unit testing experience, nightmare, or concern?