Voice of the DBA

Tag: administration

  • Minimize Typing

    It couldn’t have happened to a worse set of people, and I’m glad it did, but I’ll still take a positive lesson out of this. A hacker sent a typo in a command to a botnet and lost control. That’s kind of funny, and I’m glad it happened. The less botnets, the better, IMHO.

    The coding in this software didn’t have good error handling, which is a lesson in and of itself. Overall it seems many developers do a good job of error handling, but I still encounter more pieces of software that allow problematic input than I’d like. While we don’t have great error handling in T-SQL, you can make some checks, and you should.

    That’s not the big lesson for me. The bigger lesson is that we ought to do less typing in much of our daily work. The last decade has had me work often with companies looking to implement DevOps software pipelines and driving automation wherever possible. We want to limit the chances humans can make mistakes, which means we want to limit their typing. Or clicking, as is the case in much of today’s software.

    Instead, we want to ensure all our code or commands are reviewed by someone, they are submitted to an automated pipeline, and they are validated or practiced on some system ahead of production execution. We ought to do this for no other reason than we want to ensure we have an audit trail, but preventing typos is good as well.

    I don’t know if you can completely get away from typing, but we can reduce the number of human error mistakes if we include some static code analysis (including for commands), some peer review, some sort of unit testing, and pre-production deployment. A lot of mistakes I find are fairly simple ones. Common human error that occurs because we’re busy, we’re stressed, we’re moving too fast, or we just miss something.

    Use the computer for one of its strengths. Tediously checking the simple things that humans do wrong.

    Steve Jones

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

  • T-SQL Tuesday #158–Implementing Worst Practices

    tsqltuesdayNot that I’m looking to do this, but that’s the invitation from Raul Gonzalez this month.

    This is the monthly blog party where we write on a topic chosen by the host. All topics are tracked at T-SQL Tuesday, and if you want to host, you need a blog and then ping me.

    Just Do a Little

    I’m going to tackle two sides to this, the administrative/sysadmin side and the development side. I think it’s easy to implement worst practices, especially in busy environments, if you haven’t gained some knowledge and prepared yourself to do better.

    The main thing I’ll point out is that it can be hard to implement best practices, or sometimes even decide what is a best practice. However, you ought to be able to avoid the worst practices.

    Worst DBA Practices – Poor Setup

    One of the things that has often been done in technology, especially in the Microsoft-based world, is accept defaults, get software up and running, and forget to tackle ongoing practices. In particular, there are two things that I consider worst practices: backups and sysadmin.

    First, no backups. Above all, even above security, we need backups for our data. If we have those, at least we can recover. When you set up a new database, you ought to ensure you have backups implemented. Right away. I’m saddened that Microsoft hasn’t made it easy to implement this as a part of setup. While you can use unattended setup or dbatools or something, it takes a little prep.

    At the very least, schedule Ola’s backup solution in each instance that is set up. At least with USER_DATABASES set, this will pick up new dbs as a backup.

    Second, don’t use sa/sysadmin or any privileged account for applications or even DBA scripts. Set up another account that can be disabled, password changed, or some other security measure. Too often people never set up another account and get used to using sa.

    Truly a worst practice.

    Worst Developer Practices – Starting with SELECT * and NOLOCK

    Aaron Bertrand has a number of bad habits posts, which I think are worth reading. If you can’t adopt his best practices, at least avoid the other issues.

    Two worst practices I think create technical debt and later problems are SELECT * and NOLOCK. If you can’t do anything else, at least avoid these.

    The first (SELECT *) leads to issues with extra data movement across the wire, extra reads in SQL Server, and in general problems with refactoring items as you never know where an application requires certain columns. I know we won’t get perfect, but don’t use SELECT * in any production code. The only place for this is when you want a SELECT TOP 10 * to get a feel for what data is in the table. Every other query in an application ought to specify what columns it needs.

    Note: Using SQL Prompt and getting all columns is just as bad. Pick the ones you need.

    Secondly, NOLOCK should not be a default item. There are data integrity issues, which can cause you problems down the road. Putting this in often means everyone is terrified of removing it. Don’t start here.

  • Upgrading SQL Server Central

    The decision to upgrade database servers can be a complicated one that involves features, costs, and support requirements. While I think many people would love to run database systems for ten years, often there are concerns about support, which effectively ends after five years for SQL Server. While you can purchase extended support, is that worth the cost? A hard question to answer, but one I’ve had to confront lately. When do you upgrade a database instance?

    I’ve discussed this with a few customers, but it also came up in the context of SQL Server Central. We run a SQL Server 2016 database on Windows Server 2016. Someone recently sent a note that since 2016 is out of support, they’d like to upgrade the systems. They were thinking SQL Server 2019 on Windows Server 2022, but with the release of SQL Server 2022 that doesn’t make sense.

    I want to defer this process as long as possible. To me that means always aiming for the latest and greatest version. SQL Server releases roughly every 2-3 years, so this is the best time to upgrade for us. If we can upgrade before 12 months, we get 4+ years before we revisit this topic. If we were to upgrade to SQL Server 2019, then we’re already down to 2 years of support before we need to consider the topic again.

    I think many DBAs would feel the same way, looking to test and certify SQL Server 2022 for their internal apps. This is one reason why getting the RC0 and RC1 releases of new versions for some initial testing make sense. The sooner you can upgrade in version’s lifecycle, the longer before you do it again. Of course, if you have more than 50 servers, you might just be upgrading every year anyway, as some system is always falling out of support.

    Upgrades take time. There’s the time considering the decision, the testing a new system, the actual upgrade time, whether in place or migration, all of which eat up labor and time. Doing this for more than a few servers can become a full time job in some cases. That makes me start to really see the wisdom of using a PaaS service that’s evergreen.

    For organizations where support matters, then upgrades are a fact of life and a regular occurrence. However, if formal support isn’t an issue, you might feel differently. The more mainstream you keep your feature usage, the more likely that you can go far past 5, or even 10 years, with your database system. I know there are still companies running 2008, 2005, and even a few 2000 servers. I don’t know that the SQL Server Central code would run on SQL Server 2000, but it might. Project Nami is using fairly generic SQL. If we were more concerned about database licensing, likely we’d have not upgraded the site from ASP.NET to WordPress.

    For now, I think we’ll likely upgrade sometime in early 2023. I am not worried if things are delayed, but I do know that we also want to upgrade both WordPress and PHP. There is an order to getting all these steps completed with OS and database upgrades. Because of this, I want to ensure that we proceed soon and get things moving while my schedule is fairly light so that I can test and deal with any issues that arise. Hopefully there won’t be any, but I’ve learned to hope for the best and prepare for the worst, especially with software upgrades.

    Steve Jones

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

  • T-SQL Tuesday #157–The End of Year

    tsqltuesdayIt’s that time of the month, and I’m late. I’ve been on holiday for a week, so this is a quick post for T-SQL Tuesday. This month is hosted by Garry Bargsley, and is a fitting topic for the last month of the year.

    Garry asks about end of the year data activities. Most of his examples are administrative, but I’m sure there might be some dev activities as well. I’ve got a few thoughts on each, some of which I’ve done, some of which I wish I’d have done in the past, but recommend now.

    T-SQL Tuesday is a great place to participate in the community and a great way to show your knowledge and skills off as well for potential employees. Write your own posts on a blog, or somewhere like LinkedIn, Medium, or another site. If you want to host, ping me @way0utwest or sjones at sqlservercentral dot com.

    End of Year Administration

    When I’ve been in the Operations side of data, there aren’t a lot of things I do at the end of the year, but I do find the downtime useful for some maintenance and cleanup. Usually we are doing well reactive stuff as business slows down, so I spend time on things that I’ve wanted to do all year, but haven’t had time to tackle.

    The things I try to do:

    • Index cleanup – look for dups, unused, etc.
    • archive/delete data or tables – I try to clean data where possible. Less always makes things faster
    • Chronic issues – Think about how I can solve something and prevent future problems
    • Space planning – look over trends and be sure we’re ready for next year.
    • Security – remove old accounts (or disable) as much as possible.

    End of Year Development

    Development tasks tend to be more tightly specified, and there often isn’t a good end of year list. However, similar to administration, one thing I have tried to do when times are slow is tackle things I’d want to change, but never have time. End of year is like this, as are some times after deployments.

    Apart from code I might want to refactor or change, the big things I might look at during the end of year are:

    • Branch cleanup – easy to have some of these hanging around.
    • Pipelines – Use time to improve these, or ensure that they are noisy. Either change something, reduce tests, or try to avoid any unnecessary things causing the pipeline to go red.
    • Learning – covered below.

    Bonus – End of Year Career

    This isn’t specifically for SQL Server, but it could be. This is a good time of year to stop and try to assess how things went. It’s also good to look forward, and use slower times to make the future better. A few recommendations:

    • Always have a list of things to read/learn/practice. Use slow times to work on something. If nothing else, tackle the Advent of Code.
    • Assess your career – is this the place for you? The job, the employer, the field? Think about what things excite you and what don’t, what make days drag, or go by quickly.
    • Plan for the future – Make a few career goals. I’ve done this for a few years, and it helps me continue to learn and grow.