Rick Heiges

At the PASS Summit, the folks from PSS had a lab environment where you could sit down and go through some exercises to learn how to use tools and learn what to look for when diagnosing SQL Server problems.  PSS was also at the Seattle Summit last year and I did not get a chance to go to the labs.  This year, I was able to carve out an hour to see what it was about.  I started on the Memory labs.  Because of everything else that was going on, I did not have time to finish.  These same labs are now available for download and use on your own system.  There is a link to the files at http://www.sqlpass.org where you can obtain them.  These files are available to everyone - not just attendees of the PASS Summit.

These labs are based on SQL Server 2000 sp3a and hotfix MS03-031.  Do NOT use these on your production server.  Set up a test server, VPC, or other PC to work on these.

The labs include the following topics:

• Memory
• Performance
• Blocking
• Recovery
• Debugging

Enjoy!

Last week I spent a lot of time with my students on the basics of INSERT/UPDATE/DELETE statements.  Since they had already been exposed to the basic form of the statements, I was real excited to show them how to use all of these SELECT staements I had them doing earlier in the term with these statements.  Sub-queries still have several of them confused, but they seemed to have grasped the concept of getting the SELECT working frist before marrying it to an INSERT / UPDATE / DELETE statement. Shortly, we will start getting these statements inside of transactions.  That will really start to get them thinking!  This week is fall break! 

Here's to the future!

Once again I am blogging about material presented to my class.  This time it is Backup/Recovery basics. 

The students have been quizzed on RAID and now have a good understanding of what RAID works well for the data file and what RAID works well for the log.  Again, this is where the SQL Server difference with Access is highlighted.  We are assuming the FULL Recovery Model when discussing this subject. 

I have a scenario where a FULL backup is done at 1am Sunday and Differential backup is done on the other days also at 1am.  I have a second scenario where FULL backups are done daily at 1am.  We then go through the steps to take if a hard drive crashes on various days.  We can't forget about log backups either!  In short, this discussion really helps the students see the separation between a personal database and an enterprise class database.  We will discuss High Availability later in the course.

This is one of my favorite topics because I believe it will stick with them when they go out in the workforce and make decisions about the back-end database.  Sure an access mdb file is easier to care for in the simplest sense, but the SQL Server model puts a premium on the value of data.  It takes more work and skill to cae for a SQL Server database, but the data is protected if setup properly. 

I mean BASIC basics.

I started back to teaching class on Monday after attending PASS in Orlando.  I teach a class focused mainly on SQL Server which is why I am writing today.  Most college students don't even think about performance or safety of data or even the difference between SQL Server and Access.  It is my job to educate them on these principles as part of the course.

We had already gone over several of the key structural differences between Access and SQL Server, but they really hadn't been exposed to the real reasons why it is so important.  They knew that an Access MDB was a singular file and that a SQL Server database had a data file and a log file.  They had to know this in order to get their first project completed.  We focused mainly on RAID devices and the pros and cons of the major RAID arrays supported by Microsoft.  They understand that Hard Drives are relatively cheap and can be unreliable, and  Hard Drives are the slowest part of the system (in some cases - the user is the slowest).  RAID arrays can help alleviate some of these problems.  It is such a good feeling to see the lightbulbs come on as you see them understand the various RAID arrays and why it is important for Database Performance and the Safety of the data.  Tomorrow, we discuss another basic  feature known as Backup/Restore.  It doesn't sound sexy, but it really is a concept they can get their hands/brains around .

The really difficult part for me is to NOT tell them about all of the really neat features in SQL Server 2005 coming out to aid in the High Availability space.  They just aren't there yet to really appreciate a discussion on that topic yet.  I usually have 1 or 2 students who are ready for that discussion; that is when it is really fun.

In a couple of weeks, I will hit them with Indexing.  Most of them have an idea about what an index is, but really do not understand the underlying concepts until they are forced to do so.  I have never met anyone who went to college to become a DBA as their career goal.  Most people who become a DBA were “drafted” into service.  Hopefully, these students will be able to have a better idea of what is coming their way.

I attended a session late on Thursday on SQL Server Security by Randy Dyess.  It was a good presentation on the attack surfaces as well as pointers to other resources where deeper info could be found.

Friday's Keynote was done by HP.  The best portion was the demo of their Installation tool which simplifies installation and updates of the SQL Server instances within your organization.  Very cool!

At last year's PASS event, I really could not get over to the PSS Labs.  I just went through the Memory Labs.  Picked up a few tips to help me manage things better!  I will not be able to get through the other labs here, but the other labs include:

• Blocking and Deadlocking
• Performance
• Debugging
• Server and Data Recovery

This lab was manned by some of the best PSS folks like Ken Henderson.  Others also seemed to enjoy the labs as well.