Showing posts with label TDE. Show all posts
Showing posts with label TDE. Show all posts

Tuesday, May 23, 2023

Tales From The Field Weekly Wrap Up for the Week of 05-15-2023 The Graduate & the Very BIG Week

MY WILL, MY BUG, MY GRADUATE

Just a little under 18 and one half years ago, William came into my life.  There are a lot of words to describe a new baby, but the one surround Will's birth is hard to quantify.  Don't get me wrong.  Joy, happiness, elation, wonder, awe, all-encompassing-love, all of those words were there.
Holding Will's hand on day 3

Eventually.  

Will's birth was the most intense 24 hours, and then week, of my life. 

William was my third child, so having one was not new to me, but this experience was.  Whereas his brother and sister had cried loudly, been weighed, and handed over in warming blankets to be cuddled with; William was rushed away.  He was born into this world with a collapsed lung.  

While I've spoken about this, I've never written about it before.  There is a lot flooding back to me just looking at the picture on the screen. He was born just before Christmas and I've always been thankful to the nurses, the doctors, and other stall at the Sentra Hampton Roads Hospital until we moved we used to bring them cookies every Christmas Eve, remembering those wonderful people there over night with us.  

The Graduate & Family
Let me skip forward in time.  William's lung miraculously reinflated.  After 5 days the oxygen hood came off, we were able to hold him, and feed him.  He came home a bright beautiful bouncing baby boy, and grew so very fast.  We have had lots of adventures.  

My Bug, my nickname for Will, loves roller coasters and amusement parks.  I think he is the only person in the family that loves theme parks as much as I do.  For the last 15 years he has been my roller coaster riding buddy.   

Last Thursday he graduated from High School.  Roller coaster riding buddy indeed.  That first hill was a big one.  He is off to college this Fall, but it feels like only yesterday I was holding that little hand.  One of my favorite quotes comes from the Little Buddhist Handbook, "The trouble IS you BELIEVE you have time".  Yes.  That is the trouble.

Next up, we will head to college orentationnext month.  Before then we will move, because we bought a house.  We've all been doing a lot of work on it getting it ready to move in.  William is leading the way on the painting front, having painted his room and helping is younger sister with hers.  The move is officially underway, not just because of the hours of work, or mounds of things we've already moved.  No, the way I know this is I am sore in places I forgot I could get sore.

THE BIG WEEK

We talked about it last week Dear Reader, but a very big week is upon us.  Microsoft Build starts today.  On Wednesday 5/24 & Thursday 5/25 there will be a massive Digital Event that will be simulcast across the Guy in a Cube, Power BI, and Azure Synapse YouTube channels starting at 9 am PST.

One hour before we will be live on the Tales from the Field YouTube channel.  Adam Saxton (Twitter | @awsaxton) of Guy in a Cube (Twitter | @GuyinaCube), Kal Yella & Bradley Schacht (Twitter | @BradleySchacht) from the Azure Synapse PG, and of course Josh Luedeman (Twitter | @JoshLuedeman), Neeraj Jhaveri (Twitter | @Neeraj_Jhaveri), Daniel Taylor (Twitter | @DBABulldog), Andres Padilla (@Nodestreamio), and myself will be live.  

Here are the links to the pre-show:







MONDAY 5-15-2023
On Monday my friend Daniel Taylor released an AMAZING video.  He walked through the process of taking Ola Hallengren's SQL Server Mainteance Solutions and using them with Azure SQL Database using Azure Data Factory.  


If you have Azure SQL Databases in your environment Azure SQL Database Maintenance Plans with DYNAMIC Azure Data Factory Pipeline!!  It is one of our most viewed videos ever, and Dan is very excited to add on to it!

WEDNESDAY 5-17-2023
On Wednesday I released 5 REASONS TO USE Azure SQL Managed Instance!!  This was our first list.  I had several different videos I've made and will make on Azure SQL Managed Instance.



I don't feel like enough people are aware of the service and what it can do.  If there is an item on the list you want to see a longer video on, let me know we always love the feedback!


TUESDAY 5-16-2023
On Tuesday we had our Azure Data Community Round Table featuring content from the creators in the Community for the Community!  We had a great conversation opening the show about Mother's Day weekend and the activities surrounding it for each of us, plus a Happy belated Mother's Day to all the MOM's out there!!!



