Voice of the DBA

Category: Editorial

  • Poor Database Design Realities

    One of the interesting things that I see at Redgate Software is how idealistic our developers and engineers can be. They often build our database DevOps products with the idea that customers will use well-designed databases. The systems will have primary keys, foreign keys, defaults, constraints, indexes, and more. Developers will use coding standards, and naming conventions, and will understand what data is stored in tables. Not in every case, but often.

    After all, that’s how we build software at Redgate, as teams, sharing information, publishing documentation for others, and following best practices.

    It’s cute and endearing, and unfortunately, not often true. In most cases, I find databases built by developers, accidental DBAs, or even experienced DBAs to be full of inconsistencies, lacking constraints and keys, and even duplicating some indexes and forgetting others. I often joke during one of my presentations that the main thing people should learn is to add primary keys to their tables. However, I’m not really joking.

    During a recent design session on our masking technologies, there was a discussion on masking data in tables without PKs, which is a challenge. We’re working on it, and also on being able to mask PKs themselves, as some people use the PII data as a PK. This could be a tax ID of some sort, but could also be an email address.

    When one of our account executives (Rob Boswell) heard that we were enhancing our capabilities with regards to PKs, he joked that we will soon be “primary key agnostic.”  It was a great line, and in one sense it’s true. In another, it’s sad that we need to design tooling around such poor practices.

    The reality of the world is there is a lot of bad design, bad architecture, and bad code out there. I applaud those who work to improve things in their environment, am saddened by those who don’t (either improve code or their skills), and frustrated by management not supporting efforts to be better. At the very least they should support efforts to teach your staff to code things right the first time, which helps improve future code. The next best thing is to refactor and improve older code, which can help you spend less in the cloud, or run longer with the resources you have on-premises.

    The reality is the reality we are in, but that doesn’t always need to be our future reality. We can change the future, each of us, by learning to write better code and improve how we approach our work tomorrow.

    Steve Jones

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

    Note, podcasts are only available for a limited time online.

  • Using AI for the First Draft

    At the Redgate Summit in London, I ran a panel talking about Platform Engineering and how we can make developers more productive. One of the questions from our audience revolved around AI (Artificial Intelligence) technologies and how they might assist. As a note, AI tech includes a lot of different things, like machine learning (ML) among other things, but a lot of people seeing the media and hype around LLMs (large language models)  think those are AI. They see AI as what is implemented in ChatGPT and Copilot, which is correct, but incomplete.

    One of the panelists, Jeff Smith of Redgate, said that he views the output from AI as a first draft, something that bootstraps further work by a human. This can save time and can help someone be more productive, but it’s a starting point and a boost, not a final product.

    I had not thought of AI this way, even though I expect to have to test, refine, and edit the results I’ve gotten from various assistants. While it might be possible for me to keep refining my prompt and perhaps generate better results (code and/or text), I don’t know that it’s often worth the time to do much of this. I might do a little, but it’s much easier to take the result of AI as a first draft and then make it fit my requirements.

    In much the same way I use Stack Overflow or SQL Server Central for code snippets. They often give me a starting point and something I can then adapt to my needs without all of the time and effort spent on reading an API or SDK, experimenting with the syntax, and then actually using this in some code I’m writing. Similarly, I can get a head start on writing a summary or a pitch, though I’ll admit I’ve found it less useful there. In fact, for many of my editorials, I’ve tried to use it to summarize an article or web link, and it does a very poor job (IMHO).

    Some of you might be concerned or worried about this, and some might not. To me, the average-to-poor code produced isn’t any different than the legions of Stack Overflow developers or SQL Server Central DBAs who take code from an online forum and fail to test it. Anyone using any code (or prose) should test (proof) it and verify it works appropriately in their situation. We have a lot of people who don’t do that now and AI might exacerbate that with more code, but I don’t know if we are in a worse situation. We certainly seem to be able to produce lots of average tech professionals now who turn out plenty of average code.

    Ultimately AI is a tool that does exist in the world and will continue to exist. We have to learn to work with it and use it to our advantage. Good developers and DBAs will do that. I’d like to think that many organizations will look to hire and use those people as their staff and use fewer of the people who don’t bother to test and improve the results from their tools.

    Steve Jones

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

    Note, podcasts are only available for a limited time online.

  • DBCC CLONEDATABASE is Semi-Discontinued

    I saw a tweet that DBCC CLONEDATABASE was being discontinued for production databases, which both scared me and didn’t make sense. I’ve used this a few times for a quick copy of a database and like how it works. Discontinuing it seemed strange to me.

    Then I read the blog post, which notes that it’s not being supported for production deployments. The post doesn’t explain why, but I’m guessing this is because all the stats and other metadata moves, and this might mess up the optimizer if different data is added. I don’t know who deploys production databases like this, but I could see people who have federated or sharded databases using this to create a new blank copy and then uploading data into it. Or maybe people who need new databases that are distributed onto remote office/edge devices used it? If you use this to create production dbs, let me know.

    This command will still be used for generating schema-copies for diagnostic and troubleshooting purposes, which is what it’s there for. I assume this means Microsoft Support will support you using this to investigate strange query issues or if you create a database at their request. However, there isn’t a mention of this being used for development and test environments, which is where I use it.

    Specifically, I’ve used it quite a bit lately with Redgate’s subsetter utility. I need a target that’s shaped like the source, and dbcc clonedatabase gives me that. I don’t really care about stats or anything else, I just need schema set up to move data around. It’s useful there. It’s also useful for a quick test of a deployment, where I can ensure I get the latest production schema and then run a deployment against it (hopefully using Flyway), looking for errors.

    If you use this command, don’t worry. The tool isn’t going away, and the restriction against production copies doesn’t take place until March 1, 2025. That’s nearly a year from now to change your process. If you’ve never used it, well, I don’t have a good reason to start using it, but you ought to be aware of how it works. Learning about new features is handy, as you might discover a problem the knowledge can solve. However, if you don’t know it exists, it will never enter your mind as a solution.

    Be curious and try things. It’s easy, and it’s fun.

    Steve Jones

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

    Note, podcasts are only available for a limited time online.

  • Life in a Startup

    I have worked for a few startup companies, including SQL Server Central. Each has been a different experience, and I learned a lot at each stop. However, I’m not sure I’d want to go through that process again at my age. I was thinking about the challenges and the excitement of being at a startup while reading about the founding of Reddit. The post doesn’t go a lot into the technical details or the working life, but it is an interesting read from a VC investor.

    I also found this post on Choosing Startup Life, which talks about what the author thinks about before trying to start a company. He compares this with life in a Big Tech company, which relates to lots of companies, in technology or not. The main differences are lower salaries, less infrastructure, lots of work, and upside in a startup. Big companies have higher salaries and more perks, less stress and responsibility, and not a lot of context-switching. In general, that’s been true in my experience, though in bigger companies that didn’t think they were software companies, I sometimes could end up with a lot of context-switching.

    When you work in a startup, I hope you have some passion or belief in what the company is building. I think that’s what drives a lot of Kickstarter/Indiegogo projects. Someone wants to start a business to do x and they believe in x. It’s important to do that since you are likely to need to work extra hours. Startups often are cash-strapped, so they don’t have a lot of services and infrastructure and depend on employees to work above and beyond their main job. They also are in a hurry to get cash, so they want to complete their work sooner, which means more hours per week. Not all are like this, and once there is some investment capital, hours can ease a bit, but investors want their return, so this isn’t a slow-paced, relaxed atmosphere.

    There isn’t a lot of security. I’ve had startups fail under me where we didn’t have enough money to make payroll. I’ve had them sold in an instant with the new owner deciding to drastically change the employment status for many people. At the same time, I’ve had some jobs that result in unexpected windfalls of money. I haven’t had a company go public yet, but perhaps that will happen at some point.

    I think about being in a startup like being in my first apartment in college. Nothing is provided, I don’t even know what I’m missing until I look for it, I keep long hours there, and everyone is responsible for everything. There are also a lot of arguments over who should/needs to/ought to do what.

    Is it worth it? I think that the chances of a startup becoming a success can vary. It is important to understand valuations if you are trading away hours of your life on a gamble you’ll make money. Keep an eye on the number of shares offered and the potential value they would have to reach to make a difference in your life. For that matter, make sure you know what a pile of money means for your life. USD$100,000 sounds like a lot if you got a check tomorrow, but how much would that change your life?

    If you decide to create a company and take outside investment, make sure you really understand finances.

    Startups are exciting, but make sure you like the people working around you. You’ll spend a lot of time with them, and if you don’t enjoy their company, you won’t enjoy your job. Startup life isn’t for everyone, but it can be an exciting chapter in your life.

    Steve Jones

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

    Note, podcasts are only available for a limited time online.