×
Microsoft

MCSA: SQL Server 2012/2014 Boot Camp

Course Overview

The 10 day MCSA SQL Server 2012/2014 Boot Camp prepares students to pass the three exams needed to earn this MCSA certification. Content covered is from these courses:

20461: Querying Microsoft SQL Server 2014

This training addresses the skills for writing basic Transact-SQL queries with SQL Server 2014 for Database Administrators, Developers, and other business intelligence professionals who want to understand fundamental knowledge, tools and capabilities for querying data. Core topics include:

  • Describe the basic architecture and concepts of Microsoft SQL Server 2014
  • Understand the similarities and differences between Transact-SQL and other computer languages
  • Write SELECT queries
  • Query multiple tables
  • Sort and filter data
  • Describe the use of data types in SQL Server
  • Modify data using Transact-SQL
  • Use built-in functions
  • Group and aggregate data
  • Use subqueries
  • Use table expressions
  • Use set operators
  • Use window ranking, offset and aggregate functions
  • Implement pivoting and grouping sets
  • Execute stored procedures
  • Program with T-SQL
  • Implement error handling
  • Implement transactions

20462: Administering Microsoft SQL Server 2014 Databases

This training addresses the skills and knowledge for maintaining SQL Server 2014 databases in the enterprise. Students are immersed in material explaining the different tools and capabilities for database management and maintenance. Core topics include:

  • Describe core database administration tasks and tools
  • Install and configure SQL Server 2014
  • Configure SQL Server databases and storage
  • Plan and implement a backup strategy
  • Restore databases from backups
  • Import and export data. Monitor SQL Server
  • Trace SQL Server activity
  • Manage SQL Server security
  • Audit data access and encrypt data
  • Perform ongoing database maintenance
  • Automate SQL Server maintenance with SQL Server Agent Jobs
  • Configure Database Mail, alerts and notifications

20463: Implementing a Data Warehouse with MS SQL Server

This training benefits data warehouse professionals who want to understand the process for implementing data warehouses for Business Intelligence solutions with Microsoft SQL Server 2014 and additional SQL data services. Core topics include:

  • Describe data warehouse concepts and architecture considerations
  • Select an appropriate hardware platform for a data warehouse
  • Design and implement a data warehouse
  • Implement Data Flow in an SSIS Package
  • Implement Control Flow in an SSIS Package
  • Debug and Troubleshoot SSIS packages
  • Implement an ETL solution that supports incremental data extraction
  • Implement an ETL solution that supports incremental data loading
  • Implement data cleansing by using Microsoft Data Quality Services
  • Implement Master Data Services to enforce data integrity
  • Extend SSIS with custom scripts and components
  • Deploy and Configure SSIS packages
  • Describe how BI solutions can consume data from the data warehouse

Schedule

MCSA: SQL Server 2012/2014 Boot Camp

date
location
1/04/21 - 1/15/21 (10 days)

8:30AM - 4:30PM

Tysons Corner, VA
Open
1/25/21 - 2/05/21 (10 days)

8:30AM - 4:30PM

Columbia, MD
Open
1/25/21 - 2/05/21 (10 days)

8:30AM - 4:30PM

Online
Open
2/22/21 - 3/05/21 (10 days)

8:30AM - 4:30PM

Tysons Corner, VA
Open
3/08/21 - 3/19/21 (10 days)

8:30AM - 4:30PM

Columbia, MD
Open
3/08/21 - 3/19/21 (10 days)

8:30AM - 4:30PM

Online
Open
4/05/21 - 4/16/21 (10 days)

8:30AM - 4:30PM

Tysons Corner, VA
Open
4/19/21 - 4/30/21 (10 days)

8:30AM - 4:30PM

Columbia, MD
Open
4/19/21 - 4/30/21 (10 days)

8:30AM - 4:30PM

Online
Open
6/07/21 - 6/18/21 (10 days)

8:30AM - 4:30PM

Tysons Corner, VA
Open
6/07/21 - 6/18/21 (10 days)

8:30AM - 4:30PM

Columbia, MD
Open
6/07/21 - 6/18/21 (10 days)