As always the star of the show is the content, so here is the content by the WONDERFUL creators by order of appearence on the show:

Honorable Mention

·         Microsoft Digital Event Pre-Show: Azure FastTrack, Power BI CAT, & Azure Synapse Analytics PG   by  Bradley Ball, featuring Adam Saxton & Bradley Schacht & Guy in Cube Twitter @SQLBalls, @BradleySchacht, & @GuyInACube

Neeraj

Scaling strategies for large scale AzureCognitive Services deployments  by RK Iyer

Bradley

·         Announcing General Availability of Azure Data Studio extension SQL Database Projects by Steve Jones Twitter @Way0utWest via @SQLServerCentrl

Andres

·         Getting started with Azure Machine LearningResponsible AI components  by Ruth Yakubu Twitter @ruthieyakubu

Josh

·         Azure Resource Inventory by Claudio Merola Twitter @ClaudioMerola_

Neeraj

·         ChatGPT + Python+ Power BI   by Gabe Araujo, M.Sc. Twitter @AIGenesis3

Bradley

·         Script to Decode SQL Server Locking and BlockingWait Resources   by Eric Blinn Twitter @mssqltips

Andres

·         Pandas, Spark, and Polars - When To Use Which?  by Martin Karlsoon Twitter @HelloKarlsson

Josh

·         A Beginner's Guide to Database Reliability Engineering by Kovid Rathee Twitter @Kovid_R

Neeraj

·         How Microsoft can help you go passwordless thisWorld Password Day by Alex Weinert  Twitter @Alex_T_Weinert

Bradley

·         T-SQL Tuesday 162 – Execution plans according toChatGPT  by Hugo Kornelis Twitter @Hugo_Kornelis

Andres

·          Getting aGrip on Data and Model Drift with Azure Machine Learning  by Natasha Savic & Andreas Kopp

Josh

·         TDE: Database Backups and High Availability  by Matthew McGiffen Twitter @mattMcGiffen



WRAP IT UP
Ok Dear Reader remember today is the kick off of MS Build.  If you haven't registered go do it, it takes a few minutes and it get's you free access to the big show.

On Monday we posted USE Azure Managed Identity to Backup Azure SQL Managed Instance to Blob Storage, NOT SAS Tokens!!  if you are using Azure SQL Managed Instance you want to go give that one a look, this is the way we should be doing our COPY_ONLY backups.

Today is the Azure Data Community Round Table, followed by our live pre-shows on Wednesday & Thursday.  PLUS a very special video drop this Wednesday.

We hope you get to spend some time with us this week and as always Dear Reader, Thank You for stopping by.

Thanks,

Brad


Tuesday, September 10, 2013

T-SQL Tuesday #46 Rube Goldberg Machine aka Automating Deleting Older Certificates




Hello Dear Reader!  This is the second Tuesday of the month and you know what that means, T-SQL Tuesday the largest blog party on the Intrawebs.  T-SQL Tuesday is the brain child of SQL Community member extraordinaire Adam Machanic(@AdamMachanic | Blog), also the inventor of the word “Twote”  as in “To misquote a Tweet”, when used in a sentence it sounds like “He Twoted me wrong”.  This month our host is Rick Krueger(@DataOger | Blog).  So Rick what’s our topic?

My first exposure to Rube Goldberg Machines was playing the game Mouse Trap as a child. I work almost exclusively on the SQL development side of the house, where we sometimes build crazy creative solutions to solve business problems. We generally know the ‘right’ way to do things, but pesky issue like budgets, personnel, and deadlines get in the way. So, we channel our inner MacGyver, grab a handful paper clips and some duct tape, and then do things with SQL Server that we know shouldn’t be done (in an ideal world). And we hope nobody ever finds out how we bent the rules, because we know they will judge us (as we would judge them) and call our work a <gasp>HACK</gasp>.
So, if you would please, dust off one of those skeletons and tell us how you got really creative with SQL Server, instead of doing it ‘the right way’. In other words, tell us about your ugly SQL baby. If you’re worried about saving face, feel free to describe how you would have implemented the solution if you lived in that ideal world.”
I love mouse trap and MacGyver!  Over the years as a DBA sometimes you have to work with what you’ve got.  Other times your boss says do A, you say the best way to achieve A is by doing B & C and they say do A.   I’ve got two of these that I can think of off the top of my head.  One we used Change Data Capture in lieu of Auditing (don’t ask me why, because that version of SQL also had Auditing.  Oh Hello A…..).  The other may actually prove useful.  Which one to choose from?
“So Balls”, you say, “What’s the useful one?”
Good call Dear Reader, we’ll go with the useful one!

