Backwards Compatibility, Your Database is so Retro

I think there are a lot of misconceptions when it come to Backwards Compatibility, also known as Database Compatibility Level.

“Balls”, you say, “What is Database Compatibility Level?”

Great Question Dear Reader, man your smart!

GONNA GO BACK IN TIME!

Database Compatibility is a setting on a Database that tells SQL Server to use a particular set of Rules, when judging SQL Statements on a syntactical level.  Each version of SQL has three levels of Backwards Compatibility.  If you look at the versions of SQL over the years each has a number associated with it.  In SQL 2008 and SQL 2008 R2 the Database Compatibility levels available are 80, 90, and 100.  80 is equivalent to SQL 2000, 90 is 2005, and 100 is 2008 & 2008 R2.

 The Compatibility level is by default the most current version, UNLESS you set the Model Database to have a Compatibility level that is less than the current version.  But unless you do that, a SQL 2000 database should be in 80, 2005 should be in 90, and SQL 2008 & 2008 R2 should be in 100.

One of the simplest comparisons would to that of an emulator, by that carries some misconceptions.  I had heard previously that if you used a database version that was other that the current release you where working in then you couldn’t utilized the Advanced Features that were used in that release.  Well Dear Reader, as I’m finding out that is just flat out wrong. 

FAT, DRUNK, AND BACKWARDS COMPATIBLE IS NO WAY TO GO THROUGH LIFE

The better comparison would be to College.  One thing I’ve never forgotten is that when you enter College, you get a college catalogue.  In it the requirements are outlined for all of the different Disciplines that you could get a degree for, and what those requirements are.  Those requirements can change over time, but will stay the same for you as those laid out in your college catalogue.

“Balls”, you say, “Why would I use Database Compatibility Level or change my database to another?”

Well Dear Reader, under most circumstances you wouldn’t.  If your upgrading from SQL 2000 to 2005 one step you would take is to change your Compatibility Level from 80 to 90.  This will change the syntax rules for your database, the same way the next year of new freshman will get a new college catalogue when they start school.

If you wanted to run at a different Compatibility the reason to do so is to buy your Developers some time.  Let’s say the application has some syntax that needs to change from 2000 to 2005, or from 2005 to 2008.  How do you find that and fix that?

You can use the Upgrade Advisor, and that will work perfectly well, as long as all of your queries are in Stored Procedures.  In Perfmon you can use the Deprecated Features SQL Counter and in Profiler you can track statements by Deprecated Features to try to find Statements that contain language that is not supported in a future release, this was introduced in SQL 2008 so you cannot use this if you’re on 2005 or 2008.  There are some free 3^rd party tools that will replay traces and identify statements, but this could be a little hit or miss, and there is no way to guarantee that there are statements in an application that didn’t run and are still out there.

Depending on the load of work the business has for the Developers, this could be a great reason, to run for a brief time in a lower Database Compatibility Level.  You are able to accomplish a Database Instance Upgrade, and you can show a little Developer solidarity by giving them some extra time.  Keep in mind these features where deprecated for a reason, if you have the time in your project you should plan on altering the Compatibility Level in conjunction with the Database upgrade.

DON’T TOSS THE BABY

So the big misconception is that you have to use the previous versions Advanced Features when you use a lower Compatibility Mode.  I call shenanigans get your broom!  You don’t have to toss the Baby out with the Bathwater.  The Baby’s just fine, leave them alone.

Last week I wrote about how you can use Transparent Data Encryption in a 2008 R2 instance when the Database Compatibility Level is set to 80.  I’ve got more coming this week, but I wanted to talk about what Backwards Compatibility AKA Database Compatibility Level is and why you would use it.

Keep in mind Dear Reader, that when you look at the Database Engine it is split between Relational & Storage.  The Relational Engine handles Syntax, Query Plans, Optimization, and many other things related to how we ask for a query and the path we take to return it.  The Storage Engine not only retrieves the data, but it manages how the data is stored, it’s physical structure, placing data into cache, compression, Transparent Data Encryption, Snapshots, Backups, Mirroring, Backup Compression, and more.

Last week, Paul Randal (blog|@PaulRandal) had answered a quick question for me, about what internal Storage Engine component handled Transparent Data Encryption, to find out go look through last week’s SQL Skills (website| @SQLSkills) twitter contests, the ones on 4/27 in particular.   Needless to say a light bulb went off.

So next up Dear Reader we will tackle the myth that you cannot use Page & Row Compression on an 80 or 90 Database.  One last bit of resources if you'd like to read more about Database Compatibility Level go to MSDN and read the article on it.

Thanks,

Brad

SQL Saturday 74 Jacksonville

Hello Dear Reader, If you're visiting looking for SQL Saturday Slide Decks and info please go to the Resources page, all info is there!  Just Click Here, and come back on Monday for my SQL Saturday Roundup!

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