Voice of the DBA

Category: Editorial

  • Capturing Data Changes

    While working with a customer recently, I saw some code from them that used the OUTPUT and INTO clauses of an UPDATE statement to capture the changes made into another table. In this case, as users updated code strings in a table used in dynamic SQL, the developers wanted to capture the history of those changes in another table, giving them a rollback strategy if there were problems. This is an interesting, and focused way of auditing data changes, albeit with the work to write this code and control updates through stored procedures.

    This is one way of tracking the history of data values in many database platforms. Temporal tables are another, and we have CDC/Change Capture available in SQL Server. In the past, many people have used triggers. All of these work, with various pros and cons. In many cases, I have seen triggers used, often because many developers know how to write them and they are easy to create. Easy to get wrong as well.

    However, triggers take some work, while platforms have often built capabilities that make it easy to capture data changes and track them. Many developers often aren’t aware of these features, or haven’t spent sufficient time with them to know how to work with them or if these features even work well. This is one reason many of us write about new features, to learn about them, experiment, and help others to understand how to use them. Of course, not all features turn out to be as great as marketed.

    Today I wonder how you capture changes and audit them. Do you use triggers? Something built in? For limited auditing, and with control of the code, would you use the OUTPUT clause with your insert/update/delete code? Actually, I wonder how many of you would even consider this for limited auditing, especially with the large number of tools and frameworks that might generate their own UPDATE statement rather than call a stored procedure where you control the code. Or do you think this isn’t a good way to capture this data.

    Steve Jones

  • How Important is Zero Downtime?

    As I work with more and more customers at Redgate, I see some interesting trends. During the pandemic (and prior), we got a lot of questions on zero downtime and how to achieve database DevOps without causing problems. Those are always interesting discussions, and I find many people want magical solutions without having to change the way they work.

    The last year, however, has had more people looking to implement database DevOps and speed up their development, but not a lot of questions or demands for zero downtime during these deployments. I find that interesting as the world depends more and more on computer systems, and the customer base for many organizations may demand access to the systems at any hour of the day or night.

    However, it doesn’t seem that as many people are concerned about small moments of downtime. Does this mean that more organizations aren’t measuring uptime anymore? Perhaps the interruptions caused by software deployments aren’t being counted? Or maybe the application software has gotten better at hiding blips in database access. Perhaps feature flags are catching on as a standard practice, so database deployments are less troublesome.

    I’m not sure what has changed, but it has been noticeable by me that the importance of making changes without downtime has not been a requirement from many customers. Is that the case for many of you reading this? Are you less concerned about downtime? I think one nice thing about the move to the cloud is it’s a little less stable, and perhaps that has lowered some of the expectations of our management. Since it’s out of our control, maybe we shouldn’t be too concerned about the need for retries, either automatic or a customer pressing a button again.

    Let us know today if you feel pressure to get closer to zero downtime, either in your everyday management of databases or during deployments. Or maybe tell us if you’ve gotten so good at your job that no one every notices when you do make changes.

    Steve Jones

  • Metrics and Measures

    Many organizations have been trying to find better ways to build and deploy software for their customers. Whether they deal with the general public or internal customers, we know that delivering software that customers use can be a competitive advantage. That’s the goal of DevOps.

    While most developers and management want to do this, they sometimes forget what the goal is. Instead, they want to continue to work in a similar manner themselves while giving lip service to actual change. They often do this while pushing others to somehow produce more and better software inside the same system. I see this over and over inside various companies.

    To become better, many of us use metrics and measurement of data to help guide us in determining how to move forward. In the area of software, there are a number of research reports showing which metrics are indicative of organizations that do a good job of delivering value to their customers. There are four main metrics: deploy frequency, lead time, change fail percentage, and mean time to repair. These are highlighted, though there are plenty of other things to track in your software process.

    However, aiming to just improve their metrics as the primary goal isn’t going to make your software better. The goal is to deliver software that meets your customers’ needs. Quicker, better quality, more features, and all those things that customers use are what is important. These metrics are there to help guide you, not to be the targets of efforts.  There’s a good article that talks about some of the downsides of just trying to improve these metrics.

    The goal is the continuous delivery of value to customers. The way we do this is by experimenting with code, getting rapid feedback from customers, adjusting and improving the code, and repeating the process, learning from our efforts. We drive automation to make this smooth and easy while enabling us to get our software to customers at the pace that suits our situation. It sounds vague and amorphous, and it is.

    There is a bit of an art to developing a process that efficiently builds software. It depends highly on the people involved, and on two other things. First, guiding them to improve their process and skills with references to practices that have worked well. Second, giving them the freedom and support to experiment and learn from their efforts. In doing those two things, it’s important to remember that while you can measure how well things are changing, aiming to improve the measurements often doesn’t help you improve the goal: building better software.

    It’s good to measure things, but keep in mind that the measures are not the goal.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • How Normal Is Your World?

    The last three years have felt like a bit of a time warp. I’ll run into someone or reach out to a friend, thinking that I haven’t talked to them in a year or two. Then I realize that it’s been 4 or 5 years because the 2020-2022 time frame felt like a year or less. A long year, but still, less than the three years that passed.

    Life is mostly back to normal for me, with travel, events, vacations, coaching, work, etc. all similar to the way things were in 2019. At the same time, I’ve changed and my life has changed. Some of this is aging, some is life moving on, but some is from the pandemic.

    Spending so much time remotely has me a little less patient or excited about small trips, even around town. I cook more and my family is more often at home. My adult kids enjoy spending time with my wife and me. I appreciate family and time off more, which was good. These are positives for me.

    Work is similar to 2019, but also different. Redgate is moving to a new, smaller office, and the culture of most people being there and eating lunch together is gone. We won’t have our SQL Servery in the new building, so I’ll be going out with friends, or bringing some sort of meal in when I’m at the office. I don’t like that.

    I still get to visit customers, and more so this year than in prior years, but there are a lot more Zoom meetings and short, quick engagements than in the past. That’s a good thing, and I find it exciting.

    At the same time, we have fewer events. Last year saw 18 SQL Saturdays, and we’re on pace to exceed that this year, but we’re far below the 100 a year from 2019, even adding in Data Saturdays and other events. Every event seems smaller than in the past, which is a bit disappointing for me.

    Some people still wear masks, signs from the pandemic are still posted, and I find people to both be better and worse. They are more enjoyable at times, but at other times displaying more anger, argument, and selfishness than before 2020.  It’s a weird mix, and I think the pandemic has people quicker to get upset at little things while being more tolerant in general. In general, the world feels normal to me, but slightly evolved from 2019.

    Is life back to normal for you? It mostly is for me, though with one big difference. I appreciate life more, the people, the trips, the time, it all means a bit more after spending a few years fairly isolated and limited in many ways.

    Let us know today.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.