OUT OUT D@MN SPOT CERTIFICATE

When you are using Transparent Data Encryption one of the most important things is the certificate.  Once you enable it on a production database that certificate is just as important as your database backup.  Why?  Because in case of a catastrophic failure that backup is dependent on the certificate.  If you cannot restore the certificate to a new instance your backup is useless.  *There are some work arounds to this using backups of the Master DB, but we’ll save that for another day.*

When you look at setting up maintenance plans for your server you should create a job to back up your certificate daily.  A certificate is only 1 KB in size.  Very tiny file.  If you use a private key to encrypt your certificate it is only 1 KB in size as well.  So if you leave a year of them on your hard drive you haven’t taken up 1 MB.

As a DBA sometimes you can be anal retentive a neat freak.  I don’t keep a year’s worth of backups on hand, why would I keep a year’s worth of certificates on hand?  I’d like a process to automatically delete them and only keep the last two weeks on hand, or month on hand whatever matches up with my backup retention policy.

The problem is the automated cleanup task doesn’t work.  Sure you can go in the maintenance plan wizard, make one that looks in a directory for a .CER file, but the true problem lies in the data storage.  You have to custom script out the certificates.  If you didn’t think to add a line to the backup set history table with the extension of .cer and .key and the path to your Private Key or Certificate backups then the job won’t work.

Inserting records into the MSDB tables could work, but as a DBA new to TDE that thought hadn’t crossed my mind.  I wanted a way to back up my certificates and delete my old ones.  So I built one.

MY RUBE GOLDBERG MACHINE

This is a demo I do in my TDE presentation.  It’s up on my Resource Page and has been for some time.  Today I realized I’d never blogged about it.  My scripts heavily use XP Command Shell.  I had an audit setting in my environment that wouldn’t allow that to be on my servers.  So in this script I turn it on in the beginning and off at the end.  The nice thing about the script is I unit tested it and even if there is an error in the script the sp_configure settings are server level commands that occur outside of transactions, so they run no matter what.  The script runs quick, but it will make logged entries in the SQL Server Error log stating that XP_Command shell was turned on and off.  My audit team could live with this so I was able to implement it.
I also like to use a private key and a password for my TDE Encryption.  I don’t want the password sitting around in plain text in the job either.  So I make a database called TDE.  In it I have one table called tdeKeys.  I put two columns in there one is the name of my certificate that a private key will be created for the other is the password to use for that private key.  In secure environments you could set up column level encryption to ensure the password is not in plain text even in the table field.  The demo scripts I’m going to give you doesn’t use column level encryption.  It contains a function that retrieves the Password for the Certificate Name.
Next we will create the dynamic script to back up the certificate.  Note that I backup the Master Key as well.  If you are using column level encryption you’ll want a copy of the Master Key.  You’ll need to specify the path that you want to back up the certificates.  Also you will need to specify the certificate name.
Finally we will create the script that will use xp_cmdshell to transverse directories to manually delete our backups.  You will need to edit the file path in this script and insert the Master Key and certificate names in line 74.  Finally on line 103 you will need to alter the DATEADD function.  Right now it would only keep 4 days of certificates on hand, you’ll need to edit the DATEADD to match up your backup retention policy.

Want to see the whole presentation live?  I’ve done this for Pragmatic Works Training on the T’s, click Here to watch.  You’ve got to sign up for a Pragmatic Works account if you don’t already have one, and you’ll get free info on all the free training we do monthly!


OVERKILL

