microsoft sql server 2008 r2 administration cookbook

Microsoft SQL Server

2008 R2 Administration

Cookbook

Over 70 practical recipes for administering a

high-performance SQL Server 2008 R2 system

Satya Shyam K Jayanty

PUBLISHING

professional expertise distilled

BIRMINGHAM - MUMBAI

www.it-ebooks.info

Microsoft SQL Server 2008 R2 Administration

Cookbook

Copyright © 2011 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system,

or transmitted in any form or by any means, without the prior written permission of the

publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the

information presented. However, the information contained in this book is sold without

warranty, either express or implied. Neither the author, nor Packt Publishing, and its

dealers and distributors will be held liable for any damages caused or alleged to be

caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the

companies and products mentioned in this book by the appropriate use of capitals.

However, Packt Publishing cannot guarantee the accuracy of this information.

First published: May 2011

Production Reference: 1190511

Published by Packt Publishing Ltd.

32 Lincoln Road

Olton

Birmingham, B27 6PA, UK.

ISBN 978-1-849681-44-5

www.packtpub.com

Cover Image by Artie Ng ([email protected])

www.it-ebooks.info

Credits

Author

Satya Shyam K Jayanty

Reviewers

Vinod Kumar M

Ravikanth C

Venkatesan Prabu Jayakantham

Acquisition Editor

Kerry George

Development Editor

Maitreya Bhakal

Technical Editor

Shreerang Deshpande

Copy Editor

Laxmi Subramanian

Project Coordinator

Vishal Bodwani

Proofreader

Jacqueline McGhee

Indexer

Rekha Nair

Graphics

Geetanjali Sawant

Production Coordinator

ArvindKumar Gupta

Alwin Roy

Cover Work

ArvindKumar Gupta

Alwin Roy

www.it-ebooks.info

Foreword

Most books about SQL Server take one of the two approaches. The first approach is to

provide a broad introduction to SQL Server administration. While overviews are great for

beginners, they don't provide enough information, leaving the reader wanting for details

and practical advice.

The second approach is to attempt to cover every possible topic the author can think of.

While this may seem like a good approach, what generally ends up happening is that

each topic doesn't get the attention it really deserves. And, in order to keep the length

of the book reasonable (under 1,000 pages), a lot of practical details are left out, again,

leaving the reader wanting for more.

As both a reader and author of many SQL Server books, I have discovered that the

more focused the book's topic is, the more useful it becomes. This is why I like Satya SK

Jayanty's new book, Microsoft SQL Server 2008 R2 Administration Cookbook. It offers

over 70 practical recipes for managing and administering a high-performance SQL Server

2008 R2 system. So, instead of trying to cover every possible topic, the book, in effect, is

a collection of more than 70 extremely focused books, each providing very specific and

practical information on how to make the reader a better and more effective DBA.

Another thing I like about this book is that it is literally a recipe book. It walks with the

reader, step-by-step, through each topic, leading the reader through the most difficult of

topics. After reading each recipe, the reader will not only understand the importance of

the topic, but will also be able to implement it themselves. Nothing is left to chance.

Of course, the book could not include practical information unless the author himself has

been in the trenches and gotten his hands dirty. You are in good hands, as Satya is an

accomplished SQL Server DBA with over 15 years of real world experience. While Satya

has spent much of his career working for large organizations, what really separates him

from many other authors is that he also spends a great amount of his personal time

helping other DBAs.

www.it-ebooks.info

In 2000, I started one of the first community-based SQL Server websites on the Internet.

At first, I didn't have any forums on the website, because I knew I wouldn't have the extra

time required to devote to answering questions, on top of all the time I was spending

writing new content for the website. However, as the website became very popular, I finally

added forums, hoping that I could find some DBAs in the SQL Server community who

could help me answer questions. To my surprise, almost immediately one experienced

DBA began answering questions on a daily basis, and ended up with over 5,000 posts

the first year, and that DBA was Satya. I once asked him why he participated so much in

the forums. And he told me that answering question was a great learning experience. So

not only was he helping other DBAs by answering their questions, he was also learning

more himself. This gives Satya a great insight into the minds of DBAs and the kinds of

questions they may have, making him the perfect author for this book. As Satya began

helping out in the SQL Server Community, all of his hard work was noticed by Microsoft

and he was awarded the Microsoft SQL Server MVP award. In fact, he has been a SQL

