Voice of the DBA

Category: Editorial

  • The Upgrade Storm

    The Perfect Storm is a movie about the confluence of three weather events that combined to create a situation the resulted in the loss of all life on a fishing boat. It’s a good movie, and certainly one that might make anyone think twice about sailing off into the North Atlantic in bad weather. I certainly wouldn’t want to be traveling on the water when any type of hurricane might present.

    Glen Berry writes about a perfect storm of a different sort. The perfect storm to upgrade, with the confluence of three events he sees taking place soon. We have quite a few performance related changes coming in Windows 2019 Server, in CPU hardware, and storage technology. Those three changes could dramatically improve the performance you might see from your SQL Server licenses.

    If you were planning on updating a legacy system with a new version of SQL Server, you might do well to consider some of these hardware and software improvements. I don’t know what the costs might be, but that’s certainly something you will have to consider for your environment. The argument likely would be that if you’re updating the database server, why skimp? This is a limited resource and one that can affect your entire system. Spending some money here might make a significant difference to the way in which your system performs.

    This might be a perfect storm of a different sort for your budget and your accounting department as upgrades for SQL Server are likely to be pricey. The improvements could dramatically improve workloads by lowering the time users spend waiting or increasing the throughput of queries. Those might negate the cost of any hardware and software costs involved since time is a valuable resource and saving time is worth something.

    The problem for many people is that if you aren’t writing good code, then you might not get the benefits you want from the upgrade. Certainly the performance improvements might be lost under poor query performance. Upgrade to take advantage of improvements if they are something you need, but please, learn to write better code, and ensure your developers are regularly looking for better ways to use your expensive SQL Server licenses by writing more efficient code.

    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.

  • Rollbacks Are Normal

    Do any of you think that a rollback of a version for your application is easy? Most people struggle, and when I talk DevOps and improving your process, the number one question is about rollbacks. In fact, recently I had a few people that struggled to even listen to the early parts of DevOps because all they could think about were previous failed releases and the need to roll back. They kept saying, what about rolling back. Hopefully you can stick with me a bit longer.

    I’d argue that rollbacks are the process that needs DevOps more than ever, with smaller, more regular releases and practice at making changes. Whether forward or backward, we ought to be able to rev our software easily. I ran across a piece from the cloud platform blog at Google called Reliable releases and rollback – CRE life lessons. The title is an interesting one, but suspend some of your database skepticism until the end.

    It’s easy to consider rolling back in the early parts of the article and say “it’s way easier to roll back your application”, and it is. Applications just stomp down new (or old) versions on top of what’s there, which is often an easy thing to do. As they say at Google, “rollbacks are normal”, which certainly seems to fit with the application paradigm.

    In fact, they recommend rolling back a good release. After all, it’s much easier to practice this sort of thing when you have a working new version of software. When the release breaks your system, as mentioned in the piece, everyone’s stress level rises and the fixes often aren’t well built. Even when they work, which isn’t anywhere near all the time, there are often problems later. The idea should be to roll back and ensure everyone knows how to undo a version change. They can document the reasons for rollback and get the previous state of the application running. I hadn’t thought about this, but it makes sense. Practice in advance and be prepared. You can always re-deploy the working version.

    What about databases? They have a solution, and I like it. They want the app developers to build two versions of the application. One pre-schema change, and post. That way you deploy the first version, then the schema change(s). Then you deploy the second version. If there’s an issue, you rollback to the first version and undo the database changes. This sounds hard, but once you get into the swing of building code that survives additive changes to the database, this is easy.

    This doesn’t solve any destructive changes to the database, like dropping objects or manipulating data. I would suggest that drops are a completely separate release, and have a full backup (or snapshot) taken and saved for awhile. For data manipulation, save off the previous state of data, just in case you need to reload things.

    Becoming better at not only delivering changes to the customer, but also removing them when issues are detected is a valuable skill, and since we’re likely to have a bad release at some point, this might be a way to even further reduce the risk of deploying database changes.

    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.

  • Monitoring Costs

    I’m a believer in instrumentation and metrics being regularly gathered from your systems. Across the years I’ve built and bought monitoring software to use in various jobs, sometimes in layers where one piece of software uses data from another to provide insight about the way in which applications are performing. By gathering and analyzing this data, I can determine if more or less resources ought to be allocated to meet the demand.

    No matter how you choose to gather this information, there are costs associated with the effort. Early in the lifecycle of an application, many developers and DBAs might just check to determine if the a system is up. As time passes and dependency increases on the system, there are additional needs to understand more about the system, such as storage, processor usage, and more. SQL Server has certainly matured in this respect as well, with more and more DMVs being added over time to allow more instrumentation of the platform.

    One of the interesting things that happens when you monitor your systems is that there are various costs to your organization, some of them hidden.  Certainly purchasing or building software has a cost, but what about the other costs involved? As I work for vendor that sells this software, I’m always amazed how customers view costs. Most understand there are hardware costs, though surprisingly, a few don’t expect that.

    There are other costs, some of which are outlined in the article linked above. There are plenty of time costs, especially associated with learning and configuring the software. In fact, this can be a significant cost. Most customers fail to account for time, and regularly become frustrated as the instrumentation never seems to meet their needs. Either it hasn’t been configured to meet your needs, or you haven’t learned to use the system well. There are also some people that find the opposite, with monitoring visualizations becoming a time sink, where administrators get lost in the analysis of data and the variety of ways to examine the information available.

    It’s not just administrators, as not shortage of executives have wanted real time visualizations of metrics on systems, sometimes just standing there watching the metrics rise and fall to gain confidence in how well their systems are functioning.

    There is another cost that many people often forget about as well. Any system that monitors another will impose a load to gather that data. That could be significant, especially if you haven’t tuned your software. There will always be some load, and I think it’s necessary to have information, but you ought to be sure the load isn’t too significant.

    It’s important we monitor our systems. I would never build that system again, but rather buy some piece of software because the process is cumbersome and there are lots of choices for software that someone has spent time building. I’d rather learn to use their application well and let it help me do my job.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 4.8MB) 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.