Voice of the DBA

Author: way0utwest

Your Job Description

In every job I’ve ever held, there’s been some scope creep in my duties. No matter what I think my job is, I end up handling tasks in very different areas. While bartending, I’ve had to cook food a few times. While managing a network, I ended up performing some software development. As a DBA, I’ve assembled network cables. As a manager, I’ve had to arrange and setup dinner banquets for late working employees. Even at SQLServerCentral, I used to have to make sales calls when I expected to be more of a technical worker.

That’s the sign of a good employee. Someone that pitches in and gets the work done as needed, doing their best if the task is out of their skill area. It’s what I’d expect from most employees, though I’ve never worked with a union. Rules might be different for union employees, though I’d hope most would pitch in in a crisis. I also think this is something I look for when I interview people. I hope to get the feeling from a candidate that they’d be willing to learn to handle tasks outside of their primary position.

I was thinking about this as I read a post about job descriptions. It talks about the mess that many job postings contain, with a wide variety of requirements and duties. I think many of these descriptions, especially in technology are the result of too many chefs working on the final product. From what I’ve seen, most of the descriptions need a mythical candidate in order to meet all requirements. Companies will never find this person and instead compromise their choice based on who applies. So, always apply if you meet 50% of the requirements.

This made me wonder about the descriptions that are out there. Some of them are really crazy, but I think some of the issue is there are people that want to make their jobs sound more important than they really are, just as they may do with their job title. Keeping this in mind, what would you say is your job description? Is that what you’d list in a job posting or would you add more or take away some of the items?

I used to want a more important title, and a more complex job description as I was growing my career. These days I’m less concerned with pumping up my job, but I think that’s part of the cycle of a career. I’m curious where you are in your career and how you might describe what you actually do to others.

Steve Jones

The Voice of the DBA Podcast

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

June 16, 2017
Renaming MDF/LDF Files–SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I would guess many people would run into this situation at some point. A developer or DBA creates a database, then decides to rename it, but the logical and physical names aren’t correct. This post will look at how to do this. A couple of notes and then the process below.

This might not matter to many of you, but in development, I sometimes find I’ll rename a database and then attempt to recreate (or deploy) a new database with the old name. The mdf/ldf files don’t match, and I realize it’s because I’m using defaults.

However, I’d also say this is an issue in a DR situation. If the filenames don’t seem to match, someone might restore the wrong database or the wrong files. Or worse, think the can delete a file on the file system because there’s no database with that name.

Renaming the Database

This is easy. Right click, select Rename.

Then type the name name. In this case, I’m going from WideWorldImporters-SSDT to WideWorldImporters-RR.

That renames the database, but what about the files? If I run this:
```
sp_helpdb ‘WideWorldImporters-RR’
```
I get this:

Not really what I want. I need these mdf/ldf files to be changed. How do I do this?

I can get to the properties for the database and select the “Files” pane to get a list of files. Here I can change the logical name by clicking that field and typing a new name. I’ve done that here.

However, if I scroll to the right to the File Name column, I can’t change anything.

What I need to do is use the ALTER DATABASE command with the MODIFY FILE command. I need to do this twice.
1. Change the physical file name
2. Change the logical file name
Let’s do that. Here’s the code to change the physical name.
```
ALTER DATABASE [WideWorldImporters-RR]
 MODIFY FILE
 (   NAME = 'WideWorldImporters-SSDT_Data',
     FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\WideWorldImporters-RR.mdf'
 );
```
I need to repeat this for the log file and the MOT file. Once I change the names, I get this message.

This is key. If I were to restart my system now, when the database attempted to start and go through recovery, the files would not have been found. Now, I need to change the physical file names.

To do that, I first need to take the database offline.
```
USE master
go
ALTER DATABASE [WideWorldImporters-RR] SET OFFLINE
```
Then I go to the location of the physical files and rename them in Windows Explorer.

Now I bring the database online.
```
ALTER DATABASE [WideWorldImporters-RR] SET ONLINE
```
Once that’s done, I can then use ALTER DATABASE again to change the logical file names.
```
ALTER DATABASE [WideWorldImporters-RR]
  MODIFY FILE (NAME='USERDATA_612671E2',
               NEWNAME = 'WWI_UserData'
               );
```
And run a final sp_helpdb.

SQLNewBlogger

