Friday, July 29, 2011

How Do You Learn?

A lot of brilliant people have spent a lot of time, some have made careers out of, learning about how we as people learn.  Whether you subscribe to the Kolb’s, Gregorc’s, Honey & Mumford’s Models, or Flemings VAK/VARK model, at this point in your career you’ve found a way to learn and it works for you. 

I once had a professor, Dr. William Perry, that said repeatedly throughout his courses “Learn to Love to Learn!”.  He would talk to us about how as IT professionals you had better enjoy learning because you would be doing it for the rest of your life.  He would also add that if you did not like to learn you were in the wrong field.  He couldn’t have been more right.

“So Balls,” you say, “How do you learn?”

Glad you asked Dear Reader, let’s dive right in.

MY FLASH CARDS            

 Here is a picture of one small part of my Desk.  If you’re someone who has worked with me you’ve probably seen my flash cards.  I started this pile of flash cards when I was working on beautiful Ft. Monroe in Virginia.  I was studying for my TS and then my ITP Certifications for SQL 2005.  I added to my flash cards next when I was working for the Office of the President up in D.C.  I was studying for my TS in SQL 2008 at the time.  As I would take the train in I would read my book and highlight sections that I wanted to make flash cards out of.  Then I would re-read the book making the flash cards.

After studying for and getting my Certifications, I didn’t want to just forget what I had worked on.  So I continued to review them.   Sometimes people would walk up and say “What are you studying for?”, and I would reply with the name of the Certification I was working towards.  Sometimes I would simply reply by saying, I just want to keep the information fresh in my mind.

As working situations arise, having this information fresh in my mind proved beneficial time after time.  Learning provided better situational awareness, which only served to reinforce how important it was to continue to learn. 

Pretty quickly on I started finding myself in situations where, I would have an issue resolve it, and I’d want to continue to learn from it.  So I would make a flash card on the script, or the situation.  My focus had been on learning from books alone, and that was pretty narrow.  I realized it was just as important to expand not only what I was learning, but where I learned it from.

Back in 2009 our VP of Microsoft Technologies had told me about this upcoming event called, 24 Hours of PASS.  It was an online event, so I could watch these 1 hour training sessions from my Computer.  I persuaded work to let me work remotely, so I could watch these training sessions.  This was my first exposure to PASS, and my first window into the SQL Community, (other than the forums for ).

There was amazing session after amazing session, I learned about NUMA from Thomas Grohser (@tgrohser), I first heard Louis Davidson (@drsql | Blog) discuss relational design, and got my first ever view of Brent Ozar(@BrentO | Blog).  I was hooked, and I had gotten a lot of good information.

“So Balls,” you say, “Where do you get your learning material from books and webinars?”

Great question Dear Reader, but the answer is I get it from just about everywhere.

When you look at my flash card pile up above, know that I’ve thrown a lot out.  I gave some to friends, and the flash cards have been a constant evolution.  Sometimes material in books are wrong, sometimes material on Books Online are wrong, and sometimes you never know what will be useful.

Here are the places that I take notes from that turn into flash Cards, and would be my recommendations.

A.      Books
Books are a great source of knowledge and there are a lot of really good SQL Server books out there.  Right now I’m reading Microsoft 2008 Internals, Click Here to View.   I’m also reading Professional SQL Server 2008 Internals and Troubleshooting, Click Here to View.  The great thing about books is that when you buy them, you can always have them with you.  Nothing beats having knowledge at your finger tips.  IPAD, Knook, whatever buy them and study them.

B.      SQL Server Central
Whether it be the Question of the Day, an Article in the newsletter, or a really great discussion on the forums.  There is more information than you could ask for in a lifetime.  You can get help with your problems, and do plenty of learning as well.

Last month at MagicPASS we had a Microsoft Certified Master speak to us about the Relational Engine and how Cache works Internally.  It was free, we had tacos, and there are a lot of great DBA’s just like you at the meetings.  This is a fabulous place for networking and for learning.  Go to the PASS website and find the User Group nearest to you.  Don’t forget the Virtual Chapters which provide FREE webinars monthly, sometimes twice a month!

D.      24 Hours of PASS
This is another FREE event, I hope your sensing a trend here you don’t have to spend a lot of money to learn, and it will have the TOP names in the field of SQL Database Administration and Business Intelligence.  All it requires is your time.

E.       Channel 9
This is Microsoft’s online learning resource for the Public.  You can find Tech Ed Presentations, Informational videos by the Project Teams, and even new technology previews.  Another great FREE source.

