Today I took part in a SQL 2008 Hands-on-Lab at the Microsoft office in Farmington, CT. I know what you are thinking, “Hey, didn’t that guy already take part in that lab last week? What gives? How many times is he going to skip out on a day in the office for a day in a lab?” Well, this lab was different. The previous lab was centered on upgrading your instances to SQL 2008, with some time for playing around after the lab was complete. Today was just pure happy-fun-play-time with some of the new features in SQL 2008. I was given a brief intro which took about thirty minutes and then it was time to dive right in. We had five labs and two optional labs. Of course, we know that nothing is ever optional, right? So, let’s just say there were seven labs.

One thing that was different about this facility than the one in Waltham is the structure of the learning center. Waltham has dedicated lab rooms for a handful of people to work at one time. Here we were in a classroom style setting which was not very conducive to learning. For example, everyone coming and going would be a distraction. Plus people talking while the instructor was talking was also a distraction. Fortunately I had chosen a seat that would allow for minimal distractions. One thing I found odd was about a third of the class left before lunch. I am not certain if they got through all their labs or if they were just looking for a day off from work. Or perhaps I am the geek. Oh well.

Here is a breakdown of the labs and topics covered.

Using the Resource Governor to Throttle Backups

This is becoming one of my favorite new features of SQL 2008. The lab was set up to give the user an idea for not only how it works, but to see it in action. Most importantly the lab also had this note:

“The resource pool enables you to create limits for resource usage. These limits are applied if other applications require the resources. If no other applications are running, the limits are not applied.”

What that means is if you configure the RG to limit your database backup process to consume no more than five percent of the available CPU, if no other applications are running your backup process will consume whatever CPU it needs to complete. Boy, I really wish I had known that little nugget before I started putting together a presentation last week. Those are two hours I wish I had back.

For more info on Resource Governor concepts, click here.

Transparent Data Encryption Tutorial

Okay, I will admit it, every time I see something with the word ‘encryption’ I look the other way. Yeah, I know those features are there, but they also introduce a layer of complexity that may or may not be desired. And then the whole idea of keys, certificates, etc., well, I just never had a desire to spend any time digging through the details.

Well, the TDE lab changed a lot of that for me today. The lab was designed to demonstrate how you can use TDE to encrypt your entire database. The beauty here would be that this encryption is transparent to the end user (hence the name), there is no need to decrypt the data. So, the end user has no idea that the data is encrypted, they can browse the database just as if nothing was different.

So what is different? Well, the backup, that’s what is different. When you take a backup of a database that has had TDE applied that backup cannot be restored to another server without the server master key. So, this is great for protecting your data should it ever be lost, stolen, or happen to walk out of your local storage facility. Unfortunately it is not great should you want to easily move database backups between different servers (hello, restoring from production to test anyone?) without the need for carrying an extra set of keys around. Still, it should be easy enough to set up a process to handle everything in order for your team to continue to perform routine tasks.

For more info on TDE, click here.

Spatial Data Tutorial

This lab was long, mostly due to my having to type in latitude and longitude coordinates over, and over, and over. And then, after typing them over, and over, and over, I was able to point to some files that had more coordinates that I would be able to copy and paste, and copy and paste, and copy and paste. Did you know that there are five different ways to insert records with point values into a table? Neither did I. And, now that I know, I really wish I didn’t. Honestly, whoever put together this lab was probably just dismissed from the CIA for water-boarding and decided to go into writing tech labs.

Here was the real test. When I got home I told my children that there were five ways to insert this data and I had to do it eight times. They were unimpressed. Case closed.

Now, after getting dried off I was able to run some queries against the data I had entered and the functions and results were pretty cool. Not sure how useful they would be to me today, unless I was trying to locate just how many square miles of bacon there is in Iowa, but I am sure this is all very useful to someone. Somewhere. Just not to me today, but I am glad I had the chance to be introduced to the material.

For a great explanation of spatial data and applications, click here.

SQL Server Analysis Services Tutorial

The next lab was focused on some basic administrative tasks involving SSAS. To be honest, I am not certain there is much difference between this version ad the SQL 2005 version of SSAS. I took a class on SSAS last year and perhaps that is why much of the details in this lab were familiar to me. We did a backup, we modified security, we did a restore, we configured service accounts, *YAWN*. Sorry, I was just having a flashback to earlier today. Anyway, Like I was saying, much of this lab was focused on giving a very basic overview of some very basic admin tasks that you may or may not have been exposed to already.

