SQL Server MCM Training Videos – Further Reading

INTRODUCTION

(Edit 2017: Some of these resources may be quite far out-of-date, as this list has not been maintained since 2012 when the MCM program was cancelled by Microsoft.)

This web page contains links to further study aids for each of the training videos to help you study for the Microsoft Certified Master – SQL Server 2008  and 2012 certifications. These blog posts, Whitepapers, and other recordings are from a variety of sources that we trust and recommend to our customers. Note that there are no references to SQL Server 2008 Books Online – an MCM candidate should be able to find appropriate BOL topics. Also note that this is not an exhaustive list.

This is just a small taster of what you need to know. We offer a comprehensive online and in-person training program that will help you towards your MCM/MCSM certification, as well as provide any DBA with a wealth of knowledge that can immediately be put to use.

Thanks, Paul and Kimberly

DATABASE STRUCTURES

Books

• SQL Server 2008 Internals – Ch 3, 5 (Microsoft Press, Kalen Delaney, Paul S. Randal, Kimberly L. Tripp, et al)

Blog Posts/Categories

• Paul’s SQLskills.com blog, On-Disk Structures category
• Paul’s SQLskills.com blog, LOB Data category

NEW DATABASE STRUCTURES IN 2008

Books

• SQL Server 2008 Internals – Ch 7 (Microsoft Press, Kalen Delaney, Paul S. Randal, Kimberly L. Tripp, et al)

Whitepapers

• Data Compression: Strategy, Capacity Planning and Best Practices
• FILESTREAM Storage in SQL Server 2008

Blog Posts/Categories

• Paul’s SQLskills.com blog, FILESTREAM category
• SQL Server Storage Engine blog, Data Compression category
• Paul’s SQLskills.com blog, Sparse Columns category

DATA FILE INTERNALS AND MAINTENANCE

Books

• SQL Server 2008 Internals – Ch 3 (Microsoft Press, Kalen Delaney, Paul S. Randal, Kimberly L. Tripp, et al)

Whitepapers

• Disk Partition Alignment Best Practices for SQL Server
• Physical Database Storage Design
• Working with Tempdb in SQL Server 2005

Blog Posts/Categories

• Paul’s SQLskills.com blog, Instant Initialization category
• Paul’s SQLskills.com blog, Shrink category
• Paul’s SQLskills.com blog, tempdb category
• Paul’s SQLskills.com blog, Database Maintenance category

LOG FILE INTERNALS AND MAINTENANCE

Books

• SQL Server 2008 Internals – Ch 4 (Microsoft Press, Kalen Delaney, Paul S. Randal, Kimberly L. Tripp, et al)

Blog Posts/Categories

• Paul’s SQLskills.com blog, Transaction Log category
• Kimberly’s SQLskills.com blog, Transaction Log category

Magazine Articles

• Understanding Logging and Recovery in SQL Server (TechNet Magazine, February 2009, Paul S. Randal)

Community Articles

• Diagnosing Transaction Log Performance Issues and Limits of the Log Manager

INDEX INTERNALS

Books

• SQL Server 2008 Internals – Ch 6 (Microsoft Press, Kalen Delaney, Paul S. Randal, Kimberly L. Tripp, et al)
• SQL Server 2008 Internals – Ch 6 (companion content)

Blog Posts/Categories

• Paul’s SQLskills.com blog, Indexes From Every Angle category
• Kimberly’s SQLskills.com blog, Indexes category

CLUSTERED INDEX DEBATE

Books

• SQL Server 2008 Internals – Ch 6 (Microsoft Press, Kalen Delaney, Paul S. Randal, Kimberly L. Tripp, et al)

Blog Posts/Categories

• Kimberly’s SQLskills.com blog, Clustering Key category

Whitepapers

• Comparing Tables Organized with Clustered Indexes versus Heaps

STATISTICS

Books

• SQL Server 2008 Internals – Ch 8 (Microsoft Press, Kalen Delaney, Paul S. Randal, Kimberly L. Tripp, et al)
• SQL Server 2008 Query Performance Tuning Distilled – Ch 7 (APress, Apress, Grant Fritchey and Sajal Dam)

Blog Posts/Categories

• Kimberly’s SQLskills.com blog, Statistics category
• SQL Server Query Processing Team blog, various statistics categories

Whitepapers

• Statistics Used by the Query Optimizer in Microsoft SQL Server 2008
• Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator

INDEXING STRATEGIES

From the video:

• Credit Database .zip
• Demo Scripts .zip

Blog Posts/Categories

