MCSA: SQL Server 2012/2014 Boot Camp

Course Details
Code: 6445
Tuition (USD): $6,595.00 $5,935.50 • Virtual (9 days)
$6,595.00 $5,935.50 • Classroom (9 days)
Course Details
GSA (USD): $4,983.63 • Virtual (9 days)
$4,983.63 • Classroom (9 days)

In this accelerated learning environment, you will build your skills and prepare for the MCSA: SQL Server 2012/2014 certification exams. This extended-hours boot camp led by senior instructors includes targeted lectures using Microsoft Learning recommended content as well as 12 months of access to more than 40 hands-on remote labs.

This boot camp includes all the exam vouchers required for certification, along with one free retake voucher if needed. It also includes access to our Microsoft Boot Camp Exclusives, providing indispensable certification exam prep.

Note: Microsoft Learning has rebranded the MCSA certification to MCSA: SQL Server 2012/2014. Please see https://www.microsoft.com/en-us/learning/mcsa-sql-certification.aspx for certification details.

You will prepare for these exams:

  • 70-461: Querying Microsoft SQL Server 2014
  • 70-462: Administering Microsoft SQL Server 2012/2014 Databases
  • 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012/2014

 

Skills Gained

  • The technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2012
  • The knowledge and skills to maintain Microsoft SQL Server 2012 databases
  • Create a data warehouse with SQL Server 2012
  • Implement ETL with SQL Server Integration Services
  • Validate and cleanse data with SQL Server Data Quality Services and SQL Server Master Data Services
  • Key topics and concepts addressed on the MCSA: SQL Server 2012 exams

Who Can Benefit

  • IT professionals with at least two years of hands-on experience implementing and administering database systems
  • Database administrators, systems engineers, systems analysts, and technical consultants who want to prepare for their MCSA: SQL Server 2012/2014 credential in an accelerated format

Note: This course, like all courses, is provided only to individuals sponsored by an employer (business, government agency, non-profit, etc.). Fee-paying members of the general public are not permitted to register. For more information, please contact us at 1-800-268-7737.

Prerequisites

  • Working knowledge of relational databases
  • Two years of hands-on experience working in a SQL Server 2008 or SQL Server 2012 environment
  • Basic knowledge of the Microsoft Windows operating system and its core functionality

Course Details

Querying Microsoft SQL Server 2014 (M20461)

  • SQL Server Architecture
  • SQL Server Editions and Versions
  • SQL Server Management Studio

2. Introduction to T-SQL Querying

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

3. Writing SELECT Queries

  • Writing Simple SELECT Statements
  • Eliminating Duplicates with DISTINCT
  • Using Column and Table Aliases
  • Writing Simple CASE Expressions

4. Querying Multiple Tables

  • Joins
  • Querying with Inner and Outer Joins
  • Querying with Cross Joins and Self Joins

5. Sorting and Filtering Data

  • Sorting Data
  • Filtering Data with a WHERE Clause
  • Filtering with the TOP and OFFSET-FETCH Options
  • Working with Unknown and Missing Values

6. Working with SQL Server 2014 Data Types

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

7. DML to Modify Data

  • Insert Data
  • Modify and Delete Data

8. Using Built-In Functions

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

9. Grouping and Aggregating Data

  • Using Aggregate Functions
  • Using the GROUP BY Clause
  • Filtering Groups with HAVING

10. Subqueries

  • Writing Self-Contained Subqueries
  • Writing Correlated Subqueries
  • Using the EXISTS Predicate with Subqueries

11. Using Table Expressions

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

12. Using Set Operators

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

13. Using Window Ranking, Offset, and Aggregate Functions

  • Creating Windows with OVER
  • Exploring Window Functions

14. Pivoting and Grouping Sets

  • Writing Queries with PIVOT and UNPIVOT
  • Working with Grouping Sets

15. Query Data with Stored Procedures

  • Querying Data with Stored Procedures
  • Passing Parameters to Stored Procedures
  • Creating Simple Stored Procedures
  • Working with Dynamic SQL

16. Programming with T-SQL

  • T-SQL Programming Elements
  • Controlling Program Flow

17. Implementing Error Handling

  • Using TRY/CATCH Blocks
  • Working with Error Information

18. Implementing Transactions

  • Transactions and the Database Engine
  • Controlling Transactions

19. Appendix 1: Improve Query Performance

  • Factors in Query Performance
  • Display Query Performance Data

20. Appendix 2: Query SQL Server Metadata

  • Query System Catalog Views and Functions
  • Execute System Stored Procedures
  • Query Dynamic Management Objects

 

Administering Microsoft SQL Server Databases 2014 (M20462)

1. Introduction to SQL Server 2014 Database Administration

  • Database Administration Overview
  • Introduction to the SQL Server Platform
  • Database Management Tools and Techniques

2. Installing and Configuring SQL Server 2014

  • Plan SQL Server Installation
  • Install SQL Server 2014 and Post-Installation Configuration

3. Work with Databases and Storage

  • Introduction to Data Storage with SQL Server
  • Manage Storage for System Databases and User Databases
  • Move Database Files
  • Configure the Buffer Pool Extension

4. Plan and Implement a Backup Strategy

  • SQL Server Recovery Models
  • Plan a Backup Strategy
  • Backup Databases and Transaction Logs
  • Backup Options and Backup Reliability

5. Restore SQL Server 2014 Databases

  • Restore Process and Databases Restoration
  • Advanced Restore Scenarios
  • Point-in-Tim Recovery

