Voice of the DBA

Category: Blog

Using a DMK in TDE
When you setup TDE, you need to create a DMK (Database Master Key) in the master database if one doesn’t exist. This key forms the basis of a chain of protection for your data inside of the SQL Server instance. The hierarchy is:
- The DMK exists in the master database. This protects a certificate or asymmetric key by encrypting it.
- The certificate in turn encrypts the DEK in a database using TDE. This certificate is required to open the DEK in the database, or open the DEK during a restore operation.
- The DEK encrypts the data.
There is only one DMK in each database, so the DMK in master for TDE could be used to protect multiple certificates, asymmetric keys, or symmetric keys that exist in master for use by TDE or any other encryption mechanism.

A particular DMK is not needed to restore a TDE database on another instance. Rather the new instance of SQL Server should have a DMK created in the master database. Then the backup of the certificate is restored on the other instance, protected by the DMK there. Once this is complete, the TDE database can be restored.

If you’d like to know more about what a DMK is, I’ve written about it in a previous post.
February 2, 2017
Speaking at SQL Konferenz 2017

I’m heading back across the Atlantic this February for SQL Konferenz. This is an intimate SQL Server event in Darmstadt (near Frankfurt). I was fortunate to be picked to speak in 2015, but skipped 2016 as the timing wasn’t good. This year, things lined up for me, and I’ll be attending the conference after a short visit to the Redgate offices.

I will be presenting a talk on Database Devops, updated from last year, and looking at ways in which you can get your database into a better software development process.

There are a number of other great sessions on all aspects of SQL Server, both the core engine and BI topics. There are also a good selection of presentations in both English and German. Just for fun, I might go to a German one to see if I can follow along. Perhaps I’ll go see the Cloud Service in Azure one.

You can register today for SQL Konferenz and come enjoy the event with me. See you in February.

February 1, 2017
The 10 Day Cleanse
Disclosure: It hasn’t been 10 days, but I wanted to write something at this point, after 7 days. I know some of you are trying to be more healthy, so I’m posting some thoughts.

Let me cut to the chase, and then some thoughts. First, this is a week for me with no coffee, no alcohol, no soda, and almost no food. I’m amazed I got here. I’ve lost 12+ pounds from when I started.

I’ve been doing the Green Smoothie Cleanse with my wife. For the last seven days, I’ve been subsisting on
1. green smoothies (I get 64oz/day)
2. decaf tea (green and detox)
3. carrots
4. grapes
5. nuts
6. hard boiled eggs
7. peanut butter (and cashew butter, which is really good)
8. water
That’s it. I drink about 4 of these a day:

And these smoothies, usually in 3-4 chunks.

Lovely, huh?

I’m hungry often, but mostly I’m unsatisfied. It’s a mixed bag for me. I’ve lost weight. I think I’m slimmer and healthier, but I also think about food. A lot. All the time.

I also think about how I think about food. I realize it’s comfort for me, and probably need to take less comfort and be more careful.

It’s been an interesting week. Three days to go and then I’ll be back on a semi normal diet. There is a transition with one smoothie and then better eating. I’ll try that a bit, but I spend 10 days traveling for work, which will be tough. At least I’m more aware of what I eat and what I can tolerate with regards to hunger.
February 1, 2017
Disabling #sqlprompt Formatting
I love SQL Prompt, especially the new formatting engine. However, it’s not perfect, and there are times I don’t want code reformatted. One great example is when I write INSERT statements. Here’s what I might write:
```
INSERT dbo.Payments_A
  ( [Month], SerialNumber, DateBegin, DateEnd, paid)
VALUES
  ( 'Mar-15', '0000000000001', '3/16/2015 0:00', '4/10/2015 0:00', 5000.01),
  ( 'Apr-15', '0000000000001', '4/7/2015 0:00' , '4/13/2015 0:00', 0),
  ( 'Apr-15', '0000000000001', '4/10/2015 0:00', '4/30/2015 0:00', 15000.00);
```
I often build a single row, then I might copy/paste to add the other rows and then manually change the data. When I use my reformat command (CTRL+K, Y), I get this:
```
INSERT dbo.Payments_A
(
    [Month],
    SerialNumber,
    DateBegin,
    DateEnd,
    paid
)
VALUES
(
    'Mar-15', '0000000000001', '3/16/2015 0:00', '4/10/2015 0:00', 5000.01
),
(
    'Apr-15', '0000000000001', '4/7/2015 0:00', '4/13/2015 0:00', 0
),
(
    'Apr-15', '0000000000001', '4/10/2015 0:00', '4/30/2015 0:00', 15000.00
);
```
That’s not bad, but it’s hard to read and ends up eating up a bunch of screen space when I try to look at code. What I really want is for that INSERT statement to not be reformatted, even though I may want the spacing and line feeds in other code.

Fortunately, SQL prompt allows me some control. If I highlight my code, I get a little box to the left of the code. There’s a hand with a finger pointing at it in the image below.

If I hit CTRL, I get a drop down (or I can click). In this box, I can type snippets, or words. Note I’ve typed “dis” below, and I see that one of my options is to disable formatting for the selection.

If I select this, I’ll get comments added to my code that SQL Prompt can read.

Now I can hit CTRL_K, Y, and I’ll get all my code formatted, except what’s inside of the comments.

Watch this and a few more SQL Prompt tips from a short video shown at last year’s SQL in the City:
January 31, 2017