Voice of the DBA

Category: Editorial

  • An Upgrade Slog

    I saw a blog post from Randolph West recently that asked How do you restore a SQL Server 2000 database in the year 2024? It’s a bit of a process, involving an intermediate version and two restores. Randolph also points out the need to run DBCC after the first restore, which is a good idea. I wonder how many people would take the time to do this, or even think about it as an upgrade step?

    This was interesting to read as I had a customer ask me about doing this a few months back. They were trying to clean up their database estate and modernize some of their older systems. This was becoming a big project for them, as they had several pre-2017 systems, none of which were in support. Auditors, regulatory authorities, and even business partners see this as a large security risk and get concerned if you’re running older software.

    I’ve felt that in most cases, I ought to be able to run a database server for close to a decade. I certainly need to patch it with CUs in that time, but the support lifecycle says that you get mainstream support for 5 years and then extended support (paid) for 5 more. That extended cycle also includes security patches, so ten years seems reasonable.

    As a side note, the final support lifecycle for 2014 ends on 9 Jul 2024. That’s a decade if you upgraded in the first year of release.

    However, many of us have multiple instances, and upgrading those can be a chore. Perhaps you trust that nothing breaks, but I would say for many larger organizations, upgrades are a constant fact of life, and it is important to probably start testing upgrades at five years, knowing it might take 1-2 years to upgrade all instances of a given version. That’s if you don’t find issues in testing. If you test a 2017->2022 upgrade now and find issues, you might spend time mitigating these, or maybe wait for SQL Server 2025 (my guess) and hope you don’t have the same issues. There are also the challenges of in-place vs. side-by-side upgrades, and you might choose one in testing, but decide to change for the final upgrade for various reasons. All those things can cause delays.

    I still find myself a little nervous about the “evergreen” versions of SQL Server, where Microsoft patches them as needed. I know they try hard not to break any backward compatibility, but if they do, then you’re stuck. I prefer to schedule my upgrades and make them a normal part of the DBA job. That being said, don’t drag them out for years and years. If you still have SQL Server 2012 or older versions, you’re doing something wrong.

    Steve Jones

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

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

  • Protecting Schools

    One very common targets for hackers are schools and universities. The latter may have plenty of resources for staff, but often school systems don’t have that same type of budget. In many ways, it’s sad that hackers would target schools that struggle to educate and help others. The staff often deals with low pay and high stress already, and losing access to computer systems adds to an already tough job.

    There was an interview this week with the director of technology at one district. Johnathan Kim works for the Woodland Hills school district and is a former staffer at the Navy Cyber Defense Operations Command. That’s the type of training that I think few school district employees have. I’ve known a few people who manage technology inside schools, and while they are often smart, capable people, they aren’t security professionals.

    The interview talks about a few of the changes that Mr. Kim has made, such as enabling two-factor authentication (2FA) and removing local admin rights for many teachers. I know these are the types of rules that frustrate many workers who use computers. In fact, I ran into someone who rarely upgrades software on their development machine because so many applications require administrative rights and they don’t want to bother opening tickets more than a few times a year.

    Two-factor authentication can be a pain, and I know I get confused sometimes as I have both 2FA and MFA with different processes for different systems. It’s good in that a few times in the last year I’ve caught a hacking attempt, but it’s also a pain to deal with when I’m doing something simple. I can see why people don’t like it when they don’t understand the challenges of securing systems. Every time I find myself frustrated, I stop and remember the problems others have had, especially those that have dealt with ransomware in their organizations.

    A good point in the interview is that education can help smooth the way for security practices that feel unnecessary or disruptive. It’s good to remind ourselves why we want the least privileges needed assigned to others, and why those of us with privileged access need a second account for that access. We also ought to come up with a good story to educate others when they complain, perhaps using a story of a breach or loss to help remind others that our systems are constantly under attack.

    Steve Jones

  • Midsummer Fun

    Yesterday was Independence Day in the US and a day off for me. Today, I’m back to work. Same for my rocket engineer son, one day off. My wife remembers a few of her companies giving a 4-day weekend when a holiday falls on a Tuesday or Thursday, but that hasn’t been my experience and isn’t this week.

    I know we’re barely into summer, which technically started Jun 21, but most of my life has measured summer between the end of one school year and the beginning of another. That’s usually late May (Memorial Day) to early September (Labor Day). I think that’s a very American thing, as I know lots of my friends in Europe are just starting their break between two grade years. For those of you in the Southern Hemisphere, I’m sure this time of year is even more different.

    In any case, since it feels like midsummer to me, I wanted to take a break from technology and write about interesting fun things I’ve seen lately in the world of books and tv/movies. Even as I write that it seems strange since I think TV isn’t a concept for me anymore. I’ve gone completely to digital streaming, with episodic shows that aren’t ever broadcasted. They just drop, individually or as a whole season at once.

    In any case, I’m starting with Presumed Innocent. I read this book a long time ago, probably in 1987 or 1988. I couldn’t remember the story, but a friend recommended the series on Apple+. The first episode started slow, and I wasn’t sure I’d watch more, but the end caught both my wife’s and my own eyes. We had to watch the second episode right away. It’s dropping episodes week by week, which feels very old-fashioned and unsatisfying.

    I’ve also been working through Lost in Space on Netflix. I’ve watched the various versions of this since the old black-and-white TV version. I went through Season 1 of this a few years ago and really enjoyed it. I just realized there were more seasons and have been enjoying them. It’s a bit silly, far-fetched, and inconsistent in places, but still entertaining as a sci-fi view of colonists traveling through strange places in the galaxy.

    My wife and I completed season 1 of Resident Alien on Netflix as well. The casting is great and it was very entertaining. A fun watch for us. I haven’t found a lot of funny series lately, and fall back on old episodes of Scrubs, Community (first 2 seasons), Brooklyn Nine-Nine, the US Office, and Mom. Even though I’ve seen most of these series, they’re still good for a laugh. Once in awhile I even see an episode I haven’t seen (or don’t remember). In any case, Resident Alien was good.

    I like to read a lot. This summer, I’ve caught up on the latest in a few series I’ve enjoyed over the years. I’m working on Spinward Fringe 17, along with Observability Engineering. That latter isn’t entertainment, but it is interesting. I’ve also enjoyed these books lately: Toxic Prey, I Will Find You, Murder One, The Last Detective, and The Unincorporated Future. I’ve read a bunch more, but those were the highlights.

    I haven’t see many movies this year, but I have watched a few documentaries. The Thriller 40th Anniversary, the We Are the World documentary, and the George Michael one were great. I did see One Love (on Bob Marley), but it wasn’t that good. The music and actor are good, but the story isn’t. If you want a fun, unusual watch, try the Donut King.

    If you’ve got some fun escapes from work, either written or video, let me know. I enjoy watching a Slack channel at work that is dedicated to movies and tv, getting some recommendations there. I’m sure I’ll see something from one of you that’s worth checking out.

    Steve Jones

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

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

  • Xp_cmdshell Use Cases

    I had someone ask me recently how to run xp_cmdshell on a Linux version of SQL Server. I told them you can’t, as it’s an unsupported feature and not one that I expect to see released. I had to double-check, since I did think that supporting a BASH shell was a possibility, but it wasn’t added to the product.

    In the feedback forums, I saw a request for xp_powershell, though the feedback from MS is to use CLR for this. They suggest external access permissions, but those aren’t supported on Linux. I also didn’t see a request for shell scripts added, and I’m not sure I want one.

    A few years ago, I wrote a piece on the dangers of xp_cmdshell, as this does create a security risk. I can be mitigated, but the modern world is complex and it can be easy to make a mistake here. I’ve used xp_cmdshell often without issues, but I’ve also known the risks and tried to mitigate them with controls on the machine, network, and who can execute the procedure.

    I’ve seen people use xp_cmdshell for a number of tasks, like exporting a result set to a file, checking disk space, moving files after a BULK INSERT or backup, or some other task that is tightly related to actions taking in T-SQL. This can be a very handy utility for many administrators.

    Today I’m curious what are your use cases. Where do you use this utility, or where is it much easier than adding a PowerShell step in an Agent job? Similarly, do you use xp_fileexist or other XPs to do things are are outside of the realm of T-SQL. Leave a comment below and let us know how you use this stored procedure. Or in which situations this has proven to be useful in the past.

    And maybe vote for a BULK EXPORT 😉

    Steve Jones

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

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