“So Balls”, you say, “Is this over kill?”
<soapbox>
Well Dear Reader it depends on your environment.  You must consider Level of Effort and Level of Response, LOE and LOR.
LOE is one part you one part the hacker.  The more secure you make something the less likely that a hacker will keep going for it, or how far they will bother to go.  On your part it is how far you are willing to go to do your job.  We can also get dissuaded from going the extra mile sometimes.  Your LOE should be governed by your organizations LOR.
LOR is the response that your organization will have to the event.  One thing I like to tell folks is that if you are ever in the position that your security has been breached, and you are then talking to your boss, his/her boss, the CIO, a high ranking officer in the military, or a/multiple high ranking government official(s).  Trust me when I say that you want to be able to say you took every last step possible to protect the data under your care.  The more detail you can provide the better.  So overkill?  Maybe.  CYA.  Absolutely. Thankful that no fault on your part was found and you still have a job? Yep.
Having been in this position trust me take the extra couple steps, if you ever need it you’ll be glad you did.
</soapbox>
Thanks to Rick for hosting this today, and as always Thank You Dear Reader for stopping by!
Thanks,

Brad

Tuesday, August 14, 2012

Database Certificates and the X.509 Standard



 Hello Dear Reader, I came across an interesting discovery about a year ago and realized I’d never written about it.  I’ve done a lot of work with encryption and mainly Transparent Data Encryption.  I’ve got a presentation that I’ve given on the subject that I presented at SQL Saturday’s, Dev Conection, and SQL Rally.  I take a database, back it up, and drop the unencrypted backup in a hex editor.  This allows me to show what the contents look like before and after encryption.

I encrypt the database and take a backup and I put that in the hex editor as well.  One day in front of OPASS, the North Orlando SQL Server User Group, I dragged the certificate and private key backups in the hex editor as well and I noticed something disturbing.  Part of the encrypted backup of the certificate was in plain text!

“So Balls”, you say, “What does the certificate have to do with the X 5 O….whatever.”

Well put Dear Reader, and the short answer is again everything.

BETTER LOCK THAT $H!+ UP

The X.509 Security standards are the International Technology Union encryption guidelines for Public Key Infrastructure and Privilege Management Infrastructure.  In short these are the smart guys that make up the encryption standards we use in just about everything.   It just so happens that they have some pull over SQL Server Database Certificates as well.

So I was in front of OPASS and I was giving a presentation on Transparent Data Encryption when I made an interesting discovery.  I made all my demo’s and passwords easy so I wouldn’t have to worry which was which,  the password was ‘Brad1’.  Imagine my surprise when I pulled in the encrypted, with a private key and password (aslo ‘Brad1’), backed up contents of the Database Certificate and found laying there in plain text was ‘Brad1’.

It was my own fault for making a demo that used the same thing over and over.  I didn’t know which password leaked.  I went home entered a different value in for each place I had ‘Brad1’, backed up the certificate, and pulled it into a hex editor.  It was the Subject of the Certificate.

DON’T PUT ANYTHING IMPORTANT IN THE SUBJECT

Why would the subject be in plain text?  Good question Dear Reader.  I hopped over to MSDN to look at the documentation on database certificates, click here to view.  I found this information:

                SUBJECT ='certificate_subject_name'
The term subject refers to a field in the metadata of the certificate as defined in the X.509 standard. The subject can be up to 128 characters long. Subjects that exceed 128 characters will be truncated when they are stored in the catalog, but the binary large object (BLOB) that contains the certificate will retain the full subject name.

Nothing about why it was in plain text, but it pointed to the X.509 Security Standards.  Click here to read the X.509 Security Standards if you have trouble sleeping at night. 

The Subject is mentioned quite a bit.  The way it works out, is that the subject is used as part of a trust anchor.  Think of each certificate like a fingerprint.  Each is supposed to be encrypted and different.  Occasionally you have twins, and the certificates are so similar that you need a way to tell them apart.  In the event of that situation the Subject is used to differentiate them.

So while you would think the subject is… well… the Subject of what you will use the certificate for, it is not.  I would generate a strong password and place it in the subject anytime I use a database certificate.  But let’s do a quick demo to show.

DEMO


First we will create a Master Key and a Database Certificate.

Create Master Key Encryption By Password='MasterKeyPass1'
GO
Create Certificate DatabaseCertificate With Subject='Dont Put Anything Importiant in the subject'
GO