• Kimberly’s SQLskills.com blog, The Tipping Point category
• Kimberly’s SQLskills.com blog, Nonclustered Indexes category

INDEX ANALYSIS

Whitepapers

• Microsoft SQL Server Index Defragmentation Best Practices
• Online Indexing Operations in SQL Server

Blog Posts/Categories

• Kimberly’s SQLskills.com blog, Indexes category
• Paul’s SQLskills.com blog, Indexes From Every Angle category
• Bart Duncan’s Missing Indexes blog post

Other Links

• Ola Hallengren’s Maintenance Package

LOCKING

Books

• SQL Server 2008 Internals – Ch 10 (Microsoft Press, Kalen Delaney, Paul S. Randal, Kimberly L. Tripp, et al)
• SQL Server 2008 Query Performance Tuning Distilled – Ch 12-13 (APress, Apress, Grant Fritchey and Sajal Dam)

Blog Posts/Categories

• Paul’s SQLskills.com blog, Locking category
• Bart Duncan’s MSDN blog, Locking category

SNAPSHOT ISOLATION

Whitepapers

• SQL Server 2005 Row Versioning-Based Transaction Isolation

INDEX FRAGMENTATION

Books

• SQL Server 2008 Internals – Ch 6 (Microsoft Press, Kalen Delaney, Paul S. Randal, Kimberly L. Tripp, et al)
• SQL Server 2008 Query Performance Tuning Distilled – Ch 8 (APress, Apress, Grant Fritchey and Sajal Dam)

Whitepapers

• Online Indexing Operations in SQL Server
• Microsoft SQL Server Index Defragmentation Best Practices

Blog Posts/Categories

• Paul’s SQLskills.com blog, Fragmentation category
• Paul’s SQLskills.com blog, Indexes From Every Angle category
• Paul’s SQLskills.com blog, Indexes category

BACKUP INTERNALS & RESTORE INTERNALS

Whitepapers

• Fast and Reliable Backup and Restore of Multi-Terabytes Database over the Network

Blog Posts/Categories

• Paul’s SQLskills.com blog, Backup/Restore category

Magazine Articles

• Understanding SQL Server Backups (TechNet Magazine, July 2009, Paul S. Randal)
• SQL Server: Recovering From Disasters Using Backups (TechNet Magazine, November 2009, Paul S. Randal)

Community Articles

• Tuning the Performance of Backup Compression in SQL Server 2008
• Tuning Backup Compression Part 2

CONSISTENCY CHECKING

Books

• SQL Server 2008 Internals – Ch 11 (Microsoft Press, Kalen Delaney, Paul S. Randal, Kimberly L. Tripp, et al)

Blog Posts/Categories

• Paul’s SQLskills.com blog, CHECKDB From Every Angle category
• Paul’s SQLskills.com blog, Corruption category
• Paul’s SQLskills.com blog, IO Subsystems category
• Paul’s SQLskills.com blog, Disaster Recovery category
• Paul’s SQLskills.com blog, SQL Agent category
• Paul’s SQLskills.com blog, Repair category

Websites

• Conference corruption demo scripts and example corrupt databases

DATABASE SNAPSHOTS

Books

• SQL Server 2008 Internals – Ch 3 (Microsoft Press, Kalen Delaney, Paul S. Randal, Kimberly L. Tripp, et al)

Whitepapers

• Database Snapshot Performance Considerations under I/O-Intensive Workloads

Blog Posts/Categories

• Paul’s SQLskills.com blog, Database Snapshots category

PARTITIONING/PARTIAL DATABASE AVAILABILITY/PIECEMEAL RESTORE

Resource List

• Kendra Little’s comprehensive partitioning resource list

Whitepapers

• Partial Database Availability

Books Online (Yes I know I said no BOL, but this is the only place it’s described in detail)

• Performing Piecemeal Restores

OPTIMIZING STORED PROCEDURES

Books

• SQL Server 2008 Internals – Ch 9 (Microsoft Press, Kalen Delaney, Paul S. Randal, Kimberly L. Tripp, et al)

Whitepapers

• Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2012
• Plan Caching in SQL Server 2008

Blog Posts/Categories

• SQL Server Query Optimizer Team blog
• Gail Shaw’s blog

Community Articles

• KB Article: Troubleshooting stored procedure recompilation

SPARSE COLUMNS

Books

• SQL Server 2008 Internals – Ch 7 (Microsoft Press, Kalen Delaney, Paul S. Randal, Kimberly L. Tripp, et al)

Blog Posts/Categories

• Paul’s SQLskills.com blog, Sparse Columns category

WAITS AND QUEUES

