Author: way0utwest

  • Back to Syracuse for SQL Saturday

    Syracuse is having their first SQL Saturday on Sept 7, 2024. I’ll be there and hope to see you there as well. If you’re anywhere close, come on over and join us for a free day or learning about how to better work with data.

    I’ll be doing a professional development talk on Branding Yourself for a Dream Job. You can also check out the rest of the schedule.

    I never thought I’d go back to Syracuse when I left in 1987. I spent a couple of years at Syracuse University and learned a lot, but it was far to cold and snowy (and expensive) for me.

    Then my daughter decided to attend SUNY Potsdam and I’ve made quite a few trips through Syracuse as the closest major airport for travel to see her. I even managed to steal an hour on a trip and drive around the campus.

    I don’t know I’ll get to do much this trip, but I do hope to at least go get a slice of pizza at an old haunt.

  • Creating a Test Pipeline in Azure DevOps using a Windows Agent

    A customer was having some trouble getting started with Azure DevOps (AzDO) and building their database, so we took a step back and decided to create a simple test pipeline, so they could get a feel for how things work and then move on to more complex builds.

    This post looks at a basic pipeline on a Windows agent. I assume you have an Azure DevOps account and have created a project.

    This post is part of a series on Azure DevOps. You can click the link to see other posts.

    Setup

    We decided to start with a very simple test to start, using a task to get a directory listing. That’s a nice simple task, and one you can re-use a lot as you try to configure your pipeline.

    We created a new pipeline in our Azure DevOps project. To do this, we went to the Pipelines section of AzDO and selected “New”.

    2024-08_0004

    We next had a screen that showed choices. If you pick a repo, this wants to build a code-first, YAML pipeline. Great for coding and automation, bad for beginners. Select “Use the classic editor” at the bottom. Don’t worry, you can always move to YAML.

    2024-08_0005

    This takes us to a repository screen. We were working in Azure DevOps, so we can use Azure Repos, but if you used GitHub or some other Git repo on the Internet, you can choose that.

    You can choose to build from other branches, but I tend to start with main, though we often protect main to prevent direct commits. Once you select the repo and branch, click Continue.

    2024-08_0007

    We now get to choose a template. For teaching someone new, I Start with an empty one. Click Empty job to move on.

    2024-08_0008

    This gets us a basic pipeline. To orient you, there are multiple tabs, Tasks, Variables, Triggers, Options, History. We don’t need any of these to get started. The Tasks item is highlighted, so we see our tasks.

    The pipeline is the top level element, and most listed ot the left. The “Get Sources, is next, and is part of the pipeline as it downloads the repo. The Agent Job 1 is the container for a series of steps in our task, like a SQL Agent Job that contains steps. We’ll add different steps below here.

    2024-08_0009

    If you look to the right, you see some edit boxes. These are the top level pipeline settings. The Name is shown at the very top of the image, near the breadcrumb. If I edit this to say “Basic Directory” then this appears at the top.

    2024-08_0010

    The Agent pool is the list of possible agents. This defaults to using the Azure hosted agents from Microsoft, but you can create local agent pools if you want things to run inside your infrastructure on VMs instead. I’ll leave this, and I’ll also leave the Agent spec to Windows 2019. There are other options, which you can see.

    2024-08_0011

    Note, I am not a fan of using Latest agents. Pick a version that reduce debugging issues. Change this to a later version periodically when you are ready.

    If you click on the Agent job 1, you see some different values on the right. You can rename this agent, or specify a different agent from the pipeline. We’ll leave this alone.

    2024-08_0012

    On the line with Agent job 1, there is a + to the right. This is how we add tasks (steps) under our agent. Click this.

    2024-08_0013

    This brings up a list of tasks to the right. There are a lot of ones listed, and you can add more to your account. We’ll live with this list.

    2024-08_0014

    We could scroll or click the various categories (build, utility, test, etc.), but let’s search. Type “cmd” in the search box. We see a limited list of tasks. We’ll click Add next to the Command Line Script.

    2024-08_0015

    Once this chosen, it is added to the left under Agent job 1. Click this line to see the various settings on the right.

    2024-08_0017

    Let’s configure this. First, we’ll change the name to directory listing. Next, let’s delete everything in the script box and type “dir”. We should see this.

    2024-08_0018

    Now click “Save and queue” at the top. We get to enter a save comment and can configure the run. Just leave the defaults.

    Note: I grabbed this shot, and realized I’d accidentally set the agent to MacOS. I reset that before I continued.

    2024-08_0019

    Once we click Save and run, we should go to the screen that has meta data about this pipeline run. It looks like this. Click the line with the clue clock and Agent job 1.

    2024-08_0020

    That should bring up a log listing of the various steps. The listing on the left matches our pipeline steps. Whichever item is selected on the left filters the logs on the right to that info.

    2024-08_0021

    If we click the directory listing step, we see some info logged and then the command being run.

    2024-08_0022

    This directory listing matches what’s in our repo. We can see this easily by clicking on the repo. 2024-08_0024

    When the agent runs, the software runs inside the folder of the repo that is downloaded as part of the pipeline. If you want to get another folder listing, useful sometimes as you configure things, just change the dir command.

    Summary

    This is just a basic look at a pipeline and how to set up a simple task. It’s a useful task, and until you have your pipeline working, I’d leave this task in there. Being able to get visibility into the agent machine is often very helpful.

    I’ll look at a few other options in future posts.

  • Monday Monitor Tips: Changing the Fragmentation Alert

    I had a customer that was concerned about the fragmentation alert for indexes and wanted to know how to change it. This post discusses the change.

    This is part of a series of posts on Redgate Monitor. Click to see the other posts

    Finding Alert Configuration

    The settings for Redgate Monitor are available with the gear icon in the upper right side the menu area. You can see this below.

    2024-07-22 16_52_18-ssc-db-n3_(local) - Server Overview — Mozilla Firefox

    Click this to get a list of settings. The second section below has the alert settings, which you can see here. Click this.

    2024-07-22 16_53_49-Zoomit Zoom Window

    This brings up a long list of alerts that are a part of Redgate Monitor. Scroll down a bit and you will see a fragmented indexes alert.

    2024-07-22 17_11_54-Alert settings _ All Servers — Mozilla Firefox

    If you click this, you may see this, as the alert is disabled. This has not proven to be a very useful alert for most organizations, so it’s off. However, you can click the “customize” radio button.

    2024-07-22 17_12_05-Edit Alert Configuration — Mozilla Firefox

    When you do this, you should then see the alert settings. This is defaulting to all your servers, but you can customize the level of alerting by clicking on the left side to a server or group. I’ll cover that in another post.

    The key things here are that you can exclude read-only databases, as the fragmentation won’t change. Below that, you see there is a medium alert specified at a default of 60%. You can change the low/med/hi with the drop down as well as the percentage in the spinner.

    2024-07-22 17_12_19-Edit Alert Configuration — Mozilla Firefox

    If you want more than one alert, flip the “Use multiple alert threshholds” button and you’ll see three choices. I might suggest you not do this as if you worry about fragmentation, it’s likely only a low or medium alert.

    2024- 08_ 0029

    At the bottom, you can set a minimum index size. Smaller indexes aren’t a problem, so keep this relatively high. You might have to decide if 1000 pages is really high enough.

    Below this, you can set your notifications for this. In general, use the defaults and don’t customize this for an alert. The changes get hidden and people forget. Use the same settings everywhere.

    That’s about it. However, read these thoughts from Jeff Moden before you get too worried about index fragmentation.

    Redgate Monitor is a world class monitoring solution for your database estate. Download a trial today and see how it can help you manage your estate more efficiently.

  • A Lack of Architecture and Planning

    A few weeks ago, I was sitting in the audience, waiting for my turn to speak at DevOps Days in Minneapolis. Just before me, Xe Iaso delivered a funny and thought-provoking talk on building a social network on a whiteboard. It was very well done and had me feeling nervous about following that session.

    The talk is a bit of a satirical look at an interview Xe had for a company that tried to get them to derive an architecture for a large distributed system. It was interesting to hear Xe note that often we have architecture diagrams of what we’d like to have, but never an explanation of how we implement a large system, especially one that has to grow as our workload grows.

    This talk was a nice analogy of how often we get into situations where many of us can’t believe our system was structured this way. We often wish we could completely redesign things from scratch, and we’d do it better. Why didn’t the previous engineers think things through?

    Watch the talk. It shows how a lot of software is built. We build what we think we need, but when we get overwhelmed, or often when we get unexpected pressures from others, we make decisions that seem to be the best ones in the moment. However, a few years later, with a few of these decisions behind us, we realize that each of those choices was too short-sighted. We have a mess of software the seems cobbled together rather than well-engineered.

    I don’t believe we can engineer everything well from the beginning. I also don’t believe in early optimization, mostly because I think we are asked to build a lot of things that are never (or lightly) used. Who uses the paintbrush in Word or the FACTDOUBLE() function in Excel? However, I do believe that we ought to write code that performs well the first time, following patterns we (should) know well. We certainly should assume that whatever code we write will see at least 10X more data in production than in dev/test, so prepare for processing more data.

    I’m sure many of you know of a codebase and system that doesn’t run well. I hope most of you have another application that does run well and is fun to work on. Hopefully, the goal of your organization is to turn out more software like the latter than the former.

    Steve Jones

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

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