×
Microsoft

MCSA: SQL Server 2012/2014 Boot Camp

BONUS! Cyber Phoenix Subscription Included: All Phoenix TS students receive complimentary ninety (90) day access to the Cyber Phoenix learning platform, which hosts hundreds of expert asynchronous training courses in Cybersecurity, IT, Soft Skills, and Management and more!

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

Currently, there are no public classes scheduled. Please contact a Phoenix TS Training Consultant to discuss hosting a private class at 301-258-8200.

[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

Program Level

Advanced

Training Delivery Methods

Group Live

Duration

5 Days / 32 hours Training

CPE credits

26 NASBA CPE Credits

Field of Study

Information Technology

Advanced Prep

N/A

Course Registration

Candidates can choose to register for the course by via any of the below methods:

Upon registration completion candidates are sent an automated course registration email that includes attachments with specific information on the class and location as well as pre-course study and test preparation material approved by the course vendor. The text of the email contains a registration confirmation as well as the location, date, time and contact person of the class.

Online enrolment closes three days before course start date.

On the first day of class, candidates are provided with instructions to register with the exam provider before the exam date.

Complaint Resolution Policy

To view our complete Complaint Resolution Policy policy please click here: Complaint Resolution Policy

Refunds and Cancellations

To view our complete Refund and Cancellation policy please click here:  Refund and Cancellation Policy 

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 Objective Percentage 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 Objective Percentage 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 Objective Percentage 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%

BONUS! Cyber Phoenix Subscription Included: All Phoenix TS students receive complimentary ninety (90) day access to the Cyber Phoenix learning platform, which hosts hundreds of expert asynchronous training courses in Cybersecurity, IT, Soft Skills, and Management and more!

Phoenix TS is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints re-garding registered sponsors may be submitted to the National Registry of CPE Sponsors through its web site: www.nasbaregistry.org

Subscribe now

Get new class alerts, promotions, and blog posts

Phoenix TS needs the contact information you provide to us to contact you about our products and services. You may unsubscribe from these communications at anytime. For information on how to unsubscribe, as well as our privacy practices and commitment to protecting your privacy, check out our Privacy Policy.

Download Course Brochure

Enter your information below to download this brochure!

Name(Required)