Honestly, did the people putting this lab together think that they could cover ‘Managing Microsoft SQL Server 2008 Analysis Services’ in forty-five minutes? My class last year was three days long and we didn’t cover everything then either. The lab was not entirely useless, as I picked up a few queries on how to monitor performance. I am not sure I have ever thought about digging into SSAS for performance reasons, but now at least I am more aware that such issues can exist. More importantly I have a few basic tools with which to get started.

SQL Server Report Services Tutorial

And here we go again. This lab was designed for sixty minutes, so someone somewhere thinks that SSRS is at least 33% more difficult to manage than SSAS. Again, what is different in SSRS 2008 from SSRS 2005? Anything? The lab covered the security configuration (I am fairly certain SSRS 2005 has that same option), it covered user security and role creation (yeah, not new), and then managing subscriptions (and I suddenly lost interest).

I do not want to give the impression that the last two labs were a waste of my time, but perhaps those people who left early knew something I didn’t. Actually, they probably know a lot more than I do. But, I really am glad I spent the time poking around, there is no doubt it was worth the effort. And since I had time to spare, having finished the five labs around lunch time, I decided to do the two optional labs.

Implementing Partition Processing for Data Warehousing

Okay, partition functions and schemes are definitely not new in SQL 2008. The lab took me through the creation of additional filegroups, then a partition function, a partition scheme, and finally a partitioned table. Nothing special so far, right? I agree. I inserted some data, viewed the data, and got myself a cookie. It was that exciting.

After the creation of some views and indexes we looked at implementing a ‘sliding window’ scenario. What this means is that from time to time you may want to move data around on your partition between different filegroups using SPLIT, SWITCH, and MERGE statements, three statement I hardly ever use on a daily basis, so this lab had some merit for me. By the time my cookie was done I had archived some data and swapped it from one filegroup to another, pausing to run some SELECT statements along the way to see the affects as they happened.

I honestly feel that partitioning is one of the least used aspects of SQL 2005/8. I am not certain why that is the case, but when it comes to true data warehousing applications it makes sense to take advantage of this functionality. I think the real downside to working with these objects is that their size can be a limiting factor when trying to do development work, as your queries can take minutes or even hours to complete. So, say I have a SELECT that takes an hour to run, and I build a partition, an index or two, and the next time I run the SELECT it takes forty minutes. That might be a significant reduction, but the developer still had to wait forty minutes for it to finish, and I know I may not be happy to have spent so long trying to tune things.

When you start to calculate the costs of having two or three people try to tackle something that can be cumbersome to work with, you can see why many companies won’t bother and they will just throw money at new hardware instead. I am not making an excuse there, just trying to help people understand why those decisions are sometimes made (right or wrong).

Implementing Query Plan Freezing

The last lab of the day was on query plans, specifically how to capture and deploy plans to be reused. I found this to be quite interesting as I had not heard of plan freezing. I know in SQL 2005 you could do more with query plans than in SQL 2000, but I never really played with any of that stuff in SQL 2005, so this could all be old news to most of you. But for me it was cool to play with for a few minutes.

Essentially the idea is that if query performance is poorly afected by an upgrade you would be able to capture and use the old query plan. I have certainly seen that happen on more than one occasion and typically my response has been “bad queries get worse, good queries get better”, but that was in reference to an upgrade between versions of SQL Server. The lab gives me the impression they are talking about an application upgrade, or a database deployment of some type.

The process they outline is quite simple. Capture your current (good) plan and save it for future use. Yep, it’s that simple. Not sure how I am supposed to know when to capture a good plan, perhaps right after i run a query and deem performance to be acceptable. Then, disable that plan and save it until it is needed. My guess is I will know when it is needed when my phone rings with someone screaming at me. And I look forward to explaining to them that “everything is okay, i had the good plan here all i had to do was enable it”.

“Why was it even disabled to begin with?”

“Um…because, that’s what they taught me at DBA school. Look, it’s all very complicated and…that’s my phone…it’s SQLJackBauer…he says he will be right up to explain it to you personally.”

As if that wasn’t enough excitement to end out the day, the last part of the last lab was pretty cool. In SSMS 2008 you can use the GUI to script out plan guides in order to migrate them between databases and servers. That really was cool to be able to right click on a plan guide in the Object Explorer.

TechNet has a Virtual Lab on plan guide freezing that you can access here.

All in all, the day was well worth the trip and I highly recommend everyone taking the time themselves to become familiar with a lot of the functionality in SQL 2008 in a lab setting. If nothing else there will always be a handful of people around and available for you to ask questions, and you know the three words I live by, right?

Connect.

Learn.

Share.

Mission accomplished.