MCSE | Data Platform

110 Hours / 12 Months / Self-Paced

Course Overview:

Demonstrate your broad skill sets in building and administrating enterprise-scale data solutions both on-premises and in cloud environments with the MCSE Data Platform certification from Microsoft.  This course prepares students to take the Microsoft 70-461: Querying SQL Server 2012, 70-462: Administering SQL Server 2012 Databases, 70-463: Implementing a Data Warehouse with SQL Server 2012 Server, 70-464: Developing Microsoft SQL Server 2012, 70-465: Designing Database Solutions for Microsoft SQL Server certification exams.

This course includes FREE access for 12 months to a cloud based lab platform to assist students develop the practical information technology (IT) skills necessary to succeed in high in demand IT jobs.  This cloud based lab solution uses real equipment that enables our students to execute each practical task in a safe environment that is accessible from anywhere without needing to buy their own hardware or risk damage to their own system.

Along with providing the necessary hardware in a virtual environment, students gain access to high quality practical exercises that cover many of the exam topics they will encounter on their certifying exams.

The Microsoft 70-461: Querying SQL Server 2012 course is intended for SQL Server database administrators, implementers, system engineers, and developers. This new SQL 2012 course is focused on anyone interested in the validation of your skills and knowledge with writing queries.

 This course provides the knowledge and understanding for some of these important job functions:

  • Apply built-in scalar functions
  • Apply ranking functions
  • Combine datasets
  • Creates and alters
  • Design T-SQL stored procedures, views
  • Implementing aggregate queries, error handling, sequence generation and data types
  • Manage transactions
  • Modify data by using INSERT, UPDATE, DELETE and MERGE statements
  • Optimizing queries
  • Query data using SELECT statements and Managing XML data
  • OUTPUT clause to return data

This Microsoft 70-462: Administering SQL Server 2012 Databases course was created specifically for those individuals (Database Professionals) who install, maintain and configure as their primary job function. Any participants setting up database systems and/or responsible for efficient system operation, regular database backups stored effectively, and securing data from unauthorized access is also a candidate for this training course on SQL 2012.

 Below is a partial list of job functions associated to this training course:

  • Audit SQL Server instances
  • Back up databases
  • Configure additional SQL Server components, Instances and Services
  • Deploying a SQL Server
  • Implementing strategies, column stores and High-Availability and Disaster Recovery solutions
  • Importing/exporting
  • Install SQL Server and related services.
  • Maintenance
  • Management, configuration, permissions, alerts, logins and roles.
  • Optimization
  • Installation Planning
  • Restoring
  • Troubleshooting

This Microsoft 70-463: Implementing a Data Warehouse with SQL Server 2012 course is designed for ETL and Data Warehouse Developers who most likely focus on hands-on work creating business intelligence (BI) solutions, including data cleansing, Extract Transform Load (ETL), and Data Warehouse implementation. Primary responsibilities may include:

  • Mastering data using Master Data Services
  • Cleansing data using Data Quality Services
  • ETL Package development for data loading/transfer/transformation
  • Data Synchronization
  • One-time data or application migration
  • Ad-hoc data manipulations, transformations
  • Scheduling ETL jobs
  • Managing, configuring and deploying SQL Server Integration Services (SSIS) packages

This Microsoft 70-464: Developing Microsoft SQL Server 2012 Databases course is designed for Database developers who build and implement databases across the organization, while ensuring high levels of data availability. They perform tasks including creating database files, create data types, tables, plan create and optimize indexes, implement data integrity, implement views, stored procedures and functions, manage transactions and locks. Primary responsibilities may include:

  • Optimizing and tuning queries.
  • Creating and altering indexes and stored procedures.
  • Maintaining database integrity.
  • Optimizing indexing strategies.
  • Creating and altering tables.
  • Maintaining indexes.
  • Creating and modifying constraints.

This Microsoft 70-465: Designing Database Solutions for Microsoft SQL Server 2012 course is designed for Database professionals who design and build database solutions. These Database professionals are responsible for the creation of plans and designs for Database structure, storage, objects, and servers, as well as creating the plan for the environment in which the database solution runs. Primary responsibilities may include but are not limited to designing:

  • Data compression strategies
  • Database models
  • Full-Text Search strategies
  • High-availability solutions
  • Maintenance strategy for database servers
  • Management automation strategies
  • Migration, consolidation, and upgrade strategies
  • Database, schema, and object security parameters

