Voice of the DBA

Tag: software development

  • Embrace Stored Procedures

    I’ve read a lot of pieces on stored procedures. Most of the articles were for using stored procedures, quite a few were against their use, and a handful with extreme opinions. This piece from Rob Conery, while perhaps NSFW, is one of the latest, and more entertaining, pieces I’ve read that discusses the value stored procedures bring to an application.

    For the most part I agree with the presentation, though not completely. It’s an interesting discussion about business logic v data logic, and while I do think that some logic isn’t really central to your business, there are ways you manipulate data that might be necessary in multiple parts of your business. Those manipulations might make more sense in the database as a stored procedure rather than trying to ensure every application implements (and updates) the logic in its own code.

    I do like Mr. Conery pointing out the fact that ORMs or custom applications might be extremely wasteful in implementing a process with multiple calls, and stored procedures handle these functions more efficiently. We have fast networks and powerful hardware, but those calls do add up, especially if your system gets popular and has lots of users.

    Ultimately I think the last sentence sums things up nicely: let’s wake up to the power of our relational systems. That’s something that developers should embrace. Why only use half the power of a tool you have? I do think the judgment of how much business logic to include in the database is worth debating, but not at an application by application basis. Think about whether each call, page, form, whatever makes sense as having the programming in the front end or the back end. Whatever you decide, you should be able to explain and justify your choice to the others you work with.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • State v Migrations

    Most people work in evolutionary databases. By that I mean a database where you have some schema, and over time you are altering that schema. You might be adding columns to tables or views, changing stored procedure or function code, or something else, but you aren’t really rebuilding the database from scratch on a regular basis. Certainly some people sell software and a database that’s always being rebuild and upgraded from state X to state Y, but that’s a much more complex issue.

    For those of us that work with these evolving databases, we really have two choices in how to work on upgrades. We can store each change to the database as a script and ensure we run the correct scripts in the correct order (discarding those that aren’t needed) when we deploy changes. I’ve done that before, and it can work, but this approach requires I’ve have good control of production to prevent changes from being made in that environment that aren’t also made in development. This is the approach advocated by Paul Stovell, of Octopus Deploy.

    The other approach is to look at the state of development at some point in time, compare that to production (with a tool like SQL Compare) and then generate a script that makes the changes needed. This is how lots of people deploy their changes today, though this approach isn’t without its own issues. NOT NULL columns, renames, and more can cause problems with this approach. There are ways around these issues, but they require some work.

    Ultimately the problem of smoothly deploying changes to databases requires a bit of discipline from the DBAs and developers. Tools can help, and they certainly can reduce the work involved, but good habits and a consistent process are important to ensure that changes are made smoothly. The one thing that helps you find problems with your process and code is testing, which is something I’d recommend you implement no matter what method you choose for deploying your changes.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.4MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Release Wednesday

    I’m glad that someone else is adopting Release Wednesdays. I was surprised to see the post from Red Gate, though I shouldn’t have been. I knew we were releasing regularly and some groups were aiming for weekly releases. There was easily a 1 in 7 chance of them choosing Wednesdays, which has a special meaning for me.

    I worked at a startup company where we released every Wednesday. We worked in one week increments and developed an application across a couple years that evolved and morphed to meet differing business needs in the education market. We went from working with digital sales, to including physical products, to subscriptions. Our customers went from individuals to corporations, to departments in corporations, and all along the way we managed front end and database changes in one (or sometimes two) week increments.

    We didn’t practice Agile or Scrum or anything formal. We worked as closely as 10 people could, coordinating and communicating to get features implemented every week. We started releasing on Wednesday nights with the lead developer and myself staying late at the office Wednesday nights (usually with another developer or two for company) for an hour. That was reduced to phone calls where we deployed from home (and fixed problems) in real time. Eventually we built such a well engineered process, with thorough testing, that our deployments were completed in minutes with a few chat messages to verify we’d both finished our tasks.

    We did that for over 18 months with only one or two rollbacks, and without the all night frantic coding sessions to fix issues that I’d experienced elsewhere. It wasn’t simple, but it wasn’t hard. It required preparation, planning, a strong culture of professionalism, and support from our CTO.

    I see the same types of culture at Red Gate, and I’m not surprised by the rapid releases, nor the success we’re having in evolving, updating, and patching our products over time. I’m excited to see how much quicker we can move and how we will continue to build better software, faster. 

    One Wednesday at a time.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.7MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Kanban

    With more and more software developers trying to find ways to better build software, we have seen many new methodologies and processes being used for managing workloads, tracking development progress and more. Perhaps one of the most interesting adoptions for companies looking to speed up their development has been the use of kanban boards for tracking work in Agile or Scrum systems. Kanban came about as part of the manufacturing process at Toyota and proved very valuable for them.

    We use kanban boards all over Red Gate, for everything from software development to marketing campaigns, to books we’re publishing. It’s kind of amazing. However I hadn’t thought about using one personally until I saw this piece on LifeHacker (a related piece on visualization as well). It’s hard to know how well this would fit for me to manage my workload at SQLServerCentral. While we use a kanban board for a few tasks, like Stairway Series, I think this would be a bit complex for the variety of tasks I deal with on a regular basis. I suspect I’d need a number of kanban boards, a separate one for each type of work, and then I’d be trying to somehow manage my workload between all of them.

    However I do think that for software development, and for a limited set of tasks that you want to track and organize, kanban is a good way to easily visualize the workload. I also think this could be a good way to manage your career learning. Set a list of things to learn, break them down into tasks (watching videos, reading an article, writing a CTE) and then track your progress across the year.

    Having a visual reminder can be motivating, as long as you can make progress and see work moving through progressions. If not, it can be another source of pressure or stress that you avoid. However it’s an interesting technique that might work well for teams that are focused on a single set of work.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and LibSyn.