An easy task, with a touch of research in Books Online, but not too difficult. This took me about 10 minutes to do, and since I realized this was a good skill, I took screenshots and saved code as I went.

Then about 10 minutes to write this up.
June 15, 2017
Securing the Things

I would venture to guess that a lot of the data that will be produced in the next few years will come from various “things” that are implemented by companies, users, and governments. We tend to refer to the Internet of Things (IoT) as those computing devices that are small, specialized, and connected computing device that sends data elsewhere. Sensors, special purpose devices, and more make of the IoT spectrum, including many industrial and manufacturing hardware.

Computers grew by leaps and bounds from approximately 250 worldwide in 1955 to one million in 1980 and 30 million by 1986. In 2015, the world saw 238 million computers sold worldwide. If we add smartphones, we had around 120 million sold in 2007 and 1.4 billion sold in 2015. Tablets add to this, but really, the large market is going to be IoT devices. 15 billion connected in 2015 with an estimate of 75 billion by 2025. I would wager that is going to be a low prediction.

One of the major concerns with these devices is security, and with good reason. There have been hacks against many of these devices, and few have been designed or sold with security in mind. Quite a few devices assume a strong network perimeter inside of an organization, but history has shown us that these barriers aren’t always secure. What might be more disconcerting is that many of the hacks originate inside of the network boundary, whether from insiders or compromised devices. There’s a nice write up on the need for security with IoT. Microsoft recently proposed that we regulate privacy and security for the IoT devices.

Many of these devices will generate data that we will use as data professionals. In fact, I expect a tremendous amount of data to be generated. If devices are hacked, we may end up with lots of suspect data. Even devices are secure, they may fail or have intermittent errors. How to we detect errant data and remove it from our data sets? How can we be sure anomalies aren’t just problems or failures in the device (or network)? These will be challenges for us moving forward.

I don’t know that I think specific regulation is required, but I’d like to see some sort of framework devised. I think encryption should be required, as well as guidelines for the security of connections and data management. Perhaps a list of valid technologies could be used, growing and changing over time, including removing older items that might be outdated. For example, DES shouldn’t be allowed in any new deployments, and it really should be removed from old ones, though with some grace period. That means a regular investment in software development and upgrades that evolves our systems over time. Especially with regard to data security.

Steve Jones

The Voice of the DBA Podcast

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

June 15, 2017
A Bad First Day

I’ve started quite a few jobs throughout my career. In college, I worked in a variety of restaurants, changing employers at the start of every summer and again at the start of the school year. At some of those positions, I had a hectic beginning, with the staff shorthanded days and no one to train me, but they needed me to jump in. More than a few bartender jobs started like that, with me jumping in, making drinks, and teaching myself where things were stored. Luckily there weren’t a lot of mistakes I could make that might severely damage the business.

Fortunately the frequency of new jobs has declined over time, though if I changed positions at this point, I’d go through much of the confusion that many new starters have. Where are things, how do I get work done, what credentials and procedures do I use, and more would be the questions on my mind for a few days. Perhaps a DevOps shop might smooth the process out and prevent issues, but I could see how this type of mistake could be made: a new intern used a document to setup a database, and ended up crashing the production database.

There are lots of comments on that post, but essentially a setup document contained production values (url, user, pwd) and a junior developer used those values instead of the output from their local laptop. I common mistake. A document should have no ambiguity, and certainly should never have production passwords inside of it. Individual credentials should always be used to connect to sensitive systems, with some auditing of who connected (and hopefully what they did).

I’ve had developers crash databases before and delete data. If they’re databases I manage, there’s a backup. I’ve never been in the situation where a database I was managing didn’t have some backup. However, I’ve had clients and coworkers managing databases with no backups. Perhaps the situation I felt lots of empathy for was a client that had developers making backups before they deployed updates to a client database. One day an update didn’t work correctly, and it asn’t until a day later they realized. At that point, they couldn’t restore the backup. They called me, only to have me inform them that they were actually making striped backups. Unfortunately, they had deleted one of the stripes, and we couldn’t find it. Data loss, that fortunately didn’t kill the company, but it did cost quite a bit.

There are any number of ways that a person can make a mistake on their first day. To prevent this, a good process should limit the damage that an individual can cause because of ignorance. This might be especially important for anyone that might have access to production data.

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.

June 14, 2017