MCSE: Data Platform

This training combines and covers materials from the following courses:

Course 20461: Querying Microsoft SQL Server 2012

Course 20462: Administering Microsoft SQL Server 2012 Databases

Course 20463: Implementing a Data Warehouse with Microsoft SQL Server 2012

Course 20464: Developing Microsoft SQL Server Databases

Course 20465: Designing Solutions for SQL Server

Duration: 200 hours

Course 20461: Querying Microsoft SQL Server 2012

Overview

This course provides students with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2014. This course is the foundation for all SQL Server-related disciplines; namely, Database Administration, Database Development and Business Intelligence.

Duration: 40 hours

Course Outline

Module 1: Sorting and Filtering Data

• Sorting Data
• Filtering Data with Predicates
• Filtering with the TOP and OFFSET-FETCH
• Working with Unknown Values

Module 2: Working with SQL Server 2014 Data Types

• Introducing SQL Server 2014 Data Types
• Working with Character Data
• Working with Date and Time Data

Module 3: Introduction to T-SQL Querying

• Introducing T-SQL
• Understanding Sets
• Understanding Predicate Logic
• Understanding the Logical Order of Operations in SELECT statements

Module 4: Writing SELECT Queries

• Writing Simple SELECT Statements
• Eliminate Duplicates with DISTINCT
• Using Column and Table Aliases
• Write Simple CASE Expressions

Module 5: Querying Multiple Tables

• Understanding Joins
• Querying with Inner Joins
• Querying with Outer Joins
• Querying with Cross Joins and Self Joins

Module 6: Using Built-In Functions

• Writing Queries with Built-In Functions
• Using Conversion Functions
• Using Logical Functions
• Using Functions to Work with NULL

Module 7: Using Subqueries

• Writing Self-Contained Subqueries
• Writing Correlated Subqueries
• Using the EXISTS Predicate with Subqueries

Module 8: Using Set Operators

• Writing Queries with the UNION Operator
• Using EXCEPT and INTERSECT
• Using APPLY

Module 9: Querying data with Stored Procedures

• Writing Queries with PIVOT and UNPIVOT
• Passing Parameters to Stored Procedures
• Creating Simple Stored Procedures
• Working with Dynamic SQL

Module 10: Programming with T-SQL

• T-SQL Programming Elements
• Controlling Program Flow

Module 11: Appendix 2: Querying SQL Server Metadata

• Querying System Catalog Views and Functions
• Executing System Stored Procedures
• Querying Dynamic Management Objects

Module 12: Using DML to Modify Data

• Inserting Data
• Modifying and Deleting Data

Module 13: Grouping and Aggregating Data

• Using Aggregate Functions
• Using the GROUP BY Clause
• Filtering Groups with HAVING

Module 14: Introduction to Microsoft SQL Server 2014

• The Basic Architecture of SQL Server
• SQL Server Editions and Versions
• Getting Started with SQL Server Management Studio

Module 15: Using Table Expressions

• Using Views
• Using Inline Table-Valued Functions
• Using Derived Tables
• Using Common Table Expressions

Module 16: Using Window Ranking, Offset, and Aggregate Functions

• Creating Windows with OVER
• Exploring Window Functions

Module 17: Pivoting and Grouping Sets

• Writing Queries with PIVOT and UNPIVOT
• Working with Grouping Sets

Module 18: Implementing Error Handling

• Using TRY / CATCH Blocks
• Working with Error Information

Module 19: Implementing Transactions

• Transactions and the Database Engine
• Controlling Transactions

Module 20: Appendix 1: Improving Query Performance

• Factors in Query Performance
• Displaying Query Performance Data

Course 20462: Administering Microsoft SQL Server 2012 Databases

Overview

This course provides students with the knowledge and skills to maintain a Microsoft SQL Server 2014 database. The course focuses on teaching individuals how to use SQL Server 2014 product features and tools related to maintaining a database.

Duration: 40 hours

Course Outline

Module 1: Importing and Exporting Data

• Introduction to Transferring Data
• Importing and Exporting Table Data
• Copying or Moving a Database

Module 2: Monitoring SQL Server 2014

• Introduction to Monitoring SQL Server
• Dynamic Management Views and Functions
• Performance Monitor

Module 3: Tracing SQL Server Activity

• Tracing SQL Server Workload Activity
• Using Traces

Module 4: Introduction to SQL Server 2014 Database Administration

• Database Administration Overview
• Introduction to the SQL Server Platform
• Database Management Tools and Techniques

