www.it-ebooks.info PUBLISHED BY Microsoft Press A Division of Microsoft Corporation One Microsoft Way Redmond, Washington 98052-6399 Copyright © 2012 by William R. Stanek 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: 2011944612 ISBN: 978-0-7356-6376-3 Printed and bound in the United States of America. First Printing Microsoft Press books are available through booksellers and distributors worldwide. If you need support related to this book, email Microsoft Press Book Support at mspinput@ microsoft.com. Please tell us what you think of this book at http://www.microsoft.com/ learning/booksurvey. Microsoft and the trademarks listed at http://www.microsoft.com/about/legal/en/us/ IntellectualProperty/Trademarks/EN-US.aspx are trademarks of the Microsoft group of companies. All other marks are property of their respective owners. The example companies, organizations, products, domain names, email addresses, logos, people, places, and events depicted herein are fictitious. No association with any real company, organization, product, domain name, email 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 damages caused or alleged to be caused either directly or indirectly by this book. Acquisitions Editor: Anne Hamiliton Developmental Editor: Karen Szall Project Editor: Karen Szall Editorial Production: Christian Holdener, S4Carlisle Publishing Services Technical Reviewer: boB Taylor Copyeditor: Susan McClung Indexer: Jean Skipp Cover: Twist Creative ∙ Seattle www.it-ebooks.info To my wife—for many years, through many books, many millions of words, and many thousands of pages, she's been there, providing support and encouragement and making every place we've lived a home. To my kids—for helping me see the world in new ways, for having exceptional patience and boundless love, and for making every day an adventure. To Karen, Martin, Lucinda, Juliana, Ben, and many others who’ve helped out in ways both large and small. —William R. Stanek www.it-ebooks.info www.it-ebooks.info Contents at a Glance Introduction xxi PART I MICROSOFT SQL SERVER 2012 ESSENTIALS CHAPTER 1 Managing Your SQL Servers 3 CHAPTER 2 Managing SQL Server Services and Clients 43 PART II MICROSOFT SQL SERVER 2012 MANAGEMENT AND SECURITY CHAPTER 3 Implementing Policy-Based Management 77 CHAPTER 4 Configuring and Tuning Your SQL Servers 111 CHAPTER 5 Tuning and Linking Your SQL Servers 137 CHAPTER 6 Database Administration Essentials 193 CHAPTER 7 Implementing SQL Server 2012 Security 253 PART III MICROSOFT SQL SERVER 2012 DATA MANAGEMENT CHAPTER 8 Manipulating Schemas, Tables, and Views 313 CHAPTER 9 Using Indexes, Constraints, and Partitions 361 PART IV MICROSOFT SQL SERVER 2012 OPTIMIZATION, MAINTENANCE, AND RECOVERY CHAPTER 10 Automating and Maintaining SQL Server 2012 403 CHAPTER 11 SQL Server 2012 Backup and Recovery 453 CHAPTER 12 SQL Server 2012 Profiling and Monitoring 501 Index 535 www.it-ebooks.info www.it-ebooks.info Contents Introduction xxi PART I MICROSOFT SQL SERVER 2012 ESSENTIALS Chapter 1 Managing Your SQL Servers 3 Using SQL Server Management Studio ....................... 4 Getting Started with SQL Server Management Studio 5 Connecting to a Specific Server Instance 7 Connecting to a Specific Database 7 Managing SQL Server Groups ............................... 9 Introducing SQL Server Groups and the Registered Servers View 9 Creating a Server Group 10 Deleting a Server Group 11 Editing and Moving Server Groups 11 Adding SQL Servers to a Group 12 Managing Servers .........................................12 Registering a Connected Server 13 Registering a New Server in the Registered Servers View 14 Registering Previously Registered SQL Server 2000 Servers 15 Updating Registration for Local Servers 15 Copying Server Groups and Registration Details from One Computer to Another 15 Editing Registration Properties 18 Connecting to a Server 18 Disconnecting from a Server 18 Moving a Server to a New Group 18 Deleting a Server Registration 19 What do you think of this book? We want to hear from you! Microsoft is interested in hearing your feedback so we can continually improve our books and learning resources for you. To participate in a brief online survey, please visit: microsoft.com/learning/booksurvey vii www.it-ebooks.info Using Windows PowerShell for SQL Server Management ......19 Starting, Stopping, and Configuring SQL Server Agent ........25 Starting, Stopping, and Configuring MSDTC ..................25 Managing SQL Server Startup ..............................26 Enabling or Preventing Automatic SQL Server Startup 27 Setting Database Engine Startup Parameters 28 Managing Services from the Command Line 31 Managing the SQL Server Command- Line Executable 32 Managing Server Activity ..................................33 Examining Process Information 33 Tracking Resource Waits and Blocks 35 Troubleshooting Deadlocks and Blocking Connections 39 Tracking Command Execution in SQL Server 41 Killing Server Processes 42 Chapter 2 Managing SQL Server Services and Clients 43 Managing SQL Server Component Feature Access ............44 Managing the Network and SQL Server Native Client Configuration ....................................49 Managing the Connections Configuration 51 Specifying the Shared Memory Network Configuration 52 Specifying the Named Pipes Network Configuration 52 Specifying the TCP/IP Network Configuration 53 Configuring Security for Native Client Configurations 58 Configuring the Native Client Protocol Order 58 Configuring the Shared Memory Native Client Configuration 59 Configuring the Named Pipes Native Client Configuration 59 Configuring the TCP/IP Native Client Configuration 60 Understanding the Services Configuration ...................61 Configuring SQL Server Services ............................65 Managing Service State and Start Mode 67 Setting the Startup Service Account 69 viii Contents www.it-ebooks.info Setting Up File Streaming 70 Configuring Service Dump Directories, Error Reporting, and Customer Feedback Reporting 73 PART II MICROSOFT SQL SERVER 2012 MANAGEMENT AND SECURITY Chapter 3 Implementing Policy-Based Management 77 Introducing Policy-Based Management .....................77 Working with Policy-Based Management ....................80 Configuring Central Management Servers ...................88 Registering Central Management Servers 88 Registering Subordinate Servers and Groups 89 Moving Subordinate Servers and Server Groups 90 Deleting Subordinate Servers and Server Groups 91 Executing Statements Against Multiple Servers 91 Managing Policies Throughout the Enterprise ................92 Importing and Exporting Policies 92 Configuring and Managing Policy Facets 94 Creating and Managing Policy Conditions 95 Creating and Managing Policies 99 Managing Policy Categories and Mandating Policies 102 Evaluating Policies 104 Troubleshooting Policies 107 Chapter 4 Configuring and Tuning Your SQL Servers 111 Accessing SQL Server Configuration Data ...................112 Techniques for Managing SQL Server Configuration Options ..............................................114 Setting Configuration Options 114 Working with SET Options 115 Working with Server Options 118 Working with Database Options 120 Managing Database Compatibility 122 Configuring SQL Server with Stored Procedures .............123 Using SQL Server Management Studio for Queries 123 Contents ix www.it-ebooks.info Executing Queries and Changing Settings 124 Checking and Setting Configuration Parameters 126 Changing Settings with ALTER DATABASE 131 Chapter 5 Tuning and Linking Your SQL Servers 137 SQL Server Management Studio Essentials ..................138 Managing the Configuration with SQL Server Management Studio 138 Determining System and Server Information 140 Configuring Utility Control Points 141 Working with Linked Servers and Distributed Data ...........146 Using Distributed Queries 147 Using Distributed Transactions 150 Running the Distributed Transaction Coordinator Service 152 Configuring Authentication and Auditing ...................152 Setting the Authentication Mode 152 Setting the Auditing Level 153 Enabling or Disabling C2 Audit Logging 154 Enabling or Disabling Common Criteria Compliance 155 Tuning Memory Usage ...................................155 Working with Dynamically Configured Memory 157 Using Fixed Memory 158 Enabling AWE Memory Support 159 Optimizing Memory for Indexing 160 Allocating Memory for Queries 161 Configuring Processors and Parallel Processing ..............162 Optimizing CPU Usage 162 Setting Parallel Processing 165 Configuring Threading, Priority, and Fibers .................166 Configuring User and Remote Connections .................168 Setting Maximum User Connections 168 Setting Default Connection Options 169 Configuring Remote Server Connections 171 Managing Server Settings .................................172 Enabling or Disabling Contained Database Support 173 Enabling or Disabling File Streaming Support 174 x Contents www.it-ebooks.info