Microsoft® SQL Server® 2008 ADMINISTRATION for ORACLE® DBAs About the Authors Mark Anderson is a data platform technical specialist working in the Enterprise and Partner Group at Microsoft UK. Specializing in the SQL Server relational engine, Mark works with Microsoft’s top enterprise customers and partners, helping them to design and architect Tier-1 solutions on the Microsoft platform. Migrating and integrating with non-Microsoft platforms such as Oracle and IBM also form part of his role. Mark holds certification in both the Microsoft and Oracle database platforms. He can be contacted at [email protected]. James Fox is an independent consultant and director of Datagility, a company dedicated to helping businesses make their information work harder using the Microsoft Business Intelligence platform, including SQL Server and SharePoint. Prior to this, James has worked for Microsoft and Oracle partners, building and supporting data-driven solutions and providing SQL Server training to experienced Oracle DBAs. He can be contacted at [email protected]. Christian Bolton is the technical director for Coeo Ltd., a leading provider of SQL Server consulting and managed support services in the UK and Europe. Prior to this, Christian worked for five years at Microsoft, leading the SQL Server Premier Field Engineering team in the UK. He is a Microsoft Certified Architect, Master, and MVP for SQL Server, and lead author of Professional SQL Server 2008 Internals and Troubleshooting. Christian works out of London and lives in the south of England with his wife and children. He can be contacted at [email protected]. About the Technical Editor David Browne is a technology architect at the Microsoft Technology Center in Dallas, focusing on SQL Server solutions. He is a developer and has been writing solutions in SQL Server, .NET, Java, and Oracle for over ten years. Microsoft® SQL Server® 2008 ADMINISTRATION for ORACLE® DBAs Mark Anderson James Fox Christian Bolton New York Chicago San Francisco Lisbon London Madrid Mexico City Milan New Delhi San Juan Seoul Singapore Sydney Toronto Copyright © 2011 by The McGraw-Hill Companies. All rights reserved. Except as permitted under the United States Copyright Act of 1976, no part of this publication may be reproduced or distributed in any form or by any means, or stored in a database or retrieval system, without the prior written permission of the publisher. ISBN: 978-0-07-170065-8 MHID: 0-07-170065-X The material in this eBook also appears in the print version of this title: ISBN: 978-0-07-170064-1, MHID: 0-07-170064-1. All trademarks are trademarks of their respective owners. Rather than put a trademark symbol after every occurrence of a trademarked name, we use names in an editorial fashion only, and to the benefi t of the trademark owner, with no intention of infringement of the trademark. Where such designations appear in this book, they have been printed with initial caps. McGraw-Hill eBooks are available at special quantity discounts to use as premiums and sales promotions, or for use in corporate training programs. To contact a representative please e-mail us at [email protected]. Information has been obtained by McGraw-Hill from sources believed to be reliable. However, because of the possibility of human or mechanical error by our sources, McGraw-Hill, or others, McGraw-Hill does not guarantee the accuracy, adequacy, or completeness of any information and is not responsible for any errors or omissions or the results obtained from the use of such information. TERMS OF USE This is a copyrighted work and The McGraw-Hill Companies, Inc. (“McGrawHill”) and its licensors reserve all rights in and to the work. Use of this work is subject to these terms. Except as permitted under the Copyright Act of 1976 and the right to store and retrieve one copy of the work, you may not decompile, disassemble, reverse engineer, reproduce, modify, create derivative works based upon, transmit, distribute, disseminate, sell, publish or sublicense the work or any part of it without McGraw-Hill’s prior consent. You may use the work for your own noncommercial and personal use; any other use of the work is strictly prohibited. Your right to use the work may be terminated if you fail to comply with these terms. THE WORK IS PROVIDED “AS IS.” McGRAW-HILL AND ITS LICENSORS MAKE NO GUARANTEES OR WARRANTIES AS TO THE ACCURACY, ADEQUACY OR COMPLETENESS OF OR RESULTS TO BE OBTAINED FROM USING THE WORK, INCLUDING ANY INFORMATION THAT CAN BE ACCESSED THROUGH THE WORK VIA HYPERLINK OR OTHERWISE, AND EXPRESSLY DISCLAIM ANY WARRANTY, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. McGraw-Hill and its licensors do not warrant or guarantee that the functions contained in the work will meet your requirements or that its operation will be uninterrupted or error free. Neither McGraw-Hill nor its licensors shall be liable to you or anyone else for any inaccuracy, error or omission, regardless of cause, in the work or for any damages resulting therefrom. McGraw-Hill has no responsibility for the content of any information accessed through the work. Under no circumstances shall McGraw-Hill and/or its licensors be liable for any indirect, incidental, special, punitive, consequential or similar damages that result from the use of or inability to use the work, even if any of them has been advised of the possibility of such damages. This limitation of liability shall apply to any claim or cause whatsoever whether such claim or cause arises in contract, tort or otherwise. Packed with Hundreds of Powerful, Ready-to-Use Queries Art Tennick is an expert consultant and trainer in SSAS cubes, data mining, MDX, DMX, XMLA, Excel 2010 PowerPivot, and DAX. His website is www.MrCube.net. Available everywhere computer books are sold, in print and ebook formats. I wish to dedicate this book to two people; firstly, to my wife and best friend, Wendy, and secondly, to a constant in my life who was there when I started this book but sadly not when I finished it: “Nana” Lillian O’Conner, 1925–2010. —Mark Anderson This is for Caroline and Charlie, who helped me in their own ways from the start, and for Chloe and Alice, who only arrived when we were nearly finished. —James Fox For my parents, the often unsung heroes of my career, who I always strive to make proud. —Christian Bolton This page intentionally left blank Contents at a Glance Chapter 1 Introduction to the SQL Server Platform . . . . . . . . . . . . . . . . . . . . . . . 1 Chapter 2 SQL Server Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 Chapter 3 Installing and Configuring SQL Server . . . . . . . . . . . . . . . . . . . . . . . . 67 Chapter 4 Database Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111 Chapter 5 Security. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165 Chapter 6 Data Access and Transaction Control . . . . . . . . . . . . . . . . . . . . . . . . . 211 Chapter 7 Backup and Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251 Chapter 8 Performance Tuning and Optimization. . . . . . . . . . . . . . . . . . . . . . . . 305 Chapter 9 High Availability and Disaster Recovery . . . . . . . . . . . . . . . . . . . . . . . 347 Chapter 10 Scheduling, Automation, and Alerting . . . . . . . . . . . . . . . . . . . . . . . . 379 Chapter 11 Data Movement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 437 Chapter 12 Upgrading and Migrating to SQL Server . . . . . . . . . . . . . . . . . . . . . . . 515 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 547 vii This page intentionally left blank