What’s included?

• Authorized Courseware
• Intensive Hands on Skills Development with an Experienced Subject Matter Expert
• Hands-on practice on real Servers and extended lab support 1.800.482.3172
• Examination Vouchers & Onsite Certification Testing- (excluding Adobe and PMP Boot Camps)
• Academy Code of Honor: Test Pass Guarantee
• Optional: Package for Hotel Accommodations, Lunch and Transportation

Start	Finish	Public Price	Public Enroll	Private Price	Private Enroll
12/25/2023	12/31/2023		Enroll		Enroll
1/15/2024	1/21/2024		Enroll		Enroll
2/5/2024	2/11/2024		Enroll		Enroll
2/26/2024	3/3/2024		Enroll		Enroll
3/18/2024	3/24/2024		Enroll		Enroll
4/8/2024	4/14/2024		Enroll		Enroll
4/29/2024	5/5/2024		Enroll		Enroll
5/20/2024	5/26/2024		Enroll		Enroll
6/10/2024	6/16/2024		Enroll		Enroll
7/1/2024	7/7/2024		Enroll		Enroll
7/22/2024	7/28/2024		Enroll		Enroll
8/12/2024	8/18/2024		Enroll		Enroll
9/2/2024	9/8/2024		Enroll		Enroll
9/23/2024	9/29/2024		Enroll		Enroll
10/14/2024	10/20/2024		Enroll		Enroll
11/4/2024	11/10/2024		Enroll		Enroll
11/25/2024	12/1/2024		Enroll		Enroll
12/16/2024	12/22/2024		Enroll		Enroll
1/6/2025	1/12/2025		Enroll		Enroll

Curriculum

Querying Microsoft SQL Server 2014 (M20461)

Module 1: Introduction to Microsoft SQL Server 2014

This module introduces the SQL Server platform and major tools. It discusses editions, versions, tools used to query, documentation sources, and the logical structure of databases.

Lessons

The Basic Architecture of SQL Server
SQL Server Editions and Versions
Getting Started with SQL Server Management Studio
Lab: Working with SQL Server 2014 Tools

Module 2: Introduction to T-SQL Querying

This module introduces Transact SQL as the primary querying language of SQL Server. It discusses the basic structure of T-SQL queries, the logical flow of a SELECT statement, and introduces concepts such as predicates and set-based operations.

Lessons

Introducing T-SQL
Understanding Sets
Understanding Predicate Logic
Understanding the Logical Order of Operations in SELECT statements
Lab: Introduction to Transact-SQL Querying

Module 3: Writing SELECT Queries

This module introduces the fundamentals of the SELECT statement, focusing on queries against a single table.

Lessons

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

Module 4: Querying Multiple Tables

This module explains how to write queries that combine data from multiple sources in SQL Server. The module introduces the use of JOINs in T-SQL queries as a mechanism for retrieving data from multiple tables.

Lessons

Understanding Joins
Querying with Inner Joins
Querying with Outer Joins
Querying with Cross Joins and Self Joins
Lab: Querying Multiple Tables

Module 5: Sorting and Filtering Data

This module explains how to enhance queries to limit the rows they return, and to control the order in which the rows are displayed. The module also discusses how to resolve missing and unknown results.

Lessons

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

Module 6: Working with SQL Server 2014 Data Types

This module explains the data types SQL Server uses to store data. It introduces the many types of numeric and special-use data types. It also explains conversions between data types and the importance of type precedence.

Lessons

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

Module 7: Using DML to Modify Data

This module describes the use of Transact-SQL Data Manipulation Language to perform inserts, updates and deletes to your data.

Lessons

Inserting Data
Modifying and Deleting Data
Lab: Using DML to Modify Data

Module 8: Using Built-In Functions

This module introduces the use of functions that are built into SQL Server Denali and will discuss some common usages including data type conversion, testing for logical results, and nullability.

Lessons

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

Module 9: Grouping and Aggregating Data

