Voice of the DBA

Author: way0utwest

T-SQL Tuesday #102–Getting Started
It’s time for another blog party, on the second Tuesday of the month. For this month’s T-SQL Tuesday, Riley Major has a couple of invites. One is on giving back, which is good, but doesn’t fit me.

The second option is to explain how and why we got started, so here goes.

Giving Talks

I started SQLServerCentral with Andy Warren, Brian Knight, and a few others. The site had unexpected success, and running this in addition to all of working full time jobs was tough. However, we built the site as a place where we could share knowledge and help others. In the first year, I’m not sure if I enjoyed writing articles or answering questions more, but both kept me busy.

At some point, the Boulder SQL Server User Group invited me to give a talk. I’d been to the Denver group a few times, but hadn’t really thought about speaking. Bill Wunder was the person that convinced me to share some knowledge live and I went. I’m not sure I did a great job prepping and delivering information, but it must have been fine because I got invited back.

At first I didn’t love the experience. It’s a lot of work and in this case a few people were critically listening to and questioning me on whatever topics I chose.

A couple years later, we talked about doing a session at the PASS Summit. I hadn’t spoken there, but Brian convinced me we should do a debate on a topic and try out a new type of presentation. Looking back, this was really basic, but it was helpful and useful to do a few things.
1. Have someone on stage with me to carry the load and divert some attention away from me.
2. Pick a topic that I felt I knew something about
3. Use the format of a debate, since we argued regularly, I knew we could pull this off.
It went OK, though looking back, this was a rudimentary and unpolished session. Still, people seemed to enjoy it.

Fast forward a few more years. Andy and I had been talking about SQL Saturday and kicked it off. At first I wasn’t sure this would work, and I missed SQL Saturday #1 in Orlando. However, with a few events in other locations, I decided to fly down for SQL Saturday #8.

I can still remember sitting on an airplane, looking out the window as we descended to Orlando being amazed that I was traveling outside my local area to speak. I hadn’t delivered a talk at any conference at this point, so I was a little in awe of the experience.

I had an afternoon talk, so I had the chance to watch others, but was a bit nervous. My room filled up, with all 20-30 seats taken and almost an equal number of people on the floor. Fortunately no fire marshal or administrative people were around to thin the crowd. The talk went well, and I felt confident walking away that I could share things.

More importantly, I couldn’t answer every question, and had some “I don’t know”s or “I’ll check and get back to you” moments. In spite of those, I got great reviews and comments, so I continued on.

From there, I submitted to more events, and different events over time. I’ve been accepted at most, rejected a few, and cancelled rarely. Life does get in the way, and I’ve learned to try and not over-extend myself. I’ve also learned I can say no. I do that more than I’d like, but I’m at peace with my decisions.

I’ve even gotten over rejections. Plenty of large conferences have declined my submissions and even small ones do at times. SQL Saturday #735 – Finland just declined to choose me, and that’s fine. It’s their event, and they should pick the speakers they want, especially if they are local to the area.

The one thing I’ll leave here is that I was terrified to present to groups in high school. I’d be nervous, avoid making eye contact, and try to say as little as I could. Over time I learned I needed to talk to other people in various jobs. Often 1 or 2, but sometimes a group of 5, 6, 10 or so. That’s really a presentation, albeit an off-the-cuff one. I learned that explaining a technique to another sysadmin isn’t far off from talking to an audience. It’s different, but not a lot.

I still worry about new presentations, and I do try to practice them not only walking in circles around the office, but often with a smaller group. I would encourage you to try presenting, usually at work, because these are skills that are useful. If you’ve solved a problem, or fixed something, you can share. Give a 15 minute talk at your user group. You might not like it, and that’s fine.

If you do, however, I’d love to see you start speaking at a SQL Saturday or other conference. I’m more than happy to give up a speaking opportunity if it gets more people to share their knowledge.