F.       Webinars
Companies like Pragmatic Works, Idera, and Confio are constantly putting on Webinars monthly and for FREE!  You just need to go to their websites and sign up for their free training News Letters.

G.     SQL Saturday
Hey We have one of these coming up in Orlando on September 24thClick Here to Register!  This is an event where some of the top names in our filed present to you for FREE!  You will have MVP’s like Andy Warren(@sqlandy | blog), Rodney Landrum (@sqlbeat | Blog), or Jorge Serraga (@sqlchicken | Blog) just to name a few, presenting for you Dear Reader.

H.      MCM Videos
I wrote a Blog on this once already, MCM video’s You Should Be Watching These, and you should be.  They are a wealth of information, that scratches the surface on what you need to be a Master!


There is also a lot of great training that is not for free, SQL Rally, PASS Summit, and training courses offered by companies like SQL Skills or SQL Cruise.  They are worth their weight in gold, and you don’t have to look far to find a lot of people with the same opinions.

However you learn Dear Reader, just make sure you are learning.  I make flash cards from all these thing, and I review them.  I walk around and share them with the DBA’s I work with, and sometimes we have a quiz of the day on a topic.  I’m sure there are a lot of places that I’ve left off the list if you want to add any please do in the comments below!



Wednesday, July 27, 2011

Announcing the Full Day BI Workshop for SQL Saturday 85
Hello Dear Reader, a couple of weeks ago we had a call for Pre-Conference Speakers for SQL Saturday 85.  That call closed on the same day as our call for speakers back on July 24th.  The Program Team has been hard at work reviewing submissions as they arrived, and we got some Great Ones!  We were finally able to narrow down the selection process, and select a great event.

I Just wanted to say a quick Thank You to everyone who submitted.  Every Single Presentation we reviewed was top notch, and I would be excited and thrilled to attend any one of them.  To everyone who submitted we really appreciate the time and effort that it took to submit.  Thank You!

This was a very tough decision but in the end there was one Session that we felt would be OUTSTANDING and at the same time offer the variety in subject matter that we felt would best serve our conference attendees.

“So Balls”, you say, “Who DID you pick?”

Ahh Dear Reader we didn’t pick one, no we didn’t pick two or three, but FOUR speakers.  So without further ado I give the Full Day BI Workshop!

Business Intelligence Workshop
Jorge Segarra, Bradley Schacht, Kyle Walkter, &  Mike Davis
In this full-day workshop, you'll learn from the author team of Jorge Segarra, Mike Davis, Brad Schacht, and Kyle Walker how to build a data warehouse for your company and support it with the Microsoft business intelligence platform. We'll start with how to design and data model a data warehouse including the system preparation. Then, we'll jump into loading a data warehouse with SSIS. After SSIS, you're ready to roll the data up and provide the slice and dice reporting with SSAS. The team will walk through cube development and data enrichment with things like key performance indicators, which are essential for your future dashboards. Lastly, we will cover how to report against the data warehouse with SSRS including a primer in how to write MDX queries against the SSAS cube.
Take Home Skills:
  1. Practical knowledge of building a Dimensional Model
  2. Designing a simple ETL process using SSIS
  3. Designing a Cube
  4. Designing simple SSRS Reports
  5. Building an integrated process that fully leverages the entire MS BI stack to load a Data Warehouse
Speaker Bio(s)

Jorge Segarra  (@SQLChicken|Blog)
Jorge is a BI Consultant for Pragmatic Works and a SQL Server MVP. In addition to being a member of the Jacksonville SQL Server User Group (JSSUG) he is also a PASS Regional Mentor for the U.S. Greater Southeast region. He has also co-authored the book from Apress “SQL 2008 Pro Policy-Based Management“. Redgate Exceptional DBA of the Year 2010 Finalist.

Bradley Schacht  (@bradleyschacht|Blog)
Bradley is a consultant at Pragmatic Works in Jacksonville, FL. He was an author on the book SharePoint 2010 Business Intelligence 24-Hour Trainer. Bradley has experience on many part of the Microsoft BI platform. He has spoken at events like SQL Saturday, Code Camp, SQL Lunch and SQL Server User Groups. He is a contributor on sites such as and SQL Server Central as well as an active member of the Jacksonville SQL Server User Group (JSSUG).

Kyle Walker  (Blog)
Kyle is a Business Intelligence consultant for Pragmatic Works. His current and past work experience includes Integration Services, data warehousing concepts, Reporting Services, and Crystal Reports. He is active in the local and online community as a speaker for SQL Lunch, Jacksonville SQL Server Users Group, and past SQLSaturday events, as well as a blogger on