Now let’s back them up to disk.  We’ll encrypt the certificate using a private key, and a strong password to encrypt the private key as well.

BACKUP CERTIFICATE DatabaseCertificate TO FILE ='C:\Encrypt\DatabaseCertificate.cer'
WITH PRIVATE KEY ( FILE ='C:\Encrypt\bradprivkey.key', ENCRYPTION BY PASSWORD ='$uper$ecretP@ssword')
GO

You should have 2 files from the backup the Database Certificate and the Private Key.  


Now let’s open up the Certificate in our handy hex editor.



And there it is!  Our subject sitting in plain text, not a huge security leak but for best practices I like to tell people not to put anything important in the Subject.

Thanks for stopping by Dear Reader!

Thanks,

Brad


Monday, August 13, 2012

Database Corruption, Transparent Data Encryption, and Trace Flag 5004


This one comes straight from the email bag.  A friend recently had a problem, they were placing TDE on a database and the encryption scan had stopped at state 2 percent_complete 0. I'm bouncing around the Charlotte NC airport facing some plane delays, and I thought what better time than to play around with a little database corruption. 

“So Balls”, you say, “What does TDE stuck in an encryption scan have to do with corruption.”

Great Question Dear Reader!  The default Page_Verify setting in SQL Server is Checksum.  This means when a page is read into memory and written back to disk a Checksum is calculated based off the pages contents.  When it is written back to disk, that Checksum is there.  When the page is read again the Checksum is used as a validation.  If the Checksum fails then it tosses an error reporting the page as a Suspect Page.

Think of this like going through the TSA Checkpoint, you’ve got your ticket and your identification.  If your ticket says ‘Serenity’, but your ID says ‘Zachary’ you will probably get flagged by the system as Suspect.  In both cases that’s where the probing begins. 

WE’VE GOT A PROBLEM

For this example I’m going to use a database that I’ve corrupted called CorruptAdventure taken from a corrupted version of AdventureWorksDW2008R2.  Horrible name for a database, it was just asking for corrupting.  We’ll start out assuming everything is fine.  The powers that be want TDE, Transparent Data Encryption, enabled on the database and we will do that.  First we’ll create our Master Key and a Database Certificate to use in the encryption.

/*
Create Master Key
and Certificate
*/
USE master
GO
Create Master Key Encryption By Password='MasterKeyPass1'
GO
Create Certificate DatabaseCertificate With Subject='Dont Put Anything Importiant in the subject'
GO

Now we’ll point to CorruptAdventure and create a Database Encryption Key and set encryption to on.  Transparent Data Encryption will read each page into memory.  If it doesn’t have a checksum one will get written.  Our page has a checksum, but it’s contents have been corrupted.  When SQL calculates a checksum to validate the current on, the page will get logged to the MSDB.dbo.Suspect_Pages table.

use CorruptAdventure
go
create database encryption key
with algorithm = aes_256
encryption by server certificate DatabaseCertificate
go
Alter Database CorruptAdventure
Set Encryption on
go

It looks like it is encrypting!

Whoa! We hit our error. 

Let’s query our Suspect_Pages table.  Just like I thought we’ve got our database ID and our page ID.  The error_type column is equal to 2, this means our page was flagged suspect during a Checksum operation. 
/*
It just stalled out
Why would this happen?

A page checksum occurs on all pages
whent the TDE scan
*/
select * from msdb.dbo.suspect_pages



Now let’s run DBCC CheckDB and verify if we really have something wrong with our database.

DBCC CHECKDB(CorruptAdventure) WITH NO_INFOMSGS

Msg 8928, Level 16, State 1, Line 1
Object ID 325576198, index ID 5, partition ID 72057594043498496, alloc unit ID 72057594044940288 (type In-row data): Page (1:3874) could not be processed.  See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 325576198, index ID 5, partition ID 72057594043498496, alloc unit ID 72057594044940288 (type In-row data), page (1:3874). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 325576198, index ID 5, partition ID 72057594043498496, alloc unit ID 72057594044940288 (type In-row data). Page (1:3874) was not seen in the scan although its parent (1:3888) and previous (1:3873) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 325576198, index ID 5, partition ID 72057594043498496, alloc unit ID 72057594044940288 (type In-row data). Page (1:3875) is missing a reference from previous page (1:3874). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'FactInternetSales' (object ID 325576198).
CHECKDB found 0 allocation errors and 4 consistency errors in database 'CorruptAdventure'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (CorruptAdventure).