The MCSE Data Platform course bundle includes 5 courses:

  • Microsoft 70-461: Querying SQL Server 2012  
  • Microsoft 70-462: Administering SQL Server 2012 Databases  
  • Microsoft 70-463: Implementing a Data Warehouse with SQL Server 2012  
  • Microsoft 70-464: Developing Microsoft SQL Server 2012 Databases  
  • Microsoft 70-465: Designing Database Solutions for Microsoft SQL Server 2012 

Course Outline:

Microsoft Exam 70-461: Querying Microsoft SQL Server 2012 Curriculum:
  • Instructor Introduction
  • Course Outline
Microsoft Exam 70-461: Module 1
  • Introduction to SQL Server 2012
  • SQL Instances
  • Editions and Licensing
  • SQL Server Objects
  • System Databases
  • SQL Server Data Tools
  • Object Explorer
  • Object Scripting
  • Template Explorer
  • Query Tabs
  • Projects and Solutions
  • Customizing SSDT
  • Debugging T-SQL
  • SQLCMD
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-461: Module 2
  • T-SQL Overview
  • T-SQL Elements
  • Batch Separators
  • Line Terminator
  • Variables
  • Control Flow
  • Expressions
  • Functions
  • Predicates and Operators
  • SELECT
  • Column Aliasing
  • SELECT Execution
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-461: Module 3
  • SELECT Statements
  • Basic SELECT Statement
  • Specifying Columns
  • Derived Columns
  • Aliasing Columns
  • Calculated Columns
  • Ordering Results
  • Returning Distinct Results
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-461: Module 4
  • Filtering
  • WHERE Syntax
  • BETWEEN
  • WHERE Syntax IN
  • Filtering NULL Values
  • LIKE
  • Data Type Comparison
  • Filtering Data with WHERE
  • TOP
  • OFFSET-FETCH
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-461: Module 5
  • Join and Set Operators
  • Using Joins
  • Categories of Joins
  • INNER JOIN
  • OUTER JOIN
  • JOIN Demo
  • Set Operators
  • Set Operators Demo
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-461: Module 6
  • Subqueries and Table Expressions
  • Subqueries
  • Self Contained Subquery
  • Correlated Subquery
  • Subquery Demo
  • EXISTS
  • Common Table Expressions
  • Temporary Tables
  • Table Variables
  • Table Demo
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-461: Module 7
  • System Functions
  • Scalar Functions
  • ROWSET
  • System Function Demo
  • Aggregate Functions
  • DISTINCT
  • Aggregation Demo
  • GROUP BY
  • HAVING
  • Conversion Functions
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-461: Module 8
  • T-SQL Programming
  • Logical Functions
  • IIF, CHOOSE and CASE
  • Logical Function Demo
  • Using Views
  • Stored Procedures
  • Functions
  • Triggers
  • Views Demo
  • TRY CATCH
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-461: Module 9
  • Transactions
  • Introduction to Transactions
  • INSERT and UPDATE
  • DELETE and TRUNCATE
  • Transactions Demo
  • Managing Transactions
  • XACT_ABORT
  • Managing Transactions Demo
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-461: Module 10
  • Query Performance
  • Indexing
  • Index Strategies
  • Indexes Demo
  • Execution Plans
  • Statistics IO
  • Execution Plans Demo
  • Exam Scenarios
  • Module Review
  • Program Review
Microsoft Exam 70-462: Administering Microsoft SQL Server 2012 Databases Curriculum:
  • Instructor Introduction
  • Course Outline
Microsoft Exam 70-462: Module 1
  • Introduction to SQL Server 2012
  • SQL Server
  • SQL Server Tools
  • Management Studio Demo
  • Data Tools
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-462: Module 2
  • Installing SQL Server
  • SQL Architecture
  • Hardware Requirements
  • Software Requirements
  • Installation Process
  • Troubleshooting Installation
  • Upgrading and Unattended Installations
  • Configuration Demo
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-462: Module 3
  • Backup and Restore a Database
  • Database Architecture
  • Database Backup
  • Restoration Process
  • Restoring System Database
  • Backup and Recovery Demo
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-462: Module 4
  • Tracing Access to SQL Server
  • Auditing Options
  • Introduction to SQL Audit
  • Audit Objects and Implementation
  • Extended Events
  • Tracing Activity Demo
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-462: Module 5
  • Importing and Exporting Data
  • Introduction to Data Transfer
  • ETL Tools
  • Improving Data Transfer
  • Copying Database Methods
  • Transferring Data Demo
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-462: Module 6
  • SQL Server Security
  • Security and Authentication
  • Server and Database Roles
  • Permissions
  • Securing SQL Server Demo
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-462: Module 7
  • Automating SQL Server
  • SQL Server Agent
  • Database Mail
  • Operators, Alerts and Jobs
  • Database Configuration Demo
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-462: Module 8
  • Monitoring SQL Server
  • Dynamic Management Views and Functions
  • Profiler Trace
  • Database Tuning Advisor
  • Activity and Performance Monitor
  • Data Collection
  • Monitoring Demo
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-462: Module 9
  • Maintaining SQL Server
  • Checking Database Integrity
  • Indexing
  • Ongoing Maintenance
  • Managing and Maintaining Demo
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-462: Module 10
  • Troubleshooting SQL Server
  • Service Issues
  • Connectivity Issues
  • Locking Issues
  • Troubleshooting Demo
  • Exam Scenarios
  • Module Review
  • Program Review
