Training Kit Exam 70-462 · Administering Microsoft® SQL Sever® 2012 Databases by Thomas, Orin -- Read -- Imperial Library of Trantor

Index

Training Kit Exam 70-462: Administering Microsoft® SQL Sever® 2012 Databases A Note Regarding Supplemental Files Introduction

System Requirements

Hardware Requirements

Virtualization Hardware Requirements Physical Hardware Requirements

Software Requirements

Practice Setup Instructions

Prepare a Computer to Function as a Windows Server 2008 R2 Domain Controller Prepare AD DS Prepare a Member Server and Join It to the Domain Prepare a Second Member Server and Join It to the Domain Prepare a Third Member Server and Join It to the Domain Prepare a Fourth Member Server and Join It to the Domain Prepare a Computer Running the Server Core Installation Option and Join It to the Domain

Using the Companion CD

How to Install the Practice Tests How to Use the Practice Tests How to Uninstall the Practice Tests

Acknowledgments Errata & Book Support We Want to Hear from You Stay in Touch

Preparing for the Exam 1. Planning and Installing SQL Server 2012

Before You Begin Lesson 1: Planning Your Installation

Evaluating Installation Requirements

Operating System Requirements Processor and RAM Requirements Hard Disk Requirements Installing SQL Server 2012 on a Domain Controller Software Requirements Virtualization Requirements

Designing the Installation Planning Scale Up versus Scale Out Basics Shrinking and Growing Databases

Increasing Database Size Decreasing Database Size

Designing the Storage for New Databases Remembering Capacity Constraints Identifying a Standby Database for Reporting Identifying Windows-Level Security and Service-Level Security Performing a Core Mode Installation Benchmarking a Server

Practice: Prepare for the Installation of SQL Server 2012

Lesson Summary Lesson Review

Lesson 2: Installing SQL Server and Related Services

Configuring an Operating System Disk Installing the SQL Server Database Engine Installing SQL Server 2012 from the Command Prompt Installing SQL Server Integration Services Enabling and Disabling Features Installing SQL Server 2012 by Using a Configuration File Testing Connectivity

Practice: Prepare for and Install SQL Server 2012

Lesson Summary Lesson Review

Case Scenarios

Case Scenario 1: Planning Deployment of SQL Server 2012 Case Scenario 2: SQL Server Deployment

Suggested Practices

Configure Additional Firewall Rules and Generate a Features Discovery Report Adding and Removing Features and Adding Databases to SQL Server 2012 on a Computer Running a Server Core Operating System

Answers

Lesson 1 Lesson 2 Case Scenario 1 Case Scenario 2

2. Configuring and Managing SQL Server Instances

Before You Begin Lesson 1: Configuring SQL Server Instances

Instance-Level Settings

Configuring Memory Allocation Configuring Processor and I/O Affinity Configuring Fill Factor

Database Configuration and Standardization Distributed Transaction Coordinator Configuring Database Mail

Practice: Configure SQL Server 2012 Instance-Level Settings

Lesson Summary Lesson Review

Lesson 2: Managing SQL Server Instances

Installing Additional Instances Deploying Software Updates and Patch Management Configuring Resource Governor

Resource Pools Workload Groups Resource Governor Classification

Using WSRM with Multiple Database Engine Instances Cycle SQL Server Error Logs

Practice: Deploy and Configure Additional SQL Server 2012 Instances

Lesson Summary Lesson Review

Case Scenarios

Case Scenario 1: Instance Configuration Case Scenario 2: Additional Instances and Error Log Cycling

Suggested Practices

Configure Instances Install and Manage Multiple Instances

Answers

Lesson 1 Lesson 2 Case Scenario 1 Case Scenario 2

3. Configuring SQL Server 2012 Components

Before You Begin Lesson 1: Configuring Additional SQL Server Components

Deploying and Configuring Analysis Services Deploying and Configuring Reporting Services Deploying and Configuring SharePoint Integration Configuring SQL Server Integration Services Security Managing Full-Text Indexing Configuring FILESTREAM Configuring FileTables

Practice: Install Analysis Services and Reporting Services

Lesson Summary Lesson Review

Lesson 2: Managing and Configuring Databases

Designing and Managing Filegroups

Adding New Filegroups Moving an Index from One Filegroup to Another

Configuring and Standardizing Databases Understanding Contained Databases Using Data Compression

Row-Level Compression Unicode Compression Page-Level Compression Estimating Compression

Encrypting Databases with Transparent Data Encryption Partitioning Indexes and Tables Managing Log Files Using Database Console Commands

