Voice of the DBA

Tag: T-SQL Tuesday

  • T-SQL Tuesday #93–Interviewing Patterns

    tsqltuesdayIt’s time for T-SQL Tuesday, and this is a good topic. Not too close to T-SQL, but certainly something many people are interested in.

    Kendra Little hosts this month, and her topic is interviewing patterns and anti-patterns. Things that are good, or things that might appear good, but are bad. Be a candidate or an interviewer.

    You can read more about T-SQL Tuesday at tsqltuesday.com.

    Pattern: Groups

    One of the things that I think makes a good pattern for interviews is a group. It’s rare that any of us actually works alone or with just one person. Even if our group is 1 or 2 people, we often interact with others in different ways.

    Those other people depend on our knowledge and skill. Why not use them in an interview.

    I’ve had quite a few interviews like this, but they all are similar. Get the candidate in a conference room and bring in multiple people to ask questions. Usually it’s a round robin with everyone taking turns asking questions. Ideally, the same questions to all candidates from the same people. The discussions can veer into slightly different directions, but ideally in similar veins for everyone being considered.

    I’ve been in the candidate seat with as few as two (one isn’t a group) and as many as 12 or 13 others. This can be stressful, but if everyone is probing, then the candidate needs to interact and communicate with those that they’ll actually work with. In my mind, a good test.

    Anti-pattern: Confrontation

    I love the old Monty Python Silly Interview skit. I’ve been tempted to do something like this, but never have. It’s not fair to the candidate, and I’m not sure it tests anything well.

    I had a couple rough interviews in the past. One was where a VP of a small company was excited and interested after having talked with me over the phone and then in his office for 30-45 minutes. He then brought me to his consulting sysadmin/programmer, who peppered me with scenarios and questions. However, this gentlemen argued about every answer, and implied that I was answering incorrectly. In many cases I knew I wasn’t and said so, however the confrontation continued. In some cases, I just admitted I wasn’t sure.

    Later, I came to be friends with the guy, but never too close. He tended to rub me wrong most of the time because he was closed, cold, and argumentative. It was a good job, and we got along, but if he had been my boss (I was interviewing to be his), I wouldn’t have accepted.

    The other one was similar. I had a good phone interview with a technical person and then went in to meet the manager. He was brusk and short with me, constantly letting me know he was in charge, he didn’t think much of my credentials, and I should be expecting to work hard. With that offer was a pay cut I couldn’t accept, but not sure I would have in any case. His manner made me think that every day would have include some sort of insult, browbeating, or other unnecessary action.

    I think we can make an attempt to evaluate candidates without being jerks.

    Pattern: Flexibility

    The last pattern I like is the idea of quizzing people in a flexible manner. I’ve never liked schoolteachers or managers implying that most of our work is extremely set and susceptible to deadlines without flexibility. Or that we work a certain way. The real world isn’t like that.

    BTW, if you have hard deadlines, like shows or events, your stuff better be done well in advance, not hours before.

    When I’ve had good interviews, or gotten a good handle on a candidate’s skills, it’s from adapting to them. Some people like to show something on a computer. Some will whiteboard, some like to discuss the question in detail and ask their own questions. I’m OK in all those cases. When I work with the person, I get a better feel for how they think, how they solve problems, and what catches their eye.

    I can also evaluate their strengths and weaknesses, and decide where they might fit in our team.

    Evaluating someone is hard enough without being rigid about what we do. Expecting everyone to take a test well, or be able to answer any question orally, or any other specific way of interaction is asking to miss out on some potential good candidates, as well as potentially hiring someone that is good at interviewing, but not working.

  • T-SQL Tuesday #92–Hard Lessons

    A quick post, written last week as I’m relaxing in the mountains.

    This month the topic is lessons learned the hard way, from Raul Gonzalez. It’s interesting, and certainly one where I have experience. I make mistakes regularly, though I tend to fix them.

    You can read more about T-SQL Tuesday and learn how to participate, or even respond to old prompts at tsqltuesday.com.

    A Hard Lesson in Preparation

    I worked for a financial services company at one point, managing the live systems as well as performing database development. I had a few people working for me, some in each area, and we had contracts with a number of clients. Quite a few of these contracts specified a yearly DR test at a remote facility.

    The first year I was there, we packed up tapes and documentation, carpooled for 4 or 5 of us as if this were a disaster, and started rebuilding systems. Our contractor supplied xx servers as per our contract, and we had to start from scratch.

    We didn’t do well, and couldn’t get our web app or client server apps running completely. The db restored, but the system and docs were out of date. I knew this, and we updated some things, but we didn’t take the process seriously.

    A few months later our largest client was unhappy. Since we couldn’t get the website running, they wanted another test. At their site.

    Myself and another were chosen to fly to their site, shipping out spare servers and software. We had to wipe the drives and rebuild the system from scratch. Since the two of us had been at the DR test, we assumed we’d learned enough to do this in two days, sure we could rectify all the mistakes from the test team.

    We couldn’t. After two days, we didn’t have everything running, with too many dependencies and issues from various web components. Our developers had been running wild, with admin access to the webserver and database server, resulting in wonderful unique, snowflake servers that were hard to reproduce.

    Inadequate preparation, as well as poor security for years, contributed to the failure. I did get the database working, but that didn’t matter since this client used web access only. I had to take a large portion of the blame since I managed systems, and our company paid a penalty.

    It was embarrassing for me, personally and professionally. I hadn’t done a good enough job being ready for a disaster. This did server to motivate me to start cleaning up the systems and ensuring we could rebuild a new web server if needed, but it was a hard, and expensive lesson, to learn.

  • T-SQL Tuesday #91–Early DevOps

    tsqltuesdayThis month the T-SQL Tuesday invitation is from Grant Fritchey, my colleague at Redgate Software. Surprise, surprise, the topic is DevOps.

    I write and talk about this a lot, but for this month I’ll look back at DevOps for me in 2001.

    If you want to participate in the blog party, you need to publish on the second Tuesday of the month, GMT time. You can see the invitations at tsqltuesday.com.

    2001, A DBA’s Journey

    I started working for a small startup in 2001. I was the DBA (dev and production) with 4-5 developers. Eventually we grew to 10 developers, a couple QA people, and a UX person. Our goal was to release code every Wednesday night, and developers would work from Tues-Mon am on that week’s changes.

    The initial plan when I started was to get together with the lead dev and produce the scripts to upgrade our web application as well as the database. Early on we had a small system, so it was a few hours on Monday afternoon putting together a script for the QA server. Inevitably we’d make changes if QA found issues, and then Wednesday night we’d go into the office and usually spend a few hours deploying and fixing things. Often we had another developer or two with us to ensure that we could get the changes out.

    Over a few months we started to try and smooth our process. We talked about the issues, and started small. First with version control, Visual SourceSafe at that time. We’d get everyone to check in changes, which let us more easily determine what had changed that week.

    We also talked about what made life easier for the lead dev and me. How could we package code to easily ensure it gets deployed. For the database, this meant ensuring all files were named easily and branched to a folder.

    Ultimately, the technical challenges were few. A few scripts that automatically packaged changes for us and deployed them. However, the biggest challenge was cultural. Getting all the developers to think forward about what was needed to get code to production and slightly alter their behavior. We also had to break habits of “fixing” code in QA. We had to learn to reset QA, change our scripts in version control, and then redeploy.

    Sharing information, communicating, and adhering to a consistent process for deployments, ensuring that we practiced our production deployment on QA, helped us move to a release process that consisted of a phone call and a few minutes instead of a late night at the office.

    DevOps works, but you have to work at building a process for your environment.

  • Shipping Database Changes–T-SQL Tuesday #90

    tsqltuesday

    This is a good T-SQL Tuesday topic from James Anderson: shipping database changes. It’s especially poignant for me since I talk and present often on this topic. Much of my work at Redgate involves helping people implement DevOps for Databases, deploying changes smoothly and efficiently to databases.

    I’ve got lots of stuff here, but let me go back to a previous position, many years ago before TFS, when Visual SourceSafe (VSS) was in heavy use in development. We implemented DevOps before DevOps was a thing, and included our database changes.

    Smoothing Deployments

    I worked at a startup company and we were trying to respond to requests from management and sales. As we grew from 4 developers to 10, plus a DBA (me) and a QA person, we needed to smooth out our process. Over a few months we got into a particular schedule:

    • Monday noon – Package up all completed changes for deployment to QA.
    • Monday afternoon – Discuss and plan changes for the following week
    • Tuesday-Wednesday – Bug fixes as needed
    • Late Monday to the following Monday – write code for the next deployment
    • Wednesday evening after 8 – deploy changes to production

    I had a foot in both the development and operations world and had to reconcile the need for production to work and remain stable while also ensuring new changes could be deployed every week. We were a young, new company, and we often had database changes included each week in our package.

    When I started, we would talk to developers on Monday, start to gather all the changes from VSS for our web application, script out all database code, and then deploy to QA. Inevitably, some code would be forgotten or wrong, and we’d track it down sometime between Monday noon and Wednesday noon. Then we’d (hopefully) have a good folder of changes that we could deploy to production on Monday night, manually running .sql files or copying web code to remote servers.

    The Old Process

    When I started deploying code with this group, I’d get some dinner Wednesday afternoon and then return to the office with 2-3 other developers to deploy code. I usually had notes from various issues that QA had discovered in their testing, sometimes altering scripts in real time to ensure they would deploy and work correctly on the production database.

    Myself and the web developers would work to get code deployed in an hour or so each week, and things usually went well. We’d have hiccups and issues, but we worked through them and talent helped. It was a good team and we could usually solve our issues.

    Getting Better

    The lead developer and I both had little children at the time. Spending 12+ hours at work on Wednesday wasn’t an ideal situation for us, and we decided to get better.

    The first thing we did was ensure that all code was tracked in VSS. We had most web code here, but there were always a few files that weren’t captured, so we cleaned that up. I also added database code to VSS with the well known, time tested and proven File | Save, File | Open method of capturing SQL code. This took a few months, and some deployment issues, to get everyone in the habit of modifying code in this manner. I refused to deploy code that wasn’t in VSS, and since our CTO was a former developer, I had support.

    The other change was the lead developer and I started building a release branch of code each week. We’d move over the changes that were going to be released to this branch, which simplified our process. We could now see exactly which code was being deployed. This was before git and more modern branching strategies, but we were able to easily copy code from the mainline of development to the release branch as we made changes for this week.

    Since some changes might be in development for a few weeks, we couldn’t just grab the latest version of every file. We needed to know which pages, and which database changes would be released and which were still in development.

    Automation

    Once we had the code tracked, we began to automate deployments. Both the web developer and I built separate scripting tools because we had different needs. Web code is easier, mostly just copying files to the correct locations. We had a few token replacement issues, but some creative scripting solved those.

    For the database, I had a single application and a single database, which is a simple problem to solve. Deploying database changes were always taking the latest version of object code, which might be a CREATE, or it might be an ALTER. In my case, I separated code into folders (tables, views, etc.) and then added scripting to ensure that I deployed objects in order. For the most part, we could work in gross orders (Tables first, then views, then procs, etc.). We had a few items that were out of order, but hard coded deployment checks ensured these issues were handled.

    My additional challenge was managing QA, which was my test environment for deployments as well as the application. I added processes that would automatically refresh the QA environment from production. This was a button click to start the process. Once this was done, I’d use my deployment process to deploy the database changes for the week from VSS.

    If we found issues in QA, and code changes were needed, we went back to development, made the changes there, committed to VSS, and then repeated the process. With automation and a small database, we could rebuild the QA environment with a new package in about 15 minutes.

    Going Live

    The first couple times we deployed to production, the lead developer and I still went into the office and made sure the deployments ran. After two weeks of watching our process run in about five minutes, we realized this was silly.

    For over a year, we would continue to deploy changes every Wednesday. The lead developer and I would get on the phone every Wednesday at 8pm. We’d VPN to the office and I’d deploy database changes (5 minutes or so) and then he’d run his application scripts (2-3 minutes). We’d check a few things and then sign off.

    Life got much better, we built confidence in our ability to deploy, which meant the business trusted us to get changes out to customers every Wednesday. Perhaps most importantly, we started working more normal hours rather than crazy startup schedules. We could even make quick patch deployments on a Thur or Fri if needed, and because we could smoothly roll out changes, we were under less pressure to pile as much as possible into the next deployment. If something didn’t get done in time, it would just roll to the next week.

    Modern Tools

    I work for Redgate Software now, and we’ve spent a lot of resources (and brainpower) to try and help you deploy code in an easier way. Deploying database changes is hard, because we need to maintain the state of our system.

    These days, I might still use a home-grown, scripted approach if I had one application and one database. However, I’d really consider using other tools, because they mean my developers don’t spent time doing simple scripting, they spend time solving application problems.