Professional Microsoft SQL Server 2008 Administration E-Book

Brian Knight

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 1544

Veröffentlichungsjahr: 2011

Beschreibung

SQL Server 2008 is how-to guide for experienced DBAs. Tutorial-based, this book will get you over the learning curve of how to configure and administer SQL Server 2008. Whether you're an administrator or developer using SQL Server, you can't avoid wearing a DBA hat at some point. The book is loaded with unique tips and workarounds for the most difficult SQL Server admin issues, including managing and monitoring SQL Server, automating administration, security, performance tuning, scaling and replications, clustering, and backup and recovery. A companion website is also available.

Leseprobe

Table of Contents

Title Page

Copyright

About the Authors

Credits

Acknowledgments

Introduction

Who This Book Is For

How This Book Is Structured

What You Need to Use This Book

Conventions

Source Code

Errata

p2p.wrox.com

Chapter 1: SQL Server 2008 Architecture

The Expanding Role of a DBA

SQL Server Architecture

Editions of SQL Server

Summary

Chapter 2: SQL Server 2008 Installation Best Practices

Planning the System

Installing SQL Server

Installing Analysis Services

Burning in the System

Post-Install Configuration

Uninstalling SQL Server

Common Installation Issues

Troubleshooting a Failed Install

Summary

Chapter 3: Upgrading SQL Server 2008 Best Practices

Why Upgrade to SQL Server 2008?

Upgrading to SQL Server 2008

Pre-Upgrade Checks

Backward Compatibility

SQL Server Component Considerations

Post-Upgrade Checks

Summary

Chapter 4: Managing and Troubleshooting the Database Engine

Configuration Tools

Management Studio

Trace Flags

Getting Help from Support

Summary

Chapter 5: Automating SQL Server

Maintenance Plans

SQL Server Agent

Summary

Chapter 6: Integration Services Administration and Performance Tuning

A Tour of Integration Services

Administration of the Integration Services Service

Administration of Integration Services Packages

Applying Security to Integration Services

Summary

Chapter 7: Analysis Services Administration and Performance Tuning

Tour of Analysis Services

Administering Analysis Services Server

Administering Analysis Services Databases

Analysis Services Performance Monitoring and Tuning

Management of Analysis Services Storage

Applying Security to Analysis Services

Summary

Chapter 8: Administering the Development Features

Service Broker

Security Considerations for Service Broker

Conversation Priorities

Administering Service Broker

Introduction to CLR Integration

Summary

Chapter 9: Securing the Database Engine

Security Principles

Creating a Secure Configuration

Surface Area Configuration

Endpoints

Identity and Access Control

Schemas

Securables and Object Permissions

Encryption

SQL Server Audit

Summary

Chapter 10: Change Management

Creating Projects

Policy-Based Management

Trigger Views

sqlcmd

Creating Change Scripts

Summary

Chapter 11: Configuring the Server for Optimal Performance

What Every DBA Needs to Know about Performance

What the Developer DBA Needs to Know about Performance

What the Production DBA Needs to Know about Performance

Optimizing the Server

Configuring Server Hardware

CPU

Memory

I/O

Summary

Chapter 12: Optimizing SQL Server 2008

Application Optimization

The Silent Killer: I/O Problems

Table and Index Partitioning

Data Compression

Memory Considerations and Enhancements

CPU Considerations

Summary

Chapter 13: Monitoring Your SQL Server

The Goal of Monitoring

What's New in Monitoring for SQL Server 2008

Choosing the Appropriate Monitoring Tools

Performance Monitor

Monitoring Events

Monitoring with Dynamic Management Views and Functions

Monitoring Logs

Management Data Warehouse

Summary

Chapter 14: Performance Tuning T-SQL

Physical Query Processing

Tuning Process

Summary

Chapter 15: Indexing Your Database

Noteworthy Index-Related Features in SQL Server

Sample Database

Partitioned Tables and Indexes

Filtered Indexes and Filtered Statistics

Index Maintenance

Database Tuning Advisor

Summary

Chapter 16: Replication

Replication Overview

Replication Models

Implementing Replication

Peer-to-Peer Replication

Scripting Replication

Monitoring Replication

Summary

Chapter 17: Database Mirroring

Overview of Database Mirroring

Monitoring Database Mirroring

Troubleshooting Database Mirroring

Preparing the Mirror Server for Failover

Mirroring Multiple Databases

Database Mirroring and Other High-Availability Solutions