8:30AM - 4:30PM

Online
Open
8/09/21 - 8/20/21 (10 days)

8:30AM - 4:30PM

Tysons Corner, VA
Open
8/09/21 - 8/20/21 (10 days)

8:30AM - 4:30PM

Columbia, MD
Open
8/09/21 - 8/20/21 (10 days)

8:30AM - 4:30AM

Online
Open
9/13/21 - 9/24/21 (10 days)

8:30AM - 4:30PM

Tysons Corner, VA
Open
10/18/21 - 10/29/21 (10 days)

8:30AM - 4:30PM

Tysons Corner, VA
Open
10/18/21 - 10/29/21 (10 days)

8:30AM - 4:30PM

Columbia, MD
Open
10/18/21 - 10/29/21 (10 days)

8:30AM - 4:30PM

Online
Open
11/29/21 - 12/10/21 (10 days)

8:30AM - 4:30PM

Tysons Corner, VA
Open
12/06/21 - 12/17/21 (10 days)

8:30AM - 4:30PM

Columbia, MD
Open
12/13/21 - 12/24/21 (10 days)

8:30AM - 4:30PM

Online
Open
[image]

Not seeing a good fit?

Let us know. Our team of instructional designers, curriculum developers, and subject matter experts can create a custom course for you.

Contact Us

Learn more about custom training

Course Outline

20461: Querying Microsoft SQL Server 2014

Microsoft SQL Server 2014 Intro

  • SQL Server Architecture
  • SQL Server Versions
  • SQL Server Management Studio

T-SQL Querying Intro

  • T-SQL Intro
  • Sets
  • Predicate Logic
  • Logical Order of Operations in SELECT statements

SELECT Queries

  • Simple SELECT Statements
  • DISTINCT for Deleting Duplicates
  • Column and Table Aliases
  • CASE Expressions

Querying Multiple Tables

  • Joins
  • Inner Joins
  • Outer Joins
  • Cross Joins and Self Joins

Filtering and Sorting Data

  • Sorting Data
  • Filtering Data using Predicates
  • TOP and OFFSET-FETCH Filtering Options
  • Unknown and Missing Values

SQL Server 2014 Data Types

  • SQL Server 2014 Data Types
  • Character Data
  • Date and Time Data

DML for Editing Data

  • Adding Data
  • Editing and Eliminating Data

Built-In Functions

  • Queries with Built-In Functions
  • Conversion Functions
  • Logical Functions
  • Functions for NULL

Aggregating and Grouping Data

  • Aggregate Functions
  • GROUP BY Clause
  • HAVING and Filtering Groups

Subqueries

  • Uses of queries nested within other queries 
  • Self-Contained Subqueries
  • Correlated Subqueries
  • EXISTS Predicate and Subqueries

Table Expressions

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

Set Operators

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

Window Ranking, Offset and Aggregate Functions

  • Windows with OVER
  • Window Functions

Grouping and Pivoting Sets

  • Queries with PIVOT and UNPIVOT
  • Grouping Sets

Querying Data with Stored Procedures

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

T-SQL Programming

  • T-SQL Programming
  • Program Flow Controls

Error Handling Implementation

  • TRY / CATCH Blocks
  • Error Information

Transaction Implementation

  • Transactions and Database Engine
  • Controlling Transactions 

Optimizing Query Performance

  • Query Performance Factors
  • Presenting Query Performance Data

SQL Server Metadata Querying

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

20462: Administering Microsoft SQL Server 2014 Databases

SQL Server 2014 Database Administration Intro

  • Database Administration Intro
  • SQL Server Platform
  • Tools and Techniques

SQL Server 2014 Installation and Configuration

  • Planning SQL Server Installation
  • Installation
  • Post-Install Configuration

Databases and Storage

  • Data Storage with SQL Server
  • Storage for System Databases
  • Storage for User Databases
  • Moving Database Files
  • Buffer Pool Extension Configuration

Planning and Implementing Backup Strategies

  • SQL Server Recovery Models
  • Backup Strategies
  • Backing up Databases and Transaction Logs
  • Backup Options
  • Backup Reliability