Microsoft Exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012 Curriculum:
  • Instructor Introduction
  • Course Outline
Microsoft Exam 70-463: Module 1
  • Introduction to Data Warehousing
  • OLTP vs. OLAP
  • Data Warehouse
  • Data Warehouse Architecture
  • Creating a Data Warehouse
  • Presenting OLAP Data
  • OLTP and OLAP Comparison Demonstration
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-463: Module 2
  • Designing a Data Warehouse
  • Data Modeling
  • Data Warehouse Schemas
  • Time Dimensions
  • Data Modeling Considerations
  • Implementing a Data Model
  • Logical Model
  • Physical Model
  • Dimensions and Facts
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-463: Module 3
  • ETL with SSIS
  • Introduction to SSIS
  • Upgrading SSIS Packages
  • SSIS Tools
  • Data Flow Tasks
  • Data Sources
  • Transformations
  • Utilizing SQL Server Data Tools
  • Data Flow Task
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-463: Module 4
  • SSIS Control Flow
  • Control Flow Tasks
  • Integration Services Variables
  • Expressions
  • Precedence
  • Containers and Annotation
  • Transactions
  • Checkpoints
  • Control Flow Tasks and Precedence
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-463: Module 5
  • Troubleshooting Packages
  • Overview of Troubleshooting
  • Debugging
  • Breakpoints
  • Error Handling
  • SSIS Log Providers
  • Custom Logging
  • Troubleshooting and Logging
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-463: Module 6
  • Advanced Packages
  • Maintaining a Data Warehouse
  • Change Data Capture
  • Slowly Changing Dimensions
  • Script Components
  • Incremental Updates
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-463: Module 7
  • Data Quality Services
  • Introduction to Data Quality Services
  • Data Quality Services Components
  • Working with Data Quality Services
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-463: Module 8
  • Master Data Services
  • Introduction to Master Data Services
  • MDS vs. DQS
  • How MDS Works
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-463: Module 9
  • Deployment and Security
  • Deployment Options
  • Package Deployment
  • Securing Packages
  • Deploying SSIS Packages and Projects
  • Exam Scenarios
  • Module Review
  • Program Review
Microsoft Exam 70-464: Developing Microsoft SQL Server 2012 Databases Curriculum:
  • Instructor Introduction
  • Course Outline
Microsoft Exam 70-464: Module 1
  • Introduction to SQL Server 2012
  • SQL Instances
  • SQL Editions
  • SQL Server Tools
  • SQL Server Management Studio
  • SQL Data Tools
  • SQL Command
  • Utilizing SQL Server Tools
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-464: Module 2
  • Introduction to Data Types and Tables
  • Collations
  • Unicode Characters
  • Null Values
  • Converting Data Types
  • Introduction to Tables
  • Normalization
  • Temporary Tables
  • Working with Tables
  • Schemas
  • Primary and Foreign Keys
  • UNIQUE Constraint/Index
  • In Depth Look
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-464: Module 3
  • SQL Server Indexing
  • Introduction to Indexing
  • Clustered Indexes
  • Non Clustered Indexes
  • Maintaining Indexes
  • Index Fragmentation
  • Index Tuning
  • Creating and Altering Indexes
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-464: Module 4
  • Query Performance
  • Execution Plans
  • Reading Execution Plans
  • Execution Plan Operators
  • Plan Reuse
  • Query Performance
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-464: Module 5
  • Common Language Runtime (CLR) Integration
  • Assemblies
  • Permissions
  • CLR Objects
  • Integrating the CLR within SQL Server
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-464: Module 6
  • Transact-SQL (T-SQL) Error Handling
  • Error Severity
  • TRY CATCH Error Handling
  • TRY PARSE and TRY CONVERT
  • @@ ERROR
  • Alerts
  • Error Handling in T-SQL
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-464: Module 7
  • Views and Functions
  • Types of Views
  • Working with Views
  • Introduction to Functions
  • Working with Functions
  • Working with Views and Functions
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-464: Module 8
  • Stored Procedures and Triggers
  • Working with Stored Procedures
  • Introduction to Triggers
  • Working with Triggers
  • Stored Procedures and Triggers Demo
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-464: Module 9
  • Working with XML
  • XML Concepts
  • Schemas and Schema Collections
  • FOR XML Clause
  • FLOWR Queries
  • Working with XML in SQL Server
  • Exam Scenarios
  • Module Review