Mirroring Event Listener Setup

Database Snapshots

Summary

Chapter 18: Backup and Recovery

Types of Failure

Making Plans

Overview of Backup and Restore

Planning for Recovery

Developing and Executing a Backup Plan

Managing Backups

Backup and Restore Performance

Performing Recovery

Archiving Data

Disaster Recovery Planning

Summary

Chapter 19: SQL Server 2008 Log Shipping

Log Shipping Deployment Scenarios

Log-Shipping Architecture

Log Shipping Process

System Requirements

Deploying Log Shipping

Monitoring and Troubleshooting

Managing Changing Roles

Database Backup Plan

Integrating Log Shipping with Other High-Availability Solutions

Removing Log Shipping

Log-Shipping Performance

Upgrading to SQL Server 2008 Log Shipping

Summary

Chapter 20: Clustering SQL Server 2008

Clustering and Your Organization

Clustering: The Big Picture

Upgrading SQL Server Clustering

Getting Prepared for Clustering

Clustering Windows Server 2008

Clustering SQL Server 2008

Maintaining the Cluster

Troubleshooting Cluster Problems

Summary

Index

Advertisement

Professional Microsoft® SQL Server® 2008 Administration

Published by

Wiley Publishing, Inc.

10475 Crosspoint Boulevard

Indianapolis, IN 46256

www.wiley.com

Copyright © 2009 by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN: 978-0-470-24796-9

Library of Congress Cataloging-in-Publication Data

Microsoft SQL server 2008 administration / Brian Knight ... [et al.].

p. cm.

Includes index.

ISBN 978-0-470-24796-9 (paper/website)

1. SQL server. 2. Database management. I. Knight, Brian.

QA76.9.D3M57366 2008

005.75’85—dc22

2008037353

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone]. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, [phone], fax [phone], or online at www.wiley.com/go/permissions.

Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Website is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Website may provide or recommendations it may make. Further, readers should be aware that Internet Websites listed in this work may have changed or disappeared between when this work was written and when it is read.

For general information on our other products and services please contact our Customer Care Department within the United States at [phone], outside the United States at [phone] or fax [phone].

Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, Wrox Programmer to Programmer, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. Microsoft and SharePoint are registered trademarks of Microsoft Corporation in the United States and/or other countries. All other trademarks are the property of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books.

About the Authors

Brian Knight, SQL Server MVP, MCSE, MCDBA, is the co-founder of SQLServerCentral.com and JumpstartTV.com. Brian is a principal consultant and owner of Pragmatic Works. He runs the local SQL Server users group (JSSUG) in Jacksonville, Florida, and was on the Board of Directors of the Professional Association for SQL Server (PASS). Brian is a contributing columnist for SQL Server Standard, maintains a regular column for the database website SQLServerCentral.com, and does regular webcasts at Jumpstart TV. He is the author of nine SQL Server books. Brian is a speaker at numerous conferences, including PASS, SQL Connections, and TechEd, and many code camps. His blog can be found at www.pragmaticworks.com. Brian spends weekends practicing to be an amateur surgeon and proceeds from this book will help pay for the mobile CAT scan for his van.

Ketan Patel, B.E., electronics engineering, MCSE, MCDBA, is a senior development manager for the Business Intelligence Center of Excellence group at Microsoft. He has worked with SQL Server and other Microsoft technologies for nearly nine years. Ketan has also spoken at TechEd. He spends his spare time with his family and friends, playing cricket and watching NFL.

Wayne Snyder is recognized worldwide as a SQL Server expert and Microsoft Most Valued Professional (MVP), with over 25 years of experience in project management, database administration, software design, performance measurement, and capacity planning. He is a popular consultant, trainer, writer, and speaker, and produces a series of Web-based seminars on SQL Server 2005. Wayne has edited many SQL Server books, has SQL Training CDs with Learnkey, is president of PASS, the Professional Association for SQL Server (www.sqlpass.org), plays keyboard for a cover band named SoundBarrier (www.soundbarrierband.com), and is a managing consultant for Mariner, a Business Intelligence Company (www.mariner-usa.com).

