SQL Rally: Business Intelligence Workshop

Just TWO DAYS until the start of SQL Rally!  I’m going to the Business Intelligence Workshop Pre-Con as my first stop.  I’m as excited as a kid on the 1^st day of school!

“Balls”, you say, “Why BI?  Aren’t you a DBA?”

Excellent question Dear Reader.

I’m working on my MCITP: 2008 DBA Certifications and after that I’ll begin work on the DBA developer Certifications.  My goal, for the end of this year, is to have all the pre-qualifications to go for the MCM sometime next year.  So where does BI fit in?

BECOMING MORE INTELLIGENT ABOUT BUSINESS 

Yep I’m a DBA, and it used to be we weren’t so compartmentalized.  It used to be if you were a DBA you were the Developer, the Administrator, and the BI person (and I would bet in some smaller shops that dichotomy probably still exists).  A database, was a database, was a database and that’s all there was to it.  But as the technology has advanced so has the set of skills required to do each job.

I’d like to say I couldn’t Cube my way out of a paper bag.  But this won’t be my first go round with SSAS.  Back in 2008 I had started studying to get my BI certifications for 2005, but life has a way of guiding you and BI was not a place I was supposed to dedicate my focus back then.  I understand the concepts at a 5,000 foot level, but as for applying them I need some work.

So Why BI?  Because our data can tell us a lot of things, and while storing it, maintaining it, and protecting it is important understanding what it can tell us is important as well.  Our data can help us understand trends, patterns, and answers to questions that we might not know existed.  You probably use metrics from your servers to analyze your performance (tracking and trending) .  Well this is doing it with your business data.

KICKING SSAS ONE CUBE AT A TIME

The guys over at Pragmatic Works are the top dogs when it comes to Business Intelligence, and the Pre-Con for SQL Rally get’s you training from not One, not Two, not Three, but FOUR of the top guys in the field today.

Go over to Kendal Van Dyke’s blog  Meet the SQL Rally Pre-Con Presenter’s and read about them, l .  Patrick LeBlanc, Devin Knight, Adam Jorgensen, and Mike Davis are all at the top of this game. 

This is an area where I really want to expand my knowledge and I can’t wait to attend.  I’ll blog about this more in depth on Wednesday night as part of my SQL Rally Day by Day Series. 

I can’t wait get started!  Bring on Wednesday!

Thanks,

Brad

SQL Saturday 74 Round Up

This past Saturday was SQL Saturday 74 in Jacksonville.  And like most great trips it started out with a road trip.  Kendal Van Dyke (blog|@SQLDBA) and I had discussed driving up together, my buddy Dan Taylor (blogY b|@DBABulldog) had decided to join us by mid week, and Karen Lopez (blog|@DataChick) rounded out our cast on Friday.

Dan started his drive at 3:30 in the morning, making it to my house by 4:15, we left around 4:35 to go over to Orlando and pickup Kendal, and then we added Karen to our traveling road show around 6ish.  The road trip was one of the great highlights, that made for a very enjoyable day.  The conversations flowed like hot coffee, and the coffee flowed like......well hot coffee.

Before I knew it we were getting off the exit and heading over to the scenic campus of the University of North Florida.  This wasn't my first time in Jacksonville, but it was the first time I'd been on this University before, Dan had been to this SQL Saturday in the past and was quick to offer up directions of where to go, and at the parking garage, pictured above, we exited the car to make our way down to SQL Saturday! 

PLAMEN RATCHEV: T-SQL ENHANCEMENTS IN DENALI

So I know the Picture is blurry, but I swear this is really him.

DON STEVIC: FUNDAMENTALS OF DBAISM

BEST SWAG EVER!!!











KENDAL VAN DYKE: DR YOUR WANTED IN THE RECOVERY ROOM

at this point I should have taken more photos

LUNCH ON THE LAWN II

KEVIN BOLES: ADVANCED T-SQL SOLUTIONS

BRADLEY BALL: PAGE AND ROW COMPRESSION HOW WHY AND WHEN

BRIAN KNIGHT IRON CHEF JACKSONVILLE BI EDITION

Page & Row Compression Vs. Backwards Compatibility

"My Money is on Compression"

