EXAM 70-462 Administering Microsoft SQL Server 2012

spine 1.47”EXAM 70-462Administering MicrosoftSQL Server 2012 Databases Microsoft Certified Solutions AssociateThe new MCSA certifications validate the coretechnical skills required to build a sustainablecareer in IT. MCSA opens the door to multiplecareer paths and is a requirement for MCSEcertifications.EXAM Configure IPv4 and IPv6 addressing Deploy and configure DHCP servers, DNS servers, and DNS zones Implement IPsec, Windows Firewall, and Network AccessProtection (NAP) Plan and manage Windows Server Update Services Manage file and print services in Windows Server 2008 R2 Monitor and troubleshoot network performance2PRACTICE TESTSAssess your skills with practice tests on CD. You can work throughhundreds of questions using multiple testing modes to meet yourspecific learning needs. You get detailed explanations for rightand wrong answers —including a customized learning path thatdescribes how and where to focus your studies.microsoft.com/mspressU.S.A. 69.99Canada 80.99[Recommended]Certification/Windows Serverexam discount from Microsoft.· 15%Offer expires 12/31/15. Details inside.self-paced study guide.· OfficialPracticetests with multiple,· customizabletesting options and a···learning plan based on your results.200 practice and review questions.Case scenarios, exercises, and bestpractices.Fully searchable eBook of this guide.For system requirements, see the Introduction.70-462AdministeringMicrosoftSQL Server 2012Databases About the AuthorsTony Northrup, MCITP, MCPD, MCSE, CISSP,has written 20 books covering Windowssystem administration and development,including several Microsoft Press Training Kitsand Windows Server 2008 Networking andNetwork Access Protection (NAP).J.C. Mackin, MCITP, MCTS, MCSE, MCT, is awriter, editor, and trainer who’s worked withWindows networking technologies for 10 years.He has written several Training Kits, including forExams 70-622, 70-643, and 70-685.EX AM70-462tests oicenctCDISBN: 978-0-7356-6607-8Your kit includes: Enable remote and wireless access, including DirectAccessFor complete information on Microsoft Certifications,visit: microsoft.com/learning/certification Maximize your performance on the exam by learning to:Exam 70-642 is one of three required exams forMCSA: Windows Server 2008 certification. For alimited time, it is also valid for MCTS certification,which will be retired.Administering MicrosoftSQL Server 2012 DatabasesFully updated for Windows Server 2008 R2! Ace your preparation forthe skills measured by Exam 70-642—and on the job. Work at yourown pace through a series of lessons and reviews that fully cover eachexam objective. Then, reinforce what you’ve learned by applying yourknowledge to real-world case scenarios and practice exercises. Thisofficial Microsoft study guide is designed to help make the most ofyour study time.Training Kit1EXAM PREP GUIDEPraYour 2-in-1 Self-Paced Training KitOrin ThomasPeter WardboB TaylorTraining KitCyan Magenta Yellow Black

Exam 70-462: Administering Microsoft SQL Server 2012 DatabasesOBJECTIVECHAPTERLESSONPlan installation.11Install SQL Server and related services.12Implement a migration strategy.41Configure additional SQL Server components.31Manage SQL Server Agent.111Manage and configure databases.33Configure SQL Server instances.21Implement a SQL Server clustered instance.81Manage SQL Server instances.22Identify and resolve concurrency problems.102Collect and analyze troubleshooting data.91–6Audit SQL Server instances.63INSTALL AND CONFIGURE (19 PERCENT)MAINTAIN INSTANCES AND DATABASES (17 PERCENT)OPTIMIZE AND TROUBLESHOOT (14 PERCENT)MANAGE DATA (20 PERCENT)Configure and maintain a back up strategy.112Restore databases.113Implement and maintain indexes.101Import and export data.42IMPLEMENT SECURITY (18 PERCENT)Manage logins and server roles.51Manage database permissions.61Manage users and database roles.52Troubleshoot security.62Implement AlwaysOn.82Implement database mirroring.71Implement replication.72IMPLEMENT HIGH AVAILABILITY (12 PERCENT)Exam Objectives The exam objectives listed here are current as of this book’s publication date. Exam objectives aresubject to change at any time without prior notice and at Microsoft’s sole discretion. Please visit the Microsoft Learningwebsite for the most current listing of exam objectives: 62.mspx.