Server MVP for six years now, and he continues to help out in forums, speak at user

groups and conferences, and he even maintains his own website.

As you read through the Microsoft SQL Server 2008 R2 Administration Cookbook, rest

assured that the information you will learn will be first-rate and practical for your day-to￾day work as a DBA. As you learn from Satya's experience, give some thought as to how

you can get involved in the worldwide SQL Server community, and perhaps you can share

your knowledge with others, just as Satya has with thousands and thousands of DBAs

over the years.

Brad M McGehee

Microsoft SQL Server MVP

Director of DBA Education, Red Gate Software

[email protected]

www.it-ebooks.info

Brad M. McGehee is a MCSE+I, MCSD, and MCT (former) with a Bachelor's degree

in Economics and a Masters in Business Administration. Currently the Director of DBA

Education for Red Gate Software, Brad is an accomplished Microsoft SQL Server MVP

with over 16 years SQL Server experience, over 7 years training experience, and has been

involved in the industry since 1982.

Brad is a frequent speaker at SQL PASS, European PASS, SQL Server Connections,

SQLTeach, devLINK, SQLBits, SQL Saturdays, TechFests, Code Camps, SQL Server

user groups, and other industry seminars, where he shares his 16 years' cumulative

knowledge and experience.

In 2009, Brad made 33 public presentations to a total of 1,853 attendees, in six different

countries. In 2010, Brad made 31 public presentations to a total of 3,156 attendees in

two different countries.

Brad was the founder of the popular community site SQL-Server-Performance.com,

and operated it from 2000 through 2006, where he wrote over one million words on SQL

Server topics.

A well-respected and trusted name in SQL Server literature, Brad is the author or co￾author of more than 15 technical books and over 275 published articles. His most recent

books include How to Become an Exceptional DBA (2nd Edition), Brad's Sure Guide to

SQL Server 2008: The Top Ten New Features for DBAs, Mastering SQL Server Profiler,

and Brad's Sure Guide to SQL Server Maintenance Plans. He blogs at

www.bradmcgehee.com.

www.it-ebooks.info

About the Author

Satya Shyam K Jayanty is an Independent Consultant working as Principal Architect &

Director for D Bi A Solutions Limited based in Europe. He has consulted in a wide range of

industries, including the stock exchange, insurance, tele-communications, financial, retail,

and manufacturing sectors, among others. Satya's SQL specialty is performance tuning,

data platform review, and designing high availability database solutions.

He graduated with a Master's degree in Economics and Commerce from Osmania

University, Hyderabad (India) and earned an MBA—Systems from the University of West

England.

In 1992, Satya started his career in the IT industry as a computer operator and assistant

programmer, and then in 1996 transformed his career as a SYBASE DBA. He has been

working with SQL Server (beginning from version 4.2) for more than 15 years and is an

accomplished Microsoft SQL Server MVP since 2006. He is a regular speaker and SME

volunteer at major technology conferences such as Microsoft Tech-Ed (Europe, India, and

North America), SQL PASS (Europe and North America), SQL Bits—UK, and manages the

Scottish Area SQL Server user group based in Scotland. He is also a moderator in most

web-based SQL Server forums (Microsoft Technet and www.sql-server-performance.

com), a writer, a contributing editor, and blogs at www.sqlserver-qa.net, www.sql￾server-performance.com, and www.beyondrelational.com.

www.it-ebooks.info

Acknowledgment

Knowledge sharing, learning, and dedication gave me strength to deliver this book, not to

mention about the coordinated efforts of editors at Packt Publishing and support from my

friends and well-wishers.

I would like to start thanking my friend and fellow MVP Hemant Goswami who referred me

to Packt Publishing and pulled this product together.

I would like to extend my gratitude to the wonderful bunch of professionals at

Packt Publishing.

Kerry George, Acquisition Editor, for helping me to improve my writing skills and

challenging me to explain every aspect of content in concise.

Vishal Bodwani, Project Coordinator, who supported me all along and kept me on track

and on schedule.

Maitreya Bhakal, Development Editor, for professional support and help during edit and

proof-reading phases.

Shreerang Deshpande, Technical Editor, who supported me at the final stages of book in

formatting the book and testing the technical aspects of the recipes.

A special thanks and kudos to my technical reviewers and their feedback:

