Exam 70-432: Week 1 - Lab Exercises

Here's the first Lab Exercise for Week 1. This is for Chapter 1, Lesson 3: Installing and Configuring a SQL Server Instance.

Practice 1 Creating Service Accounts
Steps 1-3:




Step 4:




Step 5:



Step 6:



Practice 2 Install a SQL Server Instance

Step 1:



Steps 2-4:




Step 5:








Step 6:



Step 7:



Step 8:



Step 9:




Step 10:




Step 11:



Step 12:



Step 13:



Step 14: I accidentally skipped Step 14. I'll enable FILESTREAM later.

Steps 15-16:



Steps 17-18:

Exam 70-432: Week 1 - QotD

Here's this week's Question of the Day, from SQLServerCentral.com:

Which fact about DBCC CHECKDB in SQL Server 2008 R2 is not true?

1. DBCC CHECKDB uses an internal database snapshot for the transactional consistency needed to perform these checks
2. DBCC CHECKDB can repair corruption if you specify the REPAIR_ALLOW_DATA_LOSS option.
3. DBCC CHECKDB does not check binary large objects (blobs) stored in the database.
4. DBCC CHECKDB performs parallel checking of objects

I got this right by process of elimination. The first, second and fourth answers are copied verbatim from Books Online. The third option, which is not true (so it's the correct answer), comes from here: http://msdn.microsoft.com/en-us/library/ms176064%28v=sql.105%29.aspx and says that DBCC CHECKDB "Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM."

I'm not sure I agree that's the same as checking BLOB's actually stored in the database, but I guess that's splitting hairs.

Exam 70-432: Week 1 Redux

This week's online forum posting comes from a question posted in the SQLServerCentral group on LinkedIn:

"I created a table with 4 fields (Col1 Int, Col2 Varchar(20), Col3 Varchar(20), Col4 Varchar(20)). without any rows. Does this empty table occupy space on the database?"

I thought a page might get allocated at table creation time, so I started looking. In Rod Colledge's book, SQL Server 2008 Administration in Action, I found the DBCC CHECKTABLE command and put it to good use:

use TK432

go

create table PageCheck (col1 int)

go

dbcc checktable ("PageCheck");

go

DBCC results for 'PageCheck'.

There are 0 rows in 0 pages for object "PageCheck".

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

So it looks like an empty table does not take up any space (except for the table definition).

If anyone disagrees, please speak up.

Exam 70-432: Reboot!

If you've been following along, you'll know that I've been studying for the MCTS exam in SQL Server 2008. To put it bluntly, the class hasn't been going that well. I'm not getting enough time for the practice that makes the difference between just cramming for the test and really learning the material. And as I said here, the goal is knowledge, not a piece of paper.

So I've re-designed the class, with a new syllabus. Feel free to take a look, and use it if you find it useful. The new schedule calls for two weeks per course topic instead of just one. That should give me enough time for the increased hands-on workload, namely:

• Posting the lab exercises here.
• Answering at least one 'Question of the Day' from SQLServerCentral.com  (or something equivalent), and discussing how I came up with my conclusion.
• There's now a Class Project, due the day after the Final Exam. The project is to come up with a database, and create a maintenance document for it. The document will be enhanced and expanded, based on each week's topic.

As before, each week I have to make at least one significant contribution to an online forum and continue blog about the class.

There's more time now, but there's more work to do. So let's get going!

Exam 70-432: Week 2

Week 2 is finished. I didn't do as much hands-on practicing as I should have, partly because I participated in Global Accessibility Awareness Day by captioning some news video for Amara.

Here's what was done:

• Self-Test for Chapter 7 in The Real MCTS SQL Server 2008 Exam 70-432 Prep Kit
• Practice tests for Chapters 8, 9 and 10 in the Lab Manual
• Asked a question about the BIT data type and row compression in SQL Server Central 
• Posted this blog entry.

I didn't do quite as well on the tests this week: 16/20 in the Self-Test and failing the Practice Test by one question.

Next week, I'm going to try a different approach. Instead of doing all the reading first, then practicing, I'll complete the reading and practicing for each chapter, one at a time.

Speaking of next week, the topic is Managing SQL Server Security.

Next week's reading is Chapters 14, 18 and 19 in the Carpenter book, and Chapters 4 and 5 in Horninger.

The assignments (due 5/20/2012):

• Self-Test for Chapter 4 and 5 (Horninger)
• Hotek: Chapter 8 Lessons 1-3; chapter 11, Lessons 2-6. Practice tests for chapters 8 and 11.
• Post in an online forum
• Summarize the week’s work in a blog entry

Meme Monday For May

If you're an alliteration aficionado, you'll like the fact that's it's May Meme Monday! Thanks to Thomas LaRock (blog|Twitter) for a thought-provoking question:

If you could go back in time and meet yourself on Day One of your IT career, what advice would you give?

This was harder to answer than I thought it would be. I guess the most important piece of advice would be the thoroughly un-original: don't 'go with the flow' in your job, accepting things the way they are; take a more active role in managing your career.

A few more bon mots from the present to the 1970-something version of me:

• Don't waste your time trying to be a programmer; you're not going to be very good at it.
• You're going to write an application using a product named 'dBase' on a 'Personal Computer' in a few years, and you're going to think it's pretty cool. Stay focused on that.
• There's going to be this company called 'Apple'. When their stock gets ridiculously low, buy it - lots of it. We'll both be glad you did.

Exam 70-432: Week 1

The first week of class is finished!

After getting through the reading, I installed SQL Server 2008 R2 in a VM and used it to complete the practice exercises in the Carpenter book.

To recap this week's assignments:

• Self-tests for Chapters 2 and 3 in the Horninger book.
• Practice lessons for Chapters 1 and 5 in the Lab Manual (Hotek).
• Post in one or more online forums.
• Take the practice test for the first concept: Install and Configure SQL Server 2008.
• Make this blog entry.

I did well in the tests: 17/20 and 18/20 in the Self-Tests and 92% in the Practice Test. I really aced the Practice Test, because the one question I 'missed' was actually wrong: sp_filestream_configure is obsolete, the command is actually sp_configure filestream_access_level [0|1|2].

Definitely the hardest assignment was making a meaningful post in an online forum. I don't have enough experience to make a significant contribution yet. I did discuss certification training in general (and this class specifically) in SQLServerCentral.com, so I'm going to have to count that.

Starting tomorrow: Maintaining SQL Server Instances

Reading:

• Chapters 13, 14 and 16 in Carpenter.
• Chapter 7 in the Horninger book.

Assignments (due 5/13/2012):

• Self-Test for chapter 7 (Horninger)
• Hotek: Chapter 8. Lesson 1-2; chapter 9, lesson 1-3; chapter 10, lessons 2-4 Practice tests for chapters 8-10
• Post in an online forum
• Summarize the week’s work in a blog entry