SQL Server MVP Deep Dives, Volume 2

690 Pages·2011·20.674 MB·English

by  Kalen Delaney, Louis Davidson, Greg Low, Brad McGhee, Paul Nielson, Paul Randal, Kimberly Tripp

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