Showing posts with label Transparent Data Encryption. Show all posts
Showing posts with label Transparent Data Encryption. Show all posts

Tuesday, December 11, 2012

SQL Live 360 Kicks off Today!

Hello Dear Reader!  Today in beautiful Orlando FL at the Loews Royal Pacific Resort the Live 360 Conference kicks off.  Live 360 combines 4 different conferences into one.  Visual Studio, SharePoint, SQL Server, and Cloud & Virtualization all have their own conferences.  I’m helping to kick off the SQL Live 360 by presenting not once, not twice, but three times today!  

Before we dive into the content I want to say a big Thank You to my wife Silva.  Every time I’m presenting at a conference she’s taking off work to be at home and manage the kiddos.  Without her none of this is possible for me.  Thanks Silva!

“So Balls”, you say, “What are you presenting on?”

Excellent question Dear Reader.  We will be kicking off the day with Trimming Indexes Getting Your Database in Shape, next up is Transparent Data Encryption Inside and Out in SQL Server 2012, and we end the day with the Page & Row Compression Deep Dive in SQL Server 2012.  Conferences are expensive and I want to make sure that you get the most out of your experience.  So to help you decide if you should be spending your time with me, I’m placing the decks and demos online now.  They are also live on the Resource Page.  Attendees should have a copy of all of this information on their conference CD, but just in case you didn’t find it here we go!


If you’re in town for the conference I hope you get a chance to stop by.  Here are the abstracts for each presentation:

Trimming Indexes Getting Your Database In Shape
Indexes are a wonderful thing.  We should be using them, and we should be maintaining them.  But over time our production databases start to look a little pudgy around the mid-section.  Maybe they are a little bloated with Unused Indexes, maybe they have Duplicate Indexes, and possibly even Reverse Indexes.  The first step to fixing these problems it so see if you have them, and if you do the second is to set about fixing them. You could be costing yourself CPU cycles, I/Op’s, and space and never even know it.

Transparent Data Encryption Inside and Out in SQL 2012
Security is a very important part of your job and in how data is utilized.  We have many tools to make data more secure, and starting in SQL 2008 we were able to add Transparent Data Encryption to that list.  Find out What it does and What it doesn’t do, How it effects Read-Only Filegroups, Performance, Compression (Backup and Row/Page), What the X.509 Encryption Standard is and Why you should be careful of what you store and where, and other Advance Features as well as some tips on how to manage it.

Slide DeckDemos

Page & Row Compression Deep Dive with SQL Server 2012
Page and Row Compression are powerful new tools. Page & Row with SQL 2008 RTM, and Page & Row with Unicode Compression with SQL 2008 R2, and Spatial Types in SQL 2012.  We can turn it on, we can turn it off, but we want more!  What are Access Methods and how in the SQL Engine do they affect Compression?  What are the “Gotchas” of Page Compression?  How does Compression differ in the way it treats Non-Leaf & Leaf Level pages?  What additional functionality did we get in DBCC Page, DMV’s, Perfmon Counters, and Extended Events to support our usage of Compression?  How do complex query plans affect Compression?  Come find out!

Slide Deck, Demos


That’s not all, on top of having 3 great sessions to choose from if you’re still not sick of me we’ve got a speaker round table on Wednesday where I’ll be hosting a table on SQL Server 2012 and new features.  While I may not have presented on them there are a lot of great 2012 topics like Columnstore Indexes, Always On Availability Groups, Always On Clustering Improvements, and more that we could chat about.

Great SQL People like MVP’s Jen Stirrup(@JenStirrup | blog), Grant Fritchey (@GFritchey | blog), Allen White (@SQLRunr | Blog),  Thomas Larock (@SQLRockstar | Blog), and William Pearson (@Bill_Pearson) will be there hosting different tables as well!


Okay with that being said, it should be a fun filled week of SQL Learning.  There will be some wonderful night time activities as part of the conference as well. 

  I won’t make it to all of them, Wednesday night is the baby’s Christmas play at her day care can’t miss that!  For the rest of the week though I look forward to getting to see you all.

If you are in town I hope you get to stop by and say “Hi BALLS!”, and have a great time!



Wednesday, May 16, 2012

