Voice of the DBA

Author: way0utwest

Using a PoSh variable in a string- #SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

This is something I haven’t quite understood or used often, but I’ve been aware of it and wanted to learn more.

A member at SQLServerCentral wanted to embed a value in a string, and was having issues. In this case, they had this code:
```
$dt = get-date -format "_yyyyMMMdd_HHmss"
Invoke-Sqlcmd -Query "SELECT * FROM [Sandbox].[dbo].[Customer]" -ServerInstance "Plato\SQL2017" |
Export-Csv -Path E:\Documents\sql\$dt.csv -NoTypeInformation
```
In this case, there was an error with the Export-Csv cmdlet, with a syntax issue near the period. I suspected this was some variable expansion that didn’t work.

I found this post that helped me understand a bit more and decided to experiment a bit. Let’s try some things. First, I used to do this type of code:
```
$dt = Get-Date –format “yyyyMMdd”
write-host(“Today is “ + $dt)
```
I then see this:

However, I can use this code:
```
 write-host("Today is $dt")
```
That gives me the same result. Apparently, I can include the variable in the string and it gets expanded. This works with just a string, as shown here:
```
PS C:\Users\Steve> write-host("Today is $dt.csv")
Today is 20191202.csv
PS C:\Users\Steve>
```
Not the error I expected, but this makes more sense with a value that’s needed in a parameter. The blog helps explain this with the following code:
```
PS C:\Users\Steve> $directory = Get-Item 'c:\windows'
PS C:\Users\Steve> $message = "Time: $directory.CreationTime"
PS C:\Users\Steve> $message
Time: C:\windows.CreationTime
PS C:\Users\Steve>
```
An issue. However, if I use the expression evaluation of $() inside, I get this:
```
PS C:\Users\Steve> $message = "Time: $($directory.CreationTime)"
PS C:\Users\Steve> $message
Time: 09/15/2018 00:09:26
PS C:\Users\Steve>
```
That’s the trick I needed for Export-Csv. I used this code in the last line:
```
Export-Csv -Path E:\Documents\sql\$($dt).csv –NoTypeInformation
```
And the code worked as expected.

There’s likely more I should know, but I will start to use varaiables inside strings when I just need the value of the variable as a string. If I need this to better work with some property, method, or parameter value, I’ll use $() around the variable.

SQLNewBlogger

This post was about 20 minutes of me experimenting with a few things and slowly working out how some variables worked. I somewhat wrote this as I was experimenting, adding in the code that ran.

A good example of writing while learning. You could do this on your blog as you learn to work through some code or a feature.
December 18, 2019
Better Protection from Hardware

Microsoft is working on ways to protect your system at an even lower level than the bootloader. Many modern computers have UEFI protection, which allows for security in the boot process, and prevents untrusted code from running. This is intended to ensure that some other boot process cannot run and then invoke your Windows bootloader

This doesn’t protect from firmware issues, but Microsoft may have a solution. Working with hardware companies (Intel, AMD), they have a System Guard Secure Launch that provides a way to secure your OS. There’s a light explanation at Ars Technica of what this means for you. Essentially, this allows the OS to reset the CPU and ensure untrusted firmware code isn’t running

I won’t pretend to know how this works in depth, or what additional levels of security this provides, but I do recognize the problem being worked on here. I also think that criminals (and rogue nation state actors) are making deeper and more complex attacks on systems. We know that compromised code can be a major problem for our servers, and we need better mechanisms to ensure we can trust the platform on which we run our database servers

Just recently there was an alert about a backdoor in SQL Server. This was noted as being in SQL Server 2012 and 2014. At first I was surprised this didn’t get more play, then I realized this was an issue with the Windows OS being compromised and then a patch installed on the SQL Server service to allow attackers to log into the database server. Disconcerting, but if someone gains control of the Windows OS, I’d expect they can get into SQL Server.

Security is a problem with database servers, and the number and variety of attacks continues to grow. It pays to be diligent, and certainly, use whatever tools are available. Strong passwords, access controls and low privileged accounts, UEFI, SGSL, set up every security feature you can. After all, data is your organization’s most important asset.

Steve Jones

Listen to the podcast at Libsyn, Stitcher or iTunes.

December 17, 2019
Unprepared for Travel
I’ve had a month off from travel, which has been nice. It’s been an interesting time catching up on things at home, and a nice break from the disruption to my schedule that travel entails. However, all good things come to an end, and last week I headed out on Thursday morning for another trip, this time to Slovenia and the UK.