This module introduces methods for grouping data within a query, aggregating the grouped data and filtering groups with HAVING. The module is designed to help the student grasp why a SELECT clause has restrictions placed upon column naming in the GROUP BY clause as well as which columns may be listed in the SELECT clause.

.Lessons

Using Aggregate Functions
Using the GROUP BY Clause
Filtering Groups with HAVING
Lab: Grouping and Aggregating Data

Module 10: Using Subqueries

This module will introduce the use of subqueries in various parts of a SELECT statement. It will include the use of scalar and multi-result subqueries, and the use of the IN and EXISTS operators.

Lessons

Writing Self-Contained Subqueries
Writing Correlated Subqueries
Using the EXISTS Predicate with Subqueries
Lab: Using Subqueries

Module 11: Using Table Expressions

This module introduces T-SQL expressions which return a valid relational table, typically for further use in the query. The module discusses views, derived tables, common table expressions, and inline table-valued functions.

Lessons

Using Views
Using Inline Table-Valued Functions
Using Derived Tables
Using Common Table Expressions
Lab: Using Table Expressions

Module 12: Using Set Operators

This module introduces the set operators UNION, INTERSECT, and EXCEPT to compare rows between two input sets

Lessons

Writing Queries with the UNION Operator
Using EXCEPT and INTERSECT
Using APPLY
Lab: Using Set Operators

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

This module introduces window functions including ranking, aggregate, and offset functions. Much of this functionality is new to SQL Server 2012. It will cover the use of T-SQL functions such as ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, and LAST_VALUE to perform calculations against a set, or window, of rows.

Lessons

Creating Windows with OVER
Exploring Window Functions
Lab: Using Window Ranking, Offset and Aggregate Functions

Module 14: Pivoting and Grouping Sets

This module discusses techniques for pivoting data in T-SQL as well to introduce the fundamentals of the GROUPING SETS clause. It will also cover the use of GROUP BY ROLLUP and GROUP BY CUBE syntax in SQL Server.

Lessons

Writing Queries with PIVOT and UNPIVOT
Working with Grouping Sets
Lab: Pivoting and Grouping Sets

Module 15: Querying Data with Stored Procedures

This module introduces the use of existing stored procedures in a T-SQL querying environment. It discusses the use of EXECUTE, how to pass input and output parameters to a procedure, and how to invoke system stored procedures.

Lessons

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

Module 16: Programming with T-SQL

This module provides a basic introduction to T-SQL programming concepts and objects. It discusses batches, variables, control of flow elements such as loops and conditionals, how to create and execute dynamic SQL statements, and how to use synonyms.

Lessons

T-SQL Programming Elements
Controlling Program Flow
Lab: Programming with T-SQL

Module 17: Implementing Error Handling

This module introduces the use of error handlers in the T-SQL code. It will introduce the difference between compile errors and run-time errors and will cover how errors affect batches. The module will also cover how to control error handling using TRY/CATCH blocks, the use of the ERROR class of functions, and the use of the new THROW statement.

Lessons

Using TRY / CATCH Blocks
Working with Error Information
Lab: Implementing Error Handling

Module 18: Implementing Transactions

This module introduces the concepts of transaction management in SQL Server. It will provide a high-level overview of transaction properties, covers the basics of marking transactions with BEGIN, COMMIT, and ROLLBACK.

Lessons

Transactions and the Database Engine
Controlling Transactions
Lab: Implementing Transactions

Module 19: Appendix 1: Improving Query Performance

This module presents several key guidelines for writing well-performing queries, as well as ways to monitor the execution of your queries and their impact on Microsoft SQL Server

Lessons

Factors in Query Performance
Displaying Query Performance Data
Lab: Improving Query Performance

Module 20: Appendix 2: Querying SQL Server Metadata

SQL Server provides access to structured metadata by using a variety of mechanisms, such as system catalog views, system functions, dynamic management objects, and system stored procedures. In this module, you will learn how to write queries to return system metadata using these mechanisms.

Lessons