Ross LoForte is a technical architect at the Microsoft Technology Center Chicago, focused on Microsoft SQL Server solutions. Ross has more than 16 years of experience in business development, project management, and designing SQL architecture solutions. For the past eight years, Ross has been working with the Microsoft Technology Centers, and has led architecture design and proof-of-concept engagements for Microsoft's largest and most strategic customers to design enterprise, mission-critical SQL Server solutions. Ross is a SQL Server instructor at DePaul University in Chicago, and regularly presents at TechEd, SQL PASS, Gartner, TDWI, and other conferences. A published author, he has been active with the Professional Association for SQL Server, the Chicago SQL Server Users Group, and the SQL Server community for many years.

Steven Wort has been working with SQL Server for the past 14 years. He spent much of that time working as a freelance application developer and database architect building VB and Web applications on SQL Server for many of London's largest financial institutions. He moved to the United States nine years ago, joining Microsoft over seven years ago. After three years working in PSS on the SIE team, he moved over to the SQL Server Product group, spending a year working on SQL Server scalability, followed by a year working with the SQL Playback team. Two years ago Steven moved to the Windows division, where he started work on the SQL Server side of the Watson system. When he is not involved with technology, Steven can be found doing something active somewhere outdoors in the Pacific Northwest.

Geoff Hiten, a Microsoft MVP, is a senior SQL Server consultant based in Atlanta. Geoff began working with SQL Server in 1992 with version 4.2 and has used every version since. He specializes in high-availability and high-performance SQL systems. Recent projects include: system upgrades, SQL Server platform migrations, performance tuning, custom reporting solutions, and database strategy implementations. Geoff is currently on the leadership team of the Atlanta area Microsoft Database Forum Users group (AtlantaMDF) and can be found lurking in the halls at PASS (Professional Association for SQL Server) Community Summit events. In addition, Geoff has been a Subject Matter Expert for Microsoft Training Curriculum materials and has authored articles, white papers, and podcasts on SQL Server.

K. Brian Kelley (MCSE, Security+, CISA) is a systems and security architect for AgFirst Farm Credit Bank. At AgFirst he provides infrastructure and security guidance with respect to Windows-based technologies including Active Directory, Internet Information Server, and Microsoft SQL Server. Brian, author of Start to Finish Guide to SQL Server Performance Monitoring and contributing author for How to Cheat at Securing SQL Server 2005, is a regular columnist and blogger at SQLServerCentral.com focusing primarily on SQL Server security. He is also a frequent contributor for SQL Server Standard Magazine. Brian is a member of the Professional Association of SQL Server (PASS) and the Information Systems Audit and Control Association (ISACA). He is also active in the Midlands PASS chapter, an official PASS chapter for South Carolina.

Credits

Executive Editor

Robert Elliott

Development Editor

Sara Shlaer

Technical Editor

John Mueller

Production Editor

Kathleen Wisor

Copy Editor

Luann Rouff

Editorial Manager

Mary Beth Wakefield

Production Manager

Tim Tate

Vice President and Executive Group Publisher

Richard Swadley

Vice President and Executive Publisher

Joseph B. Wikert

Project Coordinator, Cover

Lynsey Stanford

Proofreader

Nancy Carrasco Publication Services, Inc.

Indexer

Robert Swanson

Acknowledgments

As always, I must thank my wife and best friend for supporting me for the past 10 years of marriage. I've been fortunate to have found a woman who doesn't fall asleep immediately when copyediting my technical writing. Thanks to my three children, Colton, Liam, and Camille, for allowing their daddy to be distracted sometimes with this book when they wanted to play. Thanks also to all the wonderful co-authors, who truly made this book possible. Once again, I must thank the Pepsi-Cola Company for inventing Mountain Dew, which drove the late night writing. —Brian Knight

I would like to thank my parents, Thakorbhai and Induben, for their unwavering and selfless support and inspiration in my life, and my wife, Sweety, for her invaluable support and understanding. I would like to acknowledge Brian Knight, the lead author, for providing me with a great opportunity to co-author this book. I would also like to acknowledge Sara Shlaer, the development editor, and John Mueller, technical editor—without them this book would not exist or contain the level of depth that it has. Last but not the least, I want to thank B. J. Moore and Jim Walch, both general managers at Microsoft, for their invaluable support and encouragement. —Ketan Patel

Thank-you to my loving wife, Vickie, whose generosity and support make my life better each day. —Wayne Snyder

I'd like to thank my wife, Anna, and my daughter, Jennifer, for their support while writing this book. Additionally, I'd like to thank Adam Hecktman and Tony Surma for their support, and for making the Microsoft Technology Center Chicago a great facility to learn and experience. —Ross LoForte