Previously on SQLBalls we discussed Backwards Compatibility and why you would want to use it, Transparent Data Encryption and how you can use that when in a Database Compatibility Level of 80 or 90.  We had also discussed Why you would want to use Page and Row Compression, when I stated my case for SQL Rally .

Now we find Page & Row Compression and Database Compatibility Level all meeting for the first time.

So Dear Reader, let’s dive right in!

PHENOMENAL COSMIC POWERS!!!! ITTY BITTY LIVING SPACE.

I wanted to create a database in SQL 2005, write out  my compression scripts see them fail, because we didn’t have compression in SQL 2005, backup the database restore it on 2008 R2, and then show that while in 90 mode the data still compresses.

 I connect to my SQL 2005 Instance, which needs to be upgraded to SP 4.  And then I run a script to create my Database, my Table, and populate the data.

IF EXISTS(SELECT * FROM SYS.databases WHERE name='compatAndCompression')

BEGIN

    DROP DATABASE compatAndCompression

END

CREATE DATABASE compatAndCompression

GO

USE compatAndCompression

GO

CREATE TABLE DBO.HEAP1(myID int IDENTITY(1,1), myData char(8000) DEFAULT 'A')

GO

DECLARE @i int

SET @i = 0

while (@i<1000)

BEGIN

    INSERT INTO DBO.HEAP1 DEFAULT VALUES

    SET @i = @i + 1

END

GO

So far everything’s fine.  Now let’s try to compress it.

sp_spaceused 'heap1'

GO

ALTER TABLE dbo.heap1 REBUILD

WITH (DATA_COMPRESSION=PAGE)

GO

sp_spaceused 'heap1'

GO

Error that is what we should expect, as 2005 didn’t have compression.

Now let’s backup our database and Connect to our 2008R2 Instance.

Now let’s restore our database.  Look at the restore section, you’ll see that the Database Internal Version is incremented.  This is an important distinction because your SQL Database do not have ONE version number (Database Compatibility Level), they have TWO.

The DBI version is incremented when you restore a database from one Version to another.  This is why if you take an 80 Compatible Database from 2008 R2, and back it up you cannot restore it to SQL 2000.  Because you cannot roll back the Internal DBI Version, for 2005 to RTM 2008 R2 that mean our DBI version will go from 611 to 661.  Let’s do a quick check to validate our database is still in 90.

And it is, so now let’s apply our Compression Script.  We’ll be using Page Compression to get the most bang for our buck.

sp_spaceused 'heap1'

GO

ALTER TABLE dbo.heap1 REBUILD

WITH (DATA_COMPRESSION=PAGE)

GO

sp_spaceused 'heap1'

GO

I wrapped this around an sp_spaceused so that way we can see the size before and after.  And behold our 8 MB table is now in the 200 KB range.

"So Ball's", you say, "Great it works on a Database that is imported from SQL 2005, are you sure it will work on a Database imported from 2000?"

Absolutely Dear Reader, time for a little Yogi Berraism, "Once Your In, Your In".  In short it doesn't matter what version you came from.  SQL Server only supports the Database Compatibility Level of the previous 2 versions.  So as long as it is an 80 or 90 compatible database, your okay.

But this would be the perfect opportunity to do the whole thing again with 2000!  So I'll skip to the good stuff, I created a SQL 2000 Database and inserted data, and then backed it up.  I'm moved it to my XP box that has a 2008 R2 Instance on it and restored it.

I highlighted the part where you see the upgrade of the DBI Version.  SQL 2000 is set to 539, and as part of the restore process the DBI version is upgraded when coming from an earlier version.  So we've got the database, let's confirm that it is still in 80 Compatibility.

And it is, so let's take our script from earlier and apply it to our heap.

sp_spaceused 'heap1'

GO

ALTER TABLE dbo.heap1 REBUILD

WITH (DATA_COMPRESSION=PAGE)

GO

sp_spaceused 'heap1'

GO

And as you can see our table has shrunk once again.

So Dear Reader you can use Transparent Data Encryption and Page & Row Compression with a Database Compatibility Level set to 90 or 80.

Join me next time when we take on Snapshots!

Until then MAKE MINE SQL!

Thanks,

Brad