Table Of Content

M A N N I N G DEEP DIVES Volume 2 E DITED BY Kalen Delaney Louis Davidson Greg Low Brad McGehee Paul Nielsen Paul Randal Kimberly Tripp MVP A UTHORS Johan Åhlén Gogula Aryalingam Glenn Berry Aaron Bertrand Kevin G. Boles Robert Cain Tim Chapman Denny Cherry Michael Coles Rod Colledge ● ● ● ● ● ● ● ● ● John Paul Cook Louis Davidson Rob Farley Grant Fritchey Darren Gosbell Sergio Govoni Allan Hirt Satya Jayanty Tibor Karaszi Jungsun Kim Tobiasz ● ● ● ● ● ● ● ● ● ● Koprowski Hugo Kornelis Ted Krueger Matija Lah Greg Low Rodney Landrum Greg Larsen Peter Larsson Andy Leonard Ami Levin John Magnabosco ● ● ● ● ● ● ● ● ● ● Jennifer McCown Brad McGehee Siddharth Mehta Ben Miller Allan Mitchell Tim Mitchell Luciano Moreira Jessica Moss Shahriar Nikkhah Paul Nielsen ● ● ● ● ● ● ● ● ● Robert Pearl Boyan Penev Pedro Perfeito Pawel Potasinski Mladen Prajdi c´ Abolfazl Radgoudarzi Denis Reznik Rafael Salas Edwin Sarmiento ● ● ● ● ● ● ● ● Chris Shaw Gail Shaw Linchi Shea Jason Strate Paul Turley William Vaughn Peter Ward Joe Webb John Welch Allen White Thiago Zavaschi ● ● ● ● ● ● ● ● ● ● The authors of this book support the children of Operation Smile SQL Server MVP Deep Dives Volume 2 SQL Server MVP Deep Dives Volume 2 Edited by Kalen Delaney Louis Davidson Greg Low ■ ■ Brad McGehee Paul Nielsen Paul Randal Kimberly Tripp ■ ■ ■ MANNING SHELTER ISLAND For online information and ordering of this and other Manning books, please visit www.manning.com. The publisher offers discounts on this book when ordered in quantity. For more information, please contact Special Sales Department Manning Publications Co. 20 Baldwin Road PO Box 261 Shelter Island, NY 11964 Email: [email protected] ©2012 by Manning Publications Co. All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by means electronic, mechanical, photocopying, or otherwise, without prior written permission of the publisher. Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in the book, and Manning Publications was aware of a trademark claim, the designations have been printed in initial caps or all caps. Recognizing the importance of preserving what has been written, it is Manning’s policy to have the books we publish printed on acid-free paper, and we exert our best efforts to that end. Recognizing also our responsibility to conserve the resources of our planet, Manning books are printed on paper that is at least 15 percent recycled and processed without the use of elemental chlorine. Development editor: Cynthia Kane Manning Publications Co. Copyeditor: Liz Welch, Linda Recktenwald 20 Baldwin Road Project editor: Barbara Mirecki PO Box 261 Typesetter: Marija Tudor Shelter Island, NY 11964 Cover designer: Marija Tudor ISBN 9781617290473 Printed in the United States of America 1 2 3 4 5 6 7 8 9 10 – MAL – 16 15 14 13 12 11 To all the children of Operation Smile MVP authors and their chapters Johan Åhlén 53 Jennifer McCown 37 Gogula Aryalingam 57 Brad McGehee 36 Glenn Berry 31 Siddharth Mehta 60 Aaron Bertrand 19 Ben Miller 26 Kevin G. Boles 20 Allan Mitchell 59 Robert Cain 45 Tim Mitchell 51 Tim Chapman 38 Luciano Moreira 27 Denny Cherry 5 Jessica M. Moss 40 Michael Coles 46 Paul Nielsen 6 Rod Colledge 14 Shahriar Nikkhah 48 John Paul Cook 21 Robert Pearl 34 Louis Davidson 4 Boyan Penev 55 Rob Farley 2 Pedro Perfeito 58 Grant Fritchey 32 Pawel Potasinski 12 Darren Gosbell 54 Mladen Prajdic´ 28 Sergio Govoni 22 Abolfazl Radgoudarzi 48 Allan Hirt 7 Denis Reznik 29 Satya Jayanty 16 Rafael Salas 52 Tibor Karaszi 17 Edwin Sarmiento 44 Jungsun Kim 39 Chris Shaw 3 Tobiasz Koprowski 18 Gail Shaw 8 Hugo Kornelis 23 Linchi Shea 35 Ted Krueger 50 Jason Strate 33 Matija Lah 24 Paul Turley 43 Rodney Landrum 15 William Vaughn 42 Greg Larsen 9 Peter Ward 13 Peter Larsson 25 Joe Webb 10 Andy Leonard 47 John Welch 49 Ami Levin 1 Allen White 30 Greg Low 41 Thiago Zavaschi 56 John Magnabosco 11 vii brief contents PART 1 ARCHITECTURE............................................... 1 1 ■ Where are my keys? 3 2 ■ “Yes, we are all individuals” A look at uniqueness in the world of SQL 16 3 ■ Architectural growth pains 26 4 ■ Characteristics of a great relational database 37 5 ■ Storage design considerations 49 6 ■ Generalization: the key to a well-designed schema 60 PART 2 DATABASE ADMINISTRATION .......................... 65 7 ■ Increasing availability through testing 67 8 ■ Page restores 79 9 ■ Capacity planning 87 10 ■ Discovering your servers with PowerShell and SMO 95 11 ■ Will the real Mr. Smith please stand up? 105 12 ■ Build your own SQL Server 2008 performance dashboard 111 13 ■ SQL Server cost recovery 121 14 ■ Best practice compliance with Policy-Based Management 128 15 ■ Using SQL Server Management Studio to the fullest 138 16 ■ Multiserver management and Utility Explorer—best tools for the DBA 146 ix