MCSE: Data Platform Solutions Expert

Demonstrate your broad skill sets in building and administrating enterprise-scale data solutions both on-premises and in cloud environments.

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

Required Courses and Exams

  • Querying Microsoft SQL Server 2012 Course 10774 Exam 461
  • Administering Microsoft SQL Server 2012 Databases Course 10775 Exam 462
  • Implementing a Data Warehouse with Microsoft SQL Server 2012 Course 10777 Exam 463

After successfully completing the above exams, you’ll earn a Microsoft Certified Solutions Associate (MCSA): SQL Server 2012 certification.

  • Developing Microsoft SQL Server 2012 Databases Course 10776 Exam 464
  • Designing Database Solutions for SQL Server 2012 Course 20465 Exam 465

Course 10776A: Developing Microsoft SQL Server 2012 Databases

Course Outline

Module 1: Introduction to SQL Server 2012 and its Toolset

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

Module 2: Working with Data Types

Using Data Types
Working with Character Data
Converting Data Types
Specialized Data Types

Module 3: Designing and Implementing Tables

Designing Tables
Working with Schemas
Creating and Altering Tables

Module 4: Ensuring Data Integrity through Constraints

Enforcing Data Integrity
Implementing Domain Integrity
Implementing Entity and Referential Integrity

Module 5: Planning for SQL Server 2012 Indexing

Core Indexing Concepts
Data Types and Indexes
Single Column and Composite Indexes

Module 6: Implementing Table Structures in SQL Server 2012

SQL Server Table Structures
Working with Clustered Indexes
Designing Effective Clustered Indexes

Module 7: Reading SQL Server 2012 Execution Plans

Execution Plan Core Concepts
Common Execution Plan Elements
Working with Execution Plans

Module 8: Improving Performance through Nonclustered Indexes

Designing Effective Nonclustered Indexes
Implementing Nonclustered Indexes
Using the Database Engine Tuning Advisor

Module 9: Designing and Implementing Views

Introduction to Views
Creating and Managing Views
Performance Considerations for Views

Module 10: Designing and Implementing Stored Procedures

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

Module 11: Merging Data and Passing Tables

Using the MERGE Statement
Implementing Table Types
Using TABLE Types As Parameters

Module 12: 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 13: Creating Highly Concurrent SQL Server 2012 Applications

Introduction to Transactions
Introduction to Locks
Management of Locking
Transaction Isolation Levels

Module 14: Handling Errors in T-SQL Code

Understanding T-SQL Error Handling
Implementing T-SQL Error Handling
Implementing Structured Exception Handling

Module 15: Responding to Data Manipulation via Triggers

Designing DML Triggers
Implementing DML Triggers
Advanced Trigger Concepts

Module 16: Implementing Managed Code in SQL Server 2012

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

Module 17: Storing XML Data in SQL Server 2012

to XML and XML Schemas
Storing XML Data and Schemas in SQL Server
Implementing the XML Data Type

Module 18: Querying XML Data in SQL Server

Using the T-SQL FOR XML Statement
Getting Started with XQuery
Shredding XML

Module 19: Working with SQL Server 2012 Spatial Data

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

Module 20: Working with Full-Text Indexes and Queries

Introduction to Full-Text Indexing
Implementing Full-Text Indexes in SQL Server
Working with Full-Text Queries

Course 20465B: Designing Database Solutions for SQL Server 2012

Course Outline

Module 1: Designing a Database Server Infrastructure

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

Module 2: Designing a Logical Database Schema

Relational Database Design Techniques
Planning Schemas and Tables

Module 3: Designing a Physical Database Implementation

Planning Files and Filegroups
Planning a Partitioning Data
Planning Compression

Module 4: Incorporating Data Files into Databases

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

Module 5: Tuning Database Performance

Optimizing Query Performance by Using Indexes
Working with Query Plans
Performance Monitoring

Module 6: Designing Database Security

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

Module 7: Policy Based Management

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

Module 8: Monitoring Server Health

Introduction to Server Health Monitoring
SQL Server Utility

Module 9: Designing a Database Backup Solution

SQL Server Backup and Restore
Planning a Recovery Strategy

Module 10: Automating Multi-Server Maintenance

Overview of Maintenance Automation
Managing Multiple Servers

Module 11: Managing SQL Server with PowerShell

Introduction to Windows PowerShell
Scripting with Windows PowerShell

Module 12: Replicating Data

SQL Server Replication
Planning Replication

Module 13: Planning High Availability

High Availability in SQL Server 2012
AlwaysOn Availability Groups