Whitepapers

• Performance Tuning Using Waits and Queues

Blog Posts/Categories

• Paul’s SQLskills.com blog, Wait Stats category

Wikis/Community Articles

• Wait Stats Introductory Reference
• Perfmon Best Practices

Websites

• Service Broker Wait Types – SQL SSB Team
• PSS Wait Stats Repository

EXTENDED EVENTS

Books

• SQL Server 2008 Internals – Ch 2 (Microsoft Press, Kalen Delaney, Paul S. Randal, Kimberly L. Tripp, et al)

Whitepapers

• Using SQL Server 2008 Extended Events, Jonathan Kehayias

Blog Posts/Categories

• Paul’s SQLskills.com blog, Extended Events category
• Jonathan’ SQLskills.com blog, Extended Events category
• Extended Events Team blog
• Resolving DTC Related Waits and Tuning Scalability of DTC

Magazine Articles

• Advanced Troubleshooting with Extended Events (TechNet Magazine, January 2009, Paul S. Randal)

BENCHMARKING AND BASELINING

Books

• Professional SQL Server 2008 Internals and Troubleshooting – Ch 9-13 (Wiley/WROX, Christian Bolton, Brent Ozar, et al)
• SQL Server 2008 Query Performance Tuning Distilled – Ch 2 (APress, Apress, Grant Fritchey and Sajal Dam)

Whitepapers

• Performance Tuning Using Waits and Queues
• Predeployment I/O Best Practices

Blog Posts/Categories

• Paul’s SQLskills.com blog, Benchmarking category

Wikis/Community Articles

• Wait Stats Introductory Reference
• Perfmon Best Practices

Websites

• Top 10 SQL Server Performance Issues for Data Warehouse and Reporting Applications – SQL CAT Team
• Top 10 SQL Server Performance Issues for OLTP Applications – SQL CAT Team
• SQL University Capacity Planning

XML

Books

• Pro SQL Server 2008 XML (APress, Michael Coles)
• A Developer’s Guide to SQL Server 2005 – Ch 9-10 (Addison-Wesley, Bob Beauchemin and Dan Sullivan)
• Essential XML Quick Reference (Addison-Wesley, Aaron Skonnard and Martin Gudgin) Note: not SQL Server-specific
• The Art of XSD – SQL Server XML Schema Collections (Red Gate Books, Jacob Sebastian)

Whitepapers

• What’s New in FOR XML in Microsoft SQL Server 2005
• Performance Optimizations for the XML Data Type in SQL Server 2005
• Introduction to XQuery in SQL Server 2005
• What’s New for XML in SQL Server 2008
• XML Options in Microsoft SQL Server 2005
• XML Support in Microsoft SQL Server 2005

Blog Posts/Categories

• Bob Beauchemin’s SQLskills.com blog, XML category

Wikis/Community Articles

• Testing SQL-XML Output by Using XQuery

SQL CLIENT

Books

• A Developer’s Guide to SQL Server 2005 – Ch 13-14 (Addison-Wesley, Bob Beauchemin and Dan Sullivan)

Whitepapers

• Guide to the Data Development Platform for .NET Developers
• Microsoft Data Development Technologies: Past, Present, and Future
• Data Access Tracing in SQL Server 2008

Blog Posts/Categories

• Bob Beauchemin’s SQLskills.com blog, Data Access category
• ADO.NET Team blog
• SQL Server Protocols Team blog
• Julie Lerman’s blog, Data Access category
• Entity Framework Design Team blog
• Pablo Castro’s blog

CLR

Books

• A Developer’s Guide to SQL Server 2005 – Chapters 2-6 (Addison-Wesley, Bob Beauchemin and Dan Sullivan)
• Customizing the Microsoft .NET Framework Common Language Runtime (Steven Pratschner)
• Expert SQL Server 2005 Development – Chapter 6 (APress, Adam Machanic, et al)

Whitepapers

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

Blog Posts/Categories

• Bob Beauchemin’s SQLskills.com blog, CLR category

Magazine Articles

• Create a User-Defined Data Type (Dan Sullivan, SQL Server Magazine, May 2004)

CHANGE DATA CAPTURE

Books

• Inside Microsoft® SQL Server® 2008: T-SQL Programming, Ch 15 (Microsoft Press, chapter by Greg Low)

Whitepapers

• Tuning the Performance of Change Data Capture in SQL Server 2008

Blog Posts/Categories

• Repltalk blog
• Learn more about using Change Data Capture (CDC) in SSIS 2008

Magazine Articles

