You are previewing Training Kit Exam 70-462: Administering Microsoft® SQL Sever® 2012 Databases.
O'Reilly logo
Training Kit Exam 70-462: Administering Microsoft® SQL Sever® 2012 Databases

Book Description

EXAM PREP GUIDE

Ace your preparation for the skills measured by Exam 70-462—and on the job—with this official Microsoft study guide. Work at your own pace through a series of lessons and reviews that fully cover each exam objective. Then, reinforce and apply what you’ve learned through real-world case scenarios and practice exercises.

Maximize your performance on the exam by mastering the skills and experience measured by these objectives:

  • Install and configure SQL Server

  • Maintain instances and databases

  • Optimize and troubleshoot SQL Server

  • Manage data

  • Implement security

  • Implement high availability

  • PRACTICE TESTS

    A Note Regarding Practice Tests

    Assess your skills with practice tests. You can work through hundreds of questions using multiple testing modes to meet your specific learning needs. You get detailed explanations for right and wrong answers—including a customized learning path that describes how and where to focus your studies.

    For customers who purchase an ebook version of this title, instructions for downloading the CD files can be found in the ebook.

    Table of Contents

    1. Training Kit Exam 70-462: Administering Microsoft® SQL Sever® 2012 Databases
    2. A Note Regarding Supplemental Files
    3. Introduction
      1. System Requirements
        1. Hardware Requirements
          1. Virtualization Hardware Requirements
          2. Physical Hardware Requirements
        2. Software Requirements
      2. Practice Setup Instructions
        1. Prepare a Computer to Function as a Windows Server 2008 R2 Domain Controller
        2. Prepare AD DS
        3. Prepare a Member Server and Join It to the Domain
        4. Prepare a Second Member Server and Join It to the Domain
        5. Prepare a Third Member Server and Join It to the Domain
        6. Prepare a Fourth Member Server and Join It to the Domain
        7. Prepare a Computer Running the Server Core Installation Option and Join It to the Domain
      3. Using the Companion CD
        1. How to Install the Practice Tests
        2. How to Use the Practice Tests
        3. How to Uninstall the Practice Tests
      4. Acknowledgments
      5. Errata & Book Support
      6. We Want to Hear from You
      7. Stay in Touch
    4. Preparing for the Exam
    5. 1. Planning and Installing SQL Server 2012
      1. Before You Begin
      2. Lesson 1: Planning Your Installation
        1. Evaluating Installation Requirements
          1. Operating System Requirements
          2. Processor and RAM Requirements
          3. Hard Disk Requirements
          4. Installing SQL Server 2012 on a Domain Controller
          5. Software Requirements
          6. Virtualization Requirements
        2. Designing the Installation
        3. Planning Scale Up versus Scale Out Basics
        4. Shrinking and Growing Databases
          1. Increasing Database Size
          2. Decreasing Database Size
        5. Designing the Storage for New Databases
        6. Remembering Capacity Constraints
        7. Identifying a Standby Database for Reporting
        8. Identifying Windows-Level Security and Service-Level Security
        9. Performing a Core Mode Installation
        10. Benchmarking a Server
          1. Practice: Prepare for the Installation of SQL Server 2012
            1. Practice: Prepare for the Installation of SQL Server 2012
        11. Lesson Summary
        12. Lesson Review
      3. Lesson 2: Installing SQL Server and Related Services
        1. Configuring an Operating System Disk
        2. Installing the SQL Server Database Engine
        3. Installing SQL Server 2012 from the Command Prompt
        4. Installing SQL Server Integration Services
        5. Enabling and Disabling Features
        6. Installing SQL Server 2012 by Using a Configuration File
        7. Testing Connectivity
          1. Practice: Prepare for and Install SQL Server 2012
            1. Practice: Prepare for and Install SQL Server 2012
        8. Lesson Summary
        9. Lesson Review
      4. Case Scenarios
        1. Case Scenario 1: Planning Deployment of SQL Server 2012
        2. Case Scenario 2: SQL Server Deployment
      5. Suggested Practices
        1. Configure Additional Firewall Rules and Generate a Features Discovery Report
        2. Adding and Removing Features and Adding Databases to SQL Server 2012 on a Computer Running a Server Core Operating System
      6. Answers
        1. Lesson 1
        2. Lesson 2
        3. Case Scenario 1
        4. Case Scenario 2
    6. 2. Configuring and Managing SQL Server Instances
      1. Before You Begin
      2. Lesson 1: Configuring SQL Server Instances
        1. Instance-Level Settings
          1. Configuring Memory Allocation
          2. Configuring Processor and I/O Affinity
          3. Configuring Fill Factor
        2. Database Configuration and Standardization
        3. Distributed Transaction Coordinator
        4. Configuring Database Mail
          1. Practice: Configure SQL Server 2012 Instance-Level Settings
            1. Practice: Configure SQL Server 2012 Instance-Level Settings
        5. Lesson Summary
        6. Lesson Review
      3. Lesson 2: Managing SQL Server Instances
        1. Installing Additional Instances
        2. Deploying Software Updates and Patch Management
        3. Configuring Resource Governor
          1. Resource Pools
          2. Workload Groups
          3. Resource Governor Classification
        4. Using WSRM with Multiple Database Engine Instances
        5. Cycle SQL Server Error Logs
          1. Practice: Deploy and Configure Additional SQL Server 2012 Instances
            1. Practice: Deploy and Configure Additional SQL Server 2012 Instances
        6. Lesson Summary
        7. Lesson Review
      4. Case Scenarios
        1. Case Scenario 1: Instance Configuration
        2. Case Scenario 2: Additional Instances and Error Log Cycling
      5. Suggested Practices
        1. Configure Instances
        2. Install and Manage Multiple Instances
      6. Answers
        1. Lesson 1
        2. Lesson 2
        3. Case Scenario 1
        4. Case Scenario 2
    7. 3. Configuring SQL Server 2012 Components
      1. Before You Begin
      2. Lesson 1: Configuring Additional SQL Server Components
        1. Deploying and Configuring Analysis Services
        2. Deploying and Configuring Reporting Services
        3. Deploying and Configuring SharePoint Integration
        4. Configuring SQL Server Integration Services Security
        5. Managing Full-Text Indexing
        6. Configuring FILESTREAM
        7. Configuring FileTables
          1. Practice: Install Analysis Services and Reporting Services
            1. Practice: Install Analysis Services and Reporting Services
        8. Lesson Summary
        9. Lesson Review
      3. Lesson 2: Managing and Configuring Databases
        1. Designing and Managing Filegroups
          1. Adding New Filegroups
          2. Moving an Index from One Filegroup to Another
        2. Configuring and Standardizing Databases
        3. Understanding Contained Databases
        4. Using Data Compression
          1. Row-Level Compression
          2. Unicode Compression
          3. Page-Level Compression
          4. Estimating Compression
        5. Encrypting Databases with Transparent Data Encryption
        6. Partitioning Indexes and Tables
        7. Managing Log Files
        8. Using Database Console Commands
          1. Maintenance Statements
          2. Informational Statements
          3. Validation Statements
          4. Miscellaneous Statements
            1. Practice: Table Partitioning, Compression, Encryption, and Log Files
        9. Lesson Summary
        10. Lesson Review
      4. Case Scenarios
        1. Case Scenario 1: Configuring FILESTREAM and FileTable
        2. Case Scenario 2: Deploying Transparent Data Encryption
      5. Suggested Practices
        1. FILESTREAM and FileTable
        2. Transparent Data Encryption and Table Partitioning
      6. Answers
        1. Lesson 1
        2. Lesson 2
        3. Case Scenario 1
        4. Case Scenario 2
    8. 4. Migrating, Importing, and Exporting
      1. Before You Begin
      2. Lesson 1: Migrating to SQL Server 2012
        1. Upgrading an Instance to SQL Server 2012
          1. Upgrading from SQL Server 2005
          2. Upgrading from SQL Server 2008
          3. Upgrading from SQL Server 2008 R2
          4. Intra-Edition Upgrades
          5. Upgrade Advisor
          6. Preparing for Upgrade with Distributed Replay Utility
          7. Performing an Upgrade
        2. Migrating a Database to a SQL Server 2012 Instance
          1. Detach a Database
          2. Attach a Database
        3. Copying Databases to Other Servers
          1. Copy Database Wizard
          2. Copying Databases with Backup and Restore
          3. Publishing a Database by Using Generate and Publish Scripts Wizard
        4. Migrating SQL Logins
          1. Practice: Migrate and Copy Databases
            1. Practice: Migrate and Copy Databases
        5. Lesson Summary
        6. Lesson Review
      3. Lesson 2: Exporting and Importing Data
        1. Copying and Exporting Data
        2. Using the SQL Server Import and Export Wizard
        3. Using BCP to Import and Export Data
        4. Importing Data by Using BULK INSERT
        5. Importing Data by Using OPENROWSET(BULK)
        6. Using Format Files
        7. Preparing Data for Bulk Operations
        8. Select Into
          1. Practice: Bulk Export and Import of Data
            1. Practice: Bulk Export and Import of Data
        9. Lesson Summary
        10. Lesson Review
      4. Case Scenarios
        1. Case Scenario 1: Consolidation at Contoso
        2. Case Scenario 2: Tailspin Toys Bulk Data
      5. Suggested Practices
        1. Implement a Migration Strategy
        2. Import and Export Data
      6. Answers
        1. Lesson 1
        2. Lesson 2
        3. Case Scenario 1
        4. Case Scenario 2
    9. 5. SQL Server Logins, Roles, and Users
      1. Before You Begin
      2. Lesson 1: Managing Logins and Server Roles
        1. SQL Logins
          1. Windows-Authenticated SQL Server Logins
          2. SQL Server–Authenticated Logins
          3. Certificate Authentication
          4. Asymmetric Key Authentication
          5. Altering Existing Logins
          6. Login-Related Catalog Views
          7. Removing Logins
          8. Denying Server Access
          9. Server Roles
        2. User-Defined Server Roles
        3. Credentials
          1. Practice: SQL Server Logins and Server Roles
            1. Practice: SQL Server Logins and Server Roles
        4. Lesson Summary
        5. Lesson Review
      3. Lesson 2: Managing Users and Database Roles
        1. Database Users
        2. Database Roles
          1. Fixed Database-Level Roles
          2. Flexible Database-Level Roles
          3. msdb Roles
          4. User and Role Catalog Views
        3. Contained Users
        4. Least Privilege
        5. Application Roles
          1. Practice: Database Users and Roles
            1. Practice: Database Users and Roles
        6. Lesson Summary
        7. Lesson Review
      4. Case Scenarios
        1. Case Scenario 1: Instance-Level Permissions for Contoso’s Accountants
        2. Case Scenario 2: Contained Databases at Fabrikam
      5. Suggested Practices
        1. Manage Logins and Server Roles
        2. Manage Users and Database Roles
      6. Answers
        1. Lesson 1
        2. Lesson 2
        3. Case Scenario 1
        4. Case Scenario 2
    10. 6. Securing SQL Server 2012
      1. Before You Begin
      2. Lesson 1: Managing Database Permissions
        1. Understanding Securables
        2. Assigning Permissions on Objects
        3. Managing Permissions by Using Database Roles
          1. Assigning Fixed Database Roles
          2. Using Flexible Database Roles
        4. Protecting Objects from Modification
        5. Using Schemas
        6. Determining Effective Permissions
          1. PRACTICE: Configuring Database Permissions
            1. PRACTICE: Configuring Database Permissions
        7. Lesson Summary
        8. Lesson Review
      3. Lesson 2: Troubleshooting SQL Server Security
        1. Troubleshooting Authentication
          1. Using Authentication Modes
          2. Resolving Client Connection Problems
        2. Troubleshooting Certificates and Keys
        3. Troubleshooting Endpoints
        4. Using Security Catalog Views
          1. PRACTICE: Troubleshooting Security
            1. PRACTICE: Troubleshooting Security
        5. Lesson Summary
        6. Lesson Review
      4. Lesson 3: Auditing SQL Server Instances
        1. Using SQL Server Audit
          1. Creating a Server Audit
          2. SQL Server Audit Action Groups and Actions
          3. Creating a Server Audit Specification
          4. Creating a Database Audit Specification
          5. Viewing SQL Server Audit Views and Functions
        2. Configuring Login Auditing
        3. Using c2 Audit Mode
        4. Common Criteria Compliance
        5. Policy-Based Management
          1. PRACTICE: Configuring SQL Server Audit
            1. PRACTICE: Configuring SQL Server Audit
        6. Lesson Summary
        7. Lesson Review
      5. Case Scenarios
        1. Case Scenario 1: Configuring Database Permissions
        2. Case Scenario 2: Troubleshooting Security
        3. Case Scenario 3: Auditing at Fabrikam
      6. Suggested Practices
        1. Manage Database Permissions
        2. Troubleshoot Security
        3. Audit SQL Server Instances
      7. Answers
        1. Lesson 1
        2. Lesson 2
        3. Lesson 3
        4. Case Scenario 1
        5. Case Scenario 2
        6. Case Scenario 3
    11. 7. Mirroring and Replication
      1. Before You Begin
      2. Lesson 1: Mirroring Databases
        1. Database Mirroring
        2. Mirroring Prerequisites
          1. Full Recovery Model
          2. Recover by Using NO RECOVERY
          3. Endpoint Firewall Rules
        3. Configuring Mirroring with Windows Authentication
        4. Configuring Mirroring with Certificate Authentication
        5. Changing Operating Modes
        6. Role Switching and Failover
        7. Monitoring Mirrored Databases
          1. Database Mirroring Monitor
          2. Monitoring Mirroring by Using Stored Procedures
        8. Upgrading Mirrored Databases
          1. Practice Mirroring a Database
            1. Practice Mirroring a Database
        9. Lesson Summary
        10. Lesson Review
      3. Lesson 2: Database Replication
        1. Replication Architecture
        2. Replication Types
        3. Snapshot Replication
          1. Configuring Snapshot Replication
          2. Configuring a Subscription
        4. Transactional Replication
        5. Peer-to-Peer Transactional Replication
        6. Merge Replication
        7. Replication Monitor
        8. Controlling Replication of Constraints, Columns, and Triggers
        9. Heterogeneous Data
          1. Practice Configuring Replication
            1. Practice Configuring Replication
        10. Lesson Summary
        11. Lesson Review
      4. Case Scenarios
        1. Case Scenario 1: Database Mirroring at Coho Vineyard
        2. Case Scenario 2: Database Replication at Tailspin Toys
      5. Suggested Practices
        1. Implement Database Mirroring
        2. Implement Replication
      6. Answers
        1. Lesson 1
        2. Lesson 2
        3. Case Scenario 1
        4. Case Scenario 2
    12. 8. Clustering and AlwaysOn
      1. Before You Begin
      2. Lesson 1: Clustering SQL Server 2012
        1. Fulfilling Edition Prerequisites
          1. Windows Server 2008 R2 as Shared Storage
          2. Connecting to the SAN by Using iSCSI Initiator
        2. Creating a Windows Server 2008 R2 Failover Cluster
        3. Installing a SQL Server Failover Cluster
          1. Complete the Installation
        4. Multi-Subnet Failover Clustering
        5. Performing Manual Failover
        6. Troubleshooting Failover Clusters
          1. Practice Building a SQL Server 2012 Failover Cluster
            1. Practice Building a SQL Server 2012 Failover Cluster
        7. Lesson Summary
        8. Lesson Review
      3. Lesson 2: AlwaysOn Availability Groups
        1. What Are AlwaysOn Availability Groups?
        2. Meeting Availability Group Prerequisites
        3. Configuring Availability Modes
        4. Selecting Failover Modes
        5. Configuring Readable Secondary Replicas
        6. Deploying AlwaysOn Availability Groups
          1. Creating an AlwaysOn Endpoint
          2. Enabling AlwaysOn Availability Groups
          3. Creating an Availability Group
          4. Creating or Adding an Availability Group Listener
          5. Adding Secondary Replicas
        7. Using Availability Groups on Failover Cluster Instances
          1. Practice Deploying AlwaysOn Availability Groups
            1. Practice Deploying AlwaysOn Availability Groups
        8. Lesson Summary
        9. Lesson Review
      4. Case Scenarios
        1. Case Scenario 1: Failover Cluster Instances at Contoso
        2. Case Scenario 2: AlwaysOn Availability Groups at Fabrikam
      5. Suggested Practices
        1. Implement a SQL Server Clustered Instance
        2. Implement AlwaysOn
      6. Answers
        1. Lesson 1
        2. Lesson 2
        3. Case Scenario 1
        4. Case Scenario 2
    13. 9. Troubleshooting SQL Server 2012
      1. Before You Begin
      2. Lesson 1: Working with Performance Monitor
        1. Getting Started with Performance Monitor
        2. Capturing Performance Monitor Data
        3. Creating Data Collector Sets
          1. Practice Using Performance Monitor
            1. Practice Using Performance Monitor
        4. Lesson Summary
        5. Lesson Review
      3. Lesson 2: Working with SQL Server Profiler
        1. Capturing Activity with SQL Server Profiler
          1. Tracing Output Options
          2. Trace Events
          3. Trace Columns
          4. Filtering Traces
          5. Trace Templates
        2. Understanding SQL Trace
        3. Reviewing Trace Output
        4. Capturing Activity with Extended Events Profiler
          1. Practice Creating Traces
            1. Practice Creating Traces
        5. Lesson Summary
        6. Lesson Review
      4. Lesson 3: Monitoring SQL Server
        1. Monitoring Activity
          1. Dynamic Management Views and Functions
          2. Querying Dynamic Management Views
        2. Working with Activity Monitor
          1. Practice Using Dynamic Management Objects
            1. Practice Using Dynamic Management Objects
        3. Lesson Summary
        4. Lesson Review
      5. Lesson 4: Using the Data Collector Tool
        1. Capturing and Managing Performance Data
          1. Using the Data Collector
          2. Defining Data Collection Sets
          3. Designing a Data Collector Topology
          4. Configuring the Data Collector
          5. Assigning Roles to Data Collector Security
          6. Monitoring the Data Collector
        2. Analyzing Collected Performance Data
          1. Server Activity Report
          2. Disk Usage Report
          3. Query Statistics Report
            1. Practice Configuring the Management Data Warehouse
        3. Lesson Summary
        4. Lesson Review
      6. Lesson 5: Identifying Bottlenecks
        1. Monitoring Disk Usage
          1. Monitoring Disk I/O and Detecting Excess Paging
          2. Isolating Disk Activity Created by SQL Server
        2. Monitoring Memory Usage
          1. Isolating Memory Used by SQL Server
        3. Monitoring CPU Usage
          1. Practice Configuring Memory Options by Using SQL Server Management Studio
            1. Practice Configuring Memory Options by Using SQL Server Management Studio
        4. Lesson Summary
        5. Lesson Review
      7. Case Scenarios
        1. Case Scenario 1: Identifying Poor Query Performance
        2. Case Scenario 2: Deploying Auditing
      8. Suggested Practices
        1. Create a Trace by Using SQL Server Profiler
        2. Capture a Performance Baseline by Using Performance Monitor
      9. Answers
        1. Lesson 1
        2. Lesson 2
        3. Lesson 3
        4. Lesson 4
        5. Lesson 5
        6. Case Scenario 1
        7. Case Scenario 2
    14. 10. Indexes and Concurrency
      1. Before You Begin
      2. Lesson 1: Implementing and Maintaining Indexes
        1. Understanding the Anatomy of a Balanced Tree (B-Tree)
        2. Understanding Index Types and Structures
          1. Clustered Indexes
          2. Non-Clustered Indexes
          3. Covering Indexes
          4. Filtered Indexes
          5. Primary XML and Secondary XML Indexes
          6. Spatial Indexes
          7. Full-Text Indexes
          8. Columnstore Indexes
        3. Designing Indexes for Efficient Retrieval
          1. Clustered Indexes
          2. Non-Clustered Indexes
          3. Covering Indexes
          4. Filtered Indexes
          5. Primary XML and Secondary XML Indexes
          6. Full-Text Indexes and Semantic Searches
          7. Columnstore Indexes
        4. Understanding Statistics
          1. Using Index Internals
          2. Moving Row-Overflow Data
        5. Creating and Modifying Indexes
          1. Choosing Fill Factors
          2. Determining Fragmentation
          3. Removing Index Fragmentation
          4. Updating Statistics
        6. Tracking Missing Indexes
        7. Reviewing Unused Indexes
          1. Practice Maintaining Indexes and Statistics
            1. Practice Maintaining Indexes and Statistics
        8. Lesson Summary
        9. Lesson Review
      3. Lesson 2: Identifying and Resolving Concurrency Problems
        1. Defining Transactions and Transaction Scope
        2. Understanding SQL Server Lock Management
          1. Lock Modes
          2. Explaining Lock Compatibility
          3. Understanding Dynamic Lock Management
          4. Understanding Transaction Isolation Levels
          5. Choosing the Read Committed Snapshot Isolation Database Option
          6. Lock Duration Based on Transaction Isolation Levels
          7. Monitoring Locks
          8. Identifying Blocking
          9. Avoiding Blocking
        3. Using AlwaysOn Replicas to Improve Concurrency
        4. Detecting and Correcting Deadlocks
          1. Capturing Deadlock Information
        5. Using Activity Monitor
        6. Diagnosing Bottlenecks
        7. Using Reports for Performance Analysis
          1. Using Standard Reports
          2. Using Custom Reports
          3. Executing the KILL Process
            1. Practice Examining Dynamic Management Views
        8. Lesson Summary
        9. Lesson Review
      4. Case Scenarios
        1. Case Scenario 1: Fabrikam Year-Query Performance
        2. Case Scenario 2: Analyzing Concurrency at Tailspin Toys
      5. Suggested Practices
        1. Implement and Maintain Indexes and Statistics
        2. Identify and Resolve Concurrency Problems
      6. Answers
        1. Lesson 1
        2. Lesson 2
        3. Case Scenario 1
        4. Case Scenario 2
    15. 11. SQL Server Agent, Backup, and Restore
      1. Before You Begin
      2. Lesson 1: Managing SQL Server Agent
        1. Executing Jobs by Using SQL Server Agent
          1. Configuring SQL Server Agent Account
          2. Setting SQL Server Agent Security
          3. Configuring SQL Server Agent Mail Profile
          4. Setting Up the SQL Server Agent Error Log
        2. Managing Alerts
        3. Managing Jobs
          1. Creating a Job
          2. Monitoring Jobs
          3. Managing Operators
        4. Monitoring Multi-Server Environments
          1. Practice Configuring SQL Server Agent, Jobs, Operators, and Alerts
            1. Practice Configuring SQL Server Agent, Jobs, Operators, and Alerts
        5. Lesson Summary
        6. Lesson Review
      3. Lesson 2: Configuring and Maintaining a Backup Strategy
        1. Understanding Backup Types
          1. Using Backup Compression
          2. Understanding Recovery Models
        2. Backing Up System Databases
        3. Backing Up Replicated Databases
        4. Backing Up Mirrored Databases
        5. Backing Up AlwaysOn Replicas
        6. Using Database Checkpoints
        7. Using Backup Devices
        8. Backing Up Media Sets
        9. Performing Backups
          1. Full and Differential Backups
          2. Performing Transaction Log Backups
        10. Viewing Backup History
          1. Practice Backing Up the AdventureWorks2012 Database
            1. Practice Backing Up the AdventureWorks2012 Database
        11. Lesson Summary
        12. Lesson Review
      4. Lesson 3: Restoring SQL Server Databases
        1. Restoring Databases
        2. Performing File Restores
        3. Performing Page Restores
        4. Restoring a Database Protected with Transparent Data Encryption
        5. Restoring System Databases
        6. Restoring Replicated Databases
        7. Checking Database Status
          1. Practice Restoring the AdventureWorks2012 Database
            1. Practice Restoring the AdventureWorks2012 Database
        8. Lesson Summary
        9. Lesson Review
      5. Case Scenarios
        1. Case Scenario 1: SQL Server Agent at Contoso
        2. Case Scenario 2: Fabrikam Backup Strategy
        3. Case Scenario 3: Recovery at Adatum
      6. Suggested Practices
        1. Manage SQL Server Agent
        2. Configure and Maintain a Backup Strategy
        3. Restore Databases
      7. Answers
        1. Lesson 1
        2. Lesson 2
        3. Lesson 3
        4. Case Scenario 1
        5. Case Scenario 2
        6. Case Scenario 3
    16. 12. Code Case Studies
      1. Case Study 1
        1. Questions
      2. Case Study 2
        1. Questions
      3. Case Study 3
        1. Questions
      4. Case Study 4
        1. Questions
      5. Answers
        1. Case Study 1
        2. Case Study 2
        3. Case Study 3
        4. Case Study 4
    17. Index
    18. About the Authors
    19. Copyright