Voice of the DBA

Category: Editorial

  • Computer Disruptions

    I travel a fair amount for work to speak at various events around the world. Traveling can be quite a disruption to my life, and I do work to limit the amount of time that I’m gone. As my kids have gotten older, I’m have less commitments at home and an extra night during a trip isn’t as much of a burden. It’s even enjoyable when my wife can accompany me to enjoy a few days visiting another part of the world.
    I still get on airplanes enough that the scheduling matters. I often plan trips to limit the number of hours I spend in transit, which means I depend on the airlines to keep to their timetable. I’ve been lucky that I haven’t experienced many delays, usually localized to a specific airplane, but I do worry when I see reports like this about computer glitches for the airlines.
    There are numerous computer systems that airlines share, some of which are still running on large mainframe systems. As the travel industry has grown, the software managing it has changed. From a single system (and company) coordinating all flights to a distributed set of applications that must interact and share data.
    Do we think this is going to happen more frequently in the future? Is the state of software development improving enough to prevent large scale issues and disruptions? As much as I’m pleased by the advancements in software development and the higher quality of code, I still think that companies often take too many shortcuts, without enough testing and evaluation in their quest to build systems quicker.
    I hope nothing goes too wrong as I’m traveling this year. I’m sure I’ll have a few delays, but if I do, hopefully I don’t miss any of the speaking engagements.
    Steve Jones

  • Understanding Your Database

    I ran across a neat post from Michael Swart recently. In doing some spring cleaning, he was looking to remove unused, or maybe unnecessary, tables from his database. He published a script that looks through the plan cache to determine what connections exist between tables. He joked that if he doesn’t find any joy when examining a table, he drops it. At least I hope he was joking. If not, I suspect we’ll see a note on LinkedIn soon.

    Actually, I’m sure he’s joking, and he makes a good point at the end of the his post. His script isn’t used to make decisions, but rather it provides a place to begin to investigate more about what the table might be used for in an application. It provides a starting point for more questions, such as is there value in removing this table. His company is embracing AWS, and they are becoming more cost conscious. This means keeping less data, and possibly moving cheaper data stores where possible to reduce license and/or hardware costs.

    I suspect that other organizations will start to embrace similar attitudes as more move to the pay per month model of the cloud. Many of us rent hardware, and when we do, the recurring costs become an issue. Suddenly we might rethink the amount of data we keep and archive or remove older data, either to reduce costs, or risks. The GDPR brings with it a push to not keep all data in perpetuity.

    The goal of better understanding our databases, and specifically tables, makes a lot of sense to me. Far too often I’ve inherited some system and only understood portions of the database. I’ve performed hours of investigation to try and better comprehend how data is stored, retrieved, and manipulated. I’ve found my share of unused tables, often renaming them for months and eventually deleting them.

    I do think that we poorly document databases, almost treating them like a file share where we drop a new item when we need it, without thinking through the usage, ensuring others know about the entity, and often forgetting it exists if we don’t regularly use it. A RDBMS isn’t a file share, or at least it’s not an inexpensive one, so we ought to be cognizant of the data we keep and trim unnecessary waste over time.

    I empathize with Michael and would relish the challenge to review and trim old tables where I could. However, I also know that often there are tables that won’t “make a difference”, either in cost or any other savings and aren’t necessarily worth the time to investigate and remove. Spending even an hour to decide if I should remove a 10 row table whose purpose is unclear doesn’t seem like a good use of time. Unfortunately, I’ve run into plenty of those, which nag me, but really shouldn’t be something I spend time on, and I have to leave them be.

    Steve Jones

    You can hear the podcast here: http://traffic.libsyn.com/voiceofthedba/understanddb_50_v2068.mp3

  • Code Building Code

    The dream for some people is to have an Artificial Intelligence (AI) system that you use to describe some requirements and it will build an application that meets your needs. Certainly some AI and ML systems have reduced the need to write code for portions of an application, but I don’t think there is any AI framework that can build an entire application from scratch.

    I was reading a blog post recently on using metadata in our database to produce a CREATE TABLE statement that could hold the output of a query. It’s not AI, but this is something I’ve done in the past, using code to help me get work done.

    Excel was one of my earliest helpers and still is. I find myself sometimes using Excel to build a series of statements that follow a pattern, but the contents of which might be based on some result set. A common example is a set of inserts based on some data. I use values in cells to build up a final statement and then copy these to SSMS or another tool and execute them. It’s quick and dirty, but it works well.

    In the past I have written code that would build other object code, usually to provide some API constructs for developers. In a few environments, we have had some standards about how to structure tables, views, and stored procedures, including at times an API-like standard that required certain functionality be implemented in stored procedures. Using a code writing stored procedure allowed me to quickly ensure that the required stored procedures were created and modified as tables were added or altered. This also ensured that we kept all these changes in sync, without depending on my to review every part of the API.

    I don’t know that I’ll see a true AI system that we can give a few specifications to and have it build a system, but the more we implement standards and known structures, the more we can use code to help us ensure those standards are implemented in a consistent manner. Using templates in our work, such as powerful snippets in SQL Prompt, along with code analysis that looks for poor practices can help us write better applications. Even if it doesn’t do all the work, these helper tools certainly improve the quality of the code we do write.

    Steve Jones

  • Trunk Based Database Development

    Early in my career we had a folder that contained our source code. Each of us developers would copy the folder to our workstation and work on our code. To prevent issues, we had read only access to the main folder.  When we thought work was finished, we had to set up a meeting with two other developers, print our code out, and go through a review. If we got two sign offs, we would send our code to a manager to copy into the main folder. Since we were assigned work in different areas, conflicts were rare and this mostly worked, although it was slow.

    Over time I evolved to use various VCSes (Version Control Systems) for development work, with different paradigms for working in teams. These at times included code reviews, but like comprehensive testing, these were optional (and often ignored) when time was short. As an industry, I think we’ve gotten better with code reviews in some sense. The popularity of git and the integration of pull requests (PR) has made it more likely that others will review your code as a habit rather than a rare activity. I certainly see constant calls for someone to examine a PR in the Redgate Slack channels.

    I think this is a good move, but I was listening to a few people talk about trunk based development recently, which has less of a chance for code reviews. In this model, developers commit their changes directly to the copy of code everyone is using. In a PR model, developers work in branches, and only have a PR is reveiwed, is the changed code merged with existing files.

    Trunk based development is a more trustworthy model. We trust developers to make changes to the code everyone is using. This is often what many of us do in database development when we have shared development databases. A change made by one developer is visible and affects everyone else. While some organizations struggle with conflicts, this does ensure that problems are visible early and that your team must learn to communicate with each other.

    One way isn’t better than the other, and if you aren’t familiar with one of them, give it a try. Work on a small PoC with someone else and experiment with how well you can make changes and what conflicts come up. Many of us get stuck developing code in a singular way unless someone forces us to change. The software development process has evolved a bit over the years and you might find that a different style of putting code together may suit your team better.

    Steve Jones

    The Voice of the DBA Podcast

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