Maintenance Statements Informational Statements Validation Statements Miscellaneous Statements

Practice: Table Partitioning, Compression, Encryption, and Log Files

Lesson Summary Lesson Review

Case Scenarios

Case Scenario 1: Configuring FILESTREAM and FileTable Case Scenario 2: Deploying Transparent Data Encryption

Suggested Practices

FILESTREAM and FileTable Transparent Data Encryption and Table Partitioning

Answers

Lesson 1 Lesson 2 Case Scenario 1 Case Scenario 2

4. Migrating, Importing, and Exporting

Before You Begin Lesson 1: Migrating to SQL Server 2012

Upgrading an Instance to SQL Server 2012

Upgrading from SQL Server 2005 Upgrading from SQL Server 2008 Upgrading from SQL Server 2008 R2 Intra-Edition Upgrades Upgrade Advisor Preparing for Upgrade with Distributed Replay Utility Performing an Upgrade

Migrating a Database to a SQL Server 2012 Instance

Detach a Database Attach a Database

Copying Databases to Other Servers

Copy Database Wizard Copying Databases with Backup and Restore Publishing a Database by Using Generate and Publish Scripts Wizard

Migrating SQL Logins

Practice: Migrate and Copy Databases

Lesson Summary Lesson Review

Lesson 2: Exporting and Importing Data

Copying and Exporting Data Using the SQL Server Import and Export Wizard Using BCP to Import and Export Data Importing Data by Using BULK INSERT Importing Data by Using OPENROWSET(BULK) Using Format Files Preparing Data for Bulk Operations Select Into

Practice: Bulk Export and Import of Data

Lesson Summary Lesson Review

Case Scenarios

Case Scenario 1: Consolidation at Contoso Case Scenario 2: Tailspin Toys Bulk Data

Suggested Practices

Implement a Migration Strategy Import and Export Data

Answers

Lesson 1 Lesson 2 Case Scenario 1 Case Scenario 2

5. SQL Server Logins, Roles, and Users

Before You Begin Lesson 1: Managing Logins and Server Roles

SQL Logins

Windows-Authenticated SQL Server Logins SQL Server–Authenticated Logins Certificate Authentication Asymmetric Key Authentication Altering Existing Logins Login-Related Catalog Views Removing Logins Denying Server Access Server Roles

User-Defined Server Roles Credentials

Practice: SQL Server Logins and Server Roles

Lesson Summary Lesson Review

Lesson 2: Managing Users and Database Roles

Database Users Database Roles

Fixed Database-Level Roles Flexible Database-Level Roles msdb Roles User and Role Catalog Views

Contained Users Least Privilege Application Roles

Practice: Database Users and Roles

Lesson Summary Lesson Review

Case Scenarios

Case Scenario 1: Instance-Level Permissions for Contoso’s Accountants Case Scenario 2: Contained Databases at Fabrikam

Suggested Practices

Manage Logins and Server Roles Manage Users and Database Roles

Answers

Lesson 1 Lesson 2 Case Scenario 1 Case Scenario 2

6. Securing SQL Server 2012

Before You Begin Lesson 1: Managing Database Permissions

Understanding Securables Assigning Permissions on Objects Managing Permissions by Using Database Roles

Assigning Fixed Database Roles Using Flexible Database Roles

Protecting Objects from Modification Using Schemas Determining Effective Permissions

PRACTICE: Configuring Database Permissions

Lesson Summary Lesson Review

Lesson 2: Troubleshooting SQL Server Security

Troubleshooting Authentication

Using Authentication Modes Resolving Client Connection Problems

Troubleshooting Certificates and Keys Troubleshooting Endpoints Using Security Catalog Views

PRACTICE: Troubleshooting Security

Lesson Summary Lesson Review

Lesson 3: Auditing SQL Server Instances

Using SQL Server Audit

Creating a Server Audit SQL Server Audit Action Groups and Actions Creating a Server Audit Specification Creating a Database Audit Specification Viewing SQL Server Audit Views and Functions

Configuring Login Auditing Using c2 Audit Mode Common Criteria Compliance Policy-Based Management

PRACTICE: Configuring SQL Server Audit

Lesson Summary Lesson Review

Case Scenarios

Case Scenario 1: Configuring Database Permissions Case Scenario 2: Troubleshooting Security Case Scenario 3: Auditing at Fabrikam

Suggested Practices

Manage Database Permissions Troubleshoot Security Audit SQL Server Instances

Answers