Heck, I’ll even co-present with you if I’m at the event. Just ask and I’ll stand in front of an audience with you.
May 8, 2018
Pride in Azure SQL Database

There’s a series on Azure SQL Database from Jovan Popovic on the SQL Server Database Engine Blog. Jovan has written posts on why database management is easier, the scalability of the platform, and a great one that claims the database engine can’t die. I don’t know that I quite believe that, especially as the guarentee is 99.99% availability. I’d expect 100% if you really think the engine can’t die on you. In any case, Jovan clearly has some pride in his work on Azure SQL Database.

What I think is interesting in the post on the ever living engine is this sentence: “Azure automatically handles patching, backups, replication, failure detection, underlying potential hardware, software or network failures, deploying bug fixes, failovers, database upgrades and other maintenance tasks.” This notes that all of these operations are completed in less than 0.01% of the database life, hence the 99.99% guarentee. While that’s not 0, it’s close, and more importantly, this is something that to which DBAs ought to pay attention.

These are often the tasks that many organizations will hire someone to complete. These tasks are becoming less of a time sink as organizations move to infrastructure as code or cloud computing, though they don’t disappear entirely. However, these tasks are mundane, tedious ones in many cases that should be solved once and then deployed easily to multiple instances.

Azure SQL Database and SQL Server share the same code base. Most features get built and tested in Azure and then will get merged into a release for a CU or new version of SQL Server. This means that as Microsoft learns how to better build these features, they will migrate them to our boxed SQL Server versions. With success stories in Azure and strong marketing, I’d bet that more and more management will be questioning whether they need more people, or even any people to handle these tasks in the latest versions of SQL Server.

Don’t panic if you’re a DBA working on SQL Server 2008/RS, 2012, 2014 or other older versions. Those editions still require your time and things will change slowly for plenty of companies. They won’t want to upgrade too many instances at once, especially when there are potential vendor costs as well. You will have a job for some time, and I don’t think that lots of those older instances are disappearing anytime soon.

That also shouldn’t mean that you rest on your existing skills and don’t learn anything new. You ought to be sure you are beginning to learn more about PowerShell, Azure, automatic indexing, and more. Improve your skills and potentially give you more career options. Even if you don’t change jobs, you’ll enjoy the learning.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.2MB) podcast or subscribe to the feed at iTunes and Libsyn.

May 7, 2018
Randomizing Names with Data Masker
Data Masker for SQL Server is pretty cool. It can reduce your attack surface area and exposure under the GDPR or any other regulation. There are plenty or rules and templates to help you anonymize data, in ways you might not have expected. Data Masker is a part of SQL Provision and the SQL Data Privacy Suite.

In this post, I want to show you a common technique that can be used to anonymize names. That’s a fairly common requirement for many datasets, and Data Masker makes it easy.

Last Names

Since Redgate is a UK company, we call last names, surnames. These might also be family names in your culture, but these are the common names shared by everyone in a family.

In any case, there is a series of datasets for last names/surnames. If I look through the list, I see these:
- Names, Surname Suffixes
- Names, Surname Titles
- Names, Surname Titles (Short List)
- Names, Surnames, Random (DE)
- Names, Surnames, Random (PT)
- Names, Surnames, Random (ES)
- Names, Surnames, Random (FR)
- Names, Surnames, Random (Large List)
- Names, Surnames, Random (NL)
- Names, Surnames, Random (Short List)
Each of these is a file with a list of names. The encoding is binary, but if you choose one, you can click the “Sample” button to see some of the entries. For example, here are the Last Names (Large List) sample:

There are country oriented sets and then a large and small list of English names. In this case, I’ll map the large list to my customer_lastname column. I could make these all upper case or choose unique values if I choose. I won’t here.

I could also use the WHERE Clause and Sampling tab to limit the replacements. The default it to replace rows where the value is NOT NULL or empty. I won’t bother and I’ll just create this rule.

First Names

