Voice of the DBA

Author: way0utwest

Post TDE–Getting Unencrypted Backups

I saw a question posted recently about someone that had disabled TDE and was still having issues restoring a backup. This doesn’t seem like that should be an issue, but it can be. A little testing shows how.

Let’s assume I have an encrypted TDE database. If I run a query, I can see the status as 3, which is encrypted.

If I take a backup at this point, the backup will be encrypted, and to restore this on another instance, I’d need to first restore the certificate. I don’t want to do that, so let’s remove encryption. This is a simple command

ALTER DATABASE TDE_Primer

SET ENCRYPTION OFF;

This runs quickly.

If I now query for encryption, I see this.

A one means that this is an unencrypted database, but a DEK (Database Encryption Key) exists. If I were to detach and examine this database file with a hex editor, the pages would be decrypted.

I’ll now take a backup and move that to another instance. Once I’ve copied that over, I’ll try to restore the backup. In T-SQL, I’ll see this:

Why is this? The database was decrypted, as was the backup. In fact, if I open my backup file in a hex editor, I can see row data.

The Problem

When SQL Server goes to restore the file, it reads part of the header. In here, the process must detect the DEK and try to decrypt that key. However, since this new instance does not have the certificate, this doesn’t work and an error is thrown, despite not needing the key since the data isn’t encrypted.

The issue here is the DEK still exists in the source database.

The Solution

Let’s fix this. I’ll return to my first instance and the original database that was TDE encrypted and now is not. I can issue this:

DROP DATABASE ENCRYPTION KEY

Once I do this, it completes quickly. This is a standard DDL command, but one that’s not often used.

Once I do this, I’ll take another backup and return to the second instance. Now when I try the restore, I see this:

If you’re having issues restoring a database that used to be TDE encrypted, try removing the DEK and then backing it up.

December 28, 2016
Back After the Break

Refreshed and back to work today. After a week at Redgate Software for SQL in the City, I took a week to travel in Ireland with my family. In that time, I really ignored almost all email and didn’t manage SQLServerCentral at all.

Well, I did fix one article bug and replied to a couple comments, but mostly I ignored things. Even over Christmas and yesterday, back in the US, I tried to really ignore most work. I spent about an hour doing some SPAM cleanup, but that was early morning because my body clock was still on GMT time.

However, I’m back at it today. Up at almost a normal time (no kids to get to school, so slightly late), I wandered to the office, made coffee, and got back to editor duties.

I feel nice and refreshed. Being disconnected in Ireland for most of a week, I really had a nice chance to relax and not think or worry about computers and technology. I even avoided most Twitter/news on my phone, just getting away.

Now it’s time to get back on track, and I’m a little overwhelmed as I’ve been out of touch for almost two weeks. Feels like I have a lot of editing to do and plenty of scheduling and certainly need to get back to learning more and improving myself.

Hope everyone else had a great holiday break.

December 27, 2016
Syntactic Sugar

There are all sort of features and enhancements that Microsoft can make to the SQL Server platform. If you look around Connect, you’ll see suggestions for improvements, such adding common checks, as well as additions like adding virtual tables. I’m sure many of you would like to see simple things, like Regex added to the T-SQL language. In fact, you might find that some of these small changes, which we can code around or build, should just be added. After all, if Dynamic Data Masking can be added, shouldn’t some other simple features be included, such as helping us solve the “string or binary data truncated” error?

These handy, useful, simple changes to the platform are often called syntactic sugar. These are changes that are simple, many of us could easily code them, but they make development easier. Or even administration in the case of the SQL Server platform. These are not necessarily expanding the power or capability of the platform, but they can make working on the system more enjoyable.

Should Microsoft create more syntactic sugar for SQL Server? Certainly they do at times, but perhaps not as much as many of us would like. The thing to keep in mind is that making changes to the SQL Server platform can be very difficult and time consuming. Adding small features that might be helpful, while enticing, can slow the evolution and development of more core product features. Or they can cause more problems than we might expect in other parts of the platform. Would you rather have something like DDM that makes obfuscating some data easier, or a more robust replication engine that recovers from more problems? Do you want stronger security features like Always Encrypted, or more robust Always On features, or is it more important to get Regex added? I think we might have differing opinions here.

These can be really hard questions, and certainly I think our feedback can help influence Microsoft. After all, if there is a nagging issue that is constantly causing issues, then maybe it’s worth a syntactic sugar improvement, even if this takes resources away from some other area. The one thing I hear from Microsoft over and over is that specific business cases and issues are more important than complaints. Express the issues you have with the platform in terms of workarounds, developer time lost, or specific performance issues rather than just a “I don’t like this” or something “doesn’t work as expected.”

Ultimately Microsoft is a business, and they do look to add new features regularly to the platform to increase sales. I get that, and I try to temper my requests and complaints. I like to see them focus a portion of resources on core improvements to systems that work, and while I think this does happen, I’d like to see a bit more improvement in existing features. Certainly our backup and log reading systems have improved over time. SSMS is decoupled and being updated regularly. There is more work to be done, and if lots of us provide specific feedback, I’m sure we will see even more core improvement over time.

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.