I found myself woefully unprepared. A list of things I’ve forgotten:
1. two pairs of bluetooth headphones
2. phone charger cable
3. usb adapter for phone cable
4. two pairs of wired headphones
5. external mouse
6. HDMI wireless adapter
7. gloves
8. belt
9. naproxen
At least I remembered to pack my laptop charger, wallet, and passport, something I haven’t always done. I also did get sneakers packed for the gym.

In the last month, I did spend a few nights in the mountains, and had some long day trips around Denver, during which I’d slowly moved a few things from my laptop bag, or roller bag, to use for a few hours. In the past I’ve been good about putting those things back in bags right away, so I’d be ready to travel. I usually keep my main luggage and laptop bag ready to go, since I travel so much. Often I can just throw a few changes of clothes in the bag and leave.

With a busy week last week, I was slightly worried I might have forgotten something, but I was in a hurry Thursday morning as I packed and didn’t double check myself. I did look for the wallet and passport, since with those I can likely replace anything I need.

When I got to the airport, I realized that I’d left the bluetooth headphones charging on my desk. I’d used one pair for a meeting while cooking, and grabbed another for the gym last week and didn’t put them back. Worse, I’d taken some of the wired headphones from a jacket and bag and used them at different times, getting lazy about putting them back. Same for the phone charger cable. I used that while cooking, and it’s sitting in the kitchen now.

These are minor issues, and I can certainly survive. Fortunately I’ve kept a spare pair of wired headphones and charging cable in my luggage for emergencies and pulled them out. I survived Slovenia without gloves, though it wasn’t that cold. I did have to buy a belt and some pain meds for an injured wrist, and I can live without the mouse.

This is the same type of thing I’ve seen in an office at work, where myself or someone deviates from a routine, gets lazy and then starts taking more shortcuts to get around the other shortcuts I’ve taken. I need to stick to a routine, and certainly adhere to any expectations I’ve set for myself and others. Hopefully I’ll remember to do this in the new year, with quite a bit of time off.
December 17, 2019
The Evolving DBA

The world is changing. Plenty of people have written about the changes in the job of the DBA (DBAKevlar, Brent Ozar), including myself. While lots of vendors might postulate their database removes the need for a DBA, and some might believe that, I think that’s disingenuous. Microsoft doesn’t hire DBAs, but they hide that job under other titles. We still need DBAs, though the job changes a little in the future.

Recently I saw a post from new PASS Board director, Melody Zacharias, where she notes that DBAs are more important than ever. She gives us five reasons, and I tend to agree with most of them. There is a need to somehow keep track of and manage data, much of which will continue to live in relational stores. As much as there is grown for key-value, graph, streaming, document, and other styles of data stores, we still use relational databases for many tasks. I don’t see that going away anytime soon, especially for legacy software that will continue to live for some time.

There is also the aspect of a DBA that involves the cleaning, checking, and verifying that data is in the form and format we expect, not to mention the need to ensure the data is intact and protected, and not changed inappropriately (or corrupt). Every system needs people that can dig through data and help decode its meaning as well as ensuring that business users can get it in to a report of some sort. Those tasks don’t go away. While end users might have more self-service query tools, that doesn’t ensure that they will actually know where the data is or how to reformat it. No end user tool is going to fix mm/dd/yyyy for a user expecting dd/mm/yyyy.

There is also the need to just manage large amounts of data. I know data scientists have struggled to work with large scales of data in the past, and not just because of hardware limitations. They spend an inordinate amount of time gathering and organizing data that is used for analysis or AI/ML operations. Future organizations won’t want to waste their time on those tasks and turn to some sort of DBA to help manage the large data needs of those people performing high level analysis.

The DBA isn’t going away, though the job is changing. Checking logs and backups is likely something you’ll do less of in the future if you work in Operations. Instead, you’ll manage more data, and you’ll certainly need some coding skills to ensure you can perform in GitOps environments, where we don’t click on things, but rather use code to define our systems and the configuration that we expect.

The evolution can be daunting, but it can also be exciting. You have time to learn and change and build new skills. Embrace the opportunity to grow and change in your career as a DBA.

Steve Jones

Listen to the podcast at Libsyn, Stitcher or iTunes.

December 16, 2019