Learn SQL Server Administration in a Month of Lunches

258 Pages·2014·18.52 MB·English

by  Don Jones

Learn SQL Server Administration
in a Month of Lunches

Learn SQL Server
Administration
in a Month of Lunches
DON JONES
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]
©2014 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.
Manning Publications Co. Development editor: Suzie Pitzen
20 Baldwin Road Copyeditor: Elizabeth Martin
PO Box 261 Proofreader: Barbara Mirecki
Shelter Island, NY 11964 Typesetter: Dennis Dalinnik
Cover designer: Leslie Haimes
ISBN: 9781617292132
Printed in the United States of America
1 2 3 4 5 6 7 8 9 10 – MAL – 19 18 17 16 15 14
To the Atlantis Team, who helped me
stay on track with my writing schedule:
Malcolm, Ellen, Michael, Gregory, Randy,
Adam, and Momma, along with Brit Lee.
And to Christopher, for helping me
stay on track every day.

brief contents
1 ■ Before you begin 1
2 ■ Server assessment and configuration 8
3 ■ T-SQL crash course 16
4 ■ Managing databases 34
5 ■ Backup and recovery 44
6 ■ Authentication: who are you? 56
7 ■ Authorization: what are you allowed to do? 65
8 ■ Accounting: what did you do? 75
9 ■ Analyzing indexes 82
10 ■ Maintaining indexes 92
11 ■ Tuning index designs 101
12 ■ Reading query execution plans 110
13 ■ Block and deadlock analysis 119
14 ■ Automating management with SQL Server Agent 128
15 ■ Multiserver management 137
16 ■ Windows PowerShell and SQL Server 143
17 ■ Using Extended Events 153
vii
viii BRIEF CONTENTS
18 ■ Monitoring and analyzing performance 166
19 ■ Options for high availability 178
20 ■ Virtualizing SQL Server 189
21 ■ Moving, migrating, and upgrading databases 197
22 ■ SQL Server performance checklist 205
23 ■ Never the end 207
contents
preface xvii
acknowledgments xviii
about this book xix
1 Before you begin 1
1.1 Is this book for you? 1
1.2 How to use this book 2
1.3 Setting up your lab environment 3
Choosing a SQL Server edition for your lab 3 ■ Selecting a version
of Windows for your lab 4 ■ My recommendation for your
lab environment 5 ■ A word about SQL Server versions 5
1.4 SQL Server documentation 5
1.5 Online resources 6
1.6 A word on my recommendations 6
1.7 Being immediately effective with SQL Server 7
2 Server assessment and configuration 8
2.1 Identifying instances and versions 8
2.2 Identifying databases 11
2.3 Identifying other server objects 13
ix