Voice of the DBA

Category: Editorial

  • AI In the Nov 2023 Enterprise

    AI is everywhere. I can’t seem to get away from stories on the technology in 2023, and while I don’t know that I’ve found it that helpful, I keep looking at it because it’s becoming a pervasive technology that most enterprises will experiment with in some way. There is a look at generative AI in the Enterprise in the O’Reilly Radar, which tracks how technology is changing and influencing the world.

    The report talks about most of the respondents to a survey using AI, which makes sense as the people responding likely have some interest in the technology. I always take these trends with a grain of salt as people who are busy and not interested might not respond. Only a small percentage of uninterested people will actually answer these things.

    As I look at the numbers with that in mind, I find it interesting that about half of the users think AI will lead to greater productivity and a small number (4%) think this means less headcount. However, only a minority (41%) have been using this for over a year. That likely means in most cases that people are experimenting like I am. This isn’t a pervasive technology in the enterprise, though I’d argue that while DevOps might be in use in most enterprises, I wonder if it’s in use in most projects at most enterprises. Culture change is hard for most people and I still meet lots of people who aren’t trying to get better at building software.

    The big challenge in many companies is finding appropriate business cases. I think that’s my struggle as well, in trying to think about how to use AI to write or build code, I struggle to think of how to prompt or what to prompt. Often by the time I define the problem, I can just write the code. If I were scaffolding out basic classes or tables, maybe I’d feel differently, but as our CTO put it, we spend most of our time figuring out the problem, not writing the code. There are legal concerns, but those are from a minority of respondents. I have a meeting with our legal department soon, which will help me iron out some of my concerns and get guidance.

    Interestingly, 77% of people are using AI in programming, with about half of those using it for work. I don’t know if we’ve done a good job understanding the IP/copyright issues here, so that’s surprising. I would guess in many organizations that don’t sell software, they don’t care about this at all. If they get code from an AI that was copied from somewhere else, if it works, who cares?

    I find that much of the code generated isn’t great. It’s junior developer level, which might not matter to many organizations. After all, they employ junior developers, and some employ senior developers with 6 months of experience 10-20 times over who write that level of code. If the AI does it faster, all the better.

    I think AI is a technology that is going to impact our lives as technical and data professionals. Whether you use it to write code or use it as a glorified search engine, it’s a tool that you want to understand and develop some skill with. Writing prompts and learning how to navigate an AI system is helpful. If it actually gives you something useful, even better. And if you learn more about building models and prompt engineering, you might find yourself with some interested opportunities in the future as I expect those jobs to grow in number across the next few years.

    Steve Jones

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

  • Migrations are Worth the Effort

    I work with a lot of customers that are trying to find better ways to build and deploy database code. Some of them use a state or model method with SQL Compare or another Redgate product, and some want to use Flyway and its hybrid migration-based framework. It’s always interesting to me how customers lean one way or the other, for various reasons.

    No matter which choice people make, we know that using migrations is more effort than a state-based approach. However, I think that it’s a better approach, especially because no matter how simple or smooth you think your development process is, at some time there will be a situation that needs a more complex approach. a migrations-based framework allows you to handle the complexity smoothly, without depending on a person to sort out the issues.

    It’s not just me, but many developers feel the same way. There’s an interesting article from a developer that gives some examples of why using migrations work well in different situations. It also acknowledges that the effort is high, but by using migrations you can accomplish complex tasks across time. something I try to get more developers to do. Don’t big-bang-deploy-everything, but break things into chunks.

    It’s not just a simple change. There are a few reasons that migrations are hard, one of which I loved. The author compares migrations to working with live electrical wires. Imagine hanging a ceiling lamp and wiring it up without turning off the power (note: don’t try that).

    One of the best reasons I have for migrations is that lots of work isn’t done at the same time, especially in a team. If each team or each piece of work is broken into separate migrations, choosing which ones to move over from developer is much easier than if all the changes are captured in a state-ful model. You still have to be aware of dependencies between migrations, but having them all separate at the beginning (and hopefully tagged to pieces of work) makes this easier.

    The other reason to choose migrations is that I can stage future changes. Imagine I need to make a change to add something in one migration and remove it later. Suppose we add a new tax column and then after Jan 1, we remove the old tax column. With migrations I can pre-write the removal and create a pull request with the “Tax Removal – DO NOT MERGE UNTIL 2024 JAN 1” title, which should be obvious to anyone. I like keeping things simple, which helps when I never know who might need to handle this work in the future.

    Steve Jones

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

  • Try, Try Again, Until It’s Right

    One of the challenges with making changes in a database environment is that undoing those changes can be hard. What’s often preferred is rolling forward with a new change to correct the issue, but that’s often done with limited analysis and thought. Instead, we hope our staff makes a quick patch and a better decision under pressure than they did with more time to examine the problem. That works if it’s a simple mistake that was made in implementation but not if we haven’t designed our solution well at the start.

    I ran across an article on DoorDash that I thought was interesting. During the pandemic, their business exploded and they outgrew the Aurora PostgreSQL database. They migrated to Cockroach, a cloud version of PostgreSQL that’s distributed and can (theoretically) scale much higher.

    The thing I found interesting is that the engineers at DoorDash were trying to break apart their monolith and get better scalability, primarily from certain tables, by extracting their tables to get single writers in a cluster, which should help them handle a larger workload. They wanted to use their main identity table as a test, which I assume is the table that tracks each user in the system. They tried to migrate this and cutover to a new cluster 4 times before a fifth attempt worked.

    I think any large migration is fraught with issues, but I appreciated the design here that allowed them to rollback their change and revert to the previous version of the database. That’s something I don’t see many teams think about or build into their database change process. I think having a clear, known, tested way to undo changes is important, at least for some of your tables.

    There are two pieces of advice they give that I often give to customers as well. First, learn to spread out changes across batches. When I work with Flyway customers, I always let them know they need to think of a migration script as a unit of deployment and break those apart as best you can. Those often also become units of rollback, so keep them small. Not necessarily every change in its own script, but don’t bundle too many things together.

    Second, keep things simple. Too often I find engineers build clever solutions that make sense to them, but no one else. You never know the quality of your next hire, so don’t overcomplicate things without a really good reason.

    Did their process work? They’ve grown to about 1.9PB of data. That’s a lot of food orders. They’ve also had other metrics of success, and seem to be saving time for their tech team, which is often one of the main reasons to build a better process and use it consistently.

    Steve Jones

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

  • Who Still Uses SP_ for Naming?

    An interesting post recently from Chad Callihan on the reasons why someone shouldn’t use sp_ to prefix their stored procedures. This is advice that I’ve seen for years, but I haven’t run into this lately with many customers. It seems I do see some usp_, but not sp_. I think that’s good, and if you’re not sure why, read Chad’s post.

    I think my main reason for not doing this is that I sometimes create procs in master. Not because it’s a good idea (it isn’t) or I want to, but because I’ll make mistakes. I’ll accidentally connect to master and deploy a script, because I’m not paying enough attention. Usually, I’ll catch this right away, but if not, soon after I’ve told a client things are working, they’ll let me know they aren’t. Then I connect to the right database.

    In a vacuum, or even as you sit reading this today, it’s easy to criticize someone else for making the mistake of connecting to the wrong database, or even forgetting to delete the object in master after I’ve created it in the right one. However, all of us are human. We’ll make mistakes. We’ll be pressed for time or sick or tired or want to leave or some other situation where we aren’t using all of our faculties. Even if none of those are true, we might depend on muscle memory because we’ve done this 1,000 times (or may 12 times in the last hour) and then don’t notice we’ve connected to the wrong database.

    What might be even worse if when we’re updating objects and potentially update in one place but not another. What if v7 of the proc is in the user database, but v5 is in master. Who knows if we (or the client) will notice things aren’t working as expected right away.

    Mistakes are one of the reasons I try hard to get everyone to follow a DevOps approach. Put your code in version control (so you know where it is when you make mistakes), use automation and CLI calls to deploy code. Whether you use something like Flyway or your own SQLCMD scripts, make sure things are automated. Then ensure you have logs with commands run, and if needed, output from the scripts.

    We’re human, we make mistakes, so figuring out how to automate a process and then ensuring that automation is running results in repeatable, reliable deployments. Something we should all want, especially the control-obsessive DBAs.

    Steve Jones

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