OPASS: Transparent Data Encryption the Lightning Round!

I’ll be presenting at OPASS on March 8^th 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?

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.

PHYSICAL DATA SOLUTION

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.

DATA IN MOTION

“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.

WHY USE IT

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!

Thanks,

Brad

Automate Deploying Maintenance Plans in SQL Server 2005, 2008, and 2008 R2

Have you ever set up a SQL Server before? If you’ve landed on this post, chances are yes you have! And for most of us we haven’t set up just one SQL Server Instance we’ve set up SQL Server many, many times.  When you have to do a repetitive task you figure out the best way to do the task as quick as you can.  For DBA’s this normally means automating the process, setting up a SQL Agent Job, a Scheduled Task, or using a 3^rd party scheduling software because we’ve normally got other fires to put out.

So when you set up a new server what are some things that you automate?  Normally you have a standard set of Jobs, Alerts, and Maintenance Plans that you want to have in place.  You document them, you put together master Scripts that you will deploy so you don’t have to create them by hand.  But that doesn’t work for Maintenance Plans.

"So Ball's", you say "So I have to make them manually, big deal."  Ahh Dear Reader when your piecemealing them one at a time, it may seem like no big deal.  But let us say that we are upgrading HUNDREDS or even THOUSANDS of servers.  Your going to want to skip as many steps as possible.

I'VE FOUND A NAME FOR MY PAIN AND IT'S BATMAN MAINTENANCE PLANS

If you Create a Maintenance Plan, let’s make a Simple one, and try to script it out you’ll notice you can’t.  So if you take a look at the tables that store the Maintenance Plans In MSDB, you'll find that the Maintenance Task is in varbinary(max) format.  You can try scripting it out, but that is the old fashion way.  

Sometimes we as DBA's get used to doing things the old fashion way, we need to get with the times.

Embracing the New Hotness SSIS!

There's a New Game in Town!  If I imported an SSIS package to your server, and you wanted to place it on another server, how would you go about this Dear Reader.  Why you'd use SSIS!  Scripting out jobs is so SQL 2000, we've grown and so has our technology, so let's treat a Maintenance Plan like we should, it's an SSIS package and we shall use SSIS to migrate it.

If you look closely at the picture you will see I have a SQL 2005 & a SQL 2008 R2 instance on my laptop.  For this demo, I will create a Maintenance Plan in 2005, and using SSIS Export the Maintenance Plan to my 2008 R2 instance.  

Step 1 Create the Maintenance Plan

Name the Maintenance Plan

This will be a simple plan with two subplan's, Integrity Check and Update Statistics.

Let's Save the Maintenance Plan and verify that it created SQL Agent jobs for each subplan.

Step 3 Let's log onto my local SSIS instance (*This could be a remote instance SSIS doesn't have to live on the server where you created your Maintenance Plan)

Because this is local I don't have to Import the Maintenance Plan it is already in my SSIS server stored under Maintenance Plans, and it will be titled what ever you name it.

Step 4 Export the Plan to the desired location

Type in the Server Name that you want to deploy your package to.  Click on the ellipsis button

Click on the Maintenance Plan folder, Click OK

And Click Okay

Here is a before, so you can see there was not Maintenance Plan.

And Here is the After the Maintenance Plan is in place. 

But Wait There's More

If you take a closer look Dear Reader you will find that your Schedule and your SQL Agent Jobs have not been created.  You must manually re-create these.  

No Schedule

And No Agent Jobs.  Creating the Schedule will take some clicking, but the Agent jobs will be created as soon as you open and save the Maintenance Plan.

Plan Owner

The Plan Owner will end up being the Windows Account that you authenticated to SSIS with, in order to set the Maintenance Plan Owner as SA you should run the following script.

SQL 2005

use msdb;

go

update dbo.sysdtspackages90

set ownersid = (

select sid from msdb.sys.syslogins where name = N'sa')

SQL 2008 & 2008 R2

UPDATE msdb.dbo.sysssispackages

