Course M20465 Designing Solutions for Microsoft SQL Server 2014

Module 1: Designing a Database Server Infrastructure
This module explains how to design an appropriate database server infrastructure for a given business application scenario - including how to decide between on-premise, cloud-based, and hybrid database servers, hardware capacity planning, considerations for storage hardware, and strategies for consolidating database server hardware.

Lessons

• Planning a Database Server Infrastructure
• Planning Server Hardware
• Considerations for Database Server Consolidation
• Managing Server Resources in a Consolidated Database Infrastructure

Lab : Planning Database Server Consolidation

• Planning for Consolidation
• Managing Resources for an Instance of SQL Server
• Managing Resources for Multiple SQL Server Instances on a Single Windows Server

Module 2: Designing a Logical Database Schema
This module explains how to design a logical schema for a database based on application requirements. This includes planning the level of normalization, and schema and table design, and the use of views.

Lessons

• Relational Database Design Techniques
• Planning Schemas and Tables

Lab : Designing a Logical Database Schema

• Plan a Database Schema
• Create a View to Display Employee Payment Information

Module 3: Designing a Physical Database Implementation
This modules explains how to design the physical implementation of a database for a given set of requirements. The design will include data files, log files, filegroups, and data partitioning, as well as whether or not to use data compression.

Lessons

• Files and Filegroups
• Partitioning Data
• Compressing Data

Lab : Designing a Physical Database Implementation

• Planning Files and Filegroups
• Implement the Timesheet Archive Strategy

Module 4: Incorporating Data Files into Databases
This modules discusses how to consider options for including data files in a database design.

Lessons

• Considerations for Working with Data Files in SQL Server 2012
• Implementing FileStream and FileTables
• Searching Data Files

Lab : Creating a Database Solution for BLOB Data

• Exercise 1: Creating a FileTable
• Exercise 2: Creating and using a Full-Text Index

Module 5: Tuning Database Performance
This module explains how to plan and manage indexes and how to use query plans to optimize database performance.

Lessons

• Optimizing Query Performance with Indexes
• Working with Query Plans
• Performance Monitoring

Lab : Planning for Database Performance

• Planning Indexes
• Testing Indexing Strategies
• Working with Execution Plans

Module 6: Designing Database Security
This module explains the key considerations for designing security for SQL Server instances and databases.

Lessons

• Introduction to Security Planning
• Planning Security
• Contained Databases
• Protecting Data with Encryption

Lab : Planning Database Security

• Planning Server and Database Security
• Implementing a Data Access Strategy
• Implementing Transparent Data Encryption

Module 7: Using Policy-Based Management
This module explains how to plan policy-based management to manage server instances, databases, and other SQL Server 2012 objects more efficiently.

Lessons

• Introduction to Policy-Based Management
• Planning and Implementing Policy-Based Management

Lab : Planning Policy-Based Management

• Exercise 1: Planning a Policy-Based Management Strategy
• Exercise 2: Implementing Policy-Based Management
• Exercise 3: Testing Policy Compliance

Module 8: Monitoring Server Health
This module explains how to plan SQL Server health monitoring and to implement health monitoring by using SQL Server Utility.

Lessons

• Introduction to ServerHealth Monitoring
• SQL Server Utility

Lab : Monitoring Server Health

• Exercise 1: Creating a Utility Control Point
• Exercise 2: Configuring Health Policies

Module 9: Designing a Database Backup Solution
This module explains how to identify and implement the appropriate backup strategy for a given scenario.

Lessons

• SQL Server Backup and Restore
• Planning a Recovery Strategy

Lab : Planning and Implementing a Backup Strategy

• Exercise 1: Planning a Backup and Restore Strategy
• Exercise 2: Implementing a Backup Strategy
• Performing a Piecemeal Restore

Module 10: Automating Multi-Server Maintenance
This module explains how to better plan and manage multi-server maintenance and automation.

Lessons

• Overview of Maintenance Automation
• Managing Multiple Servers

Lab : Automating Multi-Server Maintenance

• Exercise 1: Planning and Implementing a Multi-Server Environment
• Exercise 2: Planning Multi-Server Jobs

Module 11: Managing SQL Server with PowerShell
This module provides an overview of PowerShell and describes the benefits of using PowerShell to manage SQL Server 2012.

Lessons

• Introduction to PowerShell
• Scripting with Windows Powershell

Lab : Managing SQL Server with PowerShell

• Using PowerShell to Manage SQL Server
• Creating PowerShell Scripts

Module 12: Replicating Data
This module explains how to design an optimal replication strategy from a given set of business and technical requirements.

Lessons

• SQL Server Replication
• Planning Replication

Lab : Planning and Implementing SQL Server Replication

• Exercise 1: Planning a Replication Strategy
• Exercise 2: Implementing Replication

Module 13: Planning for High Availability
This module explains how to plan and implement a high availability solution.

Lessons

• High Availability in SQL Server 2012
• AlwaysOn Availability Groups

Lab : Implementing High Availability

• Creating an AlwaysOn Availability Group
• Using an AlwaysOn Availability Group
• Testing Failover for an AlwaysOn Availability Group