Voice of the DBA

Tag: deployment

  • Are Indexes Actually Changes to the System?

    I haven’t thought about this in some time, but Brent Ozar raises an interesting question: should index changes require change control? I’ve worked in an organization that didn’t consider an index change to be an item that was presented as an official change. They were seen somewhat like adding new users or changing a firewall rule. These were ticket requests that could be triaged and made by an individual group without notifying the rest of IT.

    I’ve also worked in organisations where an index change was viewed as a deployment and subject to change control procedures. Not that the network or security people cared about the details of the index, though the help desk might. These just wanted to be aware as any deployment change could affect their departments.

    In today’s world where many companies seek to adapt faster to changing customer demands or market opportunities, I think the idea of meeting every week to discuss any upgrades or changes to applications is somewhat silly. Certainly large changes, like major application upgrades to ERP systems, new security hardware, and alteration of core infrastructure ought to be debated in a larger group. Deploying changes to an application isn’t one I’d think deserves the debate. At least not if your organization is trying to become more efficient.

    I do think a way to avoid issues is to work more closely with the development group if you are in operations or vice versa. I’d be sure to let other app teams know about index changes? Certainly Brent lists a number of potential problems, many of which could occur. A new feature might break and prevent inserts, exports could use more disk space, imports might eat up data and backup space, queries could cause slowdowns or blocking. These are all possible, maybe even probable.

    I agree with Brent in that any changes need to be tested. I don’t know about any sort of formal change control, but there ought to be a process that evaluates the changes and mimics them in an environment that allows you to determine the production impact. Using a known, automated process with testing and well documented deployment actions will reduce the risk, but perhaps not eliminate it. However, when you find issues, if you have an automated process, it’s easy to add tests or alter the process to ensure that it will succeed in the future. Especially if you examine the effects of these changes and learn to avoid problematic development or deployment patterns in the future.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Dark Reads During a Migration

    I caught a post from the Slack Engineering team titled: Re-architecting Slack’s Workspace Preferences: How to Move to an EAV Model to Support Scalability. In the post, an engineering intern describes the move from a blob table (actually a table storing JSON data) to an EAV table. EAV tables generally don’t perform well, which isn’t the same as scalability, but in the real world these two items are interrelated. I likely would have chosen a hybrid approach, using a wider table for known items, but keeping an EAV table for potential one-offs.

    In any case, I don’t want to discuss EAV solutions. Instead I want to discuss schema migrations. That’s part of the focus I have at Redgate with our Compliant Database DevOps solutions, as deploying schema changes is a challenge for many customers. It’s why DBAs usually have jobs, but the traditional built-the-script-from-developer-descriptions-and-lists-of-changes doesn’t always work smoothly. This creates stress for the individuals and risk for the organizations.

    One of the items in Slack’s post is about a dark mode of deployment. That’s similar to what I’d call dark launching, but the idea is the same. In this case, there’s a good description of how this is helpful. In this case, there is the migration of data from one table to another. The new table was populated as part of the deployment, but rather than just using the new or old table, the application was altered to pull data from both sources and compare them. This helps to ensure the data was moved correctly. I assume issues resulted in the old data being used.

    There were a couple interesting things with this approach. First, instrumentation was used to measure the time spent pulling data from the new table, as a way of measuring performance. This also allowed the system to discover read/write bugs in the new process. If your system has any headroom and a decent workload, this is a great way of trying to ensure that a data migration worked.

    If you have a new feature, you can also use this technique. Make the database changes and add application code for your feature, but don’t expose that to the user. Instead, add code that uses the feature, sending random data to the database and reading it back. In this way, you can test that your methods work and measure the load on the database. Of course, if you do this, make sure you can silently turn off the random generation if the database is negatively affected.

    I’m a big fan of dark launching and measuring the impact of changes. In so many cases our deployments might be delayed for any number of reasons, so the pressure to release the feature today rather than tomorrow or next week is silly. I’d argue the ability to measure impact, even for a day, will help ensure better code quality for the user.

    That’s if you are given time to fix any issues you find..

    Steve Jones

    The Voice of the DBA Podcast

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

  • Republish: Release Wednesday

    In the UK getting ready for SQL in the City, so Release Wednesday being republished.

  • 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.