SQL Rally Q&A: Where is the SQL Engine does Compression Occur

This is another follow up on the Q & A’s that came about from SQL Rally, this question was asked by Jim Murphy (@SQLMurph | Blog).   Jim asked “Where in the SQL Engine does Compression Occur.”  Well Dear Reader the simple answer is in the Access Methods, but let’s dive a little deeper.

THIS IS YOUR BRAIN ON SQL

If you think of the SQL Server Engine as a brain, which has 2 hemispheres right and left, it has 2 parts the Relational Engine (right) and the Storage Engine (left). 

The Relation Engine receives a Query from the end user and it Parses it for syntax, Checks for a Query Plan, Retrieves it or Creates and Caches an execution plan, and finally it begins Executing the instructions to retrieve the Data.

This is where the Storage Engine comes into play.  The Relational Engine says here is what I want, and makes a Grocery List.  The Storage Engine takes that list and goes to the store to get all of the groceries. 

The Storage Engine receives the instructions of the Data that is required by the Relation Engine via the Access Methods.  The Access Methods will ask the Buffer Manager if this information is already in the Buffer Pool as part of the Data Cache.  If the Buffer Pool does not have the data in the Buffer Pool (ACTIVE MEMORY) then it is read off of Disk into the Buffer Pool.

As I mentioned earlier the Part of the Storage Engine that handles Compression is the Access Methods.

SO AMERICA WHY DOES THIS MATTER?

“So Balls,” You Say, “Why is this so important?”

Well Dear Reader it is because when the data is in the Buffer Pool (Active Memory) it is still in a compressed state.  So you are using the memory on your server more efficiently.  Not only is compression saving you space on disk, I/Os being read off of Disk, but the space occupied by the cached data pages is used more efficiently. 

A good example of why this is important is Transparent Data Encryption, TDE.  TDE is manage by the Buffer Pool.  That means that the data is unencrypted when it is read off of the disk and into the Buffer Pool Data Cache.  Because Page & Row Compression are managed by the Access Methods the Data is still compressed in Memory.

Another good example is when using backwards compatibility.  I've blogged about being able to use TDE on a SQL 2008 R2 Database using compatibility level 80 as well as a SQL 2008 R2 Database using Page & Row Compression using compatibility level 80.  The reason is that backwards compatibility changes the rules used by the Optimizer in the Relational Engine.

 The Storage Engine is not affected by compatibility level 80.  So knowing how SQL operates under the covers will show you how you should expect it to act, and help you know how to use the product to it's fullest extent.

Want to learn a little more about SQL Server Internals?

A great book on that is Professional SQL 2008 Internals and Troubleshooting, it is a wonderful read and there are more SQL top names on this book than you can shake a stick at.  I recommend having this book in your library.  It will help you on your way to discover more about how SQL works Internally, I know it helped me!

Thanks,

Brad

Transparent Data Encryption & Backwards Compatibility

We were working with Chris Mitchell (Twitter) from the Microsoft Technology Center in Atlanta the other day, out of the Microsoft Office in Tampa.  The Office Has a Beautiful and very distracting view, see the picture below, of the Bay area, my friend Dan Taylor(blog|Twitter) took the photo.

One of those views that makes you love living in Florida!  

But I digress, we were having a really great conversation about features that are enabled or disabled when you enter a database into 80 Compatibility level, but are running on a 2008 R2 Instance.  

When having discussions you typically throw things out by features (or at least that is what I’ve heard people do), Database Compression will not work, Filestream will not work, Encryption will not work.  But when we brought up encryption I asked does that mean Transparent Data Encryption will not work.  Neither of us knew off hand.

I’ve presented on this topic so I’ve got some scripts on standby, so I opened one up fired it off, changed the Compatibility Level, and it still worked. 

“So Balls”, you say “Prove It.”

Dear Reader I wouldn’t have it any other way!

AND….. HERE….. WE…… GO!

First we’ll create our Demo Database

/*

Create TDE Database for demo

*/

USE master

GO

IF EXISTS(select * from sys.databases where name=N'TDE')

BEGIN

    DROP DATABASE TDE

END

CREATE DATABASE TDE

GO

When we use Transparent Data Encryption we need to First Create a Master Key and a Certificate in the Master Database.