Mike Davis (@MikeDavisSQL|Blog)  
Mike Davis, MCTS, MCITP, is a Senior BI consultant at Pragmatic Works. He is an author of a few Business intelligence books. Mike is an experienced speaker and has presented at many events such as several SQL Server User Groups, Code Camps, SQL Server Launches, SQL Rally, and SQL Saturday events. Mike is an active member at his local user group (JSSUG) in Jacksonville, FL.


To Sign up for this event, Click HERE.  This full day of training is for only $99 dollars, which includes all day coffee & tea and lunch.   You may notice that every one of our speakers works for Pragmatic Works.  I’ve had a chance to see the great people at Pragmatic Works performing Half Day Training and Hourly Session, Webinars, Full Day Training, at SQL Saturday’s, at SQL Rally, and at even the Microsoft Offices in Tampa helping to explain and discuss new and upcoming technology.

They are one of the GO TO companies when it comes to BI.   They are the people writing the books, and they are the resource experts that people flock to.  If you are interested in BI, this is a day that you cannot miss!

If you are an old hat at BI, or a newbie this is the session for you.  I attended this same Pre-Con at SQL Rally.  We covered SSIS, Change Data Capture, SSAS, and SSRS.  You will truly get a look at how the full Microsoft BI suite can be utilized for your business.  A sample business was used and we walked through all the different ways to manage the data.  For the full write up look at SQL Rally: Pre Con Business Intelligence Workshop BI END to END.   This was a great day of training, and I’m really excited that we can offer it to you!

A Big Congratulations for newly minted MVP Jorge Segarra (Who will also have a Community Spotlight Session at this year’s PASS Summit), Bradley Schacht, Kyle Walker, and to Mike Davis on getting selected.  Gentlemen I look forward to seeing you in Orlando in September, and Dear Reader I hope you can make it too!



Tuesday, July 26, 2011

SQL Saturday 85 Schedule Released!

Hello Dear Reader, the moment you’ve been waiting for has arrived the Schedule for SQL Saturday 85 is now available online!  Click Here to see our AMAZING lineup!   I have a graphic of the schedule below but trust me the one on the website is much easier to read.

Tim Ford (@sqlagentman | blog) recently did a really awesome advertisement for SQL Saturday #84 in Kalamazoo Michigan, the week before ours and with a very talented lineup as well!  As a matter of fact if you are in the Michigan area you should plan on attending!  I think of what Tim did as SQL By The Numbers.  And they were some pretty impressive numbers.  And it made me curious, as to what our numbers are as well.

There was a lot of work that went into the schedule, on the part of the Speakers.   Your career is a culmination of what you have accomplished so far.  And so far our Speakers have done some pretty darn impressive things.

“So Balls,” you say, “Stop talking and show us these numbers”

Without further ado I give you SQL Saturday 85 by the numbers.

42 Sessions
21 Current & former PASS Speakers
21 Sessions presented by PASS Summit Speakers
13 Tracks at this year’s Summit
13 Tracks Presented by MVP's
9 2011 PASS Summit Speakers
9 Microsoft SQL Server MVP's
7 Tracks
2 Sessions by a Microsoft Certified Master
1 SSAS Maestros Instructor
1 Microsoft Certified Master

Wow can you believe that lineup!  I'm so excited for this event I can hardly contain it.  All we need is one more very important number.

The most important number not listed Dear Reader is you!  We’ve got a wonderful line up, a FREE day of training, that would normally go for HUNDREDS if not THOUSANDS of dollars.  What are you waiting for Sign Up Today by Registering HERE. 

Over the next couple weeks we’ll be breaking this out even more.  Each of those numbers represents a significant amount of time, learning, and expertise on the part of our Speakers, and help to make SQL Saturday 85 in Orlando a Top Notch Event.  Over the next couple weeks we will be breaking down these numbers a little more.

Thanks for stopping by and I hope to see you in Orlando in September!



Saturday, July 23, 2011

SQL Saturday 85 Shedding Some Light on the Schedule

Hello Dear Reader, we are getting close to ending the Call for Speakers for SQL Saturday 85, the call ends July 24th.  SQL Saturday 85 is in Orlando on Saturday September 24th 2011.  I’m about 90% done with the schedule,  I know you’ve got to be as excited about this as I am!

I wanted to share with you some of the Sessions that we were looking at including.  I’m picking three Today, and then Shawn McGehee (@SQLShawn | Blog) will be posting more details on his blog tomorrow. 

