Voice of the DBA

Author: way0utwest

  • 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

    tde_a

    Now I’m ready to restore the TDE database.

    tde_b

    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.

    tde_c

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

    tde_d

    Double clicking that would bring up the error:

    tde_e

    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.

  • 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.
    Steve Jones

    Windows Media Video ( 16.9MB) feed

    MP4 iPod Video ( 20.8MB) feed

    MP3 Audio ( 4.1MB) feed

    Feeds are available at iTunes and Mevio

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

  • 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.
    Steve Jones Windows Media Video ( 23.8MB) feed

    MP4 iPod Video ( 18.4MB) feed

    MP3 Audio ( 3.8MB) feed

    Feeds are available at iTunes and Mevio

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

  • iOS v Android–A Short Term Update

    I ordered my first new new phone this year. I’ve never purchased a new, unreleased model of a cell phone before. I’ve gotten some nice phones, but always 6-12 months after other people have had a chance to use, review, and complain about them. However my wife’s Galaxy S3 was nice enough that I was willing to consider changing from an iPhone (4S) to Android.

    I went to the store and tested a Note 2 and a Galaxy S4, comparing them with the iPhone 5. I liked the S4 enough in some initial toying around in the store after probably 45 minutes across a few trips. I decided to give it a try. I had a lot of friends with the Note 2 that liked the device, but the size worried me slightly since I’m traveling regularly. The S4 seemed like a nice upgrade from the 4S and large enough to use.

    I pre-ordered the phone from Verizon, before it was available, and it arrived a day earlier than I expected it. As with many modern devices, it was fairly intuitive to get turned on, powered up, and switched onto my service. Verizon has made this part really easy.

    Within a couple days I realized that battery life was an issue. I’ve had a Mophie case on the iPhone and that’s allowed me to last through long conference days. I wasn’t getting through days at home (though I was close) with the standard battery. I ordered a 5200mWA battery replacement for the S4, which was cheap and arrived in a few days. It’s thicker than the original battery, but it came with a new back that fit nicely on the device. Not only does the device now last all days, usually with > 20% battery after long days, but it also fits nicely in the same holster that carried my 4S/Mophie case.

    It took me a few days to get used to Android, though much of the setup is similar and as easy as the iPhone. Google Play works as well as the App Store, though it responds much quicker. Background downloads, updates, and notifications are better.

    However overall I have a mixed reaction to the device. I’ll talk about things I like and things I don’t, and give my impressions. I’m not trying to be objective here, or give you deep technical differences. I really don’t care about those. I care about how the phone works as a multi-use device for work and personal life.

    Note that in my past Smartphone experience, I’ve had a Sidekick, a blackberry (original, very briefly), a Windows 6 device, an Android 1.5/1.6 G1, iPhones 3, 3S, 4, 4S, and now this device. My wife has had a G1, G3, iPhone, and newer (non touch) Blackberries I’ve used. I used iOS 4, 5, 6 on devices.

    Likes

    Screen – The screen on the S4 is amazing. It’s way beyond the 4S and G3. No contest. Even for a guy that doesn’t think HD is always worth the money and has no interest in 4K for media, this screen is amazing. At times I’m in awe of it. Blows away any monitor, TV, or laptop I’ve had away, including the brand new Lenovo T430 I have.

    Notifications – I’ve been jealous of my wife’s notifications bar and window for some time. It’s just way better than the iOS stuff. I use it all the time, and it makes it easy to find what’s been happening on my device. I especially love the ability to get to Bluetooth, GPS, etc. at the top. I turn those on/off all the time.

    Keyboard – Swype is amazing. I have tried a few keyboards, but I love the Swype one and it makes it much easier to “type” one handed. If iOS had this I might never have even considered another phone. It’s freaking amazing how well it works and how fast I can compose things. It’s better for short messages than long notes, but it works really, really well.

    Selecting Text – Hands down, this crushes iOS. If I select the wrong spot, I can move the pointer easily. iOS could learn here. I still double tap to paste, and if the text is at the beginning or paragraphs, I sometimes can’t get to the top, but overall it’s great.

    Widgets – Having larger widgets, and not just icons, on screens, is great. I don’t have a lot of needs, and I’ve found lots of junk widgets, but I have a few I like. I hope iOS adopts this.

    Wi-Fi on/off – Dirt simple, and in notifications, I can turn it on very easy with one press.

    Navigation – True GPS, and the turn-by-turn directions are amazing. They warn me of changes, and the display shows me what’s next (turn, merge, etc.) AND shows me what’s after that. In unfamiliar cities, this is very pleasing.

    Google Now – Not perfect, maybe not great, but neat. If I drop appointments for things like hotels, it gives me one click navigation. Weather’s handy, sports scores, it’s a nice service. Saves me time.

    Voice – Better than Siri. I don’t use it a lot, but it works better for me.

    Speaker – I debated where this goes. The speaker is better, but it’s on the bottom, so I need a surface or cupped hand to hear it at times. Mixed emotions, but overall it’s a much better speaker.

    Dislikes

    Touch response – My wife swears it’s good, but for someone that has enjoyed the crisp, immediate response of iOS, it’s not there. It’s good, it’s nothing like older models, including my G1, but it’s still behind the iOS/iDevice integration. My wife thinks Android might be multi-tasking at times, but that’s annoying to me. Sometimes things are slow to select, or they’re hard to select with my fingers, or there’s a delay that causes me to do something else and I get messed up in my phone interactions. After months, I’m still bugged by this.

    Back Button – The “back” button on Android is handy. However on the S4 it’s a soft button. And it’s in the lower right. I can’t tell you how many times my palm has come close enough to this to trigger it. This is especially true when I’m doing things one handed. It’s not constant, but it happens enough to annoy me. This should be a hard button, IMHO.

    Camera – The camera works great, but a few things. One, it doesn’t remember the last mode I selected. Two, when I take pictures, the music stops briefly. A couple annoyances with the way it works, but those are rare, so they’re not worth complaining about. (if you must know, I don’t always know when a picture is taken, the camcorder seems to have a weird delay in starting and I sometimes don’t realize it has/hasn’t started, taps don’t seem as crisp as in iOS).

    Music – The built in music app is crap, but when I loaded all my iOS music (unconverted) onto the SD card, it played it. However the performance, the display, the integration with the OS aren’t great. I’ll also note that I use Spotify, Pandora, Google Play, and they have inconsistent integration with the OS. when I open the lock screen, sometimes I can pause/forward music, most times I can’t. That always worked in iOS. For me, this is a major fail since it can be slow to turn off or pause music. In addition, sometimes when I yank headphones or kill my headset to get music off (like at airport security), it starts playing from the speaker. Maddening.

    Lock Screen – This has been one of the most annoying things. A smartphone needs a security lock. If for no other reason than to prevent a friend from posting something stupid on Twitter/FB, texting, etc. If I enable this, then I lose the ability to get quick access to my camera. I’ve tried a few third party locks, but they are slow to open for the camera. I like taking pictures, and speed matters here. The quick flick up of iOS to take a picture and quick launch worked great. This feels like a generation or two step backwards. This might be the most annoying thing to me.

    App Quality – I’ve probably only installed 20 apps on this phone. There were the 12 or so that I really cared about, and 3 were the reason WP8 didn’t make my list. Most of the apps I’ve installed were copies of apps I had on iOS.

    Quality is worse. I can’t quite explain it, but I think coding is worse, as I have regular crashes of apps. The S4 itself might be to blame as it’s restarted itself in an abend 4 or 5 times since I’ve had it. That never happened on iOS. I probably rebooted with a power off, the 4S once every 4 or 5 months. It just worked and rarely did apps crash.

    I find navigation (in menus) weird in some apps, I find behaviors strange and annoying, and overall quality seems worse. It’s not horrible, and I may have a crash every couple or three days, but they’re annoying.

    As a few examples, Bejeweled crashes every other day, the Kindle app once a week or so. Evernote at times. However the Twitter app sometimes lets me quickly press and get a reply/retweet list, sometimes it doesn’t (see touch response above). If I select a note in Evernote, I can’t press on the note to edit. I need to pick the edit button, then press on the note. I know that’s an Evernote decision, but it affects me, on Android and not on iOS. Things like that are examples of strangeness. Spotify behaves well, but operates in ways that aren’t intuitive for me and I need to think about things. That’s not what I want in a smartphone.

    Screen Rotations – A minor thing, but an annoying part of iOS was that I’ve get 4 or 5 screens of apps and I had to scroll through them to find things. I started searching, but that felt slow as well. My wife’s G3 has 4 or 5 screens, but if you keep flicking to one side, it goes through all of them, rotating from the last to the first, as in a circle of screens. iOS didn’t do this, with a linear layout of screens, and the S4 is like that. Annoying. Going from screen 1 to screen 5 is annoying, both on the S4 and in iOS.

    The Future

    The things the S4 does well, it does really, really well. The things I dislike are little annoyances, but they add up to constant minor irritation for me. I’d trade some of the great things to get rid of the annoyances.

    I’ve decided I’m going to give the S4 to my wife when my 6 month commitment is up. She’s still drooling over it and I don’t have enough of a reason to keep it. In the short term my expectation is that I’ll try the G3 and if I don’t like it, go back to the iPhone 4S.

    However, I am looking at what Apple does with iOS7 and any device updates later this year, and I’m keeping an eye on the Windows Phone devices. I’m tempted to buy a used WP8 device and give that a try. I like the interface from limited experience and the new direction (iOS/Android feel similar) intrigues me.