Voice of the DBA

Author: way0utwest

  • The Choice of SQL Server Version

    Every quarter Brent Ozar publishes some data from his SQL ConstantCare® service. This is a service where companies contract with Brent to install a service on their instance, collect data, and give them simple, short daily emails on things they should check. It’s a good service for companies who don’t employ skilled DBAs and may relay on a developer or sysadmin to manage a SQL Server instance. While this is a self-selecting group of organizations, across his 3,100+ monitored servers, there are likely trends that could apply to the world of SQL Servers in general. After all, for every gung-ho, let’s-upgrade DBA, there’s probably a sysadmin with a similar mindset.

    In any case, the summer 2023 report form Brent shows that SQL Server 2022 adoption has slowed. His report is down, though I doubt anyone downgraded. Perhaps someone was testing and added a 2022 server in the spring they removed. Or maybe they tried 2022 and then went to the cloud. He does show 2019 growing and 2016 shrinking, which dovetails with what I see from my memory of various questions at SQL Server Central. I see people asking about moving from older versions to 2019 much more than 2022.

    I wonder what that is? Brent thinks this is because people standardized on 2019 installs and haven’t moved to 2022. So anyone adding new instances likely uses an image/setup/process for 2019. That matches with a few of my customers, who haven’t had some of their install or security processes updated and are still adding 2019 instances. I think that’s short-sighted as 2019 is 4 years old, but I also understand that people get busy and updating anything for a new version isn’t a priority.

    There have also been some problems with updates, and Brent thinks companies are skipping 2022. I don’t know, but I do wonder what you think about your estate and how things are changing. I assume if you are still running 2014- at this point, you’ll just live with the server as long as you can. I hope you’re at least on a VM so you can restore quickly if there are issues (assuming you back up VMs).

    If you run 2016/2017, are you looking to upgrade? Considering 2022 or stick with 2019? Or kick the can and hope that SQL Server 2024 or 2025 will be better? Actually, take a guess as well on the next release date. I’ll take a page from Brent and run a contest for you to guess the next release date.

    Steve Jones

  • 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.