Voice of the DBA

Author: way0utwest

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
Software Has Bugs

Every once in awhile I have someone I meet complaining about software quality, usually with regards to some piece of Microsoft software. I’ll invariably hear how there shouldn’t be bugs in SQL Server or Windows, and Microsoft isn’t testing their software. This person will claim that they write software with no bugs. I can’t remotely believe this is true, aside from small, somewhat trivial applications. All software has bugs, but there’s no way to disprove anyone’s assertions unless you can get to their software and examine it.

I do think that some people think their software is more bulletproof than it really is, perhaps because no one really does test the limits of their work. It’s easy to assume that there aren’t problems when no one has ever probed deeply in the different ways the code might execute. After all, this is one of the reasons that developers turn in code that they think works, but in which others find bugs. They haven’t explored enough different inputs against the software. This is also why I think unit testing needs to be constant and evolutionary, growing and changing as you find new issues and bugs in your code.

Recently a vulnerability was found in the Samba software, and apparently it has existed for seven years. This means lots of software was vulnerable, and everyone should be patching as soon as possible. I don’t know how many people have port 445 open to the Internet, or even open locally, but plenty of people might now know about the issue, so patching makes sense. Scans of the Internet have turned up hundreds of thousands of devices, some of which are running versions that can’t be patched.

This isn’t meant to complain about Samba, but point out that this is code that is open source, lots of people have looked at and developed against, and I would guess no shortage of security people have checked this for potential vulnerabilities. And still a bug slips through.

Security isn’t hopeless, but it’s also hard. Software development, with few boundaries limiting the choices developers can make, allows new interactions among the building blocks of code that have never been seen before. Building code is much, much more complicated than any physical structure because we don’t have any of the inherent limits of the physical environment. This means that we will, and do, struggle to test all of our software.

I don’t think we’ll ever eliminate all bugs, but we can vastly improve quality (as many companies have), by moving to a more DevOps development style where we seek to constantly improve quality as a part of software development. We also have more and more developers also seeking to improve their skills, learning new and better patterns throughout their careers, and hopefully, building better software.

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.

June 13, 2017