Voice of the DBA

Tag: automation

  • It Isn’t Worth Automating

    Every year I’m responsible for a portion of the Database Weekly newsletters. I used to do half, but right now Kathi, Grant, and I are on a rotation. Our boss puts out a schedule each year, which tells me the weeks for which I am responsible. Each January I then set up a recurring appointment for Mon/Wed/Fri on my weeks to remind me to curate articles and write an editorial.

    Each year I also think about whether or not I can automate this. Last year I spent a few hours looking into the Exchange APIs for creating appointments, at the C# code, and PoSh (which is a nightmare). I spent a few hours, got frustrated, and gave up, just manually creating appointments.

    This year I took a slightly better approach and tried to figure out how long it takes to create an appointment. I got a stopwatch and went through the process. Each recurring appointment takes about 30 sec to create and (a repetitive) 15 clicks. I have 17 weeks, so this is about 9 minutes (rounding) to get this done.

    That doesn’t seem worth automating. In a decade, I’d spent 90 minutes doing this. I’m not sure I could automate it in 90 minutes. I might learn a new skill, but I don’t do much calendaring, and honestly, I’m not sure I could do something I’d think would be reliable, portable to others, or even useful in other ways.

    Throughout my career I’ve had a lot of tasks like this as a DBA or developer. They are often short tasks, things I do fairly rarely, and are not tightly defined. Often I just do a little manual work rather than spend too much time trying to automate things. In my life, I’ve found similar tasks, where I’m not sure the time spent building automation or the maintenance requirements over time, would be worth the effort.

    I am, however, a big proponent of automation, and I think there are many places where the time spent is worth the effort. Periodically cleaning log files from servers, running data loads of know files, DevOps CI and CD processes, and more are worth the effort. Many people need these processes running often, and the work to get automation running becomes a time saver.

    Not everything needs to be automated, or even should be. Certainly, when the manual time or effort requirements are small, it pays to do a little analysis. If you disrupt the way humans do their work, then be sure your solution saves time or effort. As technologists, we often reach for the scripting/programming tools to solve problems, but be sure that you include a little thought before you start coding to be sure the process is worth automating.

    Steve Jones

     

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

  • Automation Ideas for T-SQL Tuesday #130

    tsqltuesdayThis month we have another great T-SQL Tuesday topic, and again, a host that I pressured into writing the invitation. Elizabeth Noble (@SQLZelda) and I were talking DevOps last year at a SQL Saturday, just as she was effecting some change at her employer. At an event later in the year, I challenged her to host, and here we are, with a great topic, Automation.

    That’s this month’s invitation. Elizabeth describes the process of using automation to smooth out their deployments to SQL Servers. They slowly built a CICD pipeline and migrated projects over and over to save her time.

    This month, what have we done to automate things? I have a few stories.

    Automating Data Collection

    One of the things that I am passionate about is the SQL Saturday events. I loved that PASS maintained a site, and made a feed of event data available, but over time, we’ve lost some data and I don’t think there is much impetus to maintain this over time.

    As a result, I build an automated process to grab data from the feeds and save it as an XML file on my local machine. I have the basic code up at GitHub, though I need to improve and refactor it a bit. These files change as the organizers update them, so I need a good merge process. Right now I tend to delete all 1-2KB files periodically, as the file is built once the event is approved. However, until all the speakers and sponsors are scheduled the file size is just a few KB. Most events are > 100KB once they are set.

    This is a basic way of grabbing some data, and I’m looking to build a few more data collection processes to grab data in my life and keep it around, just in case some service I use goes kaput.

    Automatic Databases

    I work at Redgate, and one of the things I’ve been spending more time doing is the automation of databases for development with the latest code and test data. This is a challenge, but a few of the things that I’ve done with Redgate that help are as follows:

    1. Use Powershell to automate the creation of SQL Clone images.
    2. Use PowerShell to update SQL Monitor alert values and add instances
    3. Use Flyway in a container to deploy changes
    4. Use our SQL Change Automation cmdlets to deploy changes to databases

    All of these items can be automated so that a user just needs to run a script, or sometimes just click a button.

    I’m slowly getting the PoSh code up on GitHub as I build demos and help customers get things done. Right now, getting organized is the biggest part of my job, because I find these questions coming over and over.

    I’m a bit proponent of DevOps and automation. Use tools and computers to do the tedious, repetitive work. Keep your day focused on solving problems and building/improving scripts.

  • Automation at Work

    I do worry about the future of work for large sections of people. When I read pieces like this one in the Atlantic on automation, there are two things that come to mind. First, we are mindlessly sticking with 19th century models of work in many cases. Second, there are opportunities that could dramatically utilize the leverage of computing power to reduce our need for humans in many cases.

    Far too often I’ve seen processes and procedures in place that exist strictly because of historical precedence. We developed some way of working, likely because of expediency. We needed something done, so we found a way for a human to do it. We continue to do it that way, often because of a factory mentality. We don’t trust workers, who come and go, to handle the process correctly, so we specify a way of doing things that we know works. Even if it doesn’t work well.

    What’s amazing to me is that many of us still do this in technological jobs. I find lots of DBAs and infrastructure people that still do an amazing amount of manual work to check logs, jobs, backups, etc. They avoid automation for a variety of reasons, but often because of laziness and fear. They don’t want to think and put time into changing a process, both avoiding coding as well as asking permission. They also fear for their jobs, as shown in the article. Automate too much and maybe the company will replace you with a less skilled, far cheaper worker.

    Perhaps I’m an outlier, but this has never been something I’ve seen in my career. When I automate things and free up time at work, I don’t sit and browse Reddit play chess, as a few profiles from the article show. Instead, I’m more like Gary. I look for, and find, ways to improve other aspects of the company. I help others. I provide more “value” for my salary. This has worked well, even in companies that had a culture of “just do your job.”. There are always a few managers that want thinkers and doers, not just people that mindlessly move through each day.

    Automation is coming, more and more every day. As I look at the evolution of the data platform from Microsoft, the growth and capabilities of cloud services, and even the amazing third party products that free up our time, I know that the bar is constantly raising for the skills we require. What we might have expected only senior level people to do in 1999, we expect juniors to know now. Not everywhere, and certainly plenty of older management is stuck with their historical views of “just do this job,” but times are changing if you seek a new employer.

    I want to see more scripting, more PowerShell, more Bash scripts, more DevOps pipelines, more systems doing tedious work. That’s because many of our scripts and our flows are still rudimentary. They’re basic, expecting the happy paths to work, with limited testing and error handling. Instead, I’d like to automate myself out of work, but then find ways to better script more robust processes, with ways that double check my code is working, and alert me when it’s not. With new responses that are more intelligent than  a simple IF..THEN statement.

    We have lots of room to improve in how we structure systems and code, whether in application development or infrastructure management. Hopefully we’ll all start to embrace more automation, and look for new opportunities rather than being fearful of change.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 4.8MB) podcast or subscribe to the feed at iTunes and Libsyn.

     

  • Use Tools

    This editorial was originally published on May 23, 2012. It is being re-run as Steve is out of town.

    As someone that works with Information Technology, I usually work on applications designed to make work easier. For example, we have accounting systems that ease the handling of debits and credits, they detect mistakes in data entry, and in general require less people to handle the accounting needs of many firms. Do we have less accountants?

    I suppose we might have less, but it seems that the finance departments, handling AP and AR in many companies is as large as I remember from my younger days. Perhaps the department is smaller than it might otherwise be, but it’s usually not small.

    In IT, we have all kinds of tools available to us that can help with our jobs. What’s more, we can create new tools as needed to do our jobs. Powershell or Perl scripting, Codeplex projects, and more are available to most of us to help us better manage systems. In SQL Server, we even have a built in framework, Policy Based Management, that helps us prevent changes or problematic configurations. If you find a third party tool that can help, you can make a case for its purchase, using the time savings translated to actual costs. One of those tools might really make your job more enjoyable..

    In theory, we ought to be able to manage many more systems per person than we used to. Does that many that we need less people? Perhaps, but it seems we grow systems fast enough that we still need to hire more staff in many environments.

    I so often see people working in IT fail to take advantage of all the tools we have to automate much of their jobs. They often tell me if they automate too much of their jobs, they won’t be needed and may get let go. Personally I think that’s an excuse not to exercise your skills, challenge your mind, and get rid of tedious work There’s no shortage of work to be done in most companies. If that’s true, why not use your tools to get rid of the tedious work and spend your time on something more interesting?

    Steve Jones