/*

Create the Master Key for the

SQL Server

*/

USE master

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD='brad1'

GO

/*

Create the Certificate that will be used

for database level encryption

*/

CREATE CERTIFICATE DatabaseCertificate WITH SUBJECT='NunyaBiznes'

GO

Now that we’ve got those let’s alter our databases compatibility level to 80, SQL 2000.

USE TDE

GO

/*

Let's Alter Our Database

And Place It in 80,SQL 2000,

Compatability Mode

*/

ALTER DATABASE TDE

SET COMPATIBILITY_LEVEL = 80

Now let’s Create our table, and Insert some data.  You’ll see that I have default values set up to simulate an SSN. 

/*

Create Table for filler data

*/

IF EXISTS(SELECT * FROM SYS.tables WHERE name='tdeData')

BEGIN

    DROP TABLE dbo.tdeData

END

CREATE TABLE dbo.tdeData(

    ID int IDENTITY(1,1) NOT NULL

    ,nameText varchar(100) default 'fakeSSN'

    ,ssnText varchar(100) default '111-11-1111'

    ,fillerText char(5000) default 'a'

)

GO

/*

Create filler data for TDE demo

*/

DECLARE @i int

SET @i = 0

WHILE (@i < 15000)

    BEGIN

         INSERT INTO tdeData DEFAULT VALUES

         SET @i = @i +1

    END

I do this in Demo’s because I want people to see that when you insert data into a Data File or a back it up to a Backup File, the data is in plain text.  That is part of the reason you use TDE, because it adds an additional layer of protection.  So let’s backup our data Pre Encryption, and look at it in a Hex Editor.

Look at that there is our social security number 111-11-1111!  Now let’s double check our Compatibility Level. 

And now let’s enable encryption. We create a Database Encryption Key using our Certificate we made earlier, and specify our algorithm.  Then we set the database encryption to on.

/*

Create Database Encryption Key

*/

USE TDE

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE DatabaseCertificate

GO

/*

Set The Encryption On

*/

ALTER DATABASE TDE

SET ENCRYPTION ON

 We’ll use a DMV to validate the encryption process that SQL MVP Jasper Smith(blog) wrote that I’ve loved to use for years now.

SELECT

    CASE e.encryption_state

                WHEN 0 THEN 'No database encryption key present, no encryption'

                WHEN 1 THEN 'Unencrypted'

                WHEN 2 THEN 'Encryption in progress'

                WHEN 3 THEN 'Encrypted'

                WHEN 4 THEN 'Key change in progress'

                WHEN 5 THEN 'Decryption in progress'

    END AS encryption_state_desc,

      e.percent_complete,

    DB_NAME(e.database_id) AS DatabaseName,

         c.name as CertificateName,

            e.encryption_state

    FROM sys.dm_database_encryption_keys AS e

    LEFT JOIN master.sys.certificates AS c

    ON e.encryptor_thumbprint = c.thumbprint

   

You just execute this after you set Encryption On and watch the internal process work, the size of the database will determine how long it runs.  For a large database this is a great DMV to show you just how long the process is taking.  And now we are encrypted! 

So let’s do another backup and open that up in a Hex Editor to see if we can find our SSN.

And as you can see our file space looks quite a bit different, and we couldn’t find the string for our SSN

WHAT IN THE NAME OF PRO-WRESTLING DOES THIS MEAN!?

First off it means that if you have to keep your database in 80 Compatibility Level, you can still use Transparent Data Encryption to secure your backups and your Data Files at rest. Your log files will be encrypted too, but this occurs at a VLF, virtual log file, level.  So the full log will not be encrypted until all VLF's have been over written.

Some other pretty cool possibilities, I asked Paul Randal (blog|twitter) what part of the Storage Engine handled the Encryption and Decryption of pages. 

I know Access Methods handles Compression, because the pages are stored in memory in a Compressed State before being passed back to the Relational Engine.  But Pages are decrypted when stored in memory?  So I didn’t think it was the Access Methods. 

Paul confirmed that it was indeed the Buffer Manager that handles TDE. 

So that leads me to think some ROCKING things may be possible, if you know what the Buffer Manager IS COOKIN!

Thanks,

Brad