SQL Server 2014 Database Restoration

  • Restore Process
  • Restoring Databases
  • Advanced Restore Cases
  • Point-in-Time Recovery

Data Importing and Exporting

  • Transferring Data
  • Importing and Exporting Table Data
  • Copying or Moving Databases

Monitoring SQL Server 2014

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

SQL Server Activity Tracing

  • Tracing Workload Activity
  • Using Traces

SQL Server Security Management

  • SQL Server Security
  • Server-Level Security
  • Database-Level Principals
  • Database Permissions

Data Access Auditing and Encryption

  • Auditing Data Access
  • SQL Server Audit Implementation

Performing Ongoing Database Maintenance

  • Database Integrity
  • Indexes Maintenance
  • Automating Routine Maintenance

Automating Management

  • SQL Server Management Automation
  • SQL Server Agent Jobs Implementation
  • SQL Server Agent Jobs Management
  • Job Step Security Contexts
  • Jobs on Multiple Servers

Alerts and Notifications for Monitoring

  • SQL Server Errors
  • Database Mail Configuration
  • Operators, Alerts, and Notifications Configuration

20463: Implementing a Data Warehouse with Microsoft SQL Server 2014

Data Warehousing Intro

  • Data Warehousing
  • Data Warehouse Solutions 

Data Warehouse Hardware Considerations 

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

Data Warehouse Design and Implementation

  • Logical Design 
  • Physical Design 

ETL Solutions

  • ETL with SSIS
  • Data Sources
  • Data Flow Implementation

Control Flow Implementation with SSIS Package

  • Control Flow
  • Building Dynamic Packages
  • Containers
  • Consistency Management

SSIS Packages Debugging and Troubleshooting

  • Debugging
  • Logging Events
  • Error Handling

Data Extraction Solution Implementation

  • Creating Plans for Data Extraction
  • Extracting Changed Data

Loading Data

  • Planning Data Loads
  • SSIS for Incremental Loads
  • Transact-SQL Loading Methods

Data Quality Enforcement

  • Data Quality
  • Cleansing Data with Data Quality Services
  • Matching Data with Data Quality Services 

Master Data Services

  • Master Data Services Intro
  • Master Data Services Model Implementation
  • Master Data Management
  • Building a Master Data Hub

Extending SQL Server Integration Services 

  • Scripts in SSIS
  • Custom Components in SSIS

SSIS Package Deployment and Configuration

  • SSIS Deployment
  • SSIS Project Deployment
  • SSIS Package Execution

Data Consumption

  • Business Intelligence Intro
  • Enterprise Business Intelligence
  • Big Data and Self-Service BI

Exam Information

Exam 70-461: Querying Microsoft SQL Server 2012/2014

Exam ObjectivePercentage of Exam
Create database objects 20–25%
Work with data 25–30%
Modify data 20–25%
Troubleshoot and optimize 25–30%

Exam 70-462: Administering Microsoft SQL Server 2012/2014 Databases 

Exam ObjectivePercentage of Exam
Install and configure 20–25%
Maintain instances and databases 15–20%
Optimize and troubleshoot 15–20%
Manage data 20–25%
Implement security 15–20%
Implement high availability 5–10%

Exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012/2014

Exam ObjectivePercentage of Exam
Design and implement a data warehouse 10–15%
Extract and transform data 20–25%
Load data 25–30%
Configure and deploy SSIS solutions 20–25%
Build data quality solutions 15–20%

Get More Information!

Fill out the form below to get more info and pricing for MCSA: SQL Server 2012/2014 Boot Camp!

  • Price Match Guarantee

    We’ll match any competitor’s price quote. Call us at 240-667-7757.

  • Included in this MCSA: SQL Server 2012/2014 Boot Camp

    • 10 days instructor-led training
    • MCSA: SQL Server 2012/2014 Boot Camp training book
    • Notepad, pen and highlighter
    • Variety of bagels, fruits, doughnuts and cereal available at the start of class*
    • Tea, coffee and soda available throughout the day*
    • Freshly baked cookies every afternoon*
Subscribe now

Get new class alerts, promotions, and blog posts