Voice of the DBA

Tag: Database Weekly

  • Using AI with Data Tasks

    The hot new technology of the year is AI. Between ChatGPT, Copilot, and generative AI, it seems that this is invading the world of computing at an incredible rate. Whether this becomes really useful and valuable or not is something that we will seem over time. There have been plenty of trends in this area in the past that haven’t become as ubiquitous as the hype would lead you to believe.

    I’ve done some light experiments with AI on my blog. To date, I haven’t found this to be that useful, other than a few cases where I basically used an AI to search the web for me. Rather than read a bunch of SSC or Stack Overflow results, the AI summarized things.

    Somewhat.

    I definitely had to test and verify the code more than I feel I’ve done with code posted in a forum. Of course, I do less experimenting because the AI results were a little more targeted to what I needed, rather than my cobbling knowledge and partial solutions together. I’m also not sure which I prefer.

    I would like to use AI for data work, and there is an article that talks about some of the ways that we’ve used AI in the past. Data profiling has made sense, and I can see value here. For data security, I’m not sure how helpful AIs have been. I’ve looked at some products, and I don’t know that I think any of them do a great job of identifying data. They do make it easy for whoever is assigned the task by doing some of the work, but they aren’t a panacea. They make mistakes, just like humans do.

    I do think data observation and looking for anomalies is a place where AI can really shine, but that’s not the data work that many of us do. It matters, but for most of us, this isn’t something we deal with.

    The future of AI was more interesting. The idea of data homogenization, taking data from different sources, and fitting it to a data model is interesting. Of course, the AI can’t make too many mistakes, or the time correcting might overwhelm the time saved. I think we see that now with humans who we ask to ETL data. If they aren’t good at it, or make lots of mistakes, those of us overseeing them might just do the entire job ourselves.

    I know that AIs are still new and immature, and while there is a lot of potential, they feel like junior staffers now, needing more handholding and micromanaging than I like to do. Perhaps they will change our careers and the way we work, but I don’t know how quickly, or even how deeply. Already I find lots of companies putting restrictions on what their employees can do with AI, which makes me think this might be more a targeted, niche technology more than a general, use-it-everywhere solution.

    Steve Jones

  • 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

  • The Best Career Advice

    I don’t know that I have the best advice, but this month’s T-SQL Tuesday is asking for people to share what they think is the best advice they’ve been given or have for you. I wrote my own piece, where I noted that learning to say “No” was one of the best things I’ve ever done. Not that I say no to everything, but I do default to no, especially when someone asks for me to tackle something new.

    Actually, it’s slightly more nuanced than that. As I’ve gotten used to my workload, I will say yes to things, and certainly, I’m more likely to commit to one-off things. It’s the longer-term, larger things that I don’t want to agree to do unless I’m sure I can deliver.

    There are lots of other things people wrote. Deb said that you should trust your instincts and realize you can contribute, even if you’re new. Hugo notes the user is often right. Mala is more cautious with work and practices discretion. Rob got the advice to take it slow, learn his job, and figure out what he likes and doesn’t.

    There are lots of other advice, from Pragati telling you to get a mentor to Mikey saying you should find a job you love. If you check out the comments in the invitation above, you’ll see plenty more responses, many with interesting back stories and more details. If you only read through one set of T-SQL Tuesday responses, this might be the one to pick.

    I’m a big fan of actively managing your career. Make the decisions that move in the direction that matters to you. As noted in a few posts, we spend a lot of our lives at work. At times more than we spend with family, so be sure you have a career you enjoy.

    This takes work, but it’s an investment that can repay itself over many years. Both in financial rewards and less stress on a regular basis. Every job is a job some days, but when you enjoy your work, it doesn’t feel like work.

    Steve Jones

  • Validating Password Expiration

    I would guess that the majority of instances I’ve had to manage in my career were those that I didn’t initially install and configure. I’ve inherited more instances than I would bother to count, and I often need to double-check what’s been done in the past. As noted in the series on new jobs from Tracy and Josephine, there are a lot of settings to check and adjust to meet your standards.

    While backups are often my first priority, security is second. I usually want to know who the sysadmins are and ensure systems are patched and configured to reduce the attack surface area. There is one other security check that I think I haven’t always been overly concerned about checking: password expiration.

    There was a post from Steve Stedman recently that mentioned the way to alter logins and ensure they have CHECK_EXPIRATION set ot on, which ensures that passwords expire and need to get changed. This is especially important for sysadmins. I try to ensure those accounts in that role are secured with AD, but there have been times when SQL accounts are used. Usually, I disable sa, but I’ve seen other accounts, especially those used by monitoring systems who seem to think sysadmin is required. It’s not.

    I don’t know that I’ve run queries to check the value in the is_expiration_checked column is appropriately set. If it’s not, then Steve’s post above will help you change those logins. That’s a handy script to have set up and use to ensure that all logins have this set. In fact, this is one of those areas where new logins could be created by junior administrators and not set the option. Perhaps this is something you want to run on a regular basis, perhaps weekly, to ensure that if any new SQL logins are created, they are done so with the password expiration set.

    Ideally, no one would ever create logins without expiration set, but sometimes things happen. I’ve seen monitoring systems set up with sysadmin privileges and passwords that never expire. A surefire way to dramatically increase the risk to your database systems. It would be better to have a known, consistent process for setting up accounts. Some companies have specific scripts, or snippets, that administrators use when tickets are filed. One customer of mine had even linked a script to a Slack command in a sysadmin channel. Only admins could use this channel, but they could use Slack to kick off scripts to create logins, add roles, and force password changes.

    No matter how you choose to handle security at a process level, it is important to include monitoring and remediation for issues. Mistakes will get made, settings altered, and exceptions approved. Sometimes we can fix things, sometimes we cannot, but knowing what our environment looks like and where we have potential issues is important not only for getting the work complete but getting the approvals to make changes that ensure better security. My recommendation is that you ensure you have a way to regularly check your systems, automatically fix issues where appropriate, and report on those that need additional approvals.

    Steve Jones