Lesson 1 Lesson 2 Lesson 3 Case Scenario 1 Case Scenario 2 Case Scenario 3

7. Mirroring and Replication

Before You Begin Lesson 1: Mirroring Databases

Database Mirroring Mirroring Prerequisites

Full Recovery Model Recover by Using NO RECOVERY Endpoint Firewall Rules

Configuring Mirroring with Windows Authentication Configuring Mirroring with Certificate Authentication Changing Operating Modes Role Switching and Failover Monitoring Mirrored Databases

Database Mirroring Monitor Monitoring Mirroring by Using Stored Procedures

Upgrading Mirrored Databases

Practice Mirroring a Database

Lesson Summary Lesson Review

Lesson 2: Database Replication

Replication Architecture Replication Types Snapshot Replication

Configuring Snapshot Replication Configuring a Subscription

Transactional Replication Peer-to-Peer Transactional Replication Merge Replication Replication Monitor Controlling Replication of Constraints, Columns, and Triggers Heterogeneous Data

Practice Configuring Replication

Lesson Summary Lesson Review

Case Scenarios

Case Scenario 1: Database Mirroring at Coho Vineyard Case Scenario 2: Database Replication at Tailspin Toys

Suggested Practices

Implement Database Mirroring Implement Replication

Answers

Lesson 1 Lesson 2 Case Scenario 1 Case Scenario 2

8. Clustering and AlwaysOn

Before You Begin Lesson 1: Clustering SQL Server 2012

Fulfilling Edition Prerequisites

Windows Server 2008 R2 as Shared Storage Connecting to the SAN by Using iSCSI Initiator

Creating a Windows Server 2008 R2 Failover Cluster Installing a SQL Server Failover Cluster

Complete the Installation

Multi-Subnet Failover Clustering Performing Manual Failover Troubleshooting Failover Clusters

Practice Building a SQL Server 2012 Failover Cluster

Lesson Summary Lesson Review

Lesson 2: AlwaysOn Availability Groups

What Are AlwaysOn Availability Groups? Meeting Availability Group Prerequisites Configuring Availability Modes Selecting Failover Modes Configuring Readable Secondary Replicas Deploying AlwaysOn Availability Groups

Creating an AlwaysOn Endpoint Enabling AlwaysOn Availability Groups Creating an Availability Group Creating or Adding an Availability Group Listener Adding Secondary Replicas

Using Availability Groups on Failover Cluster Instances

Practice Deploying AlwaysOn Availability Groups

Lesson Summary Lesson Review

Case Scenarios

Case Scenario 1: Failover Cluster Instances at Contoso Case Scenario 2: AlwaysOn Availability Groups at Fabrikam

Suggested Practices

Implement a SQL Server Clustered Instance Implement AlwaysOn

Answers

Lesson 1 Lesson 2 Case Scenario 1 Case Scenario 2

9. Troubleshooting SQL Server 2012

Before You Begin Lesson 1: Working with Performance Monitor

Getting Started with Performance Monitor Capturing Performance Monitor Data Creating Data Collector Sets

Practice Using Performance Monitor

Lesson Summary Lesson Review

Lesson 2: Working with SQL Server Profiler

Capturing Activity with SQL Server Profiler

Tracing Output Options Trace Events Trace Columns Filtering Traces Trace Templates

Understanding SQL Trace Reviewing Trace Output Capturing Activity with Extended Events Profiler

Practice Creating Traces

Lesson Summary Lesson Review

Lesson 3: Monitoring SQL Server

Monitoring Activity

Dynamic Management Views and Functions Querying Dynamic Management Views

Working with Activity Monitor

Practice Using Dynamic Management Objects

Lesson Summary Lesson Review

Lesson 4: Using the Data Collector Tool

Capturing and Managing Performance Data

Using the Data Collector Defining Data Collection Sets Designing a Data Collector Topology Configuring the Data Collector Assigning Roles to Data Collector Security Monitoring the Data Collector

Analyzing Collected Performance Data

Server Activity Report Disk Usage Report Query Statistics Report

Practice Configuring the Management Data Warehouse

Lesson Summary Lesson Review

Lesson 5: Identifying Bottlenecks

Monitoring Disk Usage

Monitoring Disk I/O and Detecting Excess Paging Isolating Disk Activity Created by SQL Server

Monitoring Memory Usage

Isolating Memory Used by SQL Server

Monitoring CPU Usage

Practice Configuring Memory Options by Using SQL Server Management Studio

Lesson Summary Lesson Review

Case Scenarios

Case Scenario 1: Identifying Poor Query Performance Case Scenario 2: Deploying Auditing