Monday we’ll have the 90% of the Schedule posted with the remainder to follow Tuesday or Wednesday.  Tuesday we will also be releasing the details of the Pre-Con selected from our Call for Pre-Con Speakers.  There is still time to Click Here and submit a Session for SQL Saturday 85, the Call will close tomorrow.

“So Balls,” you say, “You promised to some details make like a kid on a long car trip and leak already!”

You got it Dear Reader, opening away!


You know that Buck Woody (@BuckWoody | Blog), the well know speaker, Microsoft Guru, The CLOUD, and all around good guy is coming to do a Pre-Con for us, SQL Server Performance Tuning Using Application Path Analysis, but did you know that he will be PRESENTING at SQL Saturday as well!

Leaked Session Number 1: From Ground To Cloud for Databases

Speaker:  Buck Woody

“The cloud! Move everything to the cloud! No, wait, the cloud is awful! Don’t move anything to the cloud! Wait – what’s “the cloud”? Buck Woody, Microsoft’s “Real World DBA” will show you how to. figure out where your data should live, based on actual decision points. You’ll learn about Windows and SQL Azure, and when it makes sense to put data locally or remotely.

And that’s not all Dear Reader how about we have a Microsoft Certified Master in the house as well!  And not just a MCM, but a Premier Field Support Engineer with a specialization on Performance Data Warehouse!  None other than Brian Mitchell (@BrianMitchell | Blog)

Leaked Session Number 2: Applying FastTrack & PDW Best Practices to your DW
Speaker: Brian Mitchell

“SQL Server 2008 R2 Fast Track Data Warehouse and SQL Server 2008 R2 Parallel Data Warehouse share many of the same design components and best practice recommendations. We will discuss what those designs are and how you can apply those to your data warehouse. Additionally, we will walk through things you can do today to improve the performance of your data warehouse. “

So how do you follow that up?  How about with a Microsoft MVP a TEACHER of the Microsoft SSAS MASTERO Course, The Man, The Myth, The Legend Adam Jorgensen (@Adam_Jorgensen | Blog)!

Leaked Session Number 3:Analysis -0 to PowerPivot

Speaker: Adam Jorgensen
“In this session, the attehdees will learn how to drive and build a PowerPivot solution and reports as they rotate through the podium as volunteers. 

All right Dear Reader, if you haven’t registered now is the time to start rolling, Click Here to register for SQL Saturday 85!  And go visit Shawn’s Blog tomorrow to get more leaked details!  

And as always Thanks for stopping by!



Tuesday, July 19, 2011

SQL Saturday 85 Call for Speakers Closing Soon!

Hello Dear Reader just a quick reminder that the call for speakers for SQL Saturday 85 in Orlando closes this Sunday July 24th.  We have extended the call until then, and we got quite a few great submissions!  So what are you waiting for time is getting down to the wire, make sure to get your Abstract in before it is too late.

In my Previous Post, I WANT YOU To Present at SQL Saturday 85, I walked through how to make an abstract, and I even tossed out two that are on presentations I’ve I made up just to brain storm with you.  If you want some ideas go give it a read.  So the question is what are you waiting for?

“But Balls,” you say “I don’t want to get rejected, I don’t have a blog, nobody knows me.  To be honest the idea of presenting makes me nervous.”

Well Dear Reader let’s take these one at a time.


I hear you loud and clear.   But let me put your mind at ease, SQL Saturday is a community event.  This isn’t the Summit, this isn’t SQL Rally, this isn’t Tech-ED.  This is where new speakers come to start out, and where experienced speakers go to polish their material.

I know a guy on the inside, and he told me that we want to make sure everybody gets a shot.  Heck, I would rather sit this one out, (And I know several very experienced speakers that feel the same way), in order to give someone else a shot.  Don’t let this discourage you!

You should actually let this encourage you to submit!  We want you to get a shot Dear Reader!


I’ve heard people say this before.  They don’t want to present because they are not blogging.  It isn’t a prerequisite.  You don’t have to blog. 

I do it, but that is because deep down inside I like being on stage.  I like the lime light.  I think it’s fun.  But it is also a little like shaving, once you start you have to keep going, otherwise it will just look funny.

Do not let this hold you back, if you ever want to start a blog start one.  If you don’t ever want to that is fine.  This shouldn’t even be a concern, there are a couple people I’ve met that are top people in the business, they present at SQL Saturday’s and I’ve never seen them post a blog before.