Querying System Catalog Views and Functions
Executing System Stored Procedures
Querying Dynamic Management Objects
Lab: Querying SQL Server Metadata

Administering Microsoft SQL Server Database 2014 (M20462)

Module 1: Introduction to SQL Server 2014 Database Administration

This module introduces the Microsoft SQL Server 2014 platform. It describes the components, editions, and versions of SQL Server 2014, and the tasks that a database administrator commonly performs for a SQL Server instance.

Lessons

Database Administration Overview
Introduction to the SQL Server Platform
Database Management Tools and Techniques
Lab: Using SQL Server Administrative Tools

Module 2: Installing and Configuring SQL Server 2014This module explains how to assess resource requirements for SQL Server 2014 and how to install it

Lessons

Planning SQL Server Installation
Installing SQL Server 2014
Post-Installation Configuration
Lab: Installing SQL Server 2014

Module 3: Working with Databases and Storage

This module describes how data is stored in databases, how to create databases, how to manage database files, and how to move them. Other tasks related to storage, include managing the temp database and using fast storage devices to extend the SQL Server buffer pool cache are also discussed.

Lessons

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
Lab: Managing Database Storage

Module 4: Planning and Implementing a Backup Strategy

In this module, you will consider how to create a backup strategy that is aligned with organizational needs, and learn how to perform the backup operations required by that strategy.

Lessons

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

Module 5: Restoring SQL Server 2014 Databases

In this module, you will see how to restore user and system databases and how to implement point-in-time recovery.

Lessons

Understanding the Restore Process
Restoring Databases
Advanced Restore Scenarios
Working with Point-in-Time Recovery
Lab: Restoring SQL Server Databases

Module 6: Importing and Exporting Data

In this module, you will briefly explore tools and techniques so that you can import and export data to and from SQL Server.

Lessons

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

Module 7: Monitoring SQL Server 2014This module explains how to use three of the most commonly used tools: Activity Monitor, dynamic management views and functions (DMVs and DMFs), and Performance Monitor.

Lessons

Introduction to Monitoring SQL Server
Dynamic Management Views and Functions
Performance Monitor
Lab: Monitoring SQL Server 2014

Module 8: Tracing SQL Server Activity

This module describes how to use SQL Server Profiler and SQL Trace stored procedures to capture information about SQL Server, and how to use that information to troubleshoot and optimize SQL Server workloads.

Lessons

Tracing SQL Server Workload Activity
Using Traces
Lab: Tracing SQL Server Workload Activity

Module 9: Managing SQL Server Security

In this module, you will learn about the core concepts on which the SQL Server security architecture is based, and how to manage security at the server and database levels.

Lessons

Introduction to SQL Server Security
Managing Server-Level Security
Managing Database-Level Principals
Managing Database Permissions
Lab: Managing SQL Server Security

Module 10: Auditing Data Access and Encrypting Data

This module describes the available options for auditing in SQL Server, how to use and manage the SQL Server audit feature, and how to implement encryption.

Lessons

Auditing Data Access in SQL Server
Implementing SQL Server Audit
Implementing SQL Server Audit
Lab: Auditing Data Access and Encrypting Data

Module 11: Performing Ongoing Database Maintenance

This module describes common database maintenance tasks that a DBA must perform and demonstrates how to automate these tasks using maintenance plans.

Lessons

Ensuring Database Integrity
Maintaining Indexes
Automating Routine Database Maintenance
Lab: Performing Ongoing Database Maintenance

Module 12: Automating SQL Server 2014 Management

This module describes how to use SQL Server Agent to automate jobs, how to configure security contexts for jobs, and how to implement multi-server jobs.

Lessons

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

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

This module covers the configuration of database mail, alerts, and notifications.

Lessons

Monitoring SQL Server Errors
Configuring Database Mail
Configuring Operators, Alerts, and Notifications
Lab: Monitoring SQL Server by Using Alerts and Notifications

Implementing a Data Warehouse with Microsoft SQL Server (M20463)

Module 1: Introduction to Data Warehousing

