My torn data pages - June, 2008

After running through the prep-guide (looking through a pair of SQL Server 2005 glasses), I identified a couple of topics worth giving a closer look. The topics are derived from the prep-guide, my comments about the topic added in blue italics and the bulleted list refers to (mostly) BOL-resources. This post is based on the prep-guide for 70-432 with published date June 11, 2008

Installing and Configuring SQL Server 2008 (10 percent)

Configure additional SQL Server components.
This objective may include but is not limited to: SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS), replication. Not that I expect this to be really different from SQL Server 2005, but if your background is just DBA (MCTS/MCITP) it may be your first encounter with the BI-components.

• Considerations for Installing Integration Services
• Considerations for Installing Analysis Services
• Considerations for Installing Reporting Services

Maintaining SQL Server Instances (13 percent)

Implement the declarative management framework (DMF).
This objective may include but is not limited to: create a policy; verify a policy; schedule a policy compliance check; enforce a policy; create a condition.

• Administering Servers by Using Policy-Based Management Entry page, includes tutorial.
• Oh, DMF = Policy-Based Management... stupid MS-Name-Game.

Back up a SQL Server environment.
This objective may include but is not limited to: operating system-level concepts. I don't expect a lot of fireworks, but the operating system-level concepts made me curious.

• Planning for Disaster Recovery Actually, I'm still curious what is meant by operating system-level concepts. This link from BOL is actually my best shot at a document where some broader considerations are presented.

Managing SQL Server Security (15 percent)

Manage transparent data encryption.
This objective may include but is not limited to: impact of transparent data encryption on backups.

• Database Encryption in SQL Server 2008 Enterprise Edition General overview of encryption, how TDE fits in and some remarks on how backup is affected. Since the topic says "not limited to", consider the whole article to be valuable.

Maintaining a SQL Server Database (16 percent)

Back up databases.
This objective may include but is not limited to: full backups; differential backups; transaction log; compressed backups; file and filegroup backups; verifying backup. Only compressed backups is to be classified as new.

• Backup Compression (SQL Server)

Performing Data Management Tasks (14 percent)

Implement data compression.
This objective may include but is not limited to: sparse columns; page/row.

• Using Sparse Columns
• Using Column Sets

Maintain indexes.
This objective may include but is not limited to: create spatial indexes; create partitioned indexes; clustered and non-clustered indexes; XML indexes; disable and enable indexes; filtered index on sparse columns; indexes with included columns; rebuilding/reorganizing indexes; online/offline. Spatial and filtered indexes on sparse columns are of interest here, along with "is not limited to" which could be indexes on hierarchyid columns.

• Working with Spatial Indexes (Database Engine) Entry Page
• Filtered Index Design Guidelines
• Using hierarchyid Data Types (Database Engine) includes Tutorial

Optimizing SQL Server Performance (10 percent)

Implement Resource Governor.

• Managing SQL Server Workloads with Resource Governor Entry page, includes How-To

Use Performance Studio.

• Data Collection Entry page, includes How-To
• Again, Performance Studio, also an MS-Name-Game, what you're really looking for is Data Collection... and trying to get that confirmed, I found this webcast by Bill Ramos (62 minutes).

The rest, well it is all too familiar from SQL Server 2005. Sure, I'll look for some "What's new" resources, but I think the above pretty much covers what I need to familiarize my self with.

Release Candidate 0 is available for download (and downloading) and the MCTS exam 70-432 went into beta testing (and I registered). Since the beta is only running from June 9th through June 30th, I had to go for 27th as it was the only gap in my schedule. Let's see if I can find the time to blog about my preparations...

Exam-stats: 180 minutes¹, 61 question spread over 6 testlets (cases), passing score 700 points, only multiple choice questions, no simulations. I got 752, was lousy on SSAS ...and this actually was the first Microsoft exam where I really needed the time!!!  Compared to the other MCITP (70-443, 70-444 and 71-647) exams I sat, it was a lot more reading and fact-finding in the case-studies.

What surprised me on this exam, were a couple of questions targeted at the database engine. Think towards backup-requirements for filegroups (which are needed for partitioned tables), index optimization and transaction isolation levels (not mentioned in prep-guide). Unfortunately these topics aren't covered² in the courses 2794 to 2797 (or in 2791 to 2793). From the topics that are covered in the prep-guide, I'd say the number of questions was pretty balanced, only four things were really sticking out:

• Which data mining algorithm to apply in a certain scenario.
• Storage strategy for SSAS cubes.
• Slowly changing dimensions.
• Designing dimensions and hierarchies.

Useful resources for preparation.

• First of all, don't count on the Training-Kit for 70-446 from MS Press, it was announced but won't be released (MCITP BI News; some news for MCTS BI and SQL Server 2008). But if you don't take the Microsoft courses, the MCTS Training Kit for 70-445 (ISBN 9780735623415) is a useful resource.
• Prep-guide (don't go there without one).
• Peter Koller's review of 70-446 beta (and 70-445 beta).
• Jorg Klein's passing of 70-446.
• Ronald Kaijesteijns "verslag" (that is Dutch, as well as the rest of his post).

¹Actually, you get about 3 minutes per question grouped per testlet. This means for a 9 question testlet you get about 27 minutes, time left on one testlet is not added to the next. The 180 minutes should be regarded as an indication for the maximum exam length.
² At best superficially mentioned in 2796.