Just as I suspected corruption.   We got the Database ID and page number from Suspect_Pages and DBCC CHECKDB just verified that the page is indeed corrupt. Now we can find exactly what type of data is corrupted, which will determine our strategy for handling it.  We have the Object ID and Index ID for the DBCC CHECKDB Scan.

We can do a query against sys.indexes joined to sys.objects using the IndexID, 5, and ObjectId, 32576198, provided.  We will get the table name, index name, and index type.

select
     o.name as TableName
     ,i.name as IndexName
     ,i.type_desc
from
     sys.indexes i
     left join sys.objects o
     on i.object_id=o.object_id
where
     o.object_id=325576198
     and i.index_id=5



Our corruption is on a non-clustered index.  If you ever get corruption this is one of the easiest types to fix.  We drop our non-clustered index and re-create it, and it should fix everything.

USE CorruptAdventure
GO
DROP INDEX IX_FactInternetSales_OrderDateKey ON dbo.FactInternetSales
GO
CREATE NONCLUSTERED INDEX IX_FactInternetSales_OrderDateKey ON dbo.FactInternetSales
(OrderDateKey ASC)
GO

Now let’s Run DBCC CHECKDB to get a clean bill of health.

DBCC CHECKDB(CorruptAdventure) WITH NO_INFOMSGS

Excellent, looking at our TDE status it still hasn’t moved. 



The TDE encryption scan should have paused when the Checksum error occurred.  In case it didn’t you can manually pause the encryption scan and reset it with Trace Flag 5004.  Turning Trace Flag 5004 on will stop the encryption scan right where it is.  You then need to turn Trace Flag 5004 off so you can re-issue the encryption command and watch it commence.  You might not need to use Trace Flag 5004, but I like to play this one on the safe side.

DBCC TRACEON(5004)
GO
DBCC TRACEOFF(5004)
GO
ALTER DATABASE CorruptAdventure
SET ENCRYPTION ON

Let’s check our encryption status.



We are progressing again, and it looks like now we’ve completed!  Excellent, not only did we get our database encrypted but we were able to fix corruption that we were not previously aware of.  One last peek at our TDE scan and we see it is complete and our encryption_status is now 3, no longer stuck at 2.



Well my plane just arrived, so that’s all for now Dear Reader, as always Thanks for stopping by.

Thanks,

Brad

Wednesday, March 21, 2012

Spring DevConnections 2012



Hello Dear Reader, this week finds me in beautiful Miami FL.  Next week I’ll be in Vegas, for one night only.  Next week the SQL Server Conference schedule for 2012 kicks off, and how better to do it than in Las Vegas with DevConnections.  Connections could not come at a better time, with SQL 2012 RTM being available for MSDN download and available everywhere else April 1st, (yes I know April Fool’s day you can’t make this stuff up).

Joining the long list of experts speaking at DevConnections is ME!  I’m thrilled to be participating in the Launch Party for SQL Server 2012.  I was honored to be invited to present at SQL DevConnections.  For those of you not familiar with DevConnections it is a twice yearly conference that cover’s Visual Studio, ASP.NET, Sharepoint, SQL Server, Silverlight, HTML 5, Cloud, Exchange, and Windows technologies.

And it doesn’t just cover them, each technology stack is its own conference.  So literally there are 9 conferences for Microsoft Related technologies all happening in one place!   Under one roof!   Technogeeks of the World UNITE, I have found your mecca and it is called Vegas.

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

Excellent question Dear Reader and away we go.


WHAT ARE YOU PRESENTING
http://www.flickr.com/photos/skitteny/4279482815/ 


Since joining Pragmatic Works, see Pragmatic Works has Balls….SQLBalls, I have been very busy with client work, and this will mark my first presentation of the new year!  My subject will be Transparent Data Encryption in SQL 2012!  The When Dear Reader is next Thursday 3/29, 9:45 am.

I will be presenting on Transparent Data Encryption Inside and Out in SQL 2012.  Here is 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 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.

