MCSA: SQL Server 2012/2014 Bootcamp

The MCSA SQL Server 2012 / 2014 certification boot camp is a nine day comprehensive deep dive into the SQL Server covering topics such as writing queries, administration and data warehousing. This instructor led face to face training camp will teach you the skills needed to support a SQL environment.

The MCSA SQL boot camp incorporates Microsoft Official Courses -

  • 20461 Querying Microsoft SQL Server
  • 20462 Administering Microsoft SQL Server Databases
  • 20463 Implementing a Data Warehouse with Microsoft SQL Server

While attending this training program - you take three exams to test and certify for the Microsoft Certified Solutions Associate (MCSA): SQL Server 2012 / 2014 official certification. If your goal is to learn how to use SQL Server and pass the certification exams (70-461, 70-462 & 70-463) - you've found the right place. This course is a hybrid of actual hands on learning combined with focused materials mapped to exam objectives.

Exam Number:
70-461, 70-462, 70-463
Exam Name:
Querying Microsoft SQL Server 2012/2014; Administering Microsoft SQL Server 2012/2014 Databases; Implementing a Data Warehouse with Microsoft SQL Server 2012/2014
Certifications:
MCSA: SQL Server 2012/2014

Upcoming Dates

Apply
Class Date Class Hours Location GTR Price  

What’s Included

  • Comprehensive study materials, including official Microsoft courseware
  • On-site certification testing with three examination vouchers and up to three retake vouchers that are valid for one year from original class date

Course Objectives

After completing this course, you will be able to:

  • 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
  • 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
  • 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

Course Outline

20461 Querying Microsoft SQL Server

  • Module 1: Introduction to Microsoft SQL Server 2012
  • Module 2: Getting Started with SQL Azure
  • Module 3: Introduction to T-SQL Querying
  • Module 4: Writing SELECT Queries
  • Module 5: Querying Multiple Tables
  • Module 6: Sorting and Filtering Data
  • Module 7: Working with SQL Server 2012 Data Types
  • Module 8: Using Built-In Functions
  • Module 9: Grouping and Aggregating Data
  • Module 10: Using Subqueries
  • Module 11: Using Table Expressions
  • Module 12: Using Set Operators
  • Module 13: Using Window Ranking, Offset and Aggregate Functions
  • Module 14: Pivoting and Grouping Sets
  • Module 15: Querying SQL Server Metadata
  • Module 16: Executing Stored Procedures
  • Module 17: Programming with T-SQL
  • Module 18: Implementing Error Handling
  • Module 19: Implementing
  • Module 20: Improving Query Performance

20462 Administering Microsoft SQL Server Databases

  • Module 1: Introduction to SQL Server 2012 and its Toolset
  • Module 2: Preparing Systems for SQL Server 2012
  • Module 3: Installing and Configuring SQL Server 2012
  • Module 4: Working with Databases
  • Module 5: Understanding SQL Server 2012 Recovery Models
  • Module 6: Backup of SQL Server 2012 Databases
  • Module 7: Restoring SQL Server 2012 Databases
  • Module 8: Importing and Exporting Data
  • Module 9: Authenticating and Authorizing Users
  • Module 10: Assigning Server and Database Roles
  • Module 11: Authorizing Users to Access Resources
  • Module 12: Auditing SQL Server Environments
  • Module 13: Automating SQL Server 2012 Management
  • Module 14: Configuring Security for SQL Server Agent
  • Module 15: Monitoring SQL Server 2012 with Alerts and Notifications
  • Module 16: Performing Ongoing Database Maintenance
  • Module 17: Tracing Access to SQL Server 2012
  • Module 18: Monitoring SQL Server 2012
  • Module 19: Managing Multiple Servers
  • Module 20: Troubleshooting Common SQL Server 2012 Administrative Issues

20463 Implementing a Data Warehouse with Microsoft SQL Server

  • Module 1: Introduction to Data Warehousing
  • Module 2: Data Warehouse Hardware
  • Module 3: Designing and Implementing a Data Warehouse
  • Module 4: Creating an ETL Solution with SSIS
  • Module 5: Implementing Control Flow in an SSIS Package
  • Module 6: Debugging and Troubleshooting SSIS Packages
  • Module 7: Implementing an Incremental ETL Process
  • Module 8: Incorporating Data from the Cloud into a Data Warehouse
  • Module 9: Enforcing Data Quality
  • Module 10: Using Master Data Services
  • Module 11: Extending SQL Server Integration Services
  • Module 12: Deploying and Configuring SSIS Packages
  • Module 13: Consuming Data in a Data Warehouse

20461 Labs

  • Lab 1: Working with SQL Server 2012 Tools
  • Lab 2: Introduction to Transact-SQL Querying
  • Lab 3: Writing Basic SELECT Statements
  • Lab 4: Querying Multiple Tables
  • Lab 5: Sorting and Filtering Data
  • Lab 6: Working with SQL Server 2012 Data Types
  • Lab 7: Using Built-In Functions
  • Lab 8: Grouping and Aggregating Data
  • Lab 9: Using Subqueries
  • Lab 10: Using Table Expressions
  • Lab 11: Using Set Operators
  • Lab 12: Using Window Ranking, Offset and Aggregate Functions
  • Lab 13: Pivoting and Grouping Sets
  • Lab 14: Querying SQL Server Metadata
  • Lab 15: Executing Stored Procedures
  • Lab 16: Programming with T-SQL
  • Lab 17: Implementing Error Handling
  • Lab 18: Implementing Transactions
  • Lab 19: Improving Query Performance

20462 Labs

  • Lab 1: Introduction to SQL Server and its Toolset
  • Lab 2: Preparing Systems for SQL Server
  • Lab 3: Installing and Configuring SQL Server
  • Lab 4: Working with Databases
  • Lab 5: Understanding SQL Server Recovery Models
  • Lab 6: Backup of SQL Server Databases
  • Lab 7: Restoring SQL Server 2012 Databases
  • Lab 8: Importing and Exporting Data
  • Lab 9: Authenticating and Authorizing Users
  • Lab 10: Assigning Server and Database Roles
  • Lab 11: Authorizing Users to Access Resources
  • Lab 12: Auditing SQL Server Environments
  • Lab 13: Automating SQL Server Management
  • Lab 14: Configuring Security for SQL Server Agent
  • Lab 15: Monitoring SQL Agent Jobs with Alerts and Notifications
  • Lab 16: Performing Ongoing Database Maintenance
  • Lab 17: Tracing Access to SQL Server 2012
  • Lab 18: Monitoring SQL Server 2012
  • Lab 19: Managing Multiple Servers
  • Lab 20: Troubleshooting Common Issues

20463 Labs

  • Lab 1: Exploring a Data Warehousing Solution
  • Lab 2: Implementing a Data Warehouse Schema
  • Lab 3: Implementing Data Flow in a SSIS Package
  • Lab 4: Implementing Control Flow in an SSIS Package
  • Lab 5: Using Transactions and Checkpoints
  • Lab 6: Debugging and Troubleshooting an SSIS Package
  • Lab 7: Extracting Modified Data
  • Lab 8: Loading Incremental Changes
  • Lab 9: Using Cloud Data in a Data Warehouse Solution
  • Lab 10: Cleansing Data
  • Lab 11: Deduplicating Data
  • Lab 12: Implementing Master Data Services
  • Lab 13: Using Custom Components and Scripts
  • Lab 14: Deploying and Configuring SSIS Packages
  • Lab 15: Using Business Intelligence Tools