Voice of the DBA

Category: Blog

  • The Field Museum – SQL in the City 2012 – Chicago

    Chicago is a neat down. My first PASS Summit was in Chicago, back in 1999. My wife joined me with an infant son, and we took in the last baseball game of the season that year at Comisky Park. This year I was hoping to take in a game at Wrigley Field, but the season will be over for the Cubs when I arrive in Chicago on Oct 4.

    I’m arriving for the SQL in the City 2012 – Chicago event, part of our SQL in the City US Tour. We’ll be holding this one at the Field Museum, right near the lake, so guess where I’ll be running? I’ll likely job in San Francisco on Thursday before I leave, so my run along the lake will be a crack of dawn run on Friday, Oct 5. I’m hoping to convince Brent Ozar (b | t) to come with me, but I suspect he might want to sleep in before his session at the event. Maybe Jes Borland (b | t) will come instead.

    Those two, along with the incredible Powershell guru Allen White (b | t), Grant Fritchey (b | t), and a number of Red Gate developers will be speaking with me at SQL in the City 2012 -  Chicago. A bunch of great sessions are packed into one day, teaching you about SQL Server, inspiring you to try new tips and tricks, training you The Red Gate Way.

    2012_London_cThe Red Gate Way is a great location, good content, debate and discussion, answering your questions, and feeding you well. We talk about ways to make your job easier, give you new ideas to consider, and make learning fun. It’s our way of participating in, and improving the community.

    And it’s free. Just register and come down to the Field Museum on Friday, Oct 5, 2012. If you’re really ambitious, follow me on Twitter and plan on a  6:30am run along the lake. It might be cold, so dress appropriately, and we’ll do 2 miles, looking out over the water.

    Register today if you can come. I hope to shake your hand in Chicago.

    This is part of my SQL in the City series, covering a few thoughts on the US tour.

  • Dare to Disagree

    This talk on disagreement and openness is very thought provoking. I tend to agree with it, but at what point does disagreement become disharmony? How do you teach the skills to argue and disagree passionately, but do so rationally and still come to a resolution on how to move forward?

    As an example, it seems in politics we have no problem with argument and disagreement. What we lack (in my opinion) is the critical thinking and civil debate we seem to lack.

    Update: I intended the political comment to be an example of where we disagree quite openly and vocally, but don’t discuss facts. It wasn’t intended as a political statement.

    Dare to Disagree

  • Enable Transparent Data Encryption

    This is one of the things in my Encryption Primer presentation that I don’t demo. It’s really easy to do, and it’s rather mechanical, so I just show the image that has the steps from MSDN and leave it at that.

    However there are a few things I wanted to change, and test, so I thought I’d show my procedure on a local database. I roughly follow the MSDN article, but a few slight items.

    First, use master and create your keys and certificates.

    CREATE DATABASE TDETest
;
GO
USE master
;
GO
CREATE MASTER KEY
 ENCRYPTION BY PASSWORD = 'AReallyStr0ngP@ssword'
;
go
CREATE CERTIFICATE SteveCert
 WITH SUBJECT = 'My DEK Certificate'
;
go
USE TDETest
;
GO
CREATE DATABASE ENCRYPTION KEY
 WITH ALGORITHM = AES_128
 ENCRYPTION BY SERVER CERTIFICATE SteveCert
;
GO

    I created a test database here for another process, and this is roughly the setup. However before I enable the encryption, here’s what I recommend you do:

    USE master
;
go
BACKUP CERTIFICATE SteveCert
TO FILE = 'c:\SQLBackup\SteveCert'
WITH PRIVATE KEY 
(
    FILE = 'c:\SQLBackup\SteveCertPrivateKeyFile',
    ENCRYPTION BY PASSWORD = 'R@ndomP3ssW0rd'
);
go

    Encryption is serious stuff. If you lose this certificate from a server crash, you are definitely not going to be able to open your database or recover your data. Gone is gone, and data loss means data loss here.

    Back up your certificate.

    Quick question: do you know where your backup of the certificate is?

    Once this is done, you can continue on:

    USE TDETest
;
go
ALTER DATABASE TDETest
SET ENCRYPTION ON;
GO

    The encryption is quick on this new, small database.

    Now let’s see if this worked. We’ll add data and make a backup.

    CREATE TABLE MyTable( LogData VARCHAR(MAX))
;
INSERT MyTable SELECT 'This is an encrypted database'
;
GO
BACKUP DATABASE TDETest
 TO DISK='tdetest.bak'
;

    If I go to my backup location and look for this backup, I can open it in an editor.

    encrypt2

    It’s random gibberish. If I run a search for data in my table:

    encrypt1

    I get no results

    encrypt3

    Don’t think this is valid? Run this below and re-search this backup for the string. You’ll find it. This is one thing encryption protects you from.

    CREATE DATABASE NoTDE
;
GO
USE NoTDE
;
GO
CREATE TABLE MyTable( LogData VARCHAR(MAX))
;
INSERT MyTable SELECT 'This is an encrypted database'
;
GO
BACKUP DATABASE NoTDE
 TO DISK='notde.bak'
;

    The database is encrypted, but anything I do with the database doesn’t require code changes, hence the “transparent” nomenclature.

    The value of this is debatable, but I think it’s not a bad feature to implement if you have Enterprise Edition and you need this protection for PCI, HIPAA, or some other regulation.

  • The Embarcadero – SQL in the City San Francisco

    I love San Francisco. It was one of the cities that I thought I might live in someday. My kids have all been there multiple times, exploring museums, crossing the amazing Golden Gate Bridge, visting Muir Woods and more. When I heard SQL in the City was coming to S.F, I had some decisions to make. Where to run and can I see the Giants? I can’t, and time is short, so I’ll do a city run, along the E side of the city on Tuesday evening.

    logoOn Oct 3, 2012, Red Gate brings the SQL in the City tour to San Francisco and I’ll be flying in Tuesday afternoon to get ready I’m getting in a little late, so time will be short. However I’ll run, eat a late dinner, and do a prep for my sessions on Wednesday.

    Grant Fritchey (b | t), Denny Cherry (b | t), Mitchel Sellers (b ), Ernest Hwang, and plenty of Red Gate software developers will be on hand to deliver a full day of training on SQL Server administration, development, and some tips and tricks for smoother developer with Red Gate tools.

    And it’s free. Register and come learn with us.

    sqlstarThe event is done The Red Gate Way, which is first class with a great venue, good food, and even some nice drinks at the end. There are even a few prizes to win.

    It’s the chance to learn about SQL Server, ask questions, debate solutions and meet other data professionals. Our aim is to help you get inspired, solve problems, and make your job easier. We have a variety of sessions on all different aspects of SQL Server, but we’re willing to chat about anything you want to know. Just stop one of us, introduce yourself, and ask away.

    This is a great event and I’m looking forward to meeting a few of you in San Francisco.

    Register today if you can come. I hope to shake your hand in the city by the bay.

    This is part of my SQL in the City series, covering a few thoughts on the US tour.