Voice of the DBA

Author: way0utwest

  • Creative Development

    I was working with a customer recently that has a development process that both made me cringe and struck me as very creative. In this case, the customer has software they have written when spawning a few databases for each new project that is created. There are three types of databases created for each project, with unique names for the project. The DDL and DML to create these initial databases are stored in a central database as a set of rows in a table.

    To make changes to their software, developers create a project (with new databases) and then alter the application and the database to meet the requirements. These changes then need to be captured and applied to a development template database. There is a template for each of the three different databases created for projects. Changes made to these are processed and then added to the central database for new projects, and to upgrade existing ones.

    This creates a complex development process with lots of potential for mistakes and simple human error. However, that’s the state of the software, and so I’ve been trying to help them find ways to simplify this as well as make it more robust across time (and staff changes).

    The situation got me thinking, however. While they or I might not like the process, I do admire the creativity it took to set this up and build a system that allows custom software to meet their needs for project tracking work. It’s a solution that works, albeit one that now looks overly complicated. However, I wasn’t part of the initial design or the various evolutions since then. Perhaps I’d have ended up in a similar place, given the knowledge and requirements known at each point in time.

    I’m sure many of you have an architecture or a process that is unusual in some way. Perhaps you designed it, or perhaps someone else did, but there was some creativity in building a solution to a problem. Today I’m looking to hear the stories of where you’ve seen creative solutions in development, either to a programming problem or maybe to a process that manages or deploys your software. Maybe you deal with remote systems that aren’t connected. Maybe you work with large numbers of sharded databases. Perhaps you have cultural challenges that require creativity to ensure you can update your software.

    Let us know today what sorts of creative development solutions you’ve seen implemented.

    Steve Jones

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

  • A New Word: Ozurie

    Ozurie – feeling torn between the life you want and the life you have.

    I think many people feel ozurie often. I certainly had a lot of this in my younger years. I’d see the success, the partners, the adventures, the experiences others had and I felt torn. I wanted to change my life.

    Today I don’t feel this. Perhaps It’s growing older and more settled, perhaps it’s appreciating the blessings and fortune I have had. My life is really amazing. Even the things that don’t go well, like broken planes or weather damage at the ranch, or a lack of sleep because of commitments are minor issues. I think this is beyond the life I wanted and I have it.

    I hope you find that, too.

    From the Dictionary of Obscure Sorrows

  • Altering the Default Schema for a User

    I had to test something for a customer, and as a part of this there as a need to have a different default schema for a user. I wrote about that, but since this isn’t something that I (or many people) do often, I wanted to make a second post about changing the schema.

    The Scenario

    A user in a database needed to access certain objects, which were going to be located in a separate schema. The previous post looked at the issue for a new user, but for this one, I wanted to show how to change a schema for an existing user.

    This post shows how to alter a user’s default schema.

    The Solution

    When you add a user, this is a simple parameter as part of the CREATE USER DDL. In this case, you use the DEFAULT_SCHEMA parameter. The ALTER is the same, which isn’t the case with all parts of the T-SQL language. Sometimes there are procedures instead of true DDL.

    In my case, we wanted to change the default schema for a user. In the first post, the APIUser had the default of the WebAPI schema. Let’s move them to the Sales schema with this code:

    ALTER USER APIUser WITH DEFAULT_SCHEMA=Sales

GO

    That’s it and now if objects aren’t schema qualified, the APIUser will query the Sales schema first, then the dbo schema. If this user wants to query the WebAPI schema, they must schema qualify things.

    SQL New Blogger

    This was a minor part of something else I was doing. I noted this in the previous post, but then realized the ALTER was a good second post. I could have added this to the first one, but I like separating and focusing posts. Better for SEO if you care, but better for your workload and producing most posts.

    Outside of the work I was doing, the sketch of these notes took about 2 minutes, and then the entire post was < 10 minutes.

    You can do this.

  • A Paste Policy

    Most of the code I’ve written for various employers was something that lived inside the organization. This was for internal use and no one outside of the organization ever used it. It was almost always code I’d written, so I wasn’t ever worried about the provenance of the code (where it came from).

    However, I’m sure I’ve copied code from somewhere online and used that in some way. I might have taken this function from Jeff Moden and dropped it on a production server where it was used to process a string. I’m sure I’ve taken code from various places and used it in administrative functions, like Ginger’s code to manage a log file.

    I don’t know that any of those authors would have minded using their code. After all, that’s why many of us have written articles and published things at SQL Server Central. We are looking to help others grow their knowledge, as well as provide them short solutions they can incorporate into their own work. I assume most of the people answering questions at SQL Server Central or Stack Overflow feel the same way. Use the code.

    In this new age of AI, perhaps that’s OK, or perhaps it’s not. There is concern about who owns code and who has rights. At Redgate, we build software that’s sold and installed on other machines, so we have stricter guidelines. Our policy (in general) is “You MUST NOT include source code of unknown provenance in your application.” I saw a note recently that said this also applies to any AI-generated code, even if we provide the prompts.

    I doubt many organizations have policies about using code from the Internet, but I wonder if they should. Possibly there might be ownership or usage issues with AI code. If someone posts code on SSC that’s one thing, but getting code from an AI that might have been trained (and regurgitates) code that is not freely posted could be a problem.

    Maybe it doesn’t matter. After all, in your corporate CRUD application or some DBA maintenance script, it’s unlikely anyone will be auditing the code for any sort of misuse or copyright infringement.

    Steve Jones

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