MAGICPASS Tonight It’s All About Encryption

Tonight I will be live in front of my home town crew at MagicPASS, lead by the one and only Kendal Van Dyke (@SQLDBA|Blog) sponsored by our Friends over at Idera, and located at the Disney Vacation Club in beautiful Celebration Fl.  If the fact that we are just a stone’s throw from the happiest place on earth isn’t enough of a draw for you, then add to that tonight is TACO night at MagicPASS and you’ve got a the cherry on top of the sundae. No Sundae is a Sundae without the nutty topping, and that Dear Reader is me. 

“So Balls,” you say “Nutty topping….Really… Seriously….”

Ahhh…yes, I see.  Well anyway, so tonight I will be presenting on Transparent Data Encryption Inside and Out in SQL 2012.  I’ve given this presentation a couple times before and this year I’ve given it at SQL Connections and just last week at SQL Rally.  If you are interested in this topic this is a great chance to come out and learn without having to spend big buck’s to go do it.  All we need is a little of your time.  So without further ado let’s talk shop.


Transparent Data Encryption is an important topic to learn about.  Chances are if you are a SQL Professional, at some point in time your boss is going to ask you about it.  They will ask you what the Pros and the Cons of it are and the more information you have the better.  So without further Ado here is the abstract:  (If you'd like to Download the entire contents of the presentation and the scripts Click Here for the Slides and Click Here for the Scripts)

Security is a very important part of your job and in how data is utilized.  We have many tools to make data more secure, and starting in SQL 2008 we were able to add Transparent Data Encryption to that list.  Find out What it does and What it doesn’t do, How it effects Read-Only Filegroups, Performance, Compression (Backup and Row/Page), What the X.509 Encryption Standard is and Why you should be careful of what you store and where, and other Advance Features as well as some tips on how to manage it.

I’ve been in shops where we put this on everything, and I mean EVERYTHING.  I’ve also been in shops where we decided not to go with it because of the complication of certificate management and because Physical Security on Servers, Password Management, and Database management were all divided up amongst multiple departments and not all of them would sign off on it.

There is a real world aspect to everything, technology is great but it might not be for you.  I will cover this topic under the hood, breaking out a Hex Editor to look at the un-encrypted and encrypted contents of a backup file, I will discuss and give out scripts to deal with certificate management (backups and automatic deletions), and I will talk about what TDE does and doesn’t do.  If you are interested in the topic at all this should be a lot of fun, because I love questions and this is the perfect setting to ask them.


So if you think you might make it out, please click here toRSVP.  Going to MagicPASS is always a lot of fun, there are great people who work with SQL everyday.  

If you are a professional in North Orlando, Middle & South Orlando, Lakeland, Winter Haven, Plant City, or like my friend Dan Taylor (@DBABulldog|Blog) who drives in from Brandon FL, when you come you will see why it is so important to get involved in a local PASS SQL Server User Group.   You make local friends that can help you with your SQL problems, and maybe join one of our certification study groups, you make connections to your local SQL Community which will pay dividends down the road.

Come for the Community and the food, and stick around for the presentation because we’re going to have a fun night.

Thanks and I hope to see you there,


Thursday, May 10, 2012

SQL Rally Deck's and Demo's Up

Hello Dear Reader!  I'm coming to you live from the wonderful SQL Rally in Dallas Texas.  I have two sessions today, and the Slide Deck's and Demo's are live on the Resource Page.  Feel free to download them and see if you'd like to come join me, or download them and follow along!

"So Balls," you say "What are you presenting on?"

Glad you asked Dear Reader, and away we go!


The great thing about this session is even though we are using SQL 2012 99.999% of this is the same as SQL 2008 & 2008 R2.  So come and learn about TDE and leave with scripts that will help you deploy this if you are interested. I hope you'll stop by at 10:15 am I'm in room 302/303

Here's the Abstract:

Security is a very important part of your job and in how data is utilized. We have many tools to make data more secure, and starting in SQL Server 2008, we were able to add Transparent Data Encryption to that list. Find out what it does and doesn't do, how it effects read-only filegroups, performance, and compression (backup and row/page), what the X.509 encryption standard is and why you should be careful of what you store and where, and other advanced features and management tips.


This is a fun session that is all about learning.  We have demo's and we have fun, but a lot of getting better and advancing your career in SQL Server is knowing the concepts. We won't be Deep Diving but we will touch on ACID, B-Tree's, Transaction Isolation Levels, Transaction Log Internals, Recovery Models, and Backups.  I hope you'll come join me at 4:00 in room 302/303.

Here's the Abstract

The more you know about SQL Server, the more you understand how it works. SQL Server is a product we use every day, and most of us know the big concepts. At the 10,000-foot view, we know what databases, tables, and columns are. But what makes up those databases, tables, and columns? What are records, pages, extents, and allocation units? What are Full, Simple, and Bulk-Logged recovery models? What are the differences between Full, Transaction Log, Differential, and Filegroup backups? What is a piecemeal restore? This is an introduction to these concepts using SQL Server 2012. In this session, you will learn about the internal structure, recovery models, and backups and be better prepared for future learning and managing SQL Server.


I hope you enjoy your day out here there is A LOT of SQL Learning to be had!



Monday, August 15, 2011

SQL Saturday 79 Recap – (THANK YOU SQL SATURDAY 79)

It is hard to believe that SQL Saturday 79 has come and gone.  It was held in the Beautiful Carl DeSantis Building on Nova Southeastern University.  The day started out with a 3:30 am wake up.  I wanted to make sure that I had everything posted on the blog before I headed down so if anyone was looking for downloads or slides I could send them to the Resource Page for it.  After a quick shower and grabbing a cup of coffee for the ride, I hit the road about 4:30 am. 

The trip down is 187 miles from the new house in Winter Haven to Nova Southeastern, with registration starting at 7:30 and the keynote at 8 I wanted to make sure to be there in plenty of time.  It was a nice drive down, I always like watching the sun rise in the car.  I used to see it all time during the drive in to DC & on vacations driving from Virginia to Florida before the move down here.   So with the start of a beautiful day and a peaceful drive, I get to the parking garage and run into my good friend Kendal Van Dyke (@SQLDBA | Blog) on the way into the building. 

After finding registration, finding Coffee, and the restrooms it was off to the speaker room to get a Speaker Shirt.  I don’t normally spend a lot of time in the Speaker room.  I love to meet new people at SQL Saturday’s, but I didn’t make it down for the Speaker Dinner the night before so this was a nice chance to meet some of the other Speakers like Mike Hillwig (@mikehillwig | Blog), Herve Roggero (@hroggero | Blog)  , Bradley Schacht (@bradleyschacht | Blog) and it was a chance to say Hi to others that I’ve met before.  Andy Warren (@SQLAndy | Blog), Mike Davis (@MikeDavisSQL | Blog),  Mike Antonovich (@MPAntonovich| Blog ), Jose Chinchilla (@SQLJoe | Blog), Rodney Landrum (@SQLBeat | Blog), and Thomas LaRock (@SQLRockStar | Blog).

Tom LaRock made my day by remembering me.  I have been very lucky and fortunate to meet some really awesome people in the SQL Community.  There are a lot of people that I’ve gotten to hang out with on a regular basis (point’s to the list of names up above), that I just grin ear to ear thinking about.   It wasn’t that long ago that I set in one of Tom’s sessions for SQL Saturday 49, and later read Noel McKinney’s(@NoelMcKinney | Blog) Blog about it.  At the time thinking I want to get in on this, I want to present, I want to blog, I want to take the next step in my life as a DBA.   And now I say “Hi Tom I’m Brad Ba…”, and he say’s “Hi Brad I remember meeting you before.”  Awesome.

“So Balls,” you say “We get it your there, speakers are there, your gushing like a 14 year old at a Justin Bieber Concert, What about the SESSIONS?”

Well Dear Reader, I missed the first one because I was reviewing all my presentations, cutting some material and adding a little more to others.  But after that, I jumped right in!


Rodney is a SQL MVP and has been working with SQL Server for over 12 years.  There are four letters that describe Rodney’s T-SQL Alphabet Soup, no none of the dirty ones, A.W.L.D.    A is for Awesome, W wait for it, L Ledgend ……. D DAIRY.   Ahhh…..Barney Stintson would be so proud.

I would normally blog details, but Rodney’s Presentation is very interactive and a lot of fun.  He goes one letter at a time and asks the audience to guess what command he will use.  This is a great mental exercise for those of us that use SQL, and it is a lot of fun to participate in.   And it will be one of the presentations given at SQL Saturday 85 J , CLICK HERE to look at the full line up for that great event.

My Early Birds! We had another 15 minutes to fill the room after this picture!

Compression is a great topic.  Pictured above is my room as people started coming in 15 minutes before the session started.  I had one of the best crowds I’d ever had.  We cover a lot when I do this presentation, and I had a great group of people. By the time the presentation got started we had almost filled the room.  People asked questions, we had a really good back and forth.

The main thing that I tell people is that when you apply Compression you do not want to do it blindly.  I have a Monday Morning Checklist on my Resources Page and it lists all the steps you can take to determine the tables in your database that are candidates for compression.   I like to think of Compression the way that you would Indexes.  You wouldn’t just toss an index on a table and never check to see if it effected your query plans/query response times, over head for different operations (updates/inserts), or if the data was actually worth indexing.  And Compression needs the same consideration to make sure you do it right.

Compression is also very Dynamic, with the way that Allocation Units work within tables, Indexes, and Partitions you can apply Compression in a lot of different ways and we cover the full gambit.  This was a great start on the presentations and I want to say a BIG Thank You to everyone who attended this was a lot of fun to present and you were a great crowd!


Lunch was served by Azteca Real Mexican Restaurants and it was delicious.  The line was vast, the picture you see above is after waiting for 30 minutes and then going to the back of the line, the sever table was on the opposite end from me.  There were a lot of people to feed and the SQL Saturday team did a great job of getting everyone fed, with hefty portions. 

Quick thinking on their part led them to open a line on either side of the serving table to get the crowd through as quickly as possible.  I chatted with folks in line, sat with Serge & Javier (two new acquaintances) and talked about the emergence of Business Intelligence, the free training offered by the Great Folks over at Pragmatic Works on a weekly basis, and a whole host of other topics.

With my stomach full it was off to see a SQL Rockstar!


I can’t speak for you Dear Reader, but I’m seeing a lot of Virtualization going on.  There are some databases that will not translate well, that must have physical hardware.  But these databases are normally the exception and not the rule.  We all know that Virtualization is a great way to reduce your footprint, go green, yadda yadda yadda. 

So you’ve gone Virtual, now what.  Now you are still going to have users coming up to you saying “The Application is running slow, can you fix the database?”  And you will still need to ask the same questions.  Slow compared to what?  Do you have baselines, do you know how your server is behaving, Do you know how your Host & Virtualized instance are working in tandem? 

Ah I got you with the last one, and Tom got me as well.  Tom does a great job of stepping through the different pain points that you have, CPU, Memory, Disk I/O, and Network Latency.  These are all very important things to consider, after all they were important when it was just Physical Hardware.  Now you have virtualized Hardware don’t you want to make sure that you know what is going on?

The presentation is shaped around VM Ware.  The terminology is specific to it and if you are running VM Ware and have Server Engineers talking about crazy sounding stuff like ESX Hosts, then you need to know crazy things like VM Ready Time, Reservations, Limits, and Shares.  You need to know that Swapping and Ballooning can be bad, and if you understand how SQL Server uses memory it translates pretty quick.

Tom talks about his (Patten Pending) BMFF Best Metric Friend Forever.  And when it comes to troubleshooting your VM’s these are things you need to know.  If your working with Hyper-V this is still a great presentation to attend because it will get you thinking about the questions you should be asking, and the terminology that you should be learning to better support your environment.


I’ve worked a lot with Transparent Data Encryption.  It is a great technology and was a great addition to SQL Server 2008.  It is a very simple technology to turn on and off.  And like my friend Colossus to the left it affects the Physical Structure only.

During this presentation one of the cool thing we do is taking an unencrypted backup tossing it into a Hex editor and looking at the data you can start to pull out.  The fact is there is a lot of meta data and header information, but there is also all of your other data stored in plain text.

Then take that same database, apply Transparent Data Encryption, back it up, and place that backup in a Hex Editor.  Aside from some header information that cannot be encrypted so that SQL can still read it, the difference is like night and day.

We do that in this session and then I cover some tips on how to automate the backup and on disk management of your Certificates as well as the impact this will have on Advanced Features such as Mirroring and Log shipping, as well as the additional considerations you should account for when planning your DR strategy.

Once again a very interactive crowd, and a very BIG Thank You to everyone who attended!   Some of the most gratifying moments of the day came in the conversations after this session and my next one.


3rd Presentation of the Day we are now in the Home Stretch
This was the first time I had done this presentation.  My good friend Kendal Van Dyke sat in and gave me some great pointers for the next time I do this, and I’m really excited for next time.

I think this is the year where a lot of things clicked for me and I grew the most as a DBA.  I remember reading Paul Randal’s (@PaulRandal | Blog ) about the internal contents of a Page a couple years ago.  And it took me several reads to process it to the point I could make flash cards on it.  This year the internal make up of a Page has become familiar like the view out of a window.   And as I’ve continued on with learning I’ve noticed that there are a lot of things that come up time and time again.

Sometimes I stop and go, “How have I lived my WHOLE DBA life and not known this!”  So this presentation is not about becoming an expert at internals.  It is about getting to the point where you can start that journey.  In it we covered ACID, SQL’s Internal Components, the Data Hierarchy, Transaction Log Internals, Recovery Models, how Recovery Models relate to SLA’s & DR scenarios, the different types of Backups, and what a Piecemeal Restore really does. 

I had a LOT of great questions.  And even though I was able to answer all of them I think a lot of them deserve follow up blogs.  I pointed people repeatedly to the great free resources that are out there, How Do You Learn & Top 5 Reasons You Should Be On Twitter, and encouraged them to do free and paid training where they can.  The point was to better themselves, which is always a good thing.

Another BIG Thank You to everyone who attended this presentation.  I had a great time, and really appreciated all the questions.  After this presentation I had someone come up to me and say "I've been learning on topic X and the way you explained it everything just clicked and I got it."  That is one of the best thing's I've ever been told after a presentation!


And with that Dear Reader we called it a day.  I sat and talked with Kendal, Andy, and Mike and watched as the raffles began.  It was around 5 pm, a little under 12 hours since I had awoke, and I decided to call it a day, with a 3 hour car ride and another 187 miles to home.  I got some Tweets about the after party, and I wish I had made it.  Maybe next year we will make a family vacation out of this, or even better just me and Mrs. Balls, so we can stay a little longer.

This was a great event and a great day despite all the difficulties the team hit during the day (10 speakers no showed the event)!  We had a lot of great people that filled slots, the team worked tirelessly to get it all pulled together, and by the end of the day I saw a lot of smiling faces.

I Can’t wait till next year.  Now off to SQL Saturday 85 in Orlando September 24th J!

As Always Thanks for Reading!



Sunday, August 7, 2011

This weekend Saturday August the 13th, I will be trekking down to beautiful Ft. Lauderdale to speak at SQL Saturday 79.  I applied to speak back in March, and at the time I submitted 5 different sessions. I was brain storming at the time.  I’ve done a lot of presenting on Compression, and I love to present on it, but I had a couple other topics that I wanted to present on as well.  And that is one of the great things about SQL Saturday, it is all about giving Attendees the chance to learn, and giving Speakers the chance to present.  And will I ever be presenting!  I will be speaking not once, not twice, but THREE times this Saturday!

“So Balls”, you say, “What will you be presenting on?”

So Glad you asked Dear Reader J, and away we go!


I’ve presented on this topic at SQL Saturday 62 & 74, SQL Rally, MagicPASS, and Tuesday August 9th at OPASS, the SQL Server User Group for North Orlando.  Compression is a great topic, and is a technology that I believe will only increase in usage.

So often people confusing Compression, with squeezing more into one place.  It is much more like efficiently packing what you have in the best logical order.  Understanding how compression works, and what you should be compressing, and why compression could help you is key to benefiting from the cost of an Enterprise Edition License for SQL 2008 and up.   

I’m presenting a Deep Dive on this topic at the PASS Summit in October, and this presentation is the perfect preparation for that Deep Dive.  If you get a chance to stop by I’d love to have you, here is the abstract for my session.

“Page and Row Compression are powerful new tools. Vardecimal shipped with SQL 2005 SP2, Page & Row with SQL 2008 RTM, and Page & Row with Unicode Compression with SQL 2008 R2. Get an overview into how each version of compression works internally. Learn how your Allocation Units will determine if your data is a candidate for compression. Understand how your tables Update and Scan pattern’s affect the compression types you should consider. And what you should you take into consideration for additional overhead.”

Next up a little Transparent Data Encryption


In the wake of all the hacking scandals that we’ve seen recently, Security is at the forefront of many people’s minds.  If you haven’t had your CIO, Bosses Boss, or your Boss ask you about what you can do to “better secure” your databases, you will at some point.

 If you are paying for an Enterprise Edition License for SQL Server 2008 and above you have Transparent Data Encryption available to you.  It is really easy to enable, but you need to understand how it works, what it does and what it doesn’t do, additional backup considerations, what the impact will be to advanced features, and how this will add to disaster recovery scenarios.   We will cover all of that.  I’ve set up TDE with Mirroring, Log Shipping, on Vendor Databases, and on Custom Databases. 

When we talk about TDE it is physical hardening, like Colossus up above, and if you work with sensitive data this could be a real benefit to you.  I’ve got this chocked full of info.  I love questions so if you’ve got them get them ready because I’d love to help with an answer.  And if I don’t have an answer I’ll research it and blog about it!

Here’s the abstract for this presentation.

“Security is a very important part of your job and in how data is utilized. We have many tools to make data more secure, and starting in SQL 2008 we were able to add Transparent Data Encryption to that list. Find out What it does, What it doesn’t do, how it effects Read-Only Filegroups, Performance, Compression (Backup and Row/Page), and other Advance Features as well as some tips on how to manage it.”
And now a little fun with Internals!

Listing to Paul Randal's (@PaulRandal | Blog) MCM videos, he talks about spelunking in the Database engine. 

As someone who has rock climbed, caved, and generally spent a lot of time hiking around the mountains I really like the mental image of a couple guys lowering themselves deep into the SQL Engine with Hard Hat’s and Light’s on their heads. 

“Hey Paul you ever been here before?”  “Yeah, loads of time check out the granite stalagmites by the Access Methods!”  And if you are looking for spelunking that is what I’m doing, but instead of going caving we are spelunking around the kiddie pool.

Why should you come to this session?  Especially after you’ve just sat through my two other sessions J?  Because Dear Reader I want you to learn.  There is so much to learn when it comes to internals that people can spend weeks, and do, learning about one particular section and still have more to learn.  But as you continue your learning there is a lot of vocabulary you will need to know.

You need to know what the internal Data Hierarchy looks like.  You should know what the difference between a record, a page, an extent, an allocation bitmap, and Allocation Units/IAM Chain’s are.  You should know how your Transaction Log effects your Recovery Model.  You should know the different Recovery Model’s and how they relate to the different type of backups, and how backups relate to Service Level Agreements, SAL’s, and Disaster Recovery, DR.

But unlike any other presentation that I have ever done, this is just a talk, a conversation.  You could do demo’s with this, but that is a lot to squeeze into an hour & ½ .  I want to make sure that when you leave the room you know enough to feel informed.  But that you also know enough, to realize how much you do not know.  And you’re not alone. 

I don’t know it all.  Not even close.  But there are a lot of amazing experts out there that I’m still learning from, and probably will continue to learn from until they retire or I do.  But this will set you up with knowledge you might not have already known, and will make sure you are poised for future learning.  Here’s the abstract.

“The more you know about SQL Server the more you understand how it works. SQL Server is a product we use every day, and most of us know the big concepts. At the 10,000 foot view we know what Databases, Tables, and Columns are. But what makes up those Databases, Tables, and Columns. What are Records, Pages, Extents, and Allocation Units? What are Full, Simple, and Bulk-Logged Recovery? What are the differences between Full, Transaction Log, Differential, or Filegroup backups? This is an introduction to these concepts. In this session you will learn about the internal Structure, Recovery Modes, and Backups and be better prepared to for Future Learning and Managing SQL!”

So what are you waiting for Dear Reader, Click HERE to go register, come up and say “Hi!” and I hope to see you there!



Sunday, March 20, 2011

Thank You OPASS! Transparent Data Encryption Recap

At the start of March I had the great pleasure of being the opening DBA shot for the OPASS meeting.  Microsoft MVP Ken Tucker was the main act giving us an inside look at Silverlight MVVM Framework.

Going to OPASS is like going to the Oscar’s, the stars in the audience are part of the treat!  Everyone had great questions, and they were very interactive.  Jack Corbett (blog | twitter) and Karla Landrum (twitter) had a wonderful venue, Rikka Asian Bistro, for the OPASS meeting.

During my Talk I had promised to post my slide deck and the script that I used for my Demo, so I wanted to do that right off the bat.

Slide Deck – OPASS Transparent Data Encryption: The Lightning Round

Demo for the presentation


So Dear Reader during the talk we covered a couple things.  We covered Why you should use Transparent Data Encryption (TDE), How to setup TDE, how to Disable TDE, and the effects TDE has on your backup and recovery Strategy, the slide deck and demo’s are attached so I’ll let you go through those on your own I’m just going to cover things from a logical level.  We’ll step through each of those, and then we’ll get to some advanced scripts on how to self manage certificates for TDE.


Here is a slide from my deck, and this is the best reason why, if you are a DBA long enough eventually you will find yourself in a situation where a backup is lost, something will get stolen, or something will get hacked.  TDE will not save you from every scenario, but what it will do is make so when you are in this situation you have one more level of protection. 

“But Balls,” you say “This only covers your back-side as the DBA, why would my Boss care about this when it’s me on the line?”   

Well Dear Reader yes it is our back-side on the line, but so is our Boss’s, and the companies.  If you take the point of view of Jerry Macgwire “help me, help you. HELP ME, HELP YOU!”. 

Don’t go and yell at your boss, but you should know them well enough to know to find an approach that they will respond to.  If its ROI, bring up how much companies lose in money because of incidents like these, and future loss of customers profit if customer’s confidence in the business is shaken. 


Get your minds out of the gutter J.  Turning on TDE is very easy we just follow 4 easy steps
  1. In the Master Database Create a Master Key
  2. In the Master Database Create a Certificate
  3. In the User Database to be Encrypted Create a Database Encryption Key
  4. Alter the User Database and Set Encryption On

It’s that easy.  After step 4 an encryption scan will begin on the database.  It is safe for user activity.  The only things you cannot do is alter the database at the Filegroup level.  No dropping, no adding.  While you are encrypting your database, It’s probably not the best time to be doing changes to the underlying file structure, (one thing at a time!).

Keep in mind that the second the 1st user database becomes encrypted with TDE the TempDB will be encrypted as well.


“So Balls”, you say, “I turned TDE on my DEV system, did a demo for my boss, we’re not going to use it, how do I turn it off!”  We’ll Dear Reader it is pretty simple.

  1. Alter All user databases and Set Encryption Off
  2. Drop the Database Encryption Key for each User Database that was encrypted
  3. (To Unencrypt the TempDB)-Restart the SQL Service


When you turn on TDE it affects all Datafiles at rest.  All Data Files, Log Files (only the VLF’s utilized while TDE was turned on), snapshot files, and backup files.  So here are the things to consider.

If you turn on TDE on your Prod system, and you want to do a data refresh to your Dev or Staging environments you will need to restore a copy of the Certificate used to encrypt your Prod databases to your Dev or Staging server where the restore will take place.

If you want to use Log Shipping or Database Mirroring you will need to have the Certificate you use on your Primary or Principal Server on your Secondary or Mirror Server.

So if you have a Database using TDE, if you are using a technology that uses Restore and Recovery then you will need a copy of the Certificates on each SQL Instance where a copy of the Database will reside.

Now keep in mind this doesn’t mean you can’t move data around.  TDE works with data at rest.  That means that if you BCP data out of the database it is in an unencrypted state.  You don’t need to have a copy of the Certificates in your destination. 

So how does this effect your recovery strategy?  You need to backup your Certificates regularly.  You need to make sure that you plan for them like you do your backups.  If you sweep your backups to tape you’ll want to sweep your certificates to tape.  If you back up your databases to disk, backup your certificates to disk.  Keep redundant copies.  If you ever have to restore your database to a different server you will need them. 

I’ll be doing covering Key Management in my next post but all of the scripts you will need are in the download.

Each of these scripts need a little tweeking, but all the information is there.

Local DB, function, table, and encryption.sql (Sets Up a local DB, and TDE, to store passwords used in later scripts)

backup tde certificates.sql (T-SQL Script to automate certificate backups)

delete old TDE certificates.sql (T-SQL Script to automate certificate clean up)

Hope these get you on your way to learning how to use TDE!



Sunday, March 6, 2011

OPASS: Transparent Data Encryption the Lightning Round!

I’ll be presenting at OPASS on March 8th at 6:00 pm CLICK HERE to register to attend the meeting.

This is just a preview presentation so I won’t be deep diving the topic, we’ll just be scratching the surface.  So I’ll start by talking about Transparent Data Encryption, what is it?


Let’s break down the name into its three parts.

Transparent-The process is Transparent to everything going on in the server.  When you turn this on you do not need to make any changes to the way an application access the data.  As far as the Application knows this is the same database it was before it was encrypted.

Data-The data itself is affected. “So Balls,” you ask “’The Data’ is a pretty wide open term, what do you mean by ‘The Data’?”  Great question!  Any way in which SQL Stores Data is encrypted when Transparent Data Encryption is turned on, for the Database that it is turned on for.  This is a Database Level Technology similar to how Mirroring is a Database Level Technology.  Your Datafiles are encrypted and your Log File is encrypted  (VLF’s are encrypted starting when encryption is turned on, VLF’s previous to that will get encrypted when they are overwritten).

Encryption-There is some flexibility in the type of encryption Algorithm that you can select, during my demo’s I’ll be using AES 256 for my encryption level.

Need a little more?  Okay on to the next analogy.


Seeing as how I’m a comic book kind of guy, the best analogy I can give is the X-MAN Colossus.  Transparent Data Encryption is a Physical solution, your .MDF, .NDF, .LDF, .BAK, .TRN, and .DS (Snapshot files) will be encrypted on the disk while at rest.

So on the outside your armor is on, inside (when data is in motion) however your flesh and blood.


“So Balls,” you say “What is this Data in Motion?”  Well Data in Motion just means that the data has been fetched from Disk by the Storage Engine, and is cached in Memory.  So while this is Transparent to all the applications that point to your database, you must keep in mind that SQL Server is an application as well.  And if you have access to read the data before you encrypted it, you have access after.  If you are looking for Transparent Data Encryption, TDE, to provide complete row level encryption after the data is fetched, then you need to be looking at Column level encryption.  TDE will only encrypt data at rest on the disk.


This is the Fifty Million Dollar question, and I’m going to give you a preview of my slides.

When Data is stolen it effect’s not just you the DBA, Your Customer's Identities, your Businesses reputation, and the financial toll to your Business as well.

Nobody wants to be the DBA in any of those headlines.  I’ve been in the situation where there have been security breaches, and every single gap you can cover is one that you are glad you did.  If your server is breached  you could find yourself in a room with a CIO, VP’s, Directors, Civilian Leadership, a General, Base Commander, Congressional Committee,  your Project Manager, or anyone one else in a position of authority.  In that moment what would you like to say?  My database(s) had Transparent Data Encryption on them, or we passed on having TDE on the server for [name the reason, and  you WILL really need to justify it].

 Sometimes this won’t be up to you.  You can recommend it and your business can choose to pass.  All you can do is be ready, because if you stay in the business long enough eventually you’ll be in this situation, *IF you’re lucky enough to catch the breach.

In the presentation we’ll cover what a datafile looks like in an unencrypted vs. an encrypted state, how to turn TDE on and how to turn TDE off.  How it effects the Tempdb, backup compression, and some tips and scripts for self managing TDE Certificates.

I’ll have a follow up post later in the week after the presentation.  I hope to see you there!