6. Import and Export Data

  • Transferring Data
  • Import and Export Table Data
  • Copy or Move a Database

7. Monitor SQL Server 2014

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

8. Tracing SQL Server Activity

  • Trace SQL Server Workload Activity and Use Traces

9. Manage SQL Server Security

  • Introduction to SQL Server Security
  • Manage Server-Level Security and Database-Level Principals
  • Manage Database Permissions

10. Audit Data Access and Encrypt Data

  • Audit Data Access in SQL Server
  • Implement SQL Server Audit
  • Encrypt Databases

11. Perform Ongoing Database Maintenance

  • Ensure Database Integrity
  • Maintain Indexes
  • Automate Routine Database Maintenance

12. Automate SQL Server 2014 Management

  • Implement and Manage SQL Server Agent Jobs
  • Manage Job Step Security Contexts
  • Manage Job on Multiple Servers

13. Monitor SQL Server 2014 Using Alerts and Notifications

  • Monitor SQL Server Errors
  • Configure Database Mail
  • Configure Operators, Alerts, and Notifications

Implementing a Data Warehouse with Microsoft SQL Server 2014 (M20463)

1. Data Warehousing

  • Concepts and Architecture Considerations
  • Considerations for a Data Warehouse Solution

2. Data Warehouse Infrastructure

  • Hardware Selections
  • Considerations for Business Intelligence Infrastructure

3. Design and Implement a Data Warehouse

  • Logical Design, Including Dimension Tables and Fact Tables
  • Physical Implementation, Including a Star Schema, Snowflake Schema, and Time Dimension

4. Create an ETL Solution with SSIS

  • ETL with SSIS
  • Explore Source Data
  • Implement Data Flow

5. Implement Control Flow in an SSIS Package

  • Control Flow
  • Create Dynamic Packages
  • Using Containers
  • Manage Consistency with Transactions and Checkpoints

6. Debug and Troubleshoot SSIS Packages

  • Debug an SSIS Package
  • Log SSIS Package Events
  • Implement an Event Handler
  • Handle Errors in an SSIS Package

7. Implement an Incremental ETL Process

  • Incremental ETL
  • Data and Modified Data Extraction

8. Load Data into a Data Warehouse

  • Data and Modified Data Load planning
  • Incremental loads Using SSIS
  • Transact-SQL Loading Techniques

9. Enforce Data Quality

  • Microsoft SQL Server DQS
  • Use DQS to Cleanse Data
  • Use DQS to Match Data

10. Master Data Services

  • Master Data Services Concepts
  • Implement a Master Data Services Model
  • Master Data Services Tools to Manage and Create Master Data

11. Extend SSIS

  • Custom Components in SSIS
  • Scripting in SSIS

12. Deploy and Configure SSIS Packages

  • Deployment Considerations
  • Deploy SSIS Projects
  • Plan SSIS Package Execution

13. Consume Data in a Data Warehouse

Querying Microsoft SQL Server (M20461)

Lab 1: SQL Server 2014 Tools

Lab 2: Transact-SQL Querying

Lab 3: Write Basic SELECT statements

Lab 4: Query Multiple Tables

Lab 5: Sort and Filter Data

Lab 6: Work with SQL Server 2014 Data Types

Lab 7: Modify Data using DML

Lab 8: Built-In Functions

Lab 9: Group and Aggregate Data

Lab 10: Subqueries

Lab 11: Table Expressions

Lab 12: Set Operators

Lab 13: Use Window Ranking, Offset, and Aggregate Functions

Lab 14: Pivot and Group Sets

Lab 15: Execute Stored Procedures

Lab 16: Program with T-SQL

Lab 17: Implement Error Handling

Lab 18: Implement Transactions

Lab 19: Improve Query Performance

Lab 20: Query SQL Server Metadata

 

Administering Microsoft SQL Server Databases (M20462)

Lab 1: Use SQL Server Administrative Tools

Lab 2: Install SQL Server 2014

Lab 4: Manage Database Storage

Lab 4: Back Up SQL Server Databases

Lab 5: Restore SQL Server 2014 Databases

Lab 6: Import and Export Data

Lab 7: Monitor SQL Server 2014

Lab 8: Trace SQL Server Workload Activity

Lab 9: Manage SQL Server Security

Lab 10: Audit Data Access and Encrypting Data

Lab 11: Perform Ongoing Database Maintenance

Lab 12: Automate SQL Server Management

Lab 13: Monitor SQL Agent using Alerts and Notifications

 

Implementing a Data Warehouse with Microsoft SQL Server (M20463)

Lab 1: Exploring a Data Warehousing Solution

Lab 2: Planning Data Warehouse Infrastructure

Lab 3: Implementing a Data Warehouse

Lab 4: Implement Data Flow in an SSIS Package

Lab 5A: Implementing Control Flow in an SSIS Package

Lab 5B: Using Transactions and Checkpoints

Lab 6: Debugging and Troubleshooting an SSIS Package

Lab 7: Extracting Modified Data

Lab 8: Loading a Data Warehouse

Lab 9: Cleansing Data and Deduplicating Data

Lab 10: Implementing Master Data Services

Lab 11: Using Custom Scripts

Lab 12: Deploying and Configuring SSIS Packages

Lab 13: Using a Data Warehouse

Lab 13: Using a Data Warehouse

2016-02-23 10:18:13.840000000

Contact Us 1-800-803-3948
Contact Us Live Chat
FAQ Get immediate answers to our most frequently asked qestions. View FAQs arrow_forward