Voice of the DBA

Tag: administration

  • 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

  • Bare Metal

    At the first job I had as a DBA, I had to build a new server. This was in the days of SQL Server 4.2, and I was combination DBA, sysadmin, and general help desk at a small company. With a software developer consultant, we ran some tests on various machines and then ordered a collection of parts from Compaq. Back in this time, they would only customer parts of the server. We unpacked our boxes with the server, extra drive bay, various SCSI drives, and extra RAM. We assembled and tested the machine and eventually put it into production.

    In the years since then, I got out of the hardware business and left that to others. For awhile I worked in organizations with IT staff dedicated to building machines, but at some point we stopped doing that. The growth of VMWare and other hypervisors changed the paradigm for most organizations. For more than a decade, all the servers I’ve connected to are virtual machines running on hardware that my employer or a cloud provider owns and manages as a node in a cluster.

    Early on there were concerns about the overhead of using hypervisors and virtual machines. When many early workloads moved to VMs, lots of organizations left database servers on bare metal to squeeze every bit of performance out of the system that was possible. Over the years, improvements in hypervisors as well as the software used to connect storage and networks together seem to have rendered those conversations obsolete.

    Or have they?

    Today I’m wondering if any of you still have servers on bare metal. Are there systems that you continue to install the OS and database server software directly on the hardware? Or are there systems where you still ensure that there is only one VM, the database server VM, on a hardware node?

    The cloud has changed a lot of these conversations, since everything is a VM. Even many local data centers will rent you a VM, something that wasn’t possible when we first moved SQL Server Central out of a friend’s basement into a co-location facility. Back then we owned our servers and I installed them in data center racks. I would like to think we’d have moved our site to the cloud in some way over time, though maybe not. Maybe I’d still be managing a couple servers in a room in Denver.

    Are any of your organizations still running legacy systems in the same way you would have in 1999? Let us know today.

    Steve Jones

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

  • Securing All Your Connections

    I read an interesting blog from the cyber security team at Microsoft, noting you should use TLS for your SQL Server connections. I would assume most professionals know that using TLS and secure protocols across the network is important. I would also assume few of us are willing to get real certificates for all of our SQL Servers, especially those in dev and test environments.

    Setting up a certificate for a server instance isn’t hard, but it’s also not easy. It’s also something that I don’t know how easy would be to automate in many environments. I know that you can use APIs from somewhere like Let’s Encrypt, but integrating that into a server setup process would be something. What about integrating this into instances in containers? I don’t know that I think many SQL Server organizations have DNS integrated for most of their database servers, much less asking them to also get certificates set up.

    I do think that Microsoft likely has better infrastructure in place, and better staff resources, than most of us. I can see this being something needed for Azure SQL DB and Azure MI. Heck, this might be something we want for all services that we get in the cloud. Since we don’t control much of anything other than the service, and Microsoft can automate the process of generating certificates, this makes sense.

    I think this might be something that is a good idea for production systems as well, ensuring that when a DBA or system makes a privileged connection to a database server in production, the person or process is sure of which database server is at the other end of the connection. I don’t know that I’ve seen an exploit in the wild where someone impersonates a prod server and captures credentials in some man-in-the-middle attack, but I wouldn’t be surprised if it happens.

    Do you think you also ought to avoid trusting the server certificate? I don’t think this is practical in many orgs, but I’d be curious what you think today.

    Steve Jones

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

  • Actively Choosing Compatibility

    SQL Server databases have had a compatibility level for a long time. This is a setting that enables the database to process code as if it were a particular version. The levels go from 80 (SQL Server 2000) to 160 (SQL Server 2022). Each time (almost) a new version of SQL Server has been released, there has been a new compatibility level.

    However, not all versions can support all compatibility levels. For example, my SQL 2022 instance can support levels back to 100 (SQL Server 2008). If I wanted to get a database to act like a version older than 2008, for example, 2005, I would need to install a SQL Server 2012 or older instance. There is a table of engine versions and supported compatibility levels on the ALTER DATABASE Docs page.

    When you create a new database, by default, it is at the current compatibility level. However, if you upgrade a database, the level might not change. There are some limits to which versions are supported, so an upgrade might change your database.

    An administrator might choose to keep an older level for compatibility purposes. Perhaps your code has an identifier that is now a keyword. Perhaps you expect some code to behave a certain way. However, not all changes are protected by the compatibility level. Most of the time, an administrator must manually change this, which is something that can slip through the cracks. If you don’t change this right away, likely it isn’t going to change.

    There was an interesting post from Brent Ozar recently that explained a bit about compatibility levels in a SQL Server database. I wonder how many people actually actively choose a level or they just accept the default level for that instance. Brent gives some advice in the post, and his recommendations vary a bit, depending on whether you are happy with the system or not. He also recommends measuring your system and then evaluating a change. Especially if this is a database for vendor software.

    I don’t think changing or updating this setting is a priority, but I also think that being aware of when your level doesn’t match the instance and documenting this is important. At some point, through many upgrades, you might find your level isn’t supported any longer. Then your database might have immediate issues. A good monitoring system can let you know when you have mismatches that can be evaluated when there is extra time to clean up or test changes to the system.

    Each version of SQL Server adds new features, like the changes for Intelligent Query Processing. In general, we want to take advantage of these if we can. However, not all workloads respond positively, so as Brent mentions, you need to test and evaluate your workload. Hopefully, you have a clear “things are better” or “things are worse” when changing levels. When you get some queries that perform better and some worse, then you have some choices to make. Often the default is “do nothing,” which may or may not be the best decision, but the devil you know is sometimes easier to deal with than the one you don’t know.

    That’s fine but consciously make that choice. Keep an eye on your system and don’t just accept defaults, whether those are from Microsoft or the ones you’ve left after an upgrade. Actively manage your systems to get the best performance you can for clients.

    Steve Jones

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