Microsoft SQL Server 2008 Administration for Oracle DBAs

591 Pages·2010·15.52 MB·English

by  Mark Anderson

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