SET [ownersid]=0x01

I hope you find this helpful, and it get's you along the way of standardizing your Maintenance Plans in 2005, 2008, and 2008 R2.  (In a couple month's I'll be redoing all this for Denali!)

Thanks For Reading,

Brad

Thanks You Magic Pass!

I was over at the Magic Pass SUG last night and had a blast!  I wanted to share a quick link to the slide deck, here, and the sample code from the final demo where we pulled together all of the information in the presentation.

Pulling It All Together 

To understand where you’re going you’ve got to know where you’ve been, in this way Compression is no different than any other technology.  You cannot just use it blindly; Compression can be a good thing if used properly.  So the final demo I do pulls together everything that I would like people to consider.  (I’m using the AdventureWorks2008R2 Database you can get that here.)

1.       Gather Baselines.

You know your database better than I do Dear Reader, and even though Compression can be a good thing you’ll never know it if you don’t gather baselines.  You should fire up Perfmon and gather at least one week, hopefully you’ve already got more than that, so you can see what your hardware activity looks like.  Apply T-SQL Traces to your system, so you know what the statements being executed look like and how much CPU and Logical Reads they use, as well as the duration.

2.       Find your tables with the largest Row and Data size

The Technology we are using is Compression, the idea being to shrink or compact things.  If you have a 1 page table, Compression is not going to help you.  This is a technology where size matters.

3.       Determine the Allocation Unit Makeup of the table

We cover this in depth in the presentation but Compression in 2008 & 2008 R2 only works on IN_ROW_DATA or HoBTs (Heaps or B-Trees).  So if you have a lot of LOBs, (XML, Text, Varchar(MAX), Varbinary(MAX), NVARCHAR(MAX), IMAGE), or ROW_OVERFLOW_DATA aka SLOBs your table may not be a candidate for Compression.

4.       Scans & Updates – What Kind of Activity do Your Tables Have?

If your Table has a lot of Scan activity but little Update Activity then you may want to Consider Page Compression.  If you have a lot of Update Activity and you are looking to use Compression then Row may be a better fit.  (This is where testing in a non-Production Environment will pay off, know which is best).

5.       Estimate Your Compression

You’ve done all this leg work, and you’ve got numbers to base your decision on.  Now Dear Reader let’s use sp_estimate_data_compression_savings to validate the numbers we’re looking at.  One piece of advice before we run it, the ways this works is a 5% sample of your table is copied into your TempDB and actually compressed (or uncompressed if you want to estimate the space used for uncompressing a compressed table), so before you run this on a 100 GB table make sure you’ve got an extra 5 GB worth of space to spare on your TempDB drive.

6.       Gather another Baseline

Now you find out what your savings are.  And if they are big in Duration and Logical I/O’s saved then make sure to tell your boss, and keep a log of how you just helped speed up your system.

And now here’s the script.

/*============================================================

--@SQLBalls: SQLBalls@gmail.com

--Page and Row Compression How, When, And Why

--

This Sample Code is provided for the purpose of illustration only and is not intended

to be used in a production environment.  THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.

--Scripts For Updates,Scans, and Allocation Units can be

--obtained from

--Data Compression: Strategy, Capacity Planning and Best

--Practices

--http://msdn.microsoft.com/en-us/library/dd894051(SQL.100).aspx

==============================================================*/

/*

First let's switch to an existing

Database

*/

USE AdventureWorks2008R2;

GO

SET STATISTICS IO ON

SET STATISTICS TIME ON

/*

Let's use a Table Variable

and grab all of the sp_spaceused

numbers for each table in the database.

Last let's sort this by size so we can

test out the bigest table

*/

declare @myTable as Table(

                                  [Name] varchar(1000),

                                  [Rows] int,

                                  [Reserved] varchar(500),

                                  [Data] varchar(500),

                                  [Index_Size] varchar(500),

                                  [Unused] varchar(500)

                                  );

insert into @myTable