I was listening to a presentation that Buck Woody (@BuckWoody | Blog) and Brent Ozar (@BrentO | Blog) did for the PASS Summit last year and they advised “That if you don’t feel nervous when you stand up to present you probably need to sit down”. 

Which made me feel great because I always feel nervous before a presentation.  The point is if something is important to you then you will probably get nervous over it.  Presenting is an art form, it takes time to perfect your style.  Heck it takes time just to realize that you have a style and what it is!

The great thing about SQL Saturday is a lot of folks are in the same boat, and will be happy to give you feedback.  If you’d like ask me to come to your presentation and I’d be happy to sit in and give you a friendly face.  All you need to do is sign up and your half way there!  CLICK HERE to submit an Abstract for SQL Saturday 85.  I hope to see you there!



Monday, July 18, 2011

MagicPASS This Week!

The MagicPASS SQL Server User Group, SSUG in Celebration Fl, is meeting this Wednesday July 20th  and Dear Reader do we have a treat in store for you!  MVP & MCM 2008 Maciej Pilecki will be with us.   This is a wonderful opportunity to come out and meet some friends, or catch up with old ones in the SQL Community.    Also come see a top notch speaker, and up your SQL Learning!  

Normally you would only see someone like this at a Confrence, or if you are paying for some training.  Don't miss a chance to visit with one of the 87, currently, MCM's outside of Microsoft.  Here’s our speaker’s Bio:

Maciej Pilecki
Maciej Pilecki (Twitter: @DrHouseOfSQL) is a SQL Server Most Valuable Professional (MVP), Microsoft Certified Master in SQL Server 2008, Microsoft Certified Trainer and Senior Consultant with Project Botticelli. He has many years of international experience in Microsoft SQL Server database development, optimization and troubleshooting. He spends most of his time travelling around the world either advising his many customers on how to improve performance and reliability of their databases, or sharing the lessons learned with conference and user group audiences worldwide, where he is a popular and highly rated speaker. His favourite topics are the internals of the SQL Server database engine, including: SQLOS, Storage Engine and Query Optimizer and Performance Tuning
Maciej what is the topic you will be presenting on?

SQL Server Execution Plans - From Compilation To Caching To Reuse

Summary: Execution plan reuse is one of the most important aspects of building well-performing and scalable SQL Server solutions. But it’s also the one that is often overlooked during the design phase and becomes very difficult to troubleshoot later. In this session we will discuss in details the following aspects of SQL Server execution plans: compilation, re-compilation, parameterization, caching, reuse and aging. You will leave this session with full understanding of how to improve your server’s performance by increasing execution plan reuse (or how to avoid reuse in case this is necessary).

If you are interested in coming this Wednesday Here is a link to the MagicPASS website, and or to register CLICK HERE to go to the eventbrite page.  We’ll be starting the meeting at 5:00 pm, but the main event doesn’t begin until 6:30 pm.

“But Balls”, you say “I can’t make it at 5.”

No Worries Dear Reader, most people cannot.  We’ve got the room for the entire time and our fearless leader Kendal Van Dyke (@SQLDBA | Blog) will be showing a PASS presentation from Summit 2010 for those who can.  A lot of people will be arriving up and till 6:30, so don’t let the time stop you.

“But Balls”, you say “I’ll have to grab some dinner, I don’t know the area….”

Not to Worry Dear Reader, Dinner will be provided so make sure to RSVP so we have an accurate count of who to buy food for.  The meeting will be on the main Campus of Stetson University in beautiful Celebration FL, Here is the address for your GPS  Stetson University Center 800 Celebration Ave Celebration, FL 34747 or CLICK HERE for the Google Map.

The Meeting will cost you only your time and the gas to get here.  A Free dinner, a show, and a Microsoft Certified Master, you can't beat that kind of deal!

 I hope to see you at the meeting!



Thursday, July 14, 2011

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 9th OPASS, SQL Saturday 79 (Submitted, August 13th),  Pragmatic Works Webinar (August 23rd), SQL Saturday 85 Orlando (September 24th Submitted), PASS Summit 2011!!! (October), SQL Saturday 86 Tampa BI Edition (November 5th 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!



Friday, July 8, 2011

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.


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!


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.

     COUNT(*) AS [dbo.heap1 Count]

Let’s take our snapshot.

USE master;
CREATE DATABASE snapShotTest_Snapshot ON
    ( NAME = snapShotTest_Data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.BRADSQL2008R2\MSSQL\DATA\')
AS SNAPSHOT OF snapShotTest ;

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.

     COUNT(*) AS [dbo.heap1 Count]

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

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

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.