2011 Half a Year in Review

I'm looking back and looking forward at the same time.  We're about halfway up the mountain, a lot has been accomplished and more is on the horizon.  And as long as we stay on track, the view at the top is going to be spectacular.

7.       MCITP DBA SQL 2008

a.        Still sitting at the same certification levels.  It doesn’t cost a lot of money, but I don’t want to spend the money until I’ve achieved the biggest goal on my list.  Still should have this taken care of before the end of the year.  But I ranked these backwards from least to most important.

6.       MCITP DBA Developer SQL 2008

a.     I’ve got the book, I’m reading and studying for the first one.  But this is in the same boat as number 1, the house, the dog, then the certifications.

5.       13 T-SQL Tuesdays

a.     I’ve missed 3 this year so the best I can do is 10 at this point.  I’ve gotten so busy with projects in and out of work that the second Tuesday of the month just keeps sneaking up on me.  I’ve got a reminder set in my calendar so I can start again next month.  This was a exercise to get me blogging more, to get into the practice of it, and it sure has helped! I know at this point it doesn't look good 2 put off, 1 not  going to happen, but never fear Dear Reader the best is still ahead!

4.       40 Blog Posts

a.     DONE!  And this was the goal for the YEAR!  I have a feeling we are just getting started.  We’ve got some great things cooked up for SQL Saturday 85, and the PASS Summit is yet to even happen.  And Thank You Dear Reader for stopping by!

3.       6 Presentations to the SQL Community

a.     DONE!  SQL Saturday 62 (January), MAGICPass (February), OPASS (March), PASS DBA Virtual Chapter (March), SQL Saturday 74 (April), SQL RALLY!!!! (May), I took June & July off, but planned for this year August 9^th OPASS, SQL Saturday 79 (Submitted, August 13^th),  Pragmatic Works Webinar (August 23^rd), SQL Saturday 85 Orlando (September 24^th Submitted), PASS Summit 2011!!! (October), SQL Saturday 86 Tampa BI Edition (November 5^th Submitted).  Six presentations already and if I’m accepted at all of the SQL Saturdays I submitted to I’ll end up with 12.  WOW, better than I could have imagined

2.  MCM Prep

a. This is a constant and never ending effort to learn.  There is so much to learn about SQL.  Every year that I’ve spent as a DBA I look back on the previous year and think, “Wow I can’t believe I didn’t know that”.   If you ever make it to my desk you will see that for the last 3 jobs I’ve got a stack of flash cards.  I constantly keep blank ones there and work up questions.  I could get multiple flash card from the MCM video’s that SQL Skills did for Microsoft, another from a great question of the day on SQL Server Central, another from a PASS Virtual Chapter Webinar.  My list of flash cards is always growing, and my Prep is only beginning.  I don’t know that this will truly ever be Done, but I’ve benefited greatly just from taking a stab at it.

1.   A House and a Dog

a. This by far is the most important goal.  To have a home and a yard for the kids, and not to move them all over the place any more.  That was the whole point of the move to Florida, to put down roots and stay in one place.  So to that end we found a House!  We’re going through the process and should be closing before the End of July (Crossing toes, fingers, and any other body part that will cross).  We’ve found a great Lab rescue group that we have worked with, had our home visit, and gotten approved.  The only thing we are waiting on for the Dog is to have a house that we can all spread out in.

I had said in a previous Blog that if getting a House and a Dog was the only thing that we accomplished this year it would be a win.  This house is about much more than setting a goal or marking a check box from a list.  I fumble at the words to even describe how important this is, but I know it in my heart.  I feel it in my bones.  This is the biggest and the best one of the year!

So Two done, one that won’t happen, and everything else is in flight.  Not bad.  Hopefully the next update on goals will be soon!

Thanks,

Brad

Backwards Compatibility Level & Snapshots

Back in April I started a Series on the Backwards Compatibility level.  There are a lot of myths around Compatibility Level, in Books Online, (BOL), you can find plenty of literature that states that Compatibility Level determines the set of rules that the Relation Engine uses when judging syntax, creating Query Trees, and Execution Plans.  However I’ve seen plenty of places where in the forums people will say that you cannot use a feature managed by the Storage Engine, when the database is set to 80 or 90 Compatibility, and that is flat out wrong.

Previously on SQLBalls we established that you can use Transparent Data Encryption and Page & Row Compression with Databases set to 80 and 90 Compatibility Level.  Today we’ll do a quick exercise to show that we can use Snapshots against an 80 Compatible Database.

“But Balls,” You say, “You don’t care about 90 Compatibility and Snapshots?”

Great Question Dear Reader, Database Snapshots were introduced in SQL 2005.  When you look at Compatibility levels, level 90 was introduced in 2005, therefore the only Compatibility level we would need to test out is 80.

UNLIKE HARRY POTTER THESE SNAPSHOTS DON’T MOVE

Okay I needed something to go with the picture, but hang with me.  A Database Snapshot is like a picture.  You take a Picture and you have that moment in time captured.  Database Snapshots are a very similar concept.

Behind the Scenes a Snapshot is Managed by the Buffer Manager inside of the Storage Engine.  When Data is changed in memory a copy of that Data, before it is modified is written out to the Snapshot.  You see initially a Snapshot doesn’t have anything to it.  It isn’t until you change Data that you start populating a Snapshot.  As a matter of fact if you request a page that hasn’t changed, even if you query the snapshot you will be reading from the Datafile of the Database the Snapshot was taken of.