Vinod Kumar M, Technical Evangelist at Microsoft, who has helped me uncover the

additional technical content for SQL Server and highlighting many tips.

Ravikanth Chaganti, SharePoint MVP, for support and helpful references in

technical content.

Venkatesan Prabhu J, SQL Server MVP, for support and help in testing the code,

suggested improvements for the implementation steps.

www.it-ebooks.info

About the Reviewers

Vinod Kumar M has worked with SQL Server extensively since joining the industry over a

decade ago. Working on various versions from SQL Server 7.0, Oracle, and other database

technologies, he now works with Microsoft Technology Center (MTC) as a Technical

Architect. With extensive database, BI, and application background, he currently helps

customers maximize on their investments on technologies to solve real business and

technology problems. He has worked in various roles and projects involving development,

migration, deployment, networking, architecture, testing, packaging, R&D, and much more.

He holds close to 26+ Microsoft Certification on various technologies. He is also a known

speaker at Technical conferences such as Microsoft TechEd, MSDN, TechNet, and so on.

Before working with Microsoft, he was a Microsoft MVP—Server for 3+ years.

Vinod has worked for more than 11 years in computers and database systems since

earning his Engineering degree from the College of Engineering, Guindy, Chennai. He has

published numerous articles in SQL Server on multiple sites and currently writes most of

his learning onto his site and blog at Blogs.ExtremeExperts.com.

Ravikanth C has more than ten years of experience in the IT industry. At the beginning

of his career, he worked at Wipro InfoTech managing Windows, Solaris servers, and

Cisco network equipment. He currently works at Dell Inc. as a lead engineer in the

SharePoint solutions group. As a part of his work, he authored several whitepapers on

MOSS 2007 and SharePoint 2010 that provide guidance around infrastructure elements

of a SharePoint deployment. His work also involves performance testing and sizing of

SharePoint workloads on Dell servers and storage.

He is passionate about automation, and apart from his work he writes regularly on

his blog at http://www.ravichaganti.com/blog about topics related to Windows

PowerShell, Microsoft SharePoint, and Windows Server virtualization. In 2010, he

received Microsoft's Most Valuable Professional (MVP) award in Windows PowerShell. You

can also hear him speak regularly at BITPro (http://bitpro.in) user group meetings

and other in-person events in Bangalore, India.

www.it-ebooks.info

Venkatesan Prabu Jayakantham is a Microsoft SQL Server MVP for the year 2008,

2009, and 2010 and the founder of KaaShiv Info Tech (www.Kaashivinfotech.com), a

company that is dedicated to delivering software and training solutions in Chennai, India.

He has authored more than 500 articles guiding at least 400 developers per day from

250 countries all over the world. He is also passionate towards corporate training and has

trained more than 400+ IT professionals and guided 3000+ IT students in India towards

job opportunities. Venkat lives in Chennai with his wife Subashini S and his son Krishiv.

You can check his technical blog http://venkattechnicalblog.blogspot.com/.

www.it-ebooks.info

www.PacktPub.com

Support files, eBooks, discount offers and more

You might want to visit www.PacktPub.com for support files and downloads related to your book.

Did you know that Packt offers eBook versions of every book published, with PDF and ePub files

available? You can upgrade to the eBook version at www.PacktPub.com and as a print book

customer, you are entitled to a discount on the eBook copy. Get in touch with us at service@

packtpub.com for more details.

At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a

range of free newsletters and receive exclusive discounts and offers on Packt books and eBooks.

http://PacktLib.PacktPub.com

Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book

library. Here, you can access, read and search across Packt's entire library of books.

Why Subscribe?

f Fully searchable across every book published by Packt

f Copy and paste, print and bookmark content

f On demand and accessible via web browser

Free Access for Packt account holders

If you have an account with Packt at www.PacktPub.com, you can use this to access

PacktLib today and view nine entirely free books. Simply use your login credentials for

immediate access.

Instant Updates on New Packt Books

Get notified! Find out when new books are published by following @PacktEnterprise on

Twitter, or the Packt Enterprise Facebook page.

www.it-ebooks.info

www.it-ebooks.info

To my adorable wife, Hima, who supported me through late nights and odd

working hours to make my dream come true. To my children, Abhinav and Anjali,

who give me joy and worthwhile time. To my parents, whose guidance helped me

shape my life.

www.it-ebooks.info

www.it-ebooks.info