December 27, 2016
New Formatting in #SQLPrompt
I love SQL Prompt. This might be my favorite tool from Redgate Software and just when I thought it couldn’t be any better, a new formatting engine has been released.

Reading through existing source code and understanding the intent is an important part of any software developer’s life. Often, we find ourselves reading code written by other people, which can be difficult to comprehend when the structure and layout is unfamiliar. This is one reason why many companies have had coding standards that not only enforce items such as naming, but also can prescribe whether tabs or spaces are used, indentation prescriptions, and even line breaks. While our industry has some conventions, they are not as widespread as we might hope.

One of the features I find most useful about SQL Prompt is the ability to quickly reformat code in an easy to read fashion. For example, I’ll get code like this from a forum post or a co worker:
```
SELECT  * FROM
  ( SELECT 'Month' = DATEPART(mm, NewsDate), 'Count' = COUNT(*)     FROM News GROUP BY DATEPART(mm, newsdate)  ) a
```
With a quick CTRL+K, CTRL+Y, and then I see this:
```
SELECT  *
 FROM
    (
        SELECT  'Month' = DATEPART(mm, NewsDate)
              , 'Count' = COUNT(*)
         FROM   dbo.News
         GROUP BY
            DATEPART(mm, NewsDate)
    ) a
;
```
That’s much easier for me to read, and thus easier to understand, debug, and improve. Over the years I’ve become used to indentations in certain places, and different types of alignment that can help me understand how a query is structured quicker. I also find certain layout, such as commas first, easier when I try to alter and debug code.

While SQL Prompts formatting engine has worked well, it’s lacked some features that I’ve wanted. For example, I’ve written a lot of code with the Window functions in T-SQL and prior to SQL Prompt v7.3, the formatting would place then entire OVER() clause on a single line. The same thing used to happen with various DDL features as well, meaning that I’d be able to clean up some code, but I’d then go back and add various line breaks to help the code fit on one line and be easier to read.

The Way0utwest Style

That’s changed. I’ve been beta testing a new formatting engine for a few months as a part of the Experimental Features in SQL Prompt, and I love it. In fact, I’ve even added my own style, as you can see below:

This is my custom style, which I’m still working on. On a regular basis, I’ll edit the style, changing some of settings as I find new code that doesn’t quite look right to me. I’m amazed by the incredible array of options, and I find myself impressed with the SQL Prompt team once again.

This hasn’t been an effort for me. I don’t usually go poking too far into the settings or make a concerted effort to get things right the first time. Like many things with Prompt, I just want it to work in the background, correcting code. When it doesn’t format in an easy to read way, I’ll go alter a setting. For example, the first time I got some CTE code, the default style will format things like this:
```
WITH myTally (n)
AS (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM sys.sysobjects
   )
SELECT *
FROM myTally;
```
However, that isn’t quite what I want. I quickly opened the SQL Prompt menu, and I see my formatting options at the bottom:

I have also been able to access this from the Experimental Features menu (you’ll want to keep an eye on this area in the future).

Once in the style editor, I can click the colon after my style name and then edit.

There are a number of options, but CTE is one of these.

Inside this area, I can choose to do a few things. I can choose when to break to new lines, when to add spaces, and where to indent. This is truly a flexible and highly configurable formatting engine.

As I make changes, I can see the new format below with a sample query, or I can check a box to see how my current query will be reformatted. Once I’m happy, I can save the changes and move on to what I need to do: write code.

There are far too many options to work with, and it’s one reason that I don’t bother trying to set all the various settings. In some cases, I’m not even sure how I want the code formatted until I realize that there’s an issue understanding a complex layout. It’s at that point that I’ll go ahead and change some settings to adapt SQL Prompt to my current query. I can even create a new style that inherits the settings from a previous style if I’m experimenting, a topic for another blog.

Formatting for Friends

In my format list there’s a second style I’ve labeled “Grant”. This is a separate style that formats differently based on Grant’s preferences. I asked Grant for his .sqlpromptstylev2 file and added to my system. The default path is %USER_PROFILE%\AppData\Local\Red Gate\SQL Prompt 7\Stylesv2. Once I have his style, I can reformat code the way he wants it if I send it over.

Why? Certainly Grant can reformat code the way he wants it with his own SQL Prompt. It’s a nice touch fo rme to be able to work with code the way that’s easier for me and then reformat it in a way that’s easy to understand if I send a short segment over email, Slack, or some other communication means.

I’ve also worked in situations where code needed to be formatted a certain way. Various clients and employers have dictated that code is strictly laid out in a certain style for code reviews and committed in a VCS in a particular way. By having the option to quickly switch styles in Prompt, I can reformat code for others without too much effort.

SQL Prompt has been one of the more useful and helpful tools I’ve worked with in SQL Server, and just when I thought it might be mature, I get this fantastic formatting engine. It’s been fun to play with across the last few months and now it’s live as a default feature in SQL Prompt 7.3. You can now start playing with your own formatting style when you upgrade.

Or download a copy of my style and see how I prefer to read code: Way0utwest’s Style.sqlpromptstylev2

If you’re not a SQL Prompt customer, you don’t know what you’re missing. This is a vast improvement over the native intellisense and I’d say you should download an evaluation and give SQL Prompt a try today.
December 27, 2016