Author: way0utwest

Guidance

How do you manage SQL Server? Do you have a list of rules that tell you to do yy whenever xx happens? Or if yy doesn’t solve the problem, you next try zz? Some of you have guidelines like that, though it’s likely that the way you solve problems is somewhat internalized. You adjust your “rules” or “steps” based on the situation. Do you reduce or increase parallelism when you have high CPU? Either change might be the solution, depending on the causes of an issue.

SQL Server is complex. We can’t easily reduce the solutions to problems to a set of rigid protocols that ensure optimal performance. If we could, there truly wouldn’t be as much of a need for DBAs as there is. Consultants and experts are well paid, and often very busy precisely because SQL Server is not so easy to tune and optimize in many organizations.

That’s a bit of the point I saw in Grant Fritchey’s “Be Cautious Offering Guidance” piece recently. When trying to help someone improve performance, or reduce issues, or anything else, you can’t often give someone a rule of thumb that just works. You can’t give them a one sentence solution. Most things in life aren’t that simple, and neither is working on SQL Server.

The environment in which your application runs is complex and there are lots of variables that can affect the advice or guidance that an expert might recommend. It’s the reason we see DBAs often saying “it depends.”

Because it does.

August 20, 2013
Restoring a TDE Database on a New Instance
You’ve enabled Transparent Data Encryption (TDE) on one of your databases, and the server has failed. How do you get this database working on a new instance? This short post will show you how this works.

Files Needed

There are two files you need in order to restore the database.
1. A full backup of the TDE database
2. A backup of the server certificate that protects the Database Encryption Key (DEK).
You might have multiple files for the backup, and potentially other backup files (diff, log), but the process for those will be the same as any other restore once you complete this process.

If you have multiple full backup files (striped backup), just include them in the restore command as you normally would.

Prepare the New Instance

To prepare the new instance for restore, you need to ensure that you have a database master key (DMK) in the master database. You can do this by checking the master_keys DMV.
```
SELECT *
  FROM sys.symmetric_keys
```
If you have a key, that’s fine. If you don’t, you can create one like this:
```
CREATE MASTER KEY 

ENCRYPTION BY PASSWORD = 'sdkj3G3$sh'
```
Now you need to restore the certificate from your source instance. You do this with the CREATE CERTIFICATE command, and the FROM FILE option. You’ll typically find the backup from your BACKUP CERTIFICATE command in the DATA folder for SQL Server if you didn’t specify a complete path.

You do have a backup, right?

For me, I’ll run the create certificate command:
```
CREATE CERTIFICATE TDEPRimer_CertSecurity
 FROM FILE = 'D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\Backup\tdeprimer_cert'
  WITH PRIVATE KEY (
               FILE = 'D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\Backup\tdeprimer_cert.pvk',
               DECRYPTION BY PASSWORD = 'AStr0ngB@ckUpP@ssw0rd4TDEcERT%')
;
go
```
This works

Now I’m ready to restore the TDE database.

I select my backup file, and everything proceeds as a normal restore. How do I know the certificate worked?

Because I have a “Ready” at the top of the dialog.

If I didn’t have the certificate on the instance, I’d get this:

Double clicking that would bring up the error:

If the certificate is not on the instance, then the server cannot decrypt the DEK and restore the database.

Hope this helps, and if you use TDE, make sure you can do this.
August 19, 2013

Opinions and Votes

When you work with a vendor, you may report bugs, issues, or strange behaviors in software. These area facts, and are valuable pieces of information that vendors do care about. When you exclaim that a particular bug or missing feature is critical, you’re stating an opinion. The importance or priority you place on an item is not necessarily the same priority the vendor places on the item.

We see this every day in our work, as clients and customers send us issues with our applications and systems. We triage the items and may or may not fix them in the order the customer would like them fixed. Our management may set priorities that we don’t agree with. It’s a complex interaction that involves many factors, all of which are weighed differently by different people.

However the end user (client, customer, etc.) often doesn’t understand why the priorities are different and why can’t we just see how badly an item needs to be fixed? I see this all the time, though from the end user perspective. I use lots of software, and I find lots of issues and failings, especially in SQL Server. My priorities aren’t Microsoft’s priorities, and I accept that. I provide them with an opinion of what I think, not a vote. If they don’t agree with my opinion, or choose to work on other priorities, I don’t think they aren’t listening to me. I think they disagree with my opinion.

Like it or not, politics is important here. The art of influence is important, and while an individual opinion doesn’t matter, lots of opinions can have influence. Again, however, these are opinions and influences, not votes. We can convince Microsoft to change direction at times, or work on certain issues, but it takes participation and rational, reasonable debate, not childish exaggerated complaints and insults. We want to influence and convince, not upset and anger.

Steve Jones

Video and Audio versions

Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

Follow Steve Jones on Twitter to find links and database related items and announcements.

Windows Media Video ( 16.9MB)

MP4 iPod Video ( 20.8MB)

MP3 Audio ( 4.1MB)

Feeds are available at iTunes and Mevio

To submit an article, rant or editorial,
log in to the Contribution Center

August 19, 2013

Should He Stay or Should He Go?

This editorial was originally published on Sept 29, 2008. It is being republished as Steve is on vacation.

An interesting question. Read page one of this article and then give me your opinion for this Friday’s poll. Would You Fire This Person?

Should He Stay or Should He Go?

Now my first thought on reading the first page was that this is a person that I would fire. I would temper that statement with the fact that I don’t know the person and the description doesn’t really convey what is happening. I’m sure that I’d have different feelings, though I did feel I’ve known a few “Erics” in my career.

As I read through more of the article, I thought that I would probably end up following a lot of the advice given in order to fire Eric. I’d document things, I’ve give him a chance to change and improve. I’d have to spell out specific things that needed to change, though I’d like to think that I wouldn’t have gotten into that situation.

I can work around employees that don’t know things. That’s something you can fix, or try to, and if they can’t learn the things, and I can’t find you a job that matches your salary, then you have to go. However the team fit is a big deal and not having a good team means that I think Eric has to go.

In any case, place your vote in the discussion before reading beyond page 1 of the article.

Steve Jones

Video and Audio versions

Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

Follow Steve Jones on Twitter to find links and database related items and announcements.

Windows Media Video ( 23.8MB)

MP4 iPod Video ( 18.4MB)

MP3 Audio ( 3.8MB)

Feeds are available at iTunes and Mevio

To submit an article, rant or editorial,
log in to the Contribution Center

August 16, 2013