Suggested Practices

Create a Trace by Using SQL Server Profiler Capture a Performance Baseline by Using Performance Monitor

Answers

Lesson 1 Lesson 2 Lesson 3 Lesson 4 Lesson 5 Case Scenario 1 Case Scenario 2

10. Indexes and Concurrency

Before You Begin Lesson 1: Implementing and Maintaining Indexes

Understanding the Anatomy of a Balanced Tree (B-Tree) Understanding Index Types and Structures

Clustered Indexes Non-Clustered Indexes Covering Indexes Filtered Indexes Primary XML and Secondary XML Indexes Spatial Indexes Full-Text Indexes Columnstore Indexes

Designing Indexes for Efficient Retrieval

Clustered Indexes Non-Clustered Indexes Covering Indexes Filtered Indexes Primary XML and Secondary XML Indexes Full-Text Indexes and Semantic Searches Columnstore Indexes

Understanding Statistics

Using Index Internals Moving Row-Overflow Data

Creating and Modifying Indexes

Choosing Fill Factors Determining Fragmentation Removing Index Fragmentation Updating Statistics

Tracking Missing Indexes Reviewing Unused Indexes

Practice Maintaining Indexes and Statistics

Lesson Summary Lesson Review

Lesson 2: Identifying and Resolving Concurrency Problems

Defining Transactions and Transaction Scope Understanding SQL Server Lock Management

Lock Modes Explaining Lock Compatibility Understanding Dynamic Lock Management Understanding Transaction Isolation Levels Choosing the Read Committed Snapshot Isolation Database Option Lock Duration Based on Transaction Isolation Levels Monitoring Locks Identifying Blocking Avoiding Blocking

Using AlwaysOn Replicas to Improve Concurrency Detecting and Correcting Deadlocks

Capturing Deadlock Information

Using Activity Monitor Diagnosing Bottlenecks Using Reports for Performance Analysis

Using Standard Reports Using Custom Reports Executing the KILL Process

Practice Examining Dynamic Management Views

Lesson Summary Lesson Review

Case Scenarios

Case Scenario 1: Fabrikam Year-Query Performance Case Scenario 2: Analyzing Concurrency at Tailspin Toys

Suggested Practices

Implement and Maintain Indexes and Statistics Identify and Resolve Concurrency Problems

Answers

Lesson 1 Lesson 2 Case Scenario 1 Case Scenario 2

11. SQL Server Agent, Backup, and Restore

Before You Begin Lesson 1: Managing SQL Server Agent

Executing Jobs by Using SQL Server Agent

Configuring SQL Server Agent Account Setting SQL Server Agent Security Configuring SQL Server Agent Mail Profile Setting Up the SQL Server Agent Error Log

Managing Alerts Managing Jobs

Creating a Job Monitoring Jobs Managing Operators

Monitoring Multi-Server Environments

Practice Configuring SQL Server Agent, Jobs, Operators, and Alerts

Lesson Summary Lesson Review

Lesson 2: Configuring and Maintaining a Backup Strategy

Understanding Backup Types

Using Backup Compression Understanding Recovery Models

Backing Up System Databases Backing Up Replicated Databases Backing Up Mirrored Databases Backing Up AlwaysOn Replicas Using Database Checkpoints Using Backup Devices Backing Up Media Sets Performing Backups

Full and Differential Backups Performing Transaction Log Backups

Viewing Backup History

Practice Backing Up the AdventureWorks2012 Database

Lesson Summary Lesson Review

Lesson 3: Restoring SQL Server Databases

Restoring Databases Performing File Restores Performing Page Restores Restoring a Database Protected with Transparent Data Encryption Restoring System Databases Restoring Replicated Databases Checking Database Status

Practice Restoring the AdventureWorks2012 Database

Lesson Summary Lesson Review

Case Scenarios

Case Scenario 1: SQL Server Agent at Contoso Case Scenario 2: Fabrikam Backup Strategy Case Scenario 3: Recovery at Adatum

Suggested Practices

Manage SQL Server Agent Configure and Maintain a Backup Strategy Restore Databases

Answers

Lesson 1 Lesson 2 Lesson 3 Case Scenario 1 Case Scenario 2 Case Scenario 3

12. Code Case Studies

Case Study 1

Questions

Case Study 2

Questions

Case Study 3

Questions

Case Study 4

Questions

Answers

Case Study 1 Case Study 2 Case Study 3 Case Study 4

Index About the Authors Copyright

← Prev
Back
Next →

← Prev
Back
Next →