A handful of resources for SQL Server 2008 Microsoft Certified Master Knowledge Exam

I have just found out passed exam 088-970 (the knowledge one), so I am allowed to put myself into the ultimate test and take the famous lab exam. But beforehand, I would like to share with you some useful resources and tips (at least, they turned out to be useful for me).

The range of material

To take this exam, you should be familiar with the whole SQL Server Engine, from manageability through recoverability, security and performance tuning, to programmability. In other words, the exam description available at Microsoft Learning is in fact accurate.

Following white papers, books and articles will help you a lot (more on this later). There is only a handful of them, but you will find inside a lot of links to other resources (like blogs) — it turned out that those additional resources were essential as well.

Planning and Infrastructure Design

Infrastructure Planning and Design Microsoft® SQL Server® 2008 and SQL Server 2008 R2

Best Practices for Data Warehousing with SQL Server 2008 R2

Fast Track Data Warehouse 2.0 Architecture

SQL Server 2008 Upgrade Technical Reference Guide

IO Subsystems & Pre-deployment IO Best Practices

SQL Server I/O Basics, Chapter 2

Analyzing Characterizing and IO Size Considerations

FLASH Disk Opportunity for Server-Applications

Microsoft Support for iSCSI

RAID Levels and SQL Server

Description of using disk drive caches with SQL Server that every database administrator should know

Physical Database Storage Design

Disk Partition Alignment Best Practices for SQL Server

Physical Database Storage Design

Files and Filegroups Architecture

On Disk Structures, FILESTREAM and SPARSE Attributes

Microsoft SQL Server 2008 Internals by Kalen Delaney

FILESTREAM Storage in SQL Server 2008

SQLOS

Professional SQL Server 2008 Internals And Troubleshooting by Christian Bolton, Justin Langford, Brent Ozar, James Rowland-Jones and Steven Wort

How to Diagnose and Correct Errors 17883, 17884, 17887, and 17888

Transaction Log

Checkpoints and the Active Protion of the log

Write-Ahead Transaction Log

Transaction Log Logical Architecture

Transaction Log Physical Architecture

tempdb

Working with tempdb in SQL Server 2005

Capacity Planning for tempdb

TEMPDB Capacity Planning and Concurrency Considerations for Index Create and Rebuild

Data Compression

Data Compression: Strategy, Capacity Planning and Best Practices

DB Snapshots

Database Snapshot Performance Considerations under I/O-Intensive Workloads

Isolation Levels

SQL Server Concurrency Locking, Blocking and Row Versioning by Kalen Delaney

SQL Server 2005 Row Versioning-Based Transaction Isolation

Indexes from Every Angle

Online Index Operations

Partitioned Table and Index Strategies Using SQL Server 2008

Microsoft SQL Server 2000 Index Defragmentation Best Practices

Statistics, plan caching and recompilation

Statistics Used by the Query Optimizer in Microsoft SQL Server 2005

Statistics Used by the Query Optimizer in Microsoft SQL Server 2008

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

Plan Caching in SQL Server 2008

Troubleshooting Performance Problems

Troubleshooting Performance Problems in SQL Server 2005

Troubleshooting Performance Problems in SQL Server 2008

Performance Tuning with Wait Statistics

SQL Server 2005 Waits and Queues

Diagnosing and Resolving Latch Contention on SQL Server

The Data Loading Performance Guide 2008

Database Engine Tuning Advisor and Profiler

Database Engine Tuning Advisor (DTA) in SQL Server 2005

Mastering SQL Server Profiler by Brad M McGehee

Extended Events

Using SQL Server 2008 Extended Events

Backup and Restore

Backing Up and Restoring Databases in SQL Server

A Case Study: Fast and Reliable Backup and Restore of a VLDB over the Network

Partial Database Availability

CDC, CT and iFTS

SQL Server 2008 Full-Text Search: Internals and Enhancements

SQL Server Full Text Search Language Features

Best Practices for Integrated Full Text Search (iFTS) in SQL 2008

Tracking Changes in Your Enterprise Database

Tuning the Performance of Change Data Capture in SQL Server 2008

PBM and RG

Policy-Based Management

Using the Resource Governor

Spatial

Introduction to Spatial Coordinate Systems: Flat Maps for a Round Planet

Spatial Data Support in SQL SERVER 2008

XML

XML Support in Microsoft SQL Server 2005

XML Best Practices for Microsoft SQL Server 2005

XML Options in Microsoft SQL Server 2005

XML Indexes in SQL Server 2005

Performance Optimizations for the XML Data Type in SQL Server 2005

What’s New for XML in SQL Server 2008?

Client Programmability

Guide to the Data Development Platform for .NET Developers

Microsoft Data Development Technologies: Past, Present, and Future

Expert SQL Server 2005 Development by Adam Machanic with Hugo Kornelis and Lara Rubbelke

SQLCLR

Database Administrator’s Guide to SQL Server Database Engine .NET CLR Environment

