SQL Server MCM Training Videos – Further Reading

INTRODUCTION

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
• Statistics Used by the Query Optimizer in Microsoft SQL Server 2005

---

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 2000 Index Defragmentation Best Practices
• Online Indexing Operations in SQL Server 2005

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 2005
• Microsoft SQL Server 2000 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 2005
• 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

• Glenn Berry’s blog, DMV a Day series
• 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

• XML Indexes in SQL Server 2005
• XQuery Implementation in a Relational Database System
• What’s New in FOR XML in Microsoft SQL Server 2005
• Performance Optimizations for the XML Data Type in SQL Server 2005
• XML Best Practices for Microsoft 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

Websites

• FOR XML Tutorial
• XQuery Labs and Tutorials

---

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

• Using CLR Integration in SQL Server 2005
• Managed Data Access Inside SQL Server with ADO.NET and SQLCLR
• 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

• Proven SQL Server Architectures for High Availability and Disaster Recovery
• 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)

Websites

• Paul Ibison’s Replication Answers

---

SQL AZURE

Blog Posts/Categories

• SQL Azure Team blog

Wikis/Community Articles

• Shared Microsoft Wiki

Websites

• Microsoft SQL Azure site

---

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
• John Magnbosco’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
• Configuration of Hyper-V Live Migration

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?