I have to start by thanking my wife, Tracy, and two daughters, Eleanor and Caitlin, for putting up with me over the past few months of writing. They have been infinitely patient and understanding while I have spent many long hours working. I must also thank everyone in the SQL Product Group who has helped me with information about SQL Server 2008. Finally, I would like to thank everyone at Wrox Press for their help with this work over the past months.—Steven Wort

I would like to thank my wife Cheryl for being my other, better half, and my children; Victoria, Alexandra, and Katherine, who challenge me to do better simply by being who they are. —Geoff Hiten

Thanks to Kimberly, my beautiful bride and my children, James, Paul, and Faye. I love you all very much. Thanks also to Brian Knight for opening the door and to Mark Melichar for encouraging me to seize the opportunity. You guys have been great mentors and I can't thank you enough.—K. Brian Kelly

Introduction

SQL Server 2008 represents a sizable jump forward in scalability, performance, and usability for the DBA, developer, and business intelligence (BI) developer. It is no longer unheard of to have 20-terabyte databases running on a SQL Server. SQL Server administration used to just be the job of a database administrator (DBA), but as SQL Server proliferates throughout smaller companies, many developers have begun to act as administrators as well. Additionally, some of the new features in SQL Server are more developer-centric, and poor configuration of these features can result in poor performance. SQL Server now enables you to manage the policies on hundreds of SQL Servers in your environment as if you were managing a single instance. We've provided a comprehensive, tutorial-based book to get you over the learning curve of how to configure and administer SQL Server 2008.

Who This Book Is For

Whether you're an administrator or developer using SQL Server, you can't avoid wearing a DBA hat at some point. Developers often have SQL Server on their own workstations and must provide guidance to the administrator about how they'd like the production configured. Oftentimes, they're responsible for creating the database tables and indexes. Administrators or DBAs support the production servers and often inherit the database from the developer.

This book is intended for developers, DBAs, and casual users who hope to administer or may already be administering a SQL Server 2008 system and its business intelligence features, such as Integration Services. This book is a professional book, meaning the authors assume that you know the basics about how to query a SQL Server and have some rudimentary concepts of SQL Server already. For example, this book does not show you how to create a database or walk you through the installation of SQL Server using the wizard. Instead, the author of the installation chapter may provide insight into how to use some of the more advanced concepts of the installation. Although this book does not cover how to query a SQL Server database, it does cover how to tune the queries you've already written.

How This Book Is Structured

The first ten chapters of the book are about administering the various areas of SQL Server, including the developer and business intelligence features. Chapter 1 briefly covers the architecture of SQL Server and the changing role of the DBA. Chapters 2 and 3 dive into best practices on installing and upgrading to SQL Server 2008. Managing your SQL Server database instance is talked about in Chapter 4. This chapter also describes some of the hidden tools you may not even know you have.

Once you know how to manage your SQL Server, you can learn in Chapter 5 how to automate many of the redundant monitoring and maintenance tasks. This chapter also discusses best practices on configuring SQL Server Agent. Chapters 6 and 7 cover how to properly administer and automate many tasks inside of the Microsoft business intelligence products, such as Integration Services and Analysis Services. Developers will find that Chapter 8 is very useful, as it covers how to administer the development features, such as SQL CLR. Chapter 9 explains how to secure your SQL Server from many common threats and how to create logins and users. Chapter 10 covers how to create a SQL Server project and do proper change management in promoting your scripts through the various environments. It also covers the Policy-Based Management framework in SQL Server.

Chapters 11 through 15 make up the performance tuning part of the book. Chapter 11 discusses how to choose the right hardware configuration for your SQL Server in order to achieve optimal performance. After the hardware and operating system is configured, Chapter 12 shows you how to optimize your SQL Server instance for the best performance. Chapter 13 describes how to monitor your SQL Server instance for problematic issues such as blocking and locking. Chapters 14 and 15 discuss how to optimize the T-SQL that accesses your tables and then how to index your tables appropriately.

Chapters 16 through 20 consist of the high-availability chapters of the book. Chapter 16 covers how to use the various forms of replication, while database mirroring is covered in Chapter 17. Classic issues and best practices with backing up and recovering your database are discussed in Chapter 18. Chapter 19 dives deeply into the role of log shipping in your high-availability strategy, and Chapter 20 presents a step-by-step guide to clustering your SQL Server and Windows 2008 server.