Microsoft Exam 70-464: Module 10
  • Transactions
  • Implicit Transactions
  • Explicit Transactions
  • Locking
  • Working with Transactions and Locking within SQL
  • Exam Scenarios
  • Module Review
  • Course Review
Microsoft 70-465: Designing Database Solutions for Microsoft SQL Server 2012 Curriculum:
  • Instructor Introduction
  • Course Outline
Microsoft 70-465: Module 1
  • Course Introduction
  • Course Modules
  • Introduction to SQL Server 2012
  • SQL Server Instances
  • SQL Server Editions and Licensing
  • SQL Server Objects
  • System Databases
  • SQL Server Management Studio
  • SSMS Components
  • SQL Server Data Tools
  • SQLCMD
  • Working with SSMS
  • Working with SQLCMD
  • Exam Scenarios
  • Module Review
Microsoft 70-465: Module 2
  • Introduction to Databases
  • Database Files
  • Temp db
  • Working with Databases
  • Introduction to Filegroups and Files
  • Introduction to Log Files
  • Transactions
  • Moving Databases
  • Detaching and Attaching Databases
  • Backing Up and Restoring Databases
  • Copy Database Wizard
  • Database Catalog View
  • Creating and Configuring Databases
  • Exam Scenarios
  • Module Review
Microsoft 70-465: Module 3
  • Introduction to Data Types and Tables
  • Supported Data Types
  • Collations and Unicode Characters
  • Introduction to NULLS
  • Converting Data Types
  • Introduction to Tables
  • Normalization
  • Temporary Tables
  • Working with Tables
  • Schemas
  • Primary and Foreign Keys
  • UNIQUE Constraint/Index
  • Data Types in SQL Server Demonstration
  • Working with Tables Demonstration
  • Exam Scenarios
  • Module Review
Microsoft 70-465: Module 4
  • SQL Server Indexing
  • Introduction to Indexing
  • Heaps
  • Clustered Indexes
  • Non-Clustered Indexes
  • Maintaining Indexes
  • Index Fragmentation
  • SQL Server Profiler
  • Index Tuning
  • Working with Indexes Demonstration
  • SQL Server Tuning Advisor Demonstration
  • Exam Scenarios
  • Module Review
Microsoft 70-465: Module 5
  • Performance Considerations
  • Partitioning Function
  • Partition Scheme
  • Partitioned Table or Index
  • Introduction to Transaction
  • Implicit and Explicit Transactions
  • Locking
  • Working with Partition Tables, Transaction and Locks
  • Exam Scenarios
  • Module Review
Microsoft 70-465: Module 6
  • Backup and Restore
  • Database Data and Log Files
  • Recovery Model
  • Database Backups Types
  • Backup Compression and Mirrored Media Sets
  • Verifying Backups
  • Restoration Process
  • Restoring Backup Types
  • Restoring to a Point in Time
  • WITH STANDBY
  • Restoring a Database
  • Restoring System Databases
  • Restoring the Master Database
  • Backing Up and Restoring SQL Server
  • Exam Scenarios
  • Module Review
Microsoft 70-465: Module 7
  • Views and Functions
  • Types Of Views
  • Creating and Altering Views
  • Updatable and Indexed Views
  • Partitioned Views
  • Introduction to Functions
  • Function Types
  • Scalar and Table Valued Functions
  • Working with Views and Functions
  • Exam Scenarios
  • Module Review
Microsoft 70-465: Module 8
  • Stored Procedures and Triggers
  • Types of Stored Procedures
  • Parameters
  • Stored Procedure Compilation
  • Execution Context
  • Introduction to Triggers
  • Types of Triggers
  • Nested and Recursive Triggers
  • Trigger Firing Order
  • Working with Stored Procedures and Triggers
  • Exam Scenarios
  • Module Review
Microsoft 70-465: Module 9
  • SQL Server Security
  • SQL Authentication
  • SQL Logins
  • Accessing a Database
  • Authorization Across Servers
  • Server Roles
  • Database Roles
  • Application Roles
  • Authorizing Access
  • Configuring Access Using Schemas
  • Working with SQL Security
  • Exam Scenarios
  • Module Review
