Voice of the DBA

Author: way0utwest

Daily Coping 7 Jan 2021

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.

Today’s tip is to let someone know how much you appreciate them and why.

This is probably easiest with family, but I wanted to step outside my comfort zone here. I decided to take a minute and chat with someone that has taught me a lot and enriched my life the last few years.

I coached my kids when they were young, but I stopped at some point. As my daughter became a competitive volleyball player, I enjoyed watching and supporting her. It brought back memories of all the times I’d enjoyed the game as a young man. At some point she become more independent, and I was less involved in her activities. I decided to start coaching at a competitive level.

The director of the club where she spent her last 4 years is someone whose company I’ve enjoyed over the years with lots of conversations and shared memories. I spent a year apprenticing with him and then assisted a few others before getting my own team.

In the time I’ve know him, he’s taught me a lot about a game that changed from my youth. He helped me learn how to coach, with all the tricks to push, inspire, motivate, comfort, and more, outside of teaching skills. He’s challenged my thinking and helped me to grow as a more well rounded person that has to work with and influence others.

I’ve really appreciated my time with him, and hopefully will get more as the world returns to a more normal pace in the future.

January 7, 2021
Changing Values in T-SQL–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Recently I ran across a question posted by a beginner on the Internet and thought this would be a good, basic topic to cover. The question was: how can I replace a value in a comma separated string in a table?

This post covers the basics of this task.

Scenario

Suppose you have some strings in a table, and they contain multiple values. I see this often when application developers serialize some data. For example, I might create a table like this:
```
CREATE TABLE mytable
(   mykey INT NOT NULL CONSTRAINT mytablepk PRIMARY KEY
   , myval VARCHAR(100));
GO

INSERT dbo.mytable
     (mykey, myval)
VALUES
     (1, 'apple,pear,banana')
   , (2, 'pear,peach,melon');
GO

SELECT * FROM dbo.mytable AS m;
```
This has a few rows of multiple values in a field.

Imagine now I need to change pear to grape in all rows. I want a simple solution to do this.

Solution

I have seen some people try to use complex substring calls paired with other functions to do this, but T-SQL gives you a really simple solution. We have a REPLACE() function that allows us to change a string without parsing it.

The simple way to do this is like this:
```
SELECT
      m.mykey
    , m.myval
    , REPLACE(m.myval, 'pear', 'grape') AS newstring
FROM dbo.mytable AS m;
```
Always run a SELECT before an UPDATE, but in this case, I can see that pear has been removed and grape is in its place.

REPLACE() works by passing in a string as the first parameter, then a second string to search for, pear in this case, and finally a replacement. I could then put together an UPDATE statement to change my table.
```
UPDATE dbo.mytable
  SET myval = REPLACE(myval, 'pear', 'grape')
FROM dbo.mytable AS m;
```
If I run this, the results shown above for newstring will replace the myval string for all rows.

SQLNewBlogger

This is an example of a basic type of T-SQL solution that is simple, with a quick explanation. I answered this for someone and then spent 10 minutes writing this up.

A good story to have ready for an interview.
January 6, 2021
A Need for Monitoring without Administration

There was a report recently that a number of US government agencies were hacked through a network management system. Apparently Solarwinds had their code hacked, and this resulted in a backdoor being distributed to customers via software updates.

There is a lot that went wrong here, and this ought to make many system management software vendors very nervous. Attacks on your software developers, designed to allow a hacker to put backdoors into source code repositories is a wild second (or third) order attack. I would certainly be nervous to be a software developer right now, and be extra cautious about any sort of potential phishing email sent to me. Yes, that’s a thing.

The bigger issue, to me, is that monitoring most systems ought to be possible without requiring escalated privileges. While there are some ways to get metrics without requiring administrative rights, most OSes and most administrative and monitoring software expects to have complete rights to all resources.

That’s a hole in design. There are plenty of cases where we want monitoring data (and alerts/notifications) distributed to other automated systems or to interested individuals, but we don’t want to expand the number of administrators. Every additional individual or system that can potentially change something as an administrator is another potential attack vector.

We have built our core operating systems with the idea that someone needs complete control of the system to work with it. For some things, that’s true, but for resource usage, especially in the way that many of us need to watch at scale, I’m not sure that this needs to be the case. My view is that Windows, MacOS, and Linux ought to undertake fundamental design reviews to determine if they can further shrink the scope of privileges for monitoring systems.

In the meantime, granting privileged access to an automated system for monitoring ought to be done very carefully, even more carefully than for human sysadmins. This account will run by itself, and someone might not notice if it is compromised. Set strong, very long passwords, change them periodically, and audit the account to be sure it is only accessing what you think it should access.

Steve Jones

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

January 6, 2021
Daily Coping 6 Jan 2021

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.

Today’s tip is to be kind to the planet. Use less energy today.

This isn’t always easy to do, but I am trying to be a little better about this. As my kids leave the house, with the last one going in a couple weeks, I know that there should be less energy usage around here. I’ve made it a point to shut down my computer more often, and also kill more electronics that don’t need to be plugged in and running. There are some we rarely use, so flipping a power switch on a strip is the best way to reduce some usage.

I’m also trying to make less trips to the gym and store, combining things when I can. Small changes, but lots of small changes add up.

January 6, 2021