Sql Server 2008

PUBLISHED BY Microsoft Press A Division of Microsoft Corporation One Microsoft Way Redmond, Washington 98052-6399 Copyright © 2010 by Microsoft Corporation All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by any means without the written permission of the publisher. Library of Congress Control Number: 2010925075 Printed and bound in theUnited States of America. 1 2 3 4 5 6 7 8 9 WCT 5 4 3 2 1 0 Distributed in Canada by H.B. Fenn and Company Ltd. A CIP catalogue record for this book is available from the British Library. Microsoft Press books are available through booksellers and distributors worldwide. For further infor mation about international editions, contact your local Microsoft Corporation office or contact Microsoft PressInternational directly at fax (425) 936-7329. Visit our Web site at www.microsoft.com/mspress. Send comments to mspinput@microsoft.com. Microsoft, Microsoft Press, Access, ActiveX, DirectX, Expression, Expression Blend, Hotmail, IntelliSense, Internet Explorer, MS, MSDN, MS-DOS, MSN, SharePoint, Silverlight, SQL Server, Visual Basic, Visual C#, Visual Studio, Win32, Windows, Windows Live, WindowsNT, Windows Server, and Windows Vista are either registered trademarks or trademarks of the Microsoft group of companies. Other product and company names mentioned herein may be the trademarks of their respective owners. The example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious. No association with any realcompany, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred. This book expresses the author’s views and opinions. The information contained in this book is provided without any express, statutory, or implied warranties. Neither the authors, Microsoft Corporation, nor its resellers, or distributors will be held liable for any damagescaused or alleged to be caused either directly or indirectly by this book. Acquisitions Editor: Devon Musgrave Developmental Editor: Karen Szall Project Editor: Maria Gargiulo Editorial Production: Online Training Solutions, Inc. Technical Reviewer: Rozanne Whalen; Technical Review Services provided by Content Master, a member of CM Group, Ltd. Cover: Tom Draper Design

I dedicate this book to mywife and children, who make it all worthwhile.
—Ross MistRy

I dedicate this book to my husband and best friend, Gerry.
—stacia MisneR

Contents at a Glance
Introduction
PART I
CHAPTER 1 CHAPTER 2 CHAPTER 3 CHAPTER 4 CHAPTER 5

xvii

DATABASE ADMINISTRATION SQL Server 2008 R2 Editions and Enhancements Multi-Server Administration Data-Tier Applications High Availability andVirtualization Enhancements Consolidation and Monitoring BUSINESS INTELLIGENCE DEVELOPMENT Scalable Data Warehousing Master Data Services Complex Event Processing with StreamInsight Reporting Services Enhancements Self-Service Analysis with PowerPivot 109 125 145 165 189 3 21 41 63 85

PART II
CHAPTER 6 CHAPTER 7 CHAPTER 8 CHAPTER 9 CHAPTER 10

Contents
Introduction xvii

PART I

DATABASEADMINISTRATION 3
4 8

CHAPTER 1 SQL Server 2008 R2 Editions and Enhancements
Application and Multi-Server Administration Enhancements Additional SQL Server 2008 R2 Enhancements for DBAs

SQL Server 2008 R2 Enhancements for DBAs . . . . . . . . . . . . . . . . . . . . . . . . . 3

Advantages of Using Windows Server 2008 R2 . . . . . . . . . . . . . . . .. . . . . . 10 SQL Server 2008 R2 Editions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Premium Editions Core Editions Specialized Editions 12 12 13

Hardware and Software Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 Installation, Upgrade, and Migration...