Microsoft 70-465: Module 10
  • Installing and Upgrading SQL
  • Server Resources
  • 32 vs. 64 Bit
  • SQL Requirements
  • Software Requirements
  • Collations
  • Service Accounts
  • Installing SQL Server
  • Troubleshooting Installation
  • Upgrading SQL Server
  • Unattended Installations
  • Exam Scenarios
  • Module Review
  • Course Review
Querying Microsoft SQL Server 2012 Virtual Lab
  • Create and alter tables using Transact SQL syntax
  • Create and alter views
  • Create and modify constraints
  • Create and alter DML triggers
  • Query data by using SELECT statements
  • Implementing Sub queries
  • Implementing Data Types
  • Implementing Aggregate Queries
  • Query and Manage XML Data
  • Modifying Data
  • Working with Functions
  • Optimizing Queries
  • Troubleshooting Queries
Administering Microsoft SQL Server 2012 Databases Virtual Lab
  • Install SQL Server and related services
  • Implement a migration strategy
  • Configure additional SQL Server components
  • Manage SQL Server Agent
  • Manage and configure databases
  • Configure SQL Server instances
  • Manage SQL Server Instances
  • Identify and Resolve Concurrency Problems
  • Audit SQL Server Instances
  • Manage Logins and Server Roles
  • Manage Database Permissions
  • Troubleshoot Security
  • Manage Users and Database Roles
Implementing a Data Warehouse with Microsoft SQL Server 2012 Virtual Lab
  • Design and Implement a Data Warehouse
  • Define Connection Managers
  • Implementing Data Flow
  • Managing SSIS Package Execution
  • Implementing Script Tasks
  • Implement Control Flow
  • Implement Data Load Options
  • Troubleshoot Data Integration Issues
  • Install and Maitain SSIS Components
  • Implement Auditing Logging and Event Handling
  • Deploy SSIS Solutions
  • Install and maintain Data Quality Services
  • Implement Master Data Management Solutions
  • Manage Master Data Management Solutions
  • Create a Data Quality Project to Clean Data
  • Manage Data Quality Project to Clean Data
Developing Microsoft SQL Server 2012 Databases Virtual Lab
  • Create and alter tables
  • Design, implement,and troubleshoot security
  • Design the locking granularity level
  • Maintain indexes
  • Implement data types
  • Create and modify constraints
  • Work with XML data
  • Write automation scripts
  • Design and implement stored procedures
  • Design T-SQL table-valued and scalar functions
  • Create, use, and alter user-defined functions (UDFs)
  • Create and alter views
  • Create and alter indexes
  • Optimize and tune queries
  • Troubleshoot and resolve performance problems
  • Optimize indexing strategies
  • Capture and analyze execution plans
  • Collect performance and system information

All necessary materials are included.

Certification(s):

This course prepares students to take the Microsoft 70-461: Querying SQL Server 2012, 70-462: Administering SQL Server 2012 Databases, 70-463: Implementing a Data Warehouse with SQL Server 2012 Server, 70-464: Developing Microsoft SQL Server 2012, 70-465: Designing Database Solutions for Microsoft SQL Server certification exams.


System Requirements:

Internet Connectivity Requirements:
  • Cable and DSL internet connections are recommended.
Hardware Requirements:
  • Minimum Pentium 400 Mhz CPU or G3 Macintosh. 1 GHz or greater CPU recommended.
  • 256MB RAM minimum. 1 GB RAM recommended.
  • 800x600 video resolution minimum. 1025x768 recommended.
  • Speakers/Headphones to listen to Dialogue steaming audio sessions.
  • A microphone to speak in Dialogue streaming audio sessions.
Operating System Requirements:
  • Windows Vista, 7, 8, 8.1, 9, 10
  • Mac OSX 10 or higher.
  • OpenSUSE Linux 9.2 or higher.
Web Browser Requirements:
  • Google Chrome is recommended.
  • Firefox 13.x or greater.
  • Internet Explorer 6.x or greater.
  • Safari 3.2.2 or greater.
Software Requirements:
  • Adobe Flash Player 6 or greater.
  • Oracle Java 7 or greater.
  • Adobe Reader 7 or greater.
Web Browser Settings:
  • Accept Cookies
  • Disable Pop-up Blocker.


** Outlines are subject to change, as courses and materials are updated. Software is not included with the purchase of the course, unless otherwise specified. Students are responsible for the purchase and installation of the necessary course software. **