Table Of Content

Pro SQL Server 2012 Administration ■ ■ ■ Ken Simmons Sylvester Carstarphen Pro SQL Server 2012 Administration Copyright © 2012 by Ken Simmons and Sylvester Carstarphen All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system, without the prior written permission of the copyright owner and the publisher. ISBN 978-1-4302-3915-4 ISBN 978-1-4302-3916-1 (eBook) Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights. President and Publisher: Paul Manning Lead Editor: Jonathan Gennick Technical Reviewers: Jonathan Kehayias, Jorge Segarra and Don Watters Editorial Board: Steve Anglin, Ewan Buckingham, Gary Cornell, Louise Corrigan, Morgan Ertel, Jonathan Gennick, Jonathan Hassell, Robert Hutchinson, Michelle Lowman, James Markham, Matthew Moodie, Jeff Olson, Jeffrey Pepper, Douglas Pundick, Ben Renow-Clarke, Dominic Shakeshaft, Gwenan Spearing, Matt Wade, Tom Welsh Coordinating Editors: Anita Castro and Stephen Moles Copy Editor: Lori Cavanaugh Compositor: Mary Sudul Indexer: SPi Global Cover Designer: Anna Ishchenko Distributed to the book trade worldwide by Springer Science+Business Media, LLC., 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail orders-ny@springer- sbm.com, or visit www.springeronline.com. For information on translations, please e-mail [email protected], or visit www.apress.com. Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Special Bulk Sales–eBook Licensing web page at www.apress.com/bulk-sales. The information in this book is distributed on an “as is” basis, without warranty. Although every precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the information contained in this work. To my wife Susan and son Nathan –Ken Simmons To my loving wife Kimberly –Sylvester Carstarphen iii Contents at a Glance Contents..................................................................................................................vii About the Authors................................................................................................xxiii PART 1: Introducing Microsoft SQL Server 2012......................................................1 ■ Chapter 1: New Features Overview......................................................................3 ■ Chapter 2: Pre-Installation Considerations........................................................15 ■ Chapter 3: Choosing a High-Availability Solution..............................................33 PART 2: Getting Started..........................................................................................59 ■ Chapter 4: Installing and Upgrading..................................................................61 ■ Chapter 5: Post-Installation...............................................................................99 PART 3: Administering Microsoft SQL Server 2012..............................................125 ■ Chapter 6: Multi-Server Administration...........................................................127 ■ Chapter 7: Managing Security Within the Database Engine.............................159 ■ Chapter 8: Working with Database Objects.....................................................183 ■ Chapter 9: Indexing for Performance...............................................................223 ■ Chapter 10: Managing Backups.......................................................................257 ■ Chapter 11: Restore and Recovery Strategies.................................................283 ■ Chapter 12: Automating Routine Maintenance................................................319 v PART 4: Troubleshooting and Tuning....................................................................353 ■ Chapter 13: Monitoring Your Server................................................................355 ■ Chapter 14: Auditing SQL Server......................................................................389 ■ Chapter 15: Extended Events Interface............................................................403 ■ Chapter 16: Managing Query Performance......................................................421 ■ Chapter 17: Secrets to Excelling as a Professional DBA..................................451 ■ Chapter 18: What’s Next?................................................................................463 Index.....................................................................................................................471 vi ■ CONTENTS Contents Contents at a Glance.................................................................................................v About the Authors................................................................................................xxiii PART 1: Introducing Microsoft SQL Server 2012......................................................1 ■ Chapter 1: New Features Overview......................................................................3 Availability Enhancements.......................................................................................3 Online Index Rebuilds......................................................................................................3 Indirect Checkpoints........................................................................................................4 Clustering.........................................................................................................................4 AlwaysOn.........................................................................................................................4 Manageability Enhancements ..................................................................................5 Extended Events..............................................................................................................5 Database Restores...........................................................................................................6 Contained Databases.......................................................................................................7 Programmability Enhancements..............................................................................7 Functions.........................................................................................................................8 Sequence Numbers.........................................................................................................9 OFFSET and FETCH........................................................................................................11 THROW...........................................................................................................................12 EXECUTE WITH RESULT SETS........................................................................................13 Summary................................................................................................................14 ■ Chapter 2: Pre-Installation Considerations........................................................15 Choosing a SQL Server Edition...............................................................................15 Enterprise Edition..........................................................................................................16 Standard Edition............................................................................................................17 vii ■ CONTENTS Developer Edition...........................................................................................................17 Business Intelligence Edition.........................................................................................18 Web Edition....................................................................................................................18 SQL Server 2012 Licenses......................................................................................18 Determining Hardware Requirements....................................................................19 Determining CPU Needs..........................................................................................21 Deriving a Baseline Specification..................................................................................21 Consider Future Utilization.............................................................................................21 Example Using the CPU Questions.................................................................................22 Disk Subsystems....................................................................................................22 Database Sizing.............................................................................................................23 Estimating Size from a Baseline....................................................................................24 Estimating Size Based Upon Row and Column Sizes....................................................24 Determining RAID Levels........................................................................................25 Understanding RAID Levels............................................................................................25 RAID 0............................................................................................................................26 RAID 1............................................................................................................................26 RAID 5............................................................................................................................26 RAID 10..........................................................................................................................26 Choosing Your RAID Level and Laying Out Your Files....................................................27 Final Disk Storage Configuration............................................................................28 Determining Memory Requirements.......................................................................28 Choosing the Server...............................................................................................29 SQL Server Consolidation.......................................................................................29 Consolidation with Virtual Servers.........................................................................31 Summary................................................................................................................32 ■ Chapter 3: Choosing a High-Availability Solution..............................................33 What Exactly Is High Availability Anyway?............................................................33 Failover Clustering.................................................................................................34 Key Terms......................................................................................................................34 Failover Clustering Overview.........................................................................................34 Implementation..............................................................................................................36 viii ■ CONTENTS Pros and Cons of Failover Clustering.............................................................................37 Database Mirroring.................................................................................................37 Key Terms......................................................................................................................37 Database Mirroring Overview........................................................................................38 Implementation..............................................................................................................38 Snapshots for Reporting................................................................................................39 High-safety mode..........................................................................................................39 High-Performance Mode................................................................................................40 Pros and Cons of Database Mirroring............................................................................41 Copying Data with Log Shipping.............................................................................42 Key Terms......................................................................................................................42 Log Shipping Overview..................................................................................................42 Implementation..............................................................................................................42 Restoring to the Secondary Server................................................................................44 Pros and Cons of Log Shipping......................................................................................44 Making Data Available Through Replication...........................................................45 Key Terms......................................................................................................................45 Replication Overview.....................................................................................................45 Snapshot Replication.....................................................................................................46 Transactional Replication..............................................................................................47 Updateable Subscriptions..........................................................................................47 Peer-to-Peer Replication...........................................................................................49 Merge Replication..........................................................................................................50 Pros and Cons of Replication.........................................................................................51 AlwaysOn................................................................................................................51 Key Terms......................................................................................................................52 AlwaysOn Overview.......................................................................................................52 Implementation..............................................................................................................53 Pros and Cons of AlwaysOn...........................................................................................55 Other High-Availability Techniques........................................................................55 High Availability Feature Comparison....................................................................56 Summary................................................................................................................57 ix ■ CONTENTS PART 2: Getting Started..........................................................................................59 ■ Chapter 4: Installing and Upgrading..................................................................61 User Accounts.........................................................................................................62 Preparation and Prerequisites................................................................................62 SQL Server Installation Center................................................................................63 Planning.........................................................................................................................64 Installation.....................................................................................................................65 Maintenance..................................................................................................................66 Tools..............................................................................................................................67 Resources......................................................................................................................68 Advanced.......................................................................................................................69 Options...........................................................................................................................70 Installing Your First Instance.................................................................................70 Getting Started...............................................................................................................70 Choosing Your Features.................................................................................................70 Configuring the Instance................................................................................................72 Configuring the Database Engine..................................................................................72 Validating and Installing................................................................................................73 Installing More Than One Instance.........................................................................74 Preparing to Install Another Instance............................................................................75 Getting Your System Ready...........................................................................................75 Configuring the Instance................................................................................................76 Command-Line Installation....................................................................................77 Learning the Parameters...............................................................................................77 Passing Parameters to the Installer...............................................................................79 Building the Command..................................................................................................80 Choosing the Level of Silence...................................................................................80 Specifying the Action and the Components...............................................................80 Configuring the Instance...........................................................................................81 Specifying the Data Directories.................................................................................82 Executing the Command................................................................................................82 Configuration File Installation................................................................................83 x