• Tracking Changes in Your Enterprise Database (Paul S. Randal, Technet Magazine, November 2008)

CHANGE TRACKING

Books

• Inside Microsoft® SQL Server® 2008: T-SQL Programming, Ch 15 (Microsoft Press, chapter by Greg Low)

Blog Posts/Categories

• The Synchronizer
• Microsoft Sync Services Team blog

Magazine Articles

• Tracking Changes in Your Enterprise Database (Paul S. Randal, Technet Magazine, November 2008)

Websites

• Take data offline using Microsoft Sync Services for ADO.NET
• Microsoft Sync Services

FULL-TEXT SEARCH

Whitepapers

• SQL Server 2008 Full-Text Search: Internals and Enhancements
• Best Practices for Integrated Full Text Search (iFTS) in SQL 2008

Blog Posts/Categories

• SQL Server 2008 – iFTS Introduction (first in multi-part series)

Websites

• SQL Server Full Text Search Language Features (first in multi-part series)

CLUSTERING

Books

• Pro SQL Server 2008 Failover Clustering (APress, Allan Hirt)

Whitepapers

• SQL Server 2008 Failover Clustering

Blog Posts/Categories

• Windows Clustering Team blog

SQLOS

Books

• SQL Server 2008 Internals – Ch 1 (Microsoft Press, Kalen Delaney, Paul S. Randal, Kimberly L. Tripp, et al)

Whitepapers

• SQL Server I/O Basics, Chapter 2

Blog Posts/Categories

• Slava Oks’ blog (now defunct)

DATABASE MIRRORING

Books

• Pro SQL Server 2008 Mirroring (APress, Ken Simmons and Robert L. Davis)

Whitepapers

• Proven SQL Server Architectures for High Availability and Disaster Recovery
• SQL Server Replication: Providing High-Availability Using Database Mirroring
• Database Mirroring and Log Shipping Working Together
• Database Mirroring in SQL Server 2005
• Database Mirroring: Alerting on Database Mirroring Events
• Database Mirroring: Best Practices and Performance Considerations
• Implementing Application Failover with Database Mirroring

Blog Posts/Categories

• Paul’s SQLskills.com blog, Database Mirroring category

Websites

• KB 2001270: Things to consider when setting up database mirroring in SQL Server

DESIGNING AN AVAILABILITY STRATEGY

Books

• Pro SQL Server 2005 High Availability (APress, Allan Hirt)

Whitepapers

• Proven SQL Server Architectures for High Availability and Disaster Recovery
• High Availability with SQL Server 2008

Blog Posts/Categories

• Paul’s SQLskills.com blog, High Availability category

SECURITY: EXECUTE AS

Books

• Writing Secure Code (Microsoft Press, Michael Howard, David LeBlanc)

Tools

• Microsft ASP code scanner for injection vulnerabilities
• Collection of tool links

Blog Posts/Categories

• Bob Beauchemin’s SQLskills.com blog, Security category
• Kimberly’s SQLskills.com blog, Security category
• SQL Server Security Team blog
• Raul Garcia’s blog
• Laurentiu Cristofor’s blog

Community Articles

• Microsoft Security Advisory – Rise in SQL Injection Attacks Exploiting Unverified User Data Input

SPATIAL

Books

• Beginning Spatial with SQL Server 2008 (APress, Alastair Aitchison)
• Inside Microsoft® SQL Server® 2008: T-SQL Programming, Ch 14 (Microsoft Press, chapter by Ed Katibah and Isaac Kunen)

Whitepapers

• Introduction to Spatial Coordinate Systems: Flat Maps for a Round Planet
• Delivering Location Intelligence with Spatial Data

Blog Posts/Categories

• Spatial Ed
• Issac Kunen’s blog
• Hannes’s Virtual Earth blog
• Bob Beauchemin’s SQLskills.com blog, Spatial category
• Dave Does Data

Magazine Articles

• Spatial Data Support In SQL Server 2008 (Bob Beauchemin, MSDN Magazine, February 2009)

POLICY-BASED MANAGEMENT

Books

• Pro SQL Server 2008 Policy-Based Management (APress, Ken Simmons, Colin Stasiuk, Jorge Segarra)

Whitepapers

• SQL Server 2008 Policy-Based Management

RESOURCE GOVERNOR

Whitepapers

• Using the Resource Governor

SERVICE BROKER

Books

• The Rational Guide to SQL Server 2005 Service Broker (Rational Guides, Roger Wolter)
• Pro SQL Server 2008 Service Broker (APress, Klaus Aschenbrenner)
• A Developer’s Guide to SQL Server 2005 – Ch 11 (Addison-Wesley, Bob Beauchemin and Dan Sullivan)

