MOC 20462C: Administering Microsoft SQL Server Databases
Course Overview
This course provides students with the knowledge and skills to administer Microsoft SQL
Server databases.
Course Introduction 2m Course Introduction
Module 01 - Introduction to SQL Server 2014 Database Administration 52m Lesson 1: Database Administration Overview
What Makes a Good Database Administrator? Common Database Administration Tasks Documenting Database Management Procedures Lesson 2: Introduction to the SQL Server Platform SQL Server Components
SQL Server Instances SQL Server Editions SQL Server Versions
Lesson 3: Database Management Tools and Techniques SQL Server Tools
SQL Server Management Studio The sqlcmd Utility
Using Transact-SQL to Perform Management Tasks Using Windows PowerShell to Manage SQL Server Module 01 Review
Module 02 - Installing and Configuring SQL Server 2014 49m
Lesson 1: Planning SQL Server Installation Considerations for SQL Server Installation Minimum Hardware and Software Requirements Assessing CPU and Memory Requirements Storage I/O Considerations
Planning Service Accounts
Lesson 2: Installing SQL Server 2014 Options for Installing SQL Server 2014 Performing an Installation of SQL Server 2014 Performing an Unattended Installation Upgrading SQL Server
Lesson 3: Post-Installation Configuration Performing Post-Installation Checks
Configuring Services and Network Protocols Managing SQL Server Updates
Module 03 - Working with Databases and Storage 1h 25m Lesson 1: Introduction to Data Storage with SQL Server
How Data Is Stored in SQL Server Considerations for Disk Storage Devices RAID Levels
Determining File Placement and Number of Files Ensuring Sufficient File Capacity
Lesson 2: Managing Storage for System Databases SQL Server System Databases
Moving System Databases Considerations for tempdb Demo - Moving tempdb Files
Lesson 3: Managing Storage for User Databases Creating User Databases
Configuring Database Options Demo - Creating Databases Altering User Databases Managing Database Files Introduction to Filegroups
Creating and Managing Filegroups Lesson 4: Moving Database Files Moving User Database Files Detaching and Attaching Databases
Demo - Detaching and Attaching a Database Lesson 5: Configuring the Buffer Pool Extension Introduction to the Buffer Pool Extension
Considerations for Using the Buffer Pool Extension Configuring the Buffer Pool Extension
Demo - Configuring the Buffer Pool Extension Module 03 Review
Module 04 - Planning and Implementing a Backup Strategy 1h 42m
Lesson 1: Understanding SQL Server Recovery Models Overview of SQL Server Transaction Logs
Transaction Log File Structure Working with Recovery Models Capacity Planning for Transaction Logs Working with Checkpoint Options Demo - Logs and Full Recovery Lesson 2: Planning a Backup Strategy Backup Types
Determining Recovery Objectives Full Database Backup Strategies Transaction Log Backup Strategies Differential Backup Strategies Partial Backup Strategies
Discussion: Planning Backup Strategies
Lesson 3: Backing up Databases and Transaction Logs Introduction to SQL Server Backup
Performing Database Backups Performing Transaction Log Backups Performing Partial and Filegroup Backups Demo - Performing Backups
Lesson 4: Using Backup Options Copy-Only Backups
Compressing Backups
Demo - Using Backup Compression Encrypting Backups
Demo - Using Backup Encryption Lesson 5: Ensuring Backup Reliability
Determining a Retention and Testing Policy for Backups Options for Ensuring Backup Integrity
Viewing Backup History Retrieving Backup Metadata Demo - Verifying Backup Module 04 Review
Module 05 - Restoring SQL Server 2014 Databases 1h 13m Lesson 1: Understanding the Restore Process
Phases of the Restore Process Types of Restores
Preparations for Restoring Backups
Discussion: Determining Required Backups to Restore Lesson 2: Restoring Databases
Restoring a Full Database Backup Restoring a Differential Backup Restoring Transaction Log Backups Demo - Restoring Databases
Lesson 3: Advanced Restore Scenarios Restoring File or Filegroup Backups Restoring an Encrypted Backup Demo - Restoring an Encrypted Backup Restoring Data Pages
Recovering System Databases Lesson 4: Point-in-Time Recovery Overview of Point-in-Time Recovery STOPAT Option
STOPATMARK Option
Performing a Point-in-Time Recovery by Using SQL Server Management Studio Demo - Performing a Point-in-Time Recovery
Module 05 Review
Module 06 - Importing and Exporting Data 1h 10m
Lesson 1: Introduction to Transferring Data Overview of Data Transfer
Available Tools for Data Transfer
Improving the Performance of Data Transfers Disabling and Rebuilding Indexes
Lesson 2: Importing and Exporting Data The SQL Server Import and Export Wizard Demo - Using the Import and Export Wizard The bcp Utility
Demo - Using the bcp Utility The BULK INSERT Statement
Demo - Using the BULK INSERT Statement The OPENROWSET Function
Demo - Using the OPENROWSET Function Lesson 3: Copying or Moving a Database Options for Copying or Moving Databases The Copy Database Wizard
Demo - Using the Copy Database Wizard Data-Tier Applications
Demo - Exporting and Importing a Data-tier Application Module 06 Review
Module 07 - Monitoring SQL Server 2014 53m
Lesson 1: Introduction to Monitoring SQL Server SQL Server Monitoring Overview
Monitoring Tools for SQL Server Activity Monitor
Demo - Using Activity Monitor
Lesson 2: Dynamic Management Views and Functions Overview of Dynamic Management Views and Functions Viewing Activity by Using Dynamic Management Views Demo - Querying Dynamic Management Views
Lesson 3: Performance Monitor Introduction to Performance Monitor SQL Server Counters
Data Collector Sets and Logs Demo - Using Performance Monitor Module 07 Review
Module 08 - Tracing SQL Server Activity 1h 9m
Lesson 1: Tracing SQL Server Workload Activity SQL Server Profiler
Trace Events
Trace Columns and Filters Trace Templates
Viewing Trace Files
Demo - Using SQL Server Profiler SQL Trace
Demo - Using SQL Trace Lesson 2: Using Traces Replaying Traces
The Database Engine Tuning Advisor
Demo - Using the Database Engine Tuning Advisor Combining Traces with Performance Monitor Logs Demo - Correlating a Trace with Performance Data
Troubleshooting Concurrency Issues Demo - Troubleshooting Deadlocks Module 08 Review
Module 09 - Managing SQL Server Security 1h 21m
Lesson 1: Introduction to SQL Server Security Security Concepts
SQL Server Securables SQL Server Principals SQL Server Permissions
Lesson 2: Managing Server-Level Security Application Security Models
SQL Server Authentication Options Managing Logins
Managing Server-Level Roles Managing Server-Level Permissions Demo - Managing Server-Level Security Lesson 3: Managing Database-Level Principals Managing Database Users
Managing dbo and guest Access Managing Database-Level Roles
Demo - Managing Database Users and Roles Managing Application Roles
Demo - Using an Application Role Managing Users for a Contained Database Demo - Using a Contained Database Lesson 4: Managing Database Permissions Database-Level Permissions
Schemas
Table and View Permissions Executable Code Permissions Ownership Chains
Demo - Managing Permissions Module 09 Review
Module 10 - Auditing Data Access and Encrypting Data 1h Lesson 1: Auditing Data Access in SQL Server
Discussion: Auditing Data Access Common Criteria Auditing SQL Trace
DML Triggers
Demo - Using DML Triggers for Auditing SQL Server Audit
Lesson 2: Implementing SQL Server Audit SQL Server Audit Overview
Creating an Audit
Creating a Server Audit Specification Creating Database Audit Specifications User-Defined Audit Actions
Managing SQL Server Audit Demo - Using SQL Server Audit Lesson 3: Encrypting Databases Transparent Data Encryption Overview Configuring Transparent Data Encryption Moving Encrypted Databases
Demo - Implementing Transparent Data Encryption Extensible Key Management
Module 10 Review
Module 11 - Performing Ongoing Database Maintenance 54m Lesson 1: Ensuring Database Integrity
Introduction to Database Integrity Overview of DBCC CHECKDB DBCC CHECKDB Options DBCC CHECKDB Repair Options Demo - Using DBCC CHECKDB Lesson 2: Maintaining Indexes How Indexes Affect Performance Types of SQL Server Indexes Index Fragmentation
FILLFACTOR and PAD_INDEX Ongoing Maintenance of Indexes Online Index Operations Updating Statistics
Demo - Maintaining Indexes
Lesson 3: Automating Routine Database Maintenance Overview of SQL Server Maintenance Plans
Monitoring Maintenance Plans Demo - Creating a Maintenance Plan Module 11 Review
Module 12 - Automating SQL Server 2014 Management 59m
Lesson 1: Automating SQL Server Management Benefits of Automating SQL Server Management The SQL Server Agent Service
SQL Server Agent Objects
Lesson 2: Implementing SQL Server Agent Jobs Defining Jobs, Job Step Types, and Job Categories Creating Job Steps
Scheduling Jobs for Execution Demo - Creating Jobs
Lesson 3: Managing SQL Server Agent Jobs Viewing Job History
Querying SQL Server Agent-related System Tables and Views Troubleshooting Failed Jobs
Demo - Viewing Job History
Lesson 4: Managing Job Step Security Contexts Job Step Security Contexts
Proxy Accounts
Demo - Configuring Security Context Lesson 5: Managing Jobs on Multiple Servers Multiserver Administration Concepts
Considerations for Multiserver Administration Configuring Master and Target Servers Running Jobs on Target Servers Module 12 Review
Module 13 - Monitoring SQL Server 2014 with Notifications and Alerts 49m
Lesson 1: Monitoring SQL Server Errors What Is in an Error?
Error Severity Levels
Configuring the SQL Server Error Log Demo - Viewing the SQL Server Error Log Lesson 2: Configuring Database Mail Overview of Database Mail
Database Mail Profiles Database Mail Security
Database Mail Logs and Retention Demo - Configuring Database Mail
Lesson 3: Configuring Operators, Notifications, and Alerts Operator and Notifications
Demo - Configuring SQL Server Agent Operators Overview of SQL Server Alerts
Creating Alerts
Configuring Alert Actions
Troubleshooting Alerts and Notifications Demo - Configuring SQL Server Agent Alerts Module 13 Review
Course Closure