Snapshots will start out with very little actual space used, and their maximum size is the size of the Database the Snapshot was taken of, at the moment the Snapshot was taken. 

Enough Explaining let’s get to some doing!

DEMO

First we will take our database from our SQL 2000 instance and we will restore it.  How do you know it is from 2000, because the Database Internal Version number is incremented when moving from one version to the next at the end of Recovery.  And as you can see from the picture below we start at 539 and we go to 661, or in other words our internal version is going from SQL 2000 to SQL 2008 R2 RTM.

So now we’ve got our database restored.  

I’ve got one table dbo.heap1, and a quick count will show we have 15,000 records.

SELECT

     COUNT(*) AS [dbo.heap1 Count]

FROM

     dbo.heap1

Let’s take our snapshot.

USE master;

GO

CREATE DATABASE snapShotTest_Snapshot ON

    ( NAME = snapShotTest_Data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.BRADSQL2008R2\MSSQL\DATA\snapShotCompatLvl.ss')

AS SNAPSHOT OF snapShotTest ;

GO

Then we’ll delete 10,000 records from our database.

 DELETE FROM dbo.heap1 WHERE myid BETWEEN 1 AND 10000

Now let’s do a select count from our table, and we’ll see we only have 5,000 records left.

SELECT

     COUNT(*) AS [dbo.heap1 Count]

FROM

     dbo.heap1

Now let’s do the same count against our Snapshot.

USE snapShotTest_Snapshot

go

SELECT

     COUNT(*) AS [dbo.heap1 Snapshot Count]

FROM

     dbo.heap1

And we will see that our Count is still 15,000.  We dropped the records and our Snapshot still reflects the number of records we had before we dropped it.

Let’s confirm that our database is still in 80 Compatibility Level.

And it is.  So just to summarize you can use Transparent Data Encryption, Page & Row Data Compression, and Snapshots with a database in 80 Compatibility level.  Tune in next time Dear Reader when we wrap up the Series.

Thanks,

Brad

Buck Woody, Florida, and SQL Saturday 85

What do Buck Woody (Blog|@BuckWoody) and Florida have in common?  Quite a bit actually, and on September 23^rd , at the Lake Mary Marriot, we will add to that list.  Buck Woody, Mr. “The Cloud”, will be presenting on SQL Server Performance Tuning Using Application Path Analysis and it will be AWESOME!

“So Ball’s”, you say “Who is this Buck Woody fellow?”

Buck along with this Brent Ozar (Blog | @BrentO ) fellow had the number 2 ranked session at the PASS Summit last year, You’re Not Attractive But Your Presentations Can Be.  He was a featured presenter on the Quest Virtual Training for SQL Server Series, he has published 5 different SQL Server Books, had over 400 SQL Articles published, he was the President of the Tampa Bay SQL Server User Group for 5 years, a Microsoft MVP, and if you are on Twitter you see that Buck Woody and the Cloud are often mentioned in the same breath. 

Buck has a deep knowledge that comes from being where we are in the real world as a consumer of software products.  He joined Microsoft in 2006 a year after receiving his MVP award for SQL Server.  He brings the same “Real World” DBA sensibilities with a Dash of Microsoft Gusto that you would expect in one of the top names in the industry.

And by name alone he is a candidate to join Jack Corbett (Blog | @unclebiguns) in our future law firm of Biguns & Balls as a full partner.

So now let’s talks some details.

DETAILS

SQL Server Performance Tuning Using Application Path Analysis

There are a lot of resources, products and features you can use to tune the performance of your SQL Server system. Many assume you’re familiar with 400-level concepts, others don’t consider the whole stack of the client, the network, the operating system, platform and the database server. Buck Woody, Microsoft’s real-world DBA, will explain a simple, repeatable process you can follow to tune your entire application – from the client to the server. All of the tools we’ll cover are included with Windows and SQL Server:

·         Using Windows System Monitoring Tools

·         The SQL Profiler and Server Trace

·         Database Engine Tuning Advisor

·         Management Data Warehouse / Data Collector

·         Performance and Configuration Reports

·         The new Activity Monitor

·         Dynamic Management Views and System Views

·         Query Plans

·         Extended Events

·         Resource Governor

In this pre-conference session you’ll cover not only the process, but also review a real-world evaluation. You’ll take home a system and a spreadsheet you can use to monitor and tune your applications, in a simple, easy-to-understand session.

WHAT ELSE IS INCLUDED

For this full day of training, Coffee & tea during the morning and afternoon, with lunch included as well.  All of this Dear Reader for a very Reasonable $99. 

So you may be asking how do I sign up?  Very simple go over to http://sqlsaturday85precon.eventbrite.com/ all of the information is there.  Hope to see you at the event!  ( And Buck Does Too!)

CALL FOR PRE-CON SPEAKERS

As Awesome as Buck is, there is only so much of him that can go around.  So we wanted to offer 2 Pre-cons for SQL Saturday 85 this year.  I’ll be blogging about this more soon but right now head over to my friend Shawn McGehee’s  (Blog | @SQLShawn) post Pre-Con Speaker Wanted: Apply Within to get some more details ASAP about how you can come and be a Pre-Con Speaker as well!

Thanks,

Brad