Whitepapers

• Service Broker: Performance and Scalability Techniques
• An Introduction to SQL Server Service Broker
• Building Reliable, Asynchronous Database Applications Using Service Broker
• SQL Server 2005 Enables Service-Oriented Database Architectures
• Planning, Implementing, and Administering Scaleout Solutions with SQL Server 2005
• Scalability: Internals, Troubleshooting, and Best Practices for use of Scaleout Technologies in SQL Server 2005

Blog Posts/Categories

• Remus Rusanu’s blog
• Roger Wolter’s blog

REPLICATION

Books

• Pro SQL Server 2005 High Availability (APress, Allan Hirt)

Whitepapers

• Proven SQL Server Architectures for High Availability and Disaster Recovery
• SQL Server Replication: Providing High-Availability Using Database Mirroring

Blog Posts/Categories

• Paul’s SQLskills.com blog, Replication category
• REPLTalk covers Using and Tuning SQL Replication (SQL Product Support)

SQL AZURE

Blog Posts/Categories

• SQL Azure Team blog

Wikis/Community Articles

• Shared Microsoft Wiki

SECURITY – ALL MODULES

Whitepapers

• Microsoft SQL Server™ 2005 SP1 Enterprise Edition (32-bit) Common Criteria Certification
• Auditing in SQL Server 2008
• Cryptography in SQL Server
• Database Encryption in SQL Server 2008 Enterprise Edition
• SQL Server 2008 Compliance Guide
• SQL Server Best Practices – Implementation of Database Object Schemas
• SQL Server 2008 Security Overview for Database Administrators
• How to Implement Kerberos Constrained Delegation with SQL Server 2008
• SQL Server 2005 Security Best Practices – Operational and Administrative Tasks
• Engine Separation of Duties for the Application Developer

Blog Posts/Categories

• SQL Server Security Team blog
• Raul Garcia’s blog
• Laurentiu Cristofor’s Blog
• Bob Beauchemin’s SQLskills.com blog, Security category
• Lara Rubbelke’s blog
• SQLDBATIPS blog on TDE

Articles

• Common SQL Server Security Issues and Solutions (TechNet Magazine, May 2009, Paul S. Randal)
• Giving Permissions through Stored Procedures
• KB: How to Transfer the Logins and the Passwords Between Instances of SQL Server 2005

Websites

• Microsoft SQL Server Security Portal
• Microsoft SQL Server 2008 Compliance Portal

VIRTUALIZATION

Books

• Virtualizing Microsoft Tier 1 Applications with VMware vSphere 4 (Sybex, Charles A. Windom et al)

Whitepapers

• High-Performance Workloads on Hyper-V
• Performance Best Practices for VMware vSphere 4.1
• Performance and Scalability of Microsoft SQL Server on VMware vSphere

Blog Posts/Categories

• Frank Denneman’s blog
• Yellow Bricks by Duncan Epping

Wikis/Community Articles

• ESX Memory Management

Websites

• SQL Server Support in a Hardware Virtualization Environment

CONSOLIDATION

Whitepapers

• Server Consolidation with SQL Server 2008
• SQL Server Consolidation Guidance by SQLCAT
• SQL Server 2008 R2 Licensing Quick Reference Guide

Websites

• Consolidation Options by Buck Woody
• Consolidation Strategy References by Buck Woody
• SQL Server Consolidation Process Flow by Tom LaRock
• SQL Server Consolidation Strategies and Best Practices by Ross Mistry
• SQL Server Licensing Myths by Mike Walsh

IO

Whitepapers

• SQL Server I/O Basics, Chapter 2 (SQL Server 2005)
• Predeployment I/O Best Practices
• Physical Database Storage Design
• Disk Partition Alignment Best Practices for SQL Server
• SQL Server I/O Basics, Chapter 2 (SQL Server 2005)

Blog Posts/Categories

• Linchi Shea’s blog, various categories
• Scott Lowe’s Storage blog
• Stephen Foskett’s blog, Enterprise Storage category
• Paul’s SQLskills.com blog, IO Subsystems category

Wikis/Community Articles

• Wikipedia: Standard RAID Levels
• Brent Ozar on SAN Performance Tuning with SQLIO

Websites

• KB917047: Microsoft SQL Server I/O subsystem requirements for the tempdb database
• KB231619: How to use the SQLIOSim utility to simulate SQL Server activity on a disk subsystem
• PSS debunks SQL Server IO urban legends
• Which is Faster: SAN or Directly-Attached Storage?