exec ('sp_msforeachtable @command1="sp_spaceused [?]"');

select * from @myTable

order by [rows] DESC, LEFT([reserved],(LEN([reserved])-3)) DESC;

GO

/*

So we found a table to inspect

let's use our handy script from

earlier to look at the allocation unit

make up of this table

*/

SELECT

     OBJECT_NAME(sp.object_id) ObjecName,

     si.name AS IndexName,

     sps.in_row_data_page_count as In_Row,

     sps.row_overflow_used_page_count AS Row_Over_Flow,

     sps.lob_reserved_page_count AS LOB_Data

FROM

     sys.dm_db_partition_stats sps

     JOIN sys.partitions sp

          ON sps.partition_id=sp.partition_id

     JOIN sys.indexes si

          ON sp.index_id=si.index_id AND sp.object_id = si.object_id

WHERE

     OBJECTPROPERTY(sp.object_id,'IsUserTable') =1

     AND OBJECT_NAME(sp.object_id)='Sales.SalesOrderDetail';

GO

/*

So now that we know what the Allocation Units

Look like how about the Scans and the Updates

Detect Scans

*/

SELECT

     so.name AS tableName,

     si.name AS indexName,

     ios.partition_number AS [Partition],

     ios.index_id AS indexID,

     si.type_desc AS indexType,

     (ios.range_scan_count *100.0/(ios.range_scan_count +

     ios.leaf_delete_count + ios.leaf_insert_count + ios.leaf_page_merge_count + ios.leaf_update_count + ios.singleton_lookup_count)) AS percentScan

FROM

     sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL) ios

     JOIN sys.objects so

     ON so.object_id=ios.object_id

     JOIN sys.indexes si

     ON si.object_id = ios.object_id AND si.index_id = ios.index_id

WHERE

     (ios.range_scan_count + ios.leaf_insert_count + ios.leaf_delete_count + ios.leaf_update_count +ios.leaf_page_merge_count + ios.singleton_lookup_count)!=0

     AND OBJECTPROPERTY(ios.object_id, 'IsUserTable')=1  

ORDER BY

     percentScan;

GO

/*

Check for Updates

*/

SELECT

     so.name,

     si.NAME AS [indexName],

     ios.partition_number AS [Partition],

     ios.index_id AS [IndexID],

     si.type_desc AS [IndexType],

     (ios.leaf_update_count *100/(ios.range_scan_count +

     ios.leaf_insert_count +

     ios.leaf_delete_count +

     ios.leaf_update_count +

     ios.leaf_page_merge_count +

     ios.singleton_lookup_count)) AS [Update_Percentage]

FROM

     sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios

     JOIN sys.objects so

     ON so.object_id = ios.object_id

     JOIN sys.indexes si

     ON ios.object_id=si.OBJECT_ID AND ios.index_id=si.index_id

WHERE

     (ios.range_scan_count + ios.leaf_insert_count + ios.leaf_delete_count + ios.leaf_update_count + ios.leaf_page_merge_count + ios.singleton_lookup_count) !=0

     AND OBJECTPROPERTY(ios.object_id, 'IsUserTable') =1

ORDER BY

     [Update_Percentage] ASC;

GO

/*

Let's Do some Estimating

*/

sp_estimate_data_compression_savings 'Sales', 'SalesOrderDetail', NULL, NULL, ROW;

GO

sp_estimate_data_compression_savings 'Sales', 'SalesOrderDetail', NULL, NULL, PAGE;

GO

/*

Let's run a quick test

how long does an uncompressessed select take

*/

SELECT

     *

FROM

     Sales.SalesOrderDetail;

GO

    

/*

Now let's rebuild with Page

Compression

*/

ALTER TABLE Sales.SalesOrderDetail

REBUILD WITH (DATA_COMPRESSION =PAGE);

GO

/*

Let's run a quick test

how long does an compressessed select take

*/

SELECT

     *

FROM

     Sales.SalesOrderDetail;

GO


Thanks again for having me!

Brad