Voice of the DBA

Category: Editorial

  • Celebrating Lives

    I set up the sqlmemorial.org site earlier this year. It’s not quite complete, with some networking and process to work out, but it’s there, which is the important thing. The site gives us a way to remember  celebrate those that we’ve known as a part of our SQL community.

    There are only a few people that I’ve personally known well, and fewer still that I’ve met. As a result, there are only a few people about whom I can write something about from my own memories.

    I’d like to know more about the others we’ve lost.

    It can be difficult to write a memorial to someone’s life, especially someone that is close to us. It is a sad moment to think that you’ll never get the chance to spend time with a friend again. The few times I’ve had to put words down are upsetting, but they are a bit cathartic.

    The site runs from a GitHub repo, and I have a page on how to add a thought, a note, or a picture. If you have a link, a note, or a picture, please think about adding a pull request to add some depth to the description of someone’s life. Leave a note for them, for others, or for yourself. If you are unsure how to do this, feel free to email me something and I’ll add it in your name, or anonymously.

    I’m saddened when I run across a message or note from someone that’s gone, but I also have some happy memories that I hold on to. If you have any memories, please consider adding something in tribute to sqlmemorial.org.

    Steve Jones

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

  • Do as I say, not as I do

    A common saying from parents, teachers, and many managers, is that you should follow their instructions and not necessarily their behavior. This is a very human thing to do, with many of us struggling to follow the behavior that we ourselves want. Instead, we follow the vagaries of our moods and desires. We do this even as we tell others to do things that we don’t bother to do.

    It’s not just human behavior, but it applies to how many companies deal with their customers. Microsoft talks often about taking advantage of new features in code and using the platform to solve problems. They dislike adding simple “syntactic sugar” (like a numbers table), and instead prefer you build the code to handle some of these simple tasks.

    However, they don’t really follow this advice, as Andy Mallon showed with a recent post on why not to use a couple of their “recommended” stored procedures. They’re not well written for modern code, they have limitations (or bugs), and could be considered a security risk.

    To be fair, I know that changing code in something that works is always dicey, but at the very least, moving from varchar() to nvarchar() shouldn’t break anything. If there are edge cases, then write some tests and rebuild the code to work better. Maybe, more importantly, these procedures ought to model good code, as Microsoft would recommend to their customers.

    There are a lot of places where different products at Microsoft might not use SQL Server well, and I understand. These might be software developers that don’t know a lot about how to perform good data modeling or even how to take advantage of SQL Server code. However, at a company with the resources Microsoft has, I’d expect them to form teams to handle these tasks and then review and suggest changes to software like Dynamics, Sharepoint, etc. Even if they can’t use the latest features in the SQL Server codebase, they ought to model good practices for all versions.

    For many of us, we might act similarly inside a company. Often we write code out of habit, and perhaps, to expeditiously get work completed, even when we know better. Using SELECT *, leaving out error handling, and more are habits that far too many of us embrace, far too often.

    Start making some changes today. If you know there are better practices you should follow, then take the few extra moments to implement them. If you don’t know of good practices, start compiling a list, asking questions, even post an idea or question in the discussion for this editorial. We all could write better code, and that starts with us actually making an effort to model the behavior we might preach to others.

    Steve Jones

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

  • Interconnected Temp Files

    The other day I went to cook dinner for the family. I had picked a new recipe (everyone loved it), and it was going to be a bit of prep. Before I started, I turned on the speaker in the kitchen, connected my phone, and started Spotify. I got 2 sec into the song, just enough for me to turn and reach for the cutting board when the music stopped. I turned back, started it and everything repeated.

    I tried a few times, but it kept happening. I opened Spotify on the iPad we have in the kitchen, where the recipe was displayed and tried there. I had the same experience. At this point, I was getting annoyed and a little stressed. I needed to get cooking, but I also wanted some music. Maybe a little bit of OCD coming out as I checked my desktop with the same result. I updated the credit card and had my daughter check her app.

    A little searching around had me try different things (rebooting, log out/in, etc.). Finally, I found one person that noted clearing the temp files on my desktop might help. I did that, deleting a few GB and cleaning out the UserData folder for Spotify. I restarted the app, and things worked. I walked back to the kitchen and the iPad, and music played there as well. Finally, I could get dinner started.

    I’ve been enamored with some of the Spotify-connected features, allowing a few of us to listen together. I like when I listen in the car (or desktop) and then move to the other location, I can pick up where I left off. However, I hadn’t expected something like corrupt or data problems on my desktop to affect me on another device. As we start to interconnect more apps, it’s possible that a problem on one device might affect others.

    We do interconnect some systems in the data world. We have clusters and Availability Groups, and we certainly sometimes have instances or databases that create dependencies  between two systems. I doubt that many of you have one instance cause a problem with another, but it’s worth keeping in mind. We want connected systems, but we don’t want failures in one place to cascade throughout all the nodes.

    I like connected things, but I want loose coupling. I want one system to run on its own if the other has an issue, but I do want them to share data or status to improve the operation of the software. The big thing is that I don’t want one device (my desktop) to affect the operation of another (my phone). At least not while I’m cooking.

    Steve Jones

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

  • Deploying SQL Server Automatically

    I’ve had to install SQL Server many times over the years. Often it has felt that most of these installs were one-offs, a dev server, a new QA instance, a production server for a brand new application. A few times I’ve had to recover from disaster, including restoring master, but often, I just installed SQL Server manually because that was quick and guaranteed. I knew what needed to be done for most of the instances.

    However, I couldn’t be sure. In the past there were relatively few settings that were ever changed outside of the master database, but today there are more, and perhaps more importantly, the tolerance for making mistakes with any security missteps, is low. I’ve spent a bit of time learning to do unattended installs, and I’ve tried systems like FineBuild for installing SQL Server. In any size organization that might want certain standards set up, using one of these systems is important. Perhaps one of the better reasons to do this is ensure that your dev and test systems are configured the same as production to prevent any silly mis-configuration problems.

    This week I saw a post on using Ansible to install SQL Server on Linux, with all the various configuration. While I do think that it’s easier to install SQL Server on Linux programmatically, there are a number of items to set and configure. Following along the install, it’s interesting how much more this feels like something a developer would do, which is how many modern Operations groups approach installs and upgrades. Everything needs to be done without a human directly involved, often because of scale. There are so many systems we manage, often a mix of VMs, cloud, and local systems, which mean that a consistent, programmatic way of installing instances is needed.

    That’s likely one of the most important skills for a modern system administrator. We need to learn to use tools to get our work done. Whether these are free, open-source, or purchased, using a tool to work at scale is more important today than ever before. Even if you are part of a development group, learning to manage systems with code can help ensure you can scale and grow quickly, and more importantly, hand off this job easily to someone else when you reach that point.

    I think there are plenty of homemade scripts and tools that can help here, but almost every time I’ve seen one of these, it’s not very portable to other staff, especially if the author isn’t available. Too often these tools take shortcuts or are specifically tailored to the current environment and not the future one. These days, for many functions we tackle there are extremely well built tools available at no, low, or modest costs. I’d encourage anyone that is handling these tasks to learn about the tools available and pick one. I don’t know that I think any of these are necessarily easier to use, but they are all well documented and capable of handling most of your install, update, and configuration chores.

    Steve Jones