Voice of the DBA

Tag: software development

  • Avoiding Stored Procedures

    This editorial was originally published on Aug 13, 2012. It is bein re-published as Steve is on vacation.

    I ran across this piece from a developer on why he avoids stored procedures and thought it made some good arguments. The primary thrust of the piece is that ORM tools (Object-Relational Mapping) have evolved to handle most of the requirements of many applications. They also save a ton of development time since so many stored procedures written are simple CRUD type operations.

    In many cases I agree. Having developers write stored procedures is silly and a waste of time. Procedures that select a few fields, or that update a table based on a primary key are mind-numbingly simple to write, but they take time to get in place. That’s time a developer isn’t spending thinking about the application and logic. Plus with any ORMs and tools like LINQ, you can write one line of code and let the ORM handle all of the work of getting or storing the data. Good points, and in many cases that’s correct. If you do mostly CRUD type work, this is a good reason to perhaps avoid stored procedures and let a tool do the work for you.

    Unless you use a different tool. There are plenty of tools, most of them free, that will generate that CRUD code for you. A few templates or snippets will handle the front end side of the call as well, building code to call stored procedures. If you’re actually typing this stuff over and over, you are wasting time.

    My brain started to wander when I saw “A database should be limited to the role of a persistence layer” which is silly sounding when you move beyond CRUD operations. It completely shut off when I saw “your stored procedures would need to be re-written in order to migrate to MySQL, Oracle or another database” since I think this rarely happens. If it does for you, fine, but the vast majority of apps never leave their initial database.

    There are benefits in ORM tools, but you need to understand how the ORM works, what it’s strengths and weaknesses are. Blindly following the basic pattern for your state-lookup-data-editing dialog for all reporting screens is a sure way to cause yourself some problems. Allowing the ORM to define your relational database, without spending some time thinking about the benefits of good database design and proper modeling is asking for performance problems, or even integrity issues.

    ORM tools are just that tools. Used well, they can perform admirably, but just as I don’t use a hammer to drive a screw into wood, don’t depend on your ORM handling everything database related for you in an efficient manner.

    Steve Jones

  • Is Rollback Feasible for Database Changes?

    One of the things I used to show people in SQL Compare was the ability to generate a quick rollback script by switching the source and target. We can generate a deployment script going from Dev to Production, switch the source and target, and then generate the rollback script, from Production to Dev. This was the automation of a manual process I used to go through, examining changes made in deployment scripts and producing the reversing changes for various schema items.

    Really the only object that causes us problems is the table. We can easily grab previous versions of views, functions, stored procedures and other code objects, applying them on top of our deployment without worrying about maintaining state. We can go back and forth with different versions of code. Certainly our applications might have issues, but the database itself works fine and deployment is quick.

    However, that’s not the case for tables. If I add a column to a table in a deployment, how do I remove it? Certainly I can drop the column, but what happens if there’s been a data transformation, or users have entered data? I might need to preserve the data that’s been added, which is a process I might not want to script. What if I split a column or refactor data to a new table? The movement of data back to it’s original spot, while preserving any changed data, is not a trivial task. Add up enough changes in a deployment and it wouldn’t be worth running any rollback scripts. A database restore might be more efficient.

    Recently I was discussing these issues with a developer, who remarked that they never built rollback scripts. It wasn’t a good use of time. If there were issues, developers or DBAs would need to make decisions about how to handle the issues. These days, as we’ve become better at architecting database applications, we might even decide to leave the actual table alone and use view or stored procedure change to effect a virtual rollback for the application, erasing the changed schema with code rather than actually returning the table schemas to their previous versions.

    As I see our organizations depending on databases to a larger and larger degree, requiring no more downtime than actually needed, I think that rollback scripts for tables might be a thing of the past. Certainly rolling back object code is still possible, with an understanding of the impact on both the database and application when old code is used. That’s definitely a decision that needs to be made on a deployment by deployment basis.

    My view is that whenever possible, both the application and database should try to be forward and backward compatible by one version to allow changes to be made to one or the other without breaking the system. That’s not always easy, but in many cases it is with a little forethought, practice, and awareness of the architecture techniques that allow this, it can be relatively easy. In some cases, especially with smaller databases or discrete, limited changes, rollback scripts might make sense. However, I think for most of us this is a software development pattern that’s not worth considering most of the time.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Designing for the Public

    I’m a bit believer that better data, and better software can help our governments around the world operate more efficiently, and better work for our citizens. I appreciate what Tim O’Reilly has said where he notes that we want governments to “specify less and do more.” Whatever your political leanings, I think that most of us would like to have our governments work better, whatever that might mean for us.

    I ran across a short piece on the design principles of the GDS (Government Digital Service) in the UK. The details are inside of a set of tutorials that you get when you subscribe to Safari Books Online, but I did find a short list of their basic principles. The first seven are:

    • Digital by default
    • Putting users first
    • Learning from the journey
    • Building a network of trust
    • Moving barriers aside
    • Creating an environment for technology leaders to flourish
    • Don’t do everything yourself (you can’t)

    There are more, but I think that these are overall good design principles for anyone that builds digital services. These are agile ideas, or DevOps ideas, Six Sigma ideas, the ways in which we can be better by learning and evolving as we move forward. Certainly I think governments struggle with the pace of the digital transformation of the world, and trying to solve problems in one large effort doesn’t work. We need the ability to debug, and modify our systems on a regular basis. Both in public and private sectors.

    The last item might be one you don’t think about in some private industries, but certainly the idea that you can’t provide everything for customers. Perhaps you may to include an API or integration method to let customers build on what you do, and learn from what your customers might build, including those features in your next iteration. Designing for a diverse audience is hard, and certainly requires flexibility, but also the ability to make hard decisions and focus on the things you can do well, rather than trying to meet every goal and every need.

    Hopefully we’ll see more governments doing this in the future.

    Steve Jones

    The Voice of the DBA Podcast

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

  • What’s a Database Pipeline?

    One of the things I ran into a few years ago was the idea of a software pipeline. A set journey across machines and stages that your software takes. In modern, DevOps style development shops, this usually means:

    • A development machine(s)
    • A Version Control System (VCS)
    • A CI/Build server/system
    • An artifact repository
    • Test server(s)/system(s)
    • Production server(s)/system(s)

    Usually software flows along this path as code is written, compiled, and moved from one stage to the next. You may have more or less stages in your environment.

    For databases, this should be similar, with databases in

    • development
    • test/QA
    • staging/pre-production
    • production

    You could also have other stages like integration, beta, user acceptance, etc. I’ve seen people with 7, 8, or 9 databases in their environment, all a part of their entire development and operational areas.

    The database development pipeline is the set of databases that your code flows through. We’ve represented this with visually in DLM Dashboard as a flow, allowing up to 5 environments to be defined. You can see one of my pipelines in this image:

    2016-08-01 22_16_05-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

    Keeping code flowing smoothly along this pipeline is the goal of any development effort. We know this at Redgate, and our mission is to build tools to help you do this efficiently.