This is a great subject and I’m kicking off the new year by walking through the process, talking about what TDE impacts and changes (not just in your database but in your Disaster Recovery Planning), and taking a quick peak at the internals of an MDF file.



WHY SHOULD I GO
http://www.flickr.com/photos/andrewdbui/5334073051/ 

DevConnections units so many technologies in the Windows stack, it brings out some of the best in the business on all fronts, and the people presenting are amazing.   Paul Randal (Blog|@PaulRandal) and Kimberly Tripp (Blog|@KimberlyLTripp) co-chair the conference, with the level of quality that Paul & Kimberly put into everything you know that this is going to be good.

The people presenting back that up you have Aarron Bertrand (Blog|@AaronBertrand), Steve Jones (Blog|@Way0utwest), Jonathan Kehayias (Blog|@SQLPoolboy), Brent Ozar (Blog|@BrentO), Mike Walsh (Blog|@mike_walsh), Glenn Berry(Blog|@GlennAlanBerry), Grant Fritchey(Blog|@GFritchey), and many many more, to see the full list click here.  When you get speakers this good you know that the content will be amazing and it is,click here for a list of sessions.

And as if that isn’t enough it’s VEGAS!

HOPE TO SEE YOU THERE

My time in Vegas will be brief, I’m a one night only, kind of show.  Right now I’m booked to the gills with client commitments so I will be flying in Wednesday night, and back out Thursday after the presentation.

So if you get a chance in passing feel free to yell out “HI Balls”, you’ll turn a few heads but hey it is Vegas, and I’ll make sure to come over and say Hi right back.

See you there!

Thanks,

Brad

Monday, February 27, 2012

I’m Going to SQL RALLY!




Hello Dear Reader, I’ve just received great news I’M HEADED TO SQL RALLY 2012! But I didn’t get here on my own, I have you to Thank.  And I would like to do just that.  Thank You to everyone who voted for me as part of the recent Community vote!  I really appreciate it.  It is always an honor to be picked to participate in a SQL event, but it means a lot when your peers vote you in.

I promise that you will not be let down, I’ve got not one, but TWO amazing sessions that made it through the voting.

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

Great question Dear Reader, and away we go!


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.


I’ve had a lot of fun presenting on this topic in the past.  Transparent Data Encryption is a wonderful technology that we were able to start using in SQL 2008.  I was lucky very early on that as soon as I started working with 2008 I was working with TDE. 

When you use TDE there are some very important things to know and consider the first is what TDE does and doesn’t do, the second what physical changes actually occur within your database, and finally how you manage certificates and how they affect your recovery scenarios for your databases.

I’ve done a Lightening round version of this presentation for OPASS, the Orlando SQL Server User Group, my friends at Publix, and for SQL Saturday 79 South Florida this past year.  This year I start off the year with a Bang presenting on this topic at SQL Connections in Las Vegas, and now I’ll get to present on this at SQL Rally 2012 as well!


SQL Internals, Recovery Models, and Backups! OH MY!

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?  What is a Piecemeal Restore?  This is an introduction to these concepts using SQL 2012.  In this session you will learn about the internal Structure, Recovery Models, and Backups and be better prepared to for Future Learning and Managing SQL!


When I first put together this presentation it was meant to be a leap frog session. 

“So Balls,” you say “I’m not playing leap frog with you.”

No worries Dear Reader, I’m not playing a game, but what I mean by leap frog is I want you to leap ahead.  As I’ve studied SQL Server I’ve noticed that there are some fundamental concepts that you encounter over and over again.

The reason because they are all interrelated.  A.C.I.D., Transaction Isolation Levels, the internal components of SQL Server’s Relation & Storage Engine, B-Tree Structures, Pages, Allocation Units, Transaction Log management, Recovery Model’s, and Backups (that’s a mouth full OH MY!).  I’m not going to make you an expert, but I’m going to arm you with knowledge and concepts to allow you to go forth and be better prepared for future learning. 

But what we cover will apply directly to how you would choose the type of backups that are appropriate for the database systems that you manage.

THANK YOU

Once again Dear Reader and Dear SQL Community, I would just like to say Thank You.  I hope to see you and I hope to see you at SQL Rally 2012!

Thanks,

Brad