PUBLISHED BYMicrosoft PressA Division of Microsoft CorporationOne Microsoft WayRedmond, Washington 98052-6399Copyright 2012 by Orin Thomas (Content); Orin Thomas (Sample Code); Peter Ward (Sample Code); PeterWard (Content)All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by anymeans without the written permission of the publisher.Library of Congress Control Number: 2012938612ISBN: 978-0-7356-6607-8Printed and bound in the United States of America.First PrintingMicrosoft Press books are available through booksellers and distributors worldwide. If you need support relatedto this book, email Microsoft Press Book Support at mspinput@microsoft.com. Please tell us what you think ofthis book at soft and the trademarks listed at ctualProperty/Trademarks/EN-US.aspx are trademarks of the Microsoft group of companies. All other marks are property oftheir respective owners.The example companies, organizations, products, domain names, email addresses, logos, people, places, andevents 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 authors’ views and opinions. The information contained in this book is provided withoutany express, statutory, or implied warranties. Neither the authors, Microsoft Corporation, nor its resellers, ordistributors will be held liable for any damages caused or alleged to be caused either directly or indirectly bythis book.Acquisitions Editor: Anne HamiltonDevelopmental Editor: Karen SzallProject Editor: Karen SzallEditorial Production: nSight, Inc.Technical Reviewer: boB Taylor; Technical Review services provided by Content Master, a member of CMGroup, Ltd.Copyeditor: Kerin ForsythIndexer: Lucie HaskinsCover: Twist Creative Seattle

ContentsIntroductionxviiCHAPTER 1Planning and Installing SQL Server 20121CHAPTER 2Configuring and Managing SQL Server InstancesCHAPTER 3Configuring SQL Server 2012 Components105CHAPTER 4Migrating, Importing, and Exporting153CHAPTER 5SQL Server Logins, Roles, and Users193CHAPTER 6Securing SQL Server 2012229CHAPTER 7Mirroring and Replication279CHAPTER 8Clustering and AlwaysOn327CHAPTER 9Troubleshooting SQL Server 2012371CHAPTER 10Indexes and Concurrency417CHAPTER 11SQL Server Agent, Backup, and Restore465CHAPTER 12Code Case Studies523Index56761

ContentsIntroductionxviiSystem RequirementsxviiPractice Setup InstructionsxixUsing the Companion CDxxiiiAcknowledgmentsxxvErrata & Book SupportxxvWe Want to Hear from YouxxvStay in TouchxxvPreparing for the ExamChapter 1xxviiPlanning and Installing SQL Server 20121Lesson 1: Planning Your Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2Evaluating Installation Requirements2Designing the Installation7Planning Scale Up versus Scale Out Basics8Shrinking and Growing Databases9Designing the Storage for New Databases13Remembering Capacity Constraints15Identifying a Standby Database for Reporting15Identifying Windows-Level Security and Service-Level Security15Performing a Core Mode Installation17Benchmarking a Server19Lesson Summary23Lesson Review24Lesson 2: Installing SQL Server and Related Services . . . . . . . . . . . . . . . . . 26What do you think of this book? We want to hear from you!Microsoft is interested in hearing your feedback so we can continually improve ourbooks and learning resources for you. To participate in a brief online survey, please visit:microsoft.com/learning/booksurveyvii

Configuring an Operating System Disk26Installing the SQL Server Database Engine27Installing SQL Server 2012 from the Command Prompt33Installing SQL Server Integration Services34Enabling and Disabling Features36Installing SQL Server 2012 by Using a Configuration File39Testing Connectivity40Lesson Summary52Lesson Review53Case Scenarios. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54Suggested Practices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55Answers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56Chapter 2Configuring and Managing SQL Server Instances61Lesson 1: Configuring SQL Server Instances . . . . . . . . . . . . . . . . . . . . . . . . . 62Instance-Level Settings62Database Configuration and Standardization68Distributed Transaction Coordinator71Configuring Database Mail72Lesson Summary78Lesson Review78Lesson 2: Managing SQL Server Instances. . . . . . . . . . . . . . . . . . . . . . . . . . . 80Installing Additional Instances80Deploying Software Updates and Patch Management84Configuring Resource Governor86Using WSRM with Multiple Database Engine Instances91Cycle SQL Server Error Logs93Lesson Summary96Lesson Review96Case Scenarios. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98Suggested Practices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99Answers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100viiiContents

Chapter 3Configuring SQL Server 2012 Components105Lesson 1: Configuring Additional SQL Server Components . . . . . . . . . . . 106Deploying and Configuring Analysis Services106Deploying and Configuring Reporting Services108Deploying and Configuring SharePoint Integration112Configuring SQL Server Integration Services Security114Managing Full-Text Indexing116Configuring FILESTREAM118Configuring FileTables120Lesson Summary123Lesson Review124Lesson 2: Managing and Configuring Databases. . . . . . . . . . . . . . . . . . . . 125Designing and Managing Filegroups125Configuring and Standardizing Databases128Understanding Contained Databases128Using Data Compression131Encrypting Databases with Transparent Data Encryption135Partitioning Indexes and Tables137Managing Log Files140Using Database Console Commands141Lesson Summary146Lesson Review146Case Scenarios. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147Suggested Practices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148Answers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149Chapter 4Migrating, Importing, and Exporting153Lesson 1: Migrating to SQL Server 2012 . . . . . . . . . . . . . . . . . . . . . . . . . . . 154Upgrading an Instance to SQL Server 2012154Migrating a Database to a SQL Server 2012 Instance161Copying Databases to Other Servers164Migrating SQL Logins170Contentsix