Using CLR Integration in SQL Server 2005

Pro SQL Server 2005 Assemblies by Robin Dewson and Julian Skinner

Service Broker and Data-Dependent Routing

An Introduction to SQL Server Service Broker

Service Broker: Performance and Scalability Techniques

Building Reliable, Asynchronous Database Applications Using Service Broker

How SQL Server 2005 Enables Service-Oriented Database Architectures

Planning, Implementing, and Administering Scaleout Solutions with SQL Server 2005

Internals, Troubleshooting, and Best Practices for use of Scaleout Technologies in SQL Server 2005

Pro SQL Server 2008 Service Broker by Klaus Aschenbrenner

Security

Common SQL Server Security Issues and Solutions

SQL Server 2005 Security Best Practices – Operational and Administrative Tasks

Auditing in SQL Server 2008

Cryptography in SQL Server

Database Encryption in SQL Server 2008 Enterprise Edition

Engine Separation of Duties for the Application Developer

How to Implement Kerberos Constrained Delegation with SQL Server 2008

SQL Server Best Practices – Implementation of Database Object Schemas

SQL Server 2008 Compliance Guide

Database Mirrorring

Database Mirroring in SQL Server 2005

Database Mirroring (SQL Server)

Alerting on Database Mirroring Events

Database Mirroring Best Practices and Performance Considerations

Pro SQL Server 2008 Mirroring by Robert L. Davis and Ken Simmons

High Availability

High Availability with SQL Server 2008

Proven SQL Server Architectures for High Availability and Disaster Recovery

Selecting a High Availability Solution

Database Mirroring and Log Shipping Working Together

Internals, Troubleshooting, and Best Practices for use of Scaleout Technologies in SQL Server 2005

Using Replication for High Availability and Disaster Recovery: A SQL Server 2008 Technical Case Study and Best Practices

SQL Server Replication: Providing High Availability using Database Mirroring

SQL Server 2008 Failover Clustering

Pro SQL Server 2008 Failover Clustering by Allan Hirt

Consolidation

Server Consolidation with SQL Server 2008

Consolidation Using SQL Server 2008

SQL Server Consolidation at Microsoft

SQL Server Consolidation Guidance

Virtualization

Running SQL Server 2008 in a Hyper-V Environment. Best Practices and Performance Considerations

High Performance SQL Server Workloads on Hyper-V

Performance Best Practices for VMware vSphere™ 4.1

Some Thoughts and Tips

Actually, I don’t think that reading through those materials will be the best way to pass the knowledge exam. Unless you are an extraordinarily quick learner, you will probably forget most of the stuff read at the beginning before you finish. What’s more, this exam is quite different from the ITPro/MSCE ones — this time you will not be asked about some rarely used options. Instead, be prepared for a lot of “what, why and how” questions, i.e. what technology will best suit this scenario, why a specific problem arises and how to deal with a problem efficiently. In other words, you are expected to be able to use all the information in practice.

So, what are those materials used for? In my opinion, taking this exam is a great way to check your knowledge. But there are some obvious obstacles of doing this frequently. First of all, I think, the number of attempts is limited. But even if not, each one will cost you — not only the 367 euro fee, but also a 2-day trip to the nearest secure exam center. Yes, Berlin is a great place to visit, and you will even find nice steaks near the test center, but still. Hence, by reading those materials you can check your knowledge (while reading you should nod your head and say “Yeah, I know that”) at home and bit by bit. And if you find some new stuff, take your time and practice it. For example, I was scared of scalability questions and pretty confident about performance tuning, but it turned out that scalability was one of the areas where I made no mistake, and my worst part was performance tuning.

By the way, my colleague and fellow MVP Łukasz Grala got a confirmation from Microsoft Regional Service Center that MCTs 50% discount applies to MCM Knowledge and Lab exams as well.

Finally, if you are preparing for this exam, you definitely should get familiar with dozen of blogs and watch the MCM readiness videos. Why? Well, apart from their technical quality you may get a snitch on some question intentions. Quite useful, especially when you are being asked for choosing “the best solution”.

Cheers

Share this article ...

Google Plus
Ihren LinkedIn Kontakten zeigen



This entry was posted in General, Microsoft Certified Master and tagged , by Marcin Szeliga. Bookmark the permalink.
Marcin Szeliga

About Marcin Szeliga

Since 2006 invariably awarded Microsoft Most Valuable Professional title in the SQL category. A consultant, lecturer, authorized Microsoft trainer with 15 years’ experience, and a database systems architect. He prepared Microsoft partners for the upgrade to SQL Server 2008 and 2012 versions within the Train to Trainers program. A speaker at numerous conferences, including Microsoft Technology Summit, SQL Saturday, SQL Day, Microsoft Security Summit, Heroes Happen {Here}, as well as at user groups meetings. The author of many books and articles devoted to SQL Server.

2 thoughts on “A handful of resources for SQL Server 2008 Microsoft Certified Master Knowledge Exam