This edition of the book covers all the same great information we covered in the last book, and we've added loads of new content for SQL Server 2008, which adds numerous new features to improve the DBA's life. In short, the new version of SQL Server focuses on improving your efficiency, the scale of your server, and the performance of your environment, so you can do more in much less time, and with fewer resources and people. This means you can manage many servers at one time using Policy-Based Management, scale your I/O load using compression, and collect valuable information about your environment using data collectors, to name just a few key new features.

What You Need to Use This Book

To follow the examples in this book, you will need to have SQL Server 2008 installed. If you wish to learn how to administer the business intelligence features, you need to have Analysis Services and the Integration Services components installed. You need a machine that can support the minimum hardware requirements to run SQL Server 2008; and you also need the AdventureWorks2008 and AdventureWorksDW2008 databases installed. Instructions for accessing these databases can be found in the ReadMe file on this book's Web site.

Some features in this book (especially in the high-availability part) require the Enterprise or Developer Edition of SQL Server. If you do not have this edition, you will still be able to follow through some of the examples in the chapter with Standard Edition.

Conventions

To help you get the most from the text and keep track of what's happening, we've used a number of conventions throughout the book.

Boxes like this one hold important, not-to-be forgotten information that is directly relevant to the surrounding text.

Tips, hints, tricks, and asides to the current discussion are offset and placed in italics like this.

As for styles in the text:

We highlight new terms and important words when we introduce them.We show keyboard strokes like this: Ctrl+A.We show file names, URLs, and code within the text like so: persistence.properties.We present code in two different ways:

In code examples we highlight new and important code with a gray background.

The gray highlighting is not used for code that's less important in the present

context, or has been shown before; that code appears like this.

Source Code

As you work through the examples in this book, you may choose either to type in all the code manually or to use the source code files that accompany the book. All of the source code used in this book is available for download at www.wrox.com. Once at the site, simply locate the book's title (either by using the Search box or by using one of the title lists) and click the Download Code link on the book's detail page to obtain all the source code for the book.

Because many books have similar titles, you may find it easiest to search by ISBN; this book's ISBN is 978-0-470-24796-9.

Once you download the code, just decompress it with your favorite compression tool. Alternately, you can go to the main Wrox code download page at www.wrox.com/dynamic/books/download.aspx to see the code available for this book and all other Wrox books.

Errata

We make every effort to ensure that there are no errors in the text or in the code. However, no one is perfect, and mistakes do occur. If you find an error in one of our books, such as a spelling mistake or a faulty piece of code, we would be very grateful for your feedback. By sending in errata, you may save another reader hours of frustration and at the same time you will be helping us provide even higher quality information.

To find the errata page for this book, go to www.wrox.com and locate the title using the Search box or one of the title lists. Then, on the book details page, click the Book Errata link. On this page you can view all errata that has been submitted for this book and posted by Wrox editors. A complete book list, including links to each book's errata, is also available at www.wrox.com/misc-pages/booklist.shtml.

If you don't spot “your” error on the Book Errata page, go to www.wrox.com/contact/techsupport.shtml and complete the form there to send us the error you have found. We'll check the information and, if appropriate, post a message to the book's errata page and fix the problem in subsequent editions of the book.

p2p.wrox.com

For author and peer discussion, join the P2P forums at p2p.wrox.com. The forums are a Web-based system for you to post messages relating to Wrox books and related technologies and interact with other readers and technology users. The forums offer a subscription feature to e-mail you topics of interest of your choosing when new posts are made to the forums. Wrox authors, editors, other industry experts, and your fellow readers are present on these forums.

At http://p2p.wrox.com you will find a number of different forums that will help you not only as you read this book, but also as you develop your own applications. To join the forums, just follow these steps:

1. Go to p2p.wrox.com and click the Register link.

2. Read the terms of use and click Agree.

3. Complete the required information to join as well as any optional information you wish to provide and click Submit.

4. You will receive an e-mail with information describing how to verify your account and complete the joining process.

You can read messages in the forums without joining P2P but in order to post your own messages, you must join.

Once you join, you can post new messages and respond to messages other users post. You can read messages at any time on the Web. If you would like to have new messages from a particular forum e-mailed to you, click the Subscribe to this Forum icon by the forum name in the forum listing.

For more information about how to use the Wrox P2P, be sure to read the P2P FAQs for answers to questions about how the forum software works as well as many common questions specific to P2P and Wrox books. To read the FAQs, click the FAQ link on any P2P page.