Table Of ContentHigh Performance SQL Server
SQL Server
Tacklebox
Essential Tools and Scripts for the day-to-day DBA
Rodney Landrum
ISBN: 978-1-906434-24-3
SQL Server
Tacklebox
Essential Tools and Scripts
for the day-to-day DBA
By Rodney Landrum
First published by Simple Talk Publishing 2009
Copyright Rodney Landrum 2009
ISBN 978-1-906434-24-3
The right of Rodney Landrum to be identified as the author of this work has been asserted by
him in accordance with the Copyright, Designs and Patents Act 1988
All rights reserved. No part of this publication may be reproduced, stored or introduced into
a retrieval system, or transmitted, in any form, or by any means (electronic, mechanical,
photocopying, recording or otherwise) without the prior written consent of the publisher.
Any person who does any unauthorized act in relation to this publication may be liable to
criminal prosecution and civil claims for damages.
This book is sold subject to the condition that it shall not, by way of trade or otherwise, be
lent, re-sold, hired out, or otherwise circulated without the publisher's prior consent in any
form other than which it is published and without a similar condition including this condition
being imposed on the subsequent publisher.
Technical Review by Shawn McGehee
Cover Image by Paul Beckman
Edited by Tony Davis
Typeset by Gower Associates
Table of Contents
About the author .......................................................... vi
About the technical reviewer...................................... vi
Acknowledgements..................................................... vii
Introduction ................................................................ viii
Code Download.........................................................................................x
Chapter 1: Eating SQL Server installations for
breakfast ...................................................11
Specification, installation, configuration..................................................12
Specifying the physical server................................................................12
Ready to install – almost.........................................................................14
Installation done, now to configure.........................................................19
Bon Appétit..............................................................................................31
Chapter 2: The SQL Server landscape...................... 32
What information is required?.................................................................33
Automating information retrieval.............................................................44
Summary.................................................................................................59
Chapter 3: The migratory data................................... 61
Mapping out the data migration solution.................................................62
The data source......................................................................................63
Bulk data transfer tools...........................................................................64
SSIS........................................................................................................72
Data comparison tools............................................................................78
"High Availability" tools............................................................................84
Summary.................................................................................................93
Chapter 4: Managing data growth ............................. 94
Common causes of space issues............................................................95
Being a model DBA.................................................................................96
Indexes and large row counts...............................................................107
TempDB................................................................................................115
A query to determine current space utilization......................................119
Summary...............................................................................................122
Chapter 5: DBA as detective.................................... 123
System tables versus DMVs.................................................................123
Tracking down database performance issues.......................................124
Automating discovery of problems........................................................136
Summary...............................................................................................143
Chapter 6: Monitoring and notifications................. 145
Types of monitoring and notifications....................................................145
Enabling notifications............................................................................148
Backup failure notification.....................................................................154
Performance issues...............................................................................159
Stopped services and disk space shortage...........................................166
Summary...............................................................................................168
Chapter 7: Securing access to SQL Server............ 169
Overview of security challenges............................................................169
Finding SQL logins, Windows users and groups..................................171
Find Windows Active Directory group membership..............................175
Find SQL users at the database level...................................................179
Loading up the DBA repository with security data................................182
Finding service accounts with WMIC....................................................182
Surveillance...........................................................................................185
Summary...............................................................................................202
Chapter 8: Finding data corruption......................... 204
Causes of corruption.............................................................................204
Consequences of corruption.................................................................205
Fighting corruption................................................................................206
Seeking out corruption..........................................................................224
Summary...............................................................................................228
A
BOUT THE AUTHOR
Rodney Landrum has been working with SQL Server technologies for
longer than he can remember (he turned 40 in May of 2009, so his memory
is going). He writes regularly about many SQL Server technologies,
including Integration Services, Analysis Services, and Reporting Services. He
has authored three books on Reporting Services. He is a regular contributor
to SQL Server Magazine and Simple-Talk, the latter of which he sporadically
blogs on about SQL and his plethora of geek tattoos. His day job finds him
overseeing the health and well-being of a large SQL Server infrastructure in
Pensacola, Florida. He swears he owns the expression "Working with
Databases on a Day to Day Basis" and anyone who disagrees is itching to
arm wrestle. Rodney is also a SQL Server MVP.
A
BOUT THE TECHNICAL
REVIEWER
Shawn McGehee is a full-time professional DBA and a part-time amateur
developer from Pensacola, Florida. He is very active with the local SQL
users’ group in Pensacola and helps organize / speaks regularly at their
events. Shawn is also a contributing writer to popular SQL websites such as
Simple Talk and SQL Server Central. He was also a co-author of the book
"Pro SQL Server 2008 Reporting Services."
vi
A
CKNOWLEDGEMENTS
I would like to thank everyone involved in the making of this book,
peripherally and personally, but first and foremost Karla…my love, who has
been with me, spurred me on and understood when I needed a fishing or
beer respite through 5 books now. I love you.
To all my kids who also sacrificed during the writing of this book. Megan,
Ethan, Brendan and Taylor. Well, OK, Ethan did not sacrifice so much, but
he did help me understand that "Buffalo buffalo Buffalo buffalo buffalo
buffalo Buffalo buffalo" is a legitimate sentence.
Thanks to my Mom and Dad, as always. I love you. There will still be a
novel, I promise; just not a Western. Sorry, Mom.
Thanks also to Shawn McGehee, my good friend and DBA colleague, who
tech-edited the book. It is much better for it. Also, thanks Shawn, for letting
me use snippets of your hard-won code as well.
Special thanks also go to Truett Woods who has opened my eyes in a lot of
ways to good coding practices, and for the use of one of his base code
queries in Chapter 1.
Thanks to Joe Healy of devfish fame, a straight up bud whose .Net
tacklebox is more full than mine. I will be getting the devfish tattoo next.
Finally, I would personally like to thank Throwing Muses, The Pixies and
Primus for providing the music that helped me through the many late
nights. OK, so they will never read this and offer to come over to play a set
at a backyard BBQ, I know, but one can hope.
vii
I
NTRODUCTION
This book, as with almost all books, started out as an idea. I wanted to accumulate
together those scripts and tools that I have built over the years so that DBAs
could sort through them and perhaps adapt them for their own circumstances. I
know that not every script herein will be useful, and that you might ask "Why are
you using this table and not that DMV" or "Why is this code not compatible with
SQL Server 2008?" After writing about SQL Server solutions now for the past 10
years, I can expect this criticism, and understand it fully. Everyone has their own
ways of solving problems. My goal is to provide useful samples that you can
modify as you please.
I wrote the book the way it is because I did not want to bore DBAs to tears with
another 500+ page textbook-style tome with step-by-step instructions. I wanted
this book to be a novel, a book of poetry, a murder mystery, a ghost story, an epic
trilogy, a divine comedy. But realizing that this is, after all, a technical book, I
compromised by imbuing it with some humor and personality. If you make it as
far as the monster at the end of this book, my hope is that you will have been
entertained and can use the code from the Tacklebox in some fashion that will
make your lives as DBAs easier. Why "The Tacklebox," you might ask, rather than
"Zombie Queries," "You Can’t Handle the Code" or "You had me at BEGIN?" I
think, as I push halfway through my career as a DBA and author, this book is as
close as I will ever get to "The Old Man and the Sea"…oh yes, and apparently the
"Toolbox" had been copyrighted. Plus, come on! Look at the cover of the book.
How can I live here and not go fishing once in a while?
Chapter 1
Here you will find wholesome SQL Server installations on the menu, complete
with Express, Continental and Deluxe breakfast choices, depending on your
application’s appetite. And there will be a little GUI setup support here. This
chapter is about automation, and a lengthy script is included that will help you
automate SQL installations and configurations. There is some foreshadowing
lurking as well, such as code to enable a DDL trigger that I will show later in the
book. This is the chapter where your new SQL Server installation is completely
yours, having not as yet been turned over to the general populace of developers or
users. Enjoy it while you can.
Chapter 2
In this chapter, I introduce the DBA Repository, a documentation tool I have
built using Integration Services and Reporting Services. It is easy to manage one,
viii
Introduction
two or three SQL Server instances with the panoramic view the tool gives. It is
even easy to work with ten SQL Servers without documentation, but when you
have 70 or 100 or 2,000 SQL Servers without an automated documentation
solution, you cannot successfully manage your SQL Server Landscape – ironically,
that is the name of the chapter, "The SQL Server Landscape."
Chapter 3
I think we can all agree that data at rest never stays that way. No, far from it. The
data in this chapter has begun the swim up river to its spawning grounds and will
migrate and transform like the intrepid salmon (hey, a fishing reference) from the
open ocean, to river, to stream. Here, I look at different ways that data moves
about, and I investigate tools such as SSIS and BCP that help facilitate such
moves, whatever the reason, be it high availability, disaster recovery or offloaded
reporting.
Chapter 4
In this chapter, I describe one of the first hungry monsters of the book, the disk-
space consuming databases. The hunger may not be abated entirely, but it can be
controlled with proper planning and also with queries that will help you to
respond to runaway growth. Here, I will show how to battle the appetite of space-
killers with just a bit of planning, tempered with an understanding of how and why
data and log files grow to consume entire disks.
Chapter 5
There is a murder in this chapter. Someone or something is killed and most likely
you, the DBA, will be the lone killer. Of course, I am talking about processes,
SPIDs, that we see every day. Some are easier to kill than others. Some will just
not die and I will explain why, using ridiculous code that I hope you never see in
real life. The queries here were designed to help you get to the bottom of any issue
as quickly as possible without the need for DNA testing. And I am not talking
about Windows DNA, if you are old like me and remember this acronym for
Distributed Networking Architecture, precursor to .NET. No, no .NET here.
Chapter 6
To sleep...perchance to dream…about failures. Here, I will introduce the sleep
killer of DBAs everywhere, where jobs fail in the hours of darkness, and the on-
call DBA is awakened from slumber several times a night in order to sway,
zombie-like to the computer to restart failed backup jobs. You cannot resolve an
issue unless you know about it and here, while discussing notifications and
monitoring your SQL Server infrastructure, we will stay up late and tell horror
stories. But, in the end, you will sleep better knowing all is well.
ix
