Voice of the DBA

Tag: DevOps

  • Wasted Work

    I’ve been re-reading some of the manufacturing and DevOps books that I’ve collected over the years. In these texts, there are a number of concepts that stand out, but one that resonates with me is the concept of waste. There’s actually a good article on LinkedIn about waste that’s worth reading as well. While it’s easy to start spinning in circles and thinking everything is waste around you, step back and keep some perspective that some waste is inevitable because the world is messy. Don’t get too caught up in waste, but try to reduce waste where you can.

    One of the areas that I think contains a lot of waste in software development is the effort to build features that won’t be used. Like many of you, I have been asked for no shortage of changes to software in the past. Often those changes require both application and database changes, usually to support a new way of conducting business.

    The interesting thing to me is that often I’ll work my through a queue and get changes completed, a percentage of which rarely, or even never, get used. What seemed like a good idea when it was requested, specified, and scheduled may not be a good idea weeks or months later.

    In other cases the latest request is labeled as important and needing to be completed ASAP. This might displace older work, perhaps even pushing it to the lowest priority level where it may never get completed. That’s fine, as if the work isn’t important enough to be pushed by someone, perhaps it isn’t needed.

    To me, this is one of the advantages of working in a DevOps style flow, with small changes being developed and released. If clients start to use the feature and need additional development, we can continue to enhance the feature. If the users don’t use it, which we know from either future requests or instrumentation (the latter is preferred), then we can put more effort into the areas that are more important to our organization.

    With less waste.

    We don’t work on large projects to completion, wasting work on the parts of projects that will not be used. Instead, we complete small parts and keep shifting our focus to the areas that are most important to clients.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Rolling Back Migrations

    I happen to be a fan of database migrations as a way of making and deploying database changes. This is an approach that tracks each of the scripts run by developers in their working environments and the replays these scripts in production to deploy the changes. It works really well, and is the most bulletproof method I know of for ensuring the changes will work in production. That’s not to say there aren’t issues, but it’s the approach I favor. It’s a part of what SQL Change Automation from Redgate Software does, and it’s also what Microsoft and other companies see as the future of database deployments.

    This is in contrast to making changes in development and then using some technology such as SQL Compare or Schema Compare in SSDT to create a script and use that to deploy changes. That works very well for many people, but I do find that most customers outgrow the technology with certain changes that don’t lend themselves to this state or model based method of script generation.

    In either case, rollbacks are a concern for many DBAs and developers. After all, the database is a stateful service, as our data must be maintained over time and there are certain changes that are difficult to rollback. While many developers that might have renamed an entity or added a column might be tempted to just reverse the change, this isn’t always easy to automate, especially in most of the tools we use. Often we depend on the skill of a particular individual to manually execute the reversing DDL, and possibly determine what to do with any data that was changed.

    I ran across another developer that things migrations are a better way, and that in a year of development, they never had to roll back any changes. While I agree that migrations is a more reliable process, I do think that assuming you’ll never roll back a deployment is highly optimistic. I’ve had tales of application developers being out of sync, of other applications not working with a new version of the database, and more. Some of these might be fixed with a quick roll forward, but ultimately I think that there needs to be an easy way to make rollbacks normal.

    The one downside of migrations is that the reversing transactions can be complex, since each migration script might make complex changes moving forward. There isn’t much help from most migrations tools, whether that’s SQL Change Automation, FlywayDB, Entity Framework migrations, and more. These tools can certainly generate reversing code for simple changes, but any sort of complex alteration requires custom code.

    My view is that a few simple rules govern how I view rollbacks. For views, procs, functions, I’d grab the previous version from our VCS and re-deploy that. I might try to run through a DevOps pipeline, but if I were in a hurry, I’d grab the code and run it. I’d also then recommit the old version as the latest one. For tables, we should write reversing migration scripts for any entities that are risky. Risky meaning this might affect my employment status. I’d be sure I had a second deployment pipeline that I could use to run these scripts in QA, staging/pre-prod, etc. after we’d verified the code we were deploying. I’d then have checks to ensure we really were reversing the changes.

    The last rule I have is that I use time to make deployments easier. I would never add new columns and drop old ones in the same deployment. If I move or change data, I’d always ensure the old versions of data remained. That way I could reverse changes without problems. I can always do cleanup in a later deployment that just removes objects or data, but I want to be sure that old data is really no longer needed. That means I need to be organized and have a good calendar system to scheduling future cleanup work as well.

    Migrations are really a better way to do database deployment, whether you’re working in a relational system, or you might be altering documents in a NoSQL system. Replay the changes you’ve made in development, that you are sure worked on “your” machine. You’ll be more confident they’ll work on another machine.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Republish: Selling Automation to Ops

    On a plane, back to the UK, so republishing Selling Automation to Ops

  • Modeling and Design Are More Important in DevOps

    I’ve run across a few customers that are adopting DevOps processes for the database. This makes sense as I deal with Redgate Customers, many of whom are doing this. As they adopt a Compliant Database DevOps process, it seems often that there is a push for database developers to react to feature requests quickly, at the same rate that the application developers do, and push out new code regularly to support changes. This new code often includes schema changes to support new features and functions, which often means table changes because the data elements need to be stored somewhere. This, of course, means the data model changes.

    In many cases, trying to release a new feature today or tomorrow, or even next week, means making quick decisions. As an example, if we capture lots of cost and price information in an application and are enhancing this to add currency values, we’ll be adding fields to any table that stores financial data. How we do this could vary. It’s easy to add a currency lookup field to all tables, and that might be what many application developers want to do. If they’re using an ORM, they might just add this as a property to their object definition, which generate a series of ALTER TABLE statements to add the related fields. Certainly, a database developer could just generate those same scripts.

    Whether this is the best choice, or if some more normalized structures are needed, is unclear. That’s dependent on the problem domain, and if date stamps are needed to capture currency differentials at times, or even if we need additional FKs to ensure there is referential integrity, many junior developers and DBAs might not think about the implications to the data model. As time passes, this could mean additional technical debt to deal with, limiting future enhancements. This could also mean fundamental flaws in how financial data is calculated, potentially opening impacting revenue if the data model doesn’t support accurate calculations.

    Moving to DevOps doesn’t necessarily mean moving fast. It can, but it’s really about making focused, small changes at the rate that matters for your business. It also means that your data model and design of data store structures becomes more important than ever. While we can make decisions quickly, this takes experience and understanding of the business impacts, as well as the potential downfalls from different types of structures. DevOps asks us to give feedback about potential problems up and down the software development pipeline, which should include a data architect of data modeler. Someone with experience here can help to consider future implications and even provide some flexible designs that can adapt in situations where we have incomplete knowledge.

    Since we build our software on the data, we need to ensure we are properly capture the data in a way doesn’t create too much technical debt. There are many, many stories of organizations that struggle to grow their applications over time, often because of very poor data models. Many of these issues could have been avoided by consulting with senior developers, DBAs, and data architects/modelers for an hour before making a fundamental change. Even if this means keeping a consultant on retainer. The investment in reviewing and understanding the data model can pay off tremendously in the future, especially as the cost of data processing is often one of the larger costs of running an application. Whether this is an RDBMS like SQL Server or an alternative structure like CosmosDB. A little investment in modeling early can prevent the need to over-provision resources later.

    Steve Jones

    The Voice of the DBA Podcast

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