Module 5: Installing and Configuring SQL Server 2014

• Planning SQL Server Installation
• Installing SQL Server 2014
• Post-Installation Configuration

Module 6: Working with Databases and Storage

• Introduction to Data Storage with SQL Server
• Managing Storage for System Databases
• Managing Storage for User Databases
• Moving Database Files
• Configuring the Buffer Pool Extension

Module 7: Planning and Implementing a Backup Strategy

• Understanding SQL Server Recovery Models
• Planning a Backup Strategy
• Backing up Databases and Transaction Logs
• Using Backup Options
• Ensuring Backup Reliability

Module 8: Restoring SQL Server 2014 Databases

• Understanding the Restore Process
• Restoring Databases
• Advanced Restore Scenarios
• Working with Point-in-Time Recovery

Module 9: Managing SQL Server Security

• Introduction to SQL Server Security
• Managing Server-Level Security
• Managing Database-Level Principals
• Managing Database Permissions

Module 10: Auditing Data Access and Encrypting Data

• Auditing Data Access in SQL Server
• Implementing SQL Server Audit
• Encrypting Databases

Module 11: Performing Ongoing Database Maintenance

• Ensuring Database Integrity
• Maintaining Indexes
• Automating Routine Database Maintenance

Module 12: Automating SQL Server 2014 Management

• Automating SQL Server Management
• Implementing SQL Server Agent Jobs
• Managing SQL Server Agent Jobs
• Managing Job Step Security Contexts
• Managing Jobs on Multiple Servers

Module 13: Monitoring SQL Server 2014 by Using Alerts and Notifications

• Monitoring SQL Server Errors
• Configuring Database Mail
• Configuring Operators, Alerts, and Notifications

Course 20463: Implementing a Data Warehouse with Microsoft SQL Server 2012

 

Overview

This course describes how to implement a data warehouse platform to support a BI solution. Students will learn how to create a data warehouse with Microsoft® SQL Server® 2014, implement ETL with SQL Server Integration Services, and validate and cleanse data with SQL Server Data Quality Services and SQL Server Master Data Services.

Duration: 40 hours

 

Course Outline

Module 1: Designing and Implementing a Data Warehouse

• Logical Design for a Data Warehouse
• Physical design for a data warehouse

Module 2: Creating an ETL Solution with SSIS

• Introduction to ETL with SSIS
• Exploring Data Sources
• Implementing Data Flow

Module 3: Implementing Control Flow in an SSIS Package

• Introduction to Control Flow
• Creating Dynamic Packages
• Using Containers
• Managing Consistency

Module 4: Debugging and Troubleshooting SSIS Packages

• Debugging an SSIS Package
• Logging SSIS Package Events
• Handling Errors in an SSIS Package

Module 5: Implementing an Incremental ETL Process

• Introduction to Incremental ETL
• Extracting Modified Data
• Loading Modified data

Module 6: Using Master Data Services

• Master Data Services Concepts
• Implementing a Master Data Services Model
• Managing Master Data
• Creating a Master Data Hub

Module 7: Extending SQL Server Integration Services

• Using Scripts in SSIS
• Using Custom Components in SSIS

Module 8: Deploying and Configuring SSIS Packages

• Overview of SSIS Deployment
• Deploying SSIS Projects
• Planning SSIS Package Execution

Module 9: Enforcing Data Quality

• Introduction to Data Quality
• Using Data Quality Services to Cleanse Data
• Using Data Quality Services to Match data

Module 10: Introduction to Data Warehousing

• Overview of Data Warehousing
• Considerations for a Data Warehouse Solution

Module 11: Data Warehouse Hardware Considerations

• Considerations for building a Data Warehouse
• Data Warehouse Reference Architectures and Appliances

Module 12: Consuming Data in a Data Warehouse

• Introduction to Business Intelligence
• Introduction to Reporting
• An Introduction to Data Analysis

Course 20464: Developing Microsoft SQL Server Databases

 

Overview

This course focusses on the creation of database objects including views, stored procedures, along with parameters, and functions. Other common aspects of procedure coding, such as indexes, concurrency, error handling, and triggers are also covered in this course.

Duration: 40 hours

 

Course Outline

Module 1: In-Memory Database Capabilities

• The Buffer Pool Extension
• Columnstore Indexes

Module 2: Designing and Implementing Views

• Introduction to Views
• Creating and Managing Views
• Performance Considerations for Views

Module 3: Designing and Implementing Stored Procedures