Replacing first names is a two stage process, or it can be if you care. Gender can matter with first names, so the process can be one of two methods:
1. replace gender = ‘F’ with female names
2. replace gender=’M’ with male names.
This is slower as it requires checking in both passes. A better solution might be:
1. replace all rows with female names
2. replace gender=’M’ with male names.
In this case only one set of checks is made in the second step. I’ll do this.

First, I’ll modify the existing substitution rule to add customer_firstname to the list of replacements. In this case, I’ll choose the Names, First Names, Female as the data set.

I’ll leave the WHERE clause alone and save this rule.

Once this is done, I’ll create a new substitution rule. For the dataset, I’ll choose male first names.

Once this is done, I want to limit replacements, so I’ll go to the WHERE clause tab and enter a valid T-SQL clause:

I’ll save this rule and then I see this:

Both rules are listed in order, but there is no linkage. My data masking can potentially run these two rules in parallel. I don’t want that. The second rule depends on the first one having run.

I can add a dependency by dragging the lower rule onto the type of the upper rule. The object behavior is a little funny here, so experiment with “grabbing” the lower rule by clicking on it and holding, them moving it up. You should end up with an indent, signifying dependency.

Now I’ll run the masking set, after querying the table in a window. Once the masking is run, I’ll open a second window in a vertical tab and re-run the same query. You can see the before and after below:

This is a basic look at a multi-stage process to anonymize data in a way that makes sense to your application. Using this technique, I can get production like data in lower environments, without using production data.

Data Masker does some really interesting things as far as cleaning and masking data, so I’d suggest you give this a try as a way to process your lower environments.

If you have questions or want to see other masks, let me know. I also have other articles on Data Masker if you’re interested.
May 7, 2018
Intrinsic or Extrinsic

I was listening to an interview recently that talked about life and why many of us do what we do. It was a piece that also discussed some of the problems with modern US society and some of the potential unexpected consequences of the way that we have evolved in this country. As the discussion between the individuals proceeded, there was one question that stood out to me.

The question was about intrinsic v extrinsic motivators. One example given was playing the piano. If you sit down at home and choose to play because you enjoy it or it relaxes you, that’s an intrinsic motivation. There could be all sorts of reasons why, but essentially you’ve made a choice to participate because you want to do so. If you go play at a bar because you need to make money, or your parents force/push you to play, or something other reason that pressures you, those are extrinsic motivators.

To be clear, one isn’t necessarily better or worse than the other, but they both affect you, as a person, differently. There are also likely a variety of different intrinsic and extrinsic motivations that you have for many of your actions. The world and life isn’t as simple as choices being the result of one of the other. Often our decisions are a blend of both.

Today, I wanted to ask you to think about the reason you’re in your career. I assume most of you are working in technology, and you have various reasons for entering this work, some of which may not be valid anymore. Perhaps you’ve found new reasons to continue to work with data. You don’t have to publicly answer, but think about this.

Why do you work with computers? Because you have to or you want to?

I’m sure this is a blend of factors for you as it is for me. I started with computers because I wanted to. I didn’t have to work with them in school because we didn’t have them at first. Even through much of my university work, computers were not ubiquitous and certainly were not required for most of my classes. I chose to work with computers and technology because I really enjoyed it. Even later, since I needed a career, I chose to work with technology instead of other industries and picked databases, which weren’t my initial choice. I did move to databases primarily for money, though I’d done some development work and enjoyed the database aspect of it.

Today, I do need to work, so I have some extrinsic motivation to continue on this path, but I also do enjoy technology, and I’d like to think that I’d continue to do this type of work even if I could make enough money in another area. My wife is different, and without money pressure, she likely won’t ever come back to technology.

Think about your motivations and pressures today. I’d be interested in how you feel if you’re willing to share. Whether you are or not, take a moment and consider how you really feel about your career.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.1MB) podcast or subscribe to the feed at iTunes and Libsyn.

May 4, 2018