This module provides an introduction to the key components of a data warehousing solution and the high-level considerations you must take into account when you embark on a data warehousing project.

Lessons

Overview of Data Warehousing
Considerations for a Data Warehouse Solution
Lab: Exploring a Data Warehousing Solution

Module 2: Data Warehouse Hardware Considerations

This module discusses considerations for selecting hardware and distributing SQL Server facilities across servers.

Lessons

Considerations for building a Data Warehouse
Data Warehouse Reference Architectures and Appliances
Lab: Planning Data Warehouse Infrastructure

Module 3: Designing and Implementing a Data Warehouse

This module describes the key considerations for the logical design of a data warehouse and then discusses best practices for its physical implementation.

Lessons

Logical Design for a Data Warehouse
Physical Design for a data warehouse
Lab: Implementing a Data Warehouse Schema

Module 4: Creating an ETL Solution with SSIS

This module discusses considerations for implementing an ETL process and then focuses on Microsoft SQL Server Integration Services (SSIS) as a platform for building ETL solutions.

Lessons

Introduction to ETL with SSIS
Exploring Data Sources
Implementing Data Flow
Lab: Implementing Data Flow in an SSIS Package

Module 5: Implementing Control Flow in an SSIS Package

This module describes how to implement ETL solutions that combine multiple tasks and workflow logic.

Lessons

Introduction to Control Flow
Creating Dynamic Packages
Using Containers
Managing Consistency
Lab: Implementing Control Flow in an SSIS PackageLab: Using Transactions and Checkpoints

Module 6: Debugging and Troubleshooting SSIS Packages

This module describes how you can debug packages to find the cause of errors that occur during execution. It then discusses the logging functionality built into SSIS that you can use to log events for troubleshooting purposes. Finally, the module describes common approaches for handling errors in control flow and data flow.

Lessons

Debugging an SSIS Package
Logging SSIS Package Events
Handling Errors in an SSIS Package
Lab: Debugging and Troubleshooting an SSIS Package

Module 7: Implementing an Incremental ETL Process

This module describes the techniques you can use to implement an incremental data warehouse refresh process.

Lessons

Introduction to Incremental ETL
Extracting Modified Data
Loading Modified data
Lab: Extracting Modified DataLab: Loading Incremental Changes

Module 8: Enforcing Data Quality

This module introduces Microsoft SQL Server Data Quality Services (DQS) and describes how you can use it to cleanse and duplicate data.

Lessons

Introduction to Data Quality
Using Data Quality Services to Cleanse Data
Using Data Quality Services to Match data
Lab: Cleansing DataLab: De-duplicating data

Module 9: Using Master Data Services

Master Data Services provides a way for organizations to standardize data and improve the quality, consistency, and reliability of the data that guides key business decisions. This module introduces Master Data Services and explains the benefits of using it.

Lessons

Master Data Services Concepts
Implementing a Master Data Services Model
Managing Master Data
Creating a Master Data Hub
Lab: Implementing Master Data Services

Module 10: Extending SQL Server Integration Services

This module describes the techniques you can use to extend SSIS. The module is not designed to be a comprehensive guide to developing custom SSIS solutions, but to provide an awareness of the fundamental steps required to use custom components and scripts in an ETL process that is based on SSIS.

Lessons

Using Scripts in SSIS
Using Custom Components in SSIS
Lab: Using Custom Components and Scripts

Module 11: Deploying and Configuring SSIS Packages

In this module, students will learn how to deploy packages and their dependencies to a server, and how to manage and monitor the execution of deployed packages.

Lessons

Overview of SSIS Deployment
Deploying SSIS Projects
Planning SSIS Package Execution
Lab: Deploying and Configuring SSIS Packages

Module 12: Consuming Data in a Data Warehouse

This module introduces business intelligence (BI) solutions and describes how you can use a data warehouse as the basis for enterprise and self-service BI.

Lessons

Introduction to Business Intelligence
Introduction to Reporting
An Introduction to Data Analysis
Lab: Using Business Intelligence Tools