• Introduction to Stored Procedures
• Working With Stored Procedures
• Implementing Parameterized Stored Procedures
• Controlling Execution Context

Module 4: Introduction to Database Development

• Introduction to the SQL Server Platform
• Working with SQL Server Tools
• Configuring SQL Server Services

Module 5: Designing and Implementing Tables

• Designing Tables
• Working with Schemas
• Creating and Altering Tables

Module 6: Ensuring Data Integrity through Constraints

• Enforcing Data Integrity
• Implementing Domain Integrity
• Implementing Entity and Referential Integrity

Module 7: Introduction to Indexing

• Core Indexing Concepts
• Single Column and Composite Indexes
• SQL Server Table Structures
• Working with Clustered Indexes

Module 8: Advanced Indexing

• Execution Plan Core Concepts
• Common Execution Plan Elements
• Working with Execution Plans
• Designing Effective Nonclustered Indexes
• Performance Monitoring

Module 9: Designing and Implementing User-Defined Functions

• Overview of Functions
• Designing and Implementing Scalar Functions
• Designing and Implementing Table-Valued Functions
• Implementation Considerations for Functions
• Alternatives to Functions

Module 10: Responding to Data Manipulation via Triggers

• Designing DML Triggers
• Implementing DML Triggers
• Advanced Trigger Concepts

Module 11: Using In-Memory Tables

• Memory-Optimized Tables
• Native Stored Procedures

Module 12: Implementing Managed Code in SQL Server

• Introduction to SQL CLR Integration
• Importing and Configuring Assemblies
• Implementing SQL CLR Integration

Module 13: Storing and Querying XML Data in SQL Server

• Introduction to XML and XML Schemas
• Storing XML Data and Schemas in SQL Server
• Implementing the XML Data Type
• Using the T-SQL FOR XML Statement
• Getting Started with XQuery
• Shredding XML

Module 14: Working with SQL Server Spatial Data

• Introduction to Spatial Data
• Working with SQL Server Spatial Data Types
• Using Spatial Data in Applications

Course 20465: Designing Solutions for SQL Server

Overview

This course focuses on planning and implementing enterprise database infrastructure solutions by using SQL Server 2014 and other Microsoft technologies. It describes how to consolidate SQL Server workloads, work with both on-premises and Microsoft Azure cloud-based solutions, and how to plan and implement high availability and disaster recovery solutions.

Duration: 40 hours

 

Course Outline

Module 1: Introduction to Cloud Data Solutions

• Overview of Cloud Computing
• SQL Server in a Private Cloud

Module 2: Introduction to Microsoft Azure

• Microsoft Azure Overview
• Microsoft Azure Storage

Module 3: Microsoft Azure SQL Database

• Introduction to Microsoft Azure SQL Database
• Microsoft Azure SQL Database Security
• Implementing and Managing Databases

Module 4: SQL Server in Microsoft Azure Virtual Machines

• Introduction to Microsoft Azure Virtual Machines
• Microsoft Azure Virtual Machine Connectivity and Security
• Creating Databases in a Microsoft Azure Virtual Machine

Module 5: Introduction to Enterprise Data Architecture

• Considerations for Enterprise Data
• Assessing an Existing Infrastructure

Module 6: Multi-Server Configuration Management

• Policy-Based Management
• Microsoft System Center

Module 7: Monitoring SQL Server 2014 Health

• Introduction to Health Monitoring
• Data Collector
• SQL Server Utility

Module 8: Consolidating Database Workloads with SQL Server 2014

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

Module 9: Introduction to High Availability in SQL Server 2014

• High Availability Concepts and Options in SQL Server 2014
• Log Shipping

Module 11: Planning High Availability and Disaster Recovery

• High Availability and Disaster Recovery with SQL Server 2014
• High Availability and Disaster Recovery for Databases in Microsoft Azure

Module 12: Clustering with Windows Server and SQL Server 2014

• Introduction to Windows Server Failover Clustering
• SQL Server Always On Failover Cluster Instances

Module 13: Replicating Data

• SQL Server Replication

Planning Replication

Certification & exams

Earning an MCSE: Data Platform certification will qualify you for such jobs as database analyst and database designer.

This training maps to the following five certification exams

Exam 461: Querying Microsoft SQL Server 2012

Exam 462: Administering Microsoft SQL Server 2012 Databases

Exam 463: Implementing a Data Warehouse with Microsoft SQL Server 2012

Exam 464: Developing Microsoft SQL Server Databases

Exam 465: Designing Solutions for SQL Server