Voice of the DBA

Category: Blog

  • 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 New Word: Exulansis

    exulansis – n. the tendency to give up trying to talk about an experience because people are unable to relate to it – whether through envy or pity or mere foreignness – which allows it to drift away from the rest of your story, until it feels out of place, almost mythical, wandering restlessly in the fog, no longer even looking for a place to land.

    I try to avoid exulansis in my verbal stories, but I know it doesn’t always work. I actually ran into this recently while traveling to a volleyball tournament as a coach. I wasn’t talking with a player, but rather a parent, talking a bit about my fatigue and challenges. On the 22nd of June, as I chatted about life, I realized I’d only been home for 7 days that month.

    This parent was a lawyer, primarily working in Denver and rarely traveling for work. They took vacations, but those tend to be spread out, where as this trip was my 4th of June. I realized as I tried to talk about the experience of crossing time zones and the world back and forth that this person couldn’t relate and my point was being busy and needing to catch up on chores, as well as dragging a bit at the tournament. The travel experiences across time zones weren’t really something that they could relate to.

    Interestingly enough, another parent heard and asked me for some tips, as they were about to embark on 3 cities in 3 weeks for work. They didn’t have exulansis from my story.

    From the Dictionary of Obscure Sorrows