Voice of the DBA

Tag: DevOps

  • Finding Production Drift

    A few years ago when I started delivering presentations about automating database delivery with Grant Fritchey, I was surprised by something. We were rehearsing a talk and Grant mentioned that there were really only two places where we knew the state of our code: in a VCS and in production. I hadn’t really thought about that before, but realized it was true.

    While the development environments are always in flux, by using a VCS, you can determine the state of your code at any point in time. However since changes can occur in production (DBAs adding indexes, changing security, etc), you can’t count on a release branch of some other designation as being a guarantee of the state of your live environment. Production is always its own version of the truth.

    Ideally all of the changes being made on your production system are tested in development, QA, and pre-production environments before being deployed, but very few companies have a mature enough deployment pipeline to allow them to push hot fixes and patches rapidly enough, especially when they need to be made in an emergency.

    More than likely some changes will always be made in production directly, especially small ones that affect data. You can’t prevent this, but you should be able to deal with the aftermath and ensure that your system doesn’t destabilize from the enhancements. Without a doubt DBAs should log any scripts they run, but it’s easy to forget things, especially when changes are made in firefighting mode or at 3am.

    That’s why having some sort of monitoring system to watch production for any changes becomes an important logging mechanism for DBAs. Whether you build one yourself, or buy some software, you use the native tools or bolt something onto SQL Server, make sure that you put something in place. Work to push your ad hoc changes back to development, and ensure they get into a VCS at some point.

    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.

  • Building Small Habits

    I was reading Kenneth Fisher’s piece on constraints recently and was struck by the fact that this is one of those small changes you make to your coding that has big benefits. In essence, rather than being lazy and writing this:

    CREATE TABLE mytable ( myID INT PRIMARY KEY, MyCol ....

    You decide to explicitly add a few more characters. You note this is a constraint and you type this:

    CREATE TABLE mytable ( myID INT CONSTRAINT PK_MyTable_MyID PRIMARY KEY, myCol

    It’s a small change, but it takes you from an object named PK__mytable__783FBF31F8293E74 to one named PK_MyTable_MyID, which is way easier to understand and debug when you have issues. More importantly, this is a DevOps move, where a developer can make it easier for operations people to understand the constructs and organization of a system that has moved to a production environment.

    There are all sorts of little coding changes you can make that improve the way you write code, and perhaps make it perform better. Aaron Bertrand has a whole series of bad coding habits, and at SQLServerCentral, we’ve published a number of pieces on different ways to code better, such as removing cursors from your code.

    By making some small improvements in your coding habits, changing to better techniques over time, you’ll slowly improve your entire codebase and help yourself and other developers code more efficiently in the future. I’d even recommend as a team that you choose a single change to make, and begin implementing it across the next month or two. Then repeat the cycle again. In a year, you’ll be amazed where you are.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Selling Automation to Ops

    The DevOps movement isn’t new in some companies. It’s the same coordination and teamwork that has existed for a long time between the development and operations staffs. Developers take advantage of the skills in Operations to get standardized environments for their work, and let the Ops people manage (and track) changes. Operations people talk to developers about the challenges and issues faced in production, and the let the developers build applications that can easily be deployed. The sharing of information ensures each group knows what the other faces, and the regular contact builds bonds and respect between employees. Neither wants to let the other down or make someone else’s job any harder than it needs to be.

    However that’s not the case in many companies where developers view Operational staff as complainers that slow the process down. Operations staff see developers as wild and irresponsible, tossing code into production that they don’t need to support and haven’t tested. Both of these views are correct in that each side sees a reality in the process that makes their job more difficult.

    Ultimately I believe it’s up to developers to change things. Those of us that build the software need to respect the problems that instability causes and learn to help ensure that our changes can be deployed smoothly. The development side of an organization has more skill in tracking changes in version control, in managing the movement of those changes among environments, and in programming systems. We should be working to help push that knowledge through to Operations personnel that become responsible for our applications.

    That means we need to build scripts and tools to migrate our changes and give them to Operations. I’d recommend that we learn how to automate the configuration of our development systems, as well as script our changes. Most modern platforms allow us to programmatically make changes, so let’s do that. Then let’s take a few hours and show Operations people how to use these scripts, and let them setup and change our development environments. It will be slow at first, but they’ll learn to make changes faster, but also bring stability to every environment from development to QA to production, and can ensure we have the same configurations everywhere. We’ll have one less thing to manage, and our changes will get deployed faster, but also more consistently.

    Ultimately we all want the same thing. Better software delivered to customers faster. We want to Ship Safe, and Ship Often.

    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.

  • Recovering from Bad Deployments

    Someone asked me recently if I stored backups of my database in version control. This person wanted to recover from a bad deployment and use a backup to do so. They felt that keeping a copy of the backup in a VCS, alongside the code being deployed, would be important. It might be, but I said that recovering from a bad deployment isn’t something I want to do with a restore if I can avoid it. Then I was asked how to recover from a bad deployment if you have a busy, 24×7 environment.

    I, of course, answered, “it depends.”

    It does depend on the deployment, but it also depends on your preparation. There are ways in which you can work to minimize the problems that might occur during a deployment. Obviously testing your scripts and deployment process is important, but it’s also good for you to understand how your scripts work and what techniques you can use to rollback problematic deployments.

    There’s a switch in SQL Compare that lets you build a deployment script, and then immediately generate a rollback script. It’s handy, but it’s also not going to always work. If you’ve added a column during deployment, you might not want to just remove it on rollback. 

    However you can prepare for issues, like having a script that might save data in the new column before you remove it. You might choose to copy the table as part of a pre-deployment process (or during deployment), having this copy of the table used in a rollback scenario. You might even bring up a warm copy of your database and prepare to swap entire databases if problems arise. This would allow you time to save and move data that was changed after your deployment, but before your rollback.

    There are lots of possibilities in how you might recover from a failed deployment, but as with many of the solutions that we build in technology, a well thought out plan makes everything run smoother.

    Steve Jones

    The Voice of the DBA Podcast

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