Voice of the DBA

Author: way0utwest

Daily Coping 11 May 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 drink an extra glass or two of water today.

Denver is a high desert, so this matters, but really this is something most of us could be better at in our lives. Our bodies could use more water, it refreshes is, cools us, can make us work (drink cold water), and can slow down our other eating and drinking.

I used to drop 4 large water bottles on my desk every morning. Since I’ve started doing some things outside and going to the gym more, I’ve gotten out of the habit. This tip was a good reminder to me, and I drank and extra bottle, trying to get back to my gallon-a-day habit.

May 11, 2021
T-SQL Tuesday #138–Technology Changes

This month the topic for T-SQL Tuesday comes from Andy Leonard, whom I reached out to be a host. I was running low on hosts last year, and Andy agreed to help me out. I had expected something on ETL, but I was surprised with this one.

Andy asks us about managing technology change. He does mention how SSIS changed as he was writing a book, meaning that things he’d learned while a project was underway no longer applied. That’s a scary place to be, and it’s one I’ve rarely experienced.

Avoiding Shifts

One reason I don’t like to test beta software, especially from Microsoft, is that the APIs, the functionality, the effects can change. I prefer to have something fairly well settled before I spend time on it.

I took a chance early on with Windows 7, when they had the “skydrive”, which become OneDrive. I could sync files from my desktop to laptop, and for someone that travels a lot, this was fantastic. It worked through the beta and until a few years later. At some point it stopped and I moved to Dropbox. I still use DropBox, even though this was reborn as OneDrive and enhanced to allow me to have personal and business “OneDrive” running at the same time.

Moving on from Immature Software

I don’t like to abandon software, and sometimes I can’t. For me, I’ve been involved with some of the products that Redgate Software has built. I often see things early and test them to give feedback to developers. This means I sometimes see things before they work well, or even before the development teams might know what they want to build.

This has happened a few times, but a couple years ago we were evolving one of our products to expand capabilities for customers. I thought we were moving in a good direction and spend quite a bit of time evaluating how the software worked. I was even starting to incorporate this into some automation pipelines and build demos, when the product direction shifted.

A fair amount of code I’d written had to be abandoned. I felt as though I’d wasted some time, but as I stopped to think, I realized that I’d been learning and growing in this area. I’d understood how things worked, and while I had to write new code, I was able to write it quicker because I understand the general problem space already. Even though I had to expend other effort, I’m not sure my early work was wasted.

Fortunately, I didn’t have deadlines for a deliverable. If I did, I might have been more upset. If it happened in the future, while needing to work more to get things done might annoy me, I’d likely understand that the growth and knowledge mean I don’t have to double my investment, and likely I’d be better positioned to write better code the second time around.

May 11, 2021
Basic XML Queries–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I ran across a question recently on querying an XML document. While I think XML is a pain and it’s not the future, there is a lot of it out there that you might need to deal with in a database. Legacy stuff will be there for awhile.

In any case, someone was struggling with this code.
```
DECLARE @x XML = 

'<?xml version="1.0" encoding="UTF-8"?>
    <PartyID>
     <PartyID>147</PartyID>
     <CampaignID>
       <CampaignID>1</CampaignID>
       <Arc>A</Arc>
       <TicPosition>2</TicPosition>
     </CampaignID>
     <CampaignID>
       <CampaignID>1</CampaignID>
       <Arc>A</Arc>
       <TicPosition>13</TicPosition>
     </CampaignID>
   </PartyID>'
SELECT

Data.Col.value('(./PartyID)[1]', 'int') As Party_ID,

Data.Col.value('(./CampaignID)[1]' , 'int') As Campaign_ID,

Data.Col.value('(./Arc)[1]', 'varchar(1)') As Arc,

Data.Col.value('(./TicPosition)[1]', 'varchar(10)') As TicPosition

FROM @x.nodes('/PartyID/CampaignID') As Data(Col)
```
The person got results where the Party_ID was NULL. Some of you might get what’s wrong, but it’s a question of understanding your context.

In this case, the FROM clause helps us understand this. When we specify the node() method, we choose a path in the document. The path we pick is PartyID/CampaignID. This puts us here in the document:
```
    <CampaignID>1</CampaignID>
       <Arc>A</Arc>
       <TicPosition>2</TicPosition>
     </CampaignID>
     <CampaignID>
       <CampaignID>1</CampaignID>
       <Arc>A</Arc>
       <TicPosition>13</TicPosition>
     </CampaignID>
```
If we are trying to specify paths on the current position with the period (.), we can only see these values. There is no PartyID here.

However, similar to a folder navigation from the command line, if I use two periods (..), I move up one level. From here, I can get the PartyID. Therefore, my code is:

SQLNewBlogger

As soon as I saw this question, I knew the issue. It was a good reminder to me to watch the path, which is why I thought this was a good thing to post about. It cements this in my memory.

In 10 minutes, I did this, just as you could.
May 10, 2021
Checking Up on Vendors

Many years ago I was in the Denver airport, waiting to board a plane. At the time I was consulting with a small startup company and they called me in a panic, having had an issue with their database deployment and needing to restore the previous version. However, they were getting the “The media set has 2 media families but only 1 are provided. All members must be provided.” error. Fortunately, I knew what was wrong and verbally walked them through the process. When I landed, I logged in and verified things were working and that they’d followed my instructions.

A few years later I was shopping for furniture with my wife one Saturday night and I got a call from a fellow Operations person at JD Edwards. They had some security issue with a SQL Server, and since I was the SME (subject matter expert), they called me for help. Sitting in a comfy lounge chair (which I never purchased), I helped them solve the issue. Later, I verified things were working.

In these cases, I was using remote hands from trusted individuals at an organization. However, these days, many people call cloud vendors (or other hosting companies) for the same type of remote hands support, but without necessarily having the accountability from that staff. Sure you can check if they solved an issue, but are you sure they didn’t read some data or change anything else?

It’s a concern, but it’s one that I think can be solved with help from vendors. Microsoft recently announced there’s an audit capability for Azure SQL Servers, allowing customer to determine what actions Microsoft support engineers might take on your system. Since we may not necessarily see what is happening when someone else is working on our PaaS or IaaS systems, it’s good to have something tracking their moves. I don’t know this gathers all queries run, but it certainly should.

I’ve seen some presentations on how customers have to authorize engineers in VMs with Customer Lockbox, and the technical details are interesting. These are time limited and audited access controls to ensure that customers are aware when a Microsoft employee touches their systems. I don’t know if this is comprehensive enough, but it is a good start.

Over time, more and more of us have to delegate some work to others, either inside or outside of our organization. We need strong controls and good tools to ensure that we can determine what happened, especially in the event of some incident taking place. Building these controls is important, and really, they should be easily enabled in all our systems. While I don’t know we have a lot of rogue employees accessing things, we do have some, and we have more that make mistakes. Auditing their actions should be something we can easily do.

Steve Jones

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

May 10, 2021