MCSA | SQL Server 2012

Hours: 110 / Access Length: 12 Months / Delivery: Online, Self-Paced
Retail Price: $2,295.00

Course Overview:

This course bundle prepares students to take the Microsoft 70-461: Querying SQL Server 2012, Microsoft 70-462: Administering SQL Server 2012 Databases, and Microsoft 70-463: Implementing a Data Warehouse with SQL Server 2012 Server certification exams.

This course bundle includes FREE access for 12 months to a cloud based lab platform to assist students develop the practical information technology (IT) skills necessary to succeed in high in demand IT jobs.  This cloud based lab solution uses real equipment that enables our students to execute each practical task in a safe environment that is accessible from anywhere without needing to buy their own hardware or risk damage to their own system.

Along with providing the necessary hardware in a virtual environment, students gain access to high quality practical exercises that cover many of the exam topics they will encounter on their certifying exams.

The MCSA SQL Server 2012 course bundle includes 3 courses:

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

This course prepares students to take the Microsoft 70-461: Querying SQL Server 2012, Microsoft 70-462: Administering SQL Server 2012 Databases, and Microsoft 70-463: Implementing a Data Warehouse with SQL Server 2012 Server certification exams.

Course Outline:

Microsoft Exam 70-461: Querying Microsoft SQL Server 2012 Curriculum:
Lesson 1: Understanding SQL Server's Role
  • Overview
  • What Is Information Technology?
  • Introduction to Databases
  • Database Servers and Applications
  • SQL Server's Role
  • Summary
  • Chapter Essentials
Lesson 2: Installing SQL Server 2012
  • Installation Planning
  • Installing a Default Instance
  • Installing Named Instances
  • Installing to a Cluster
  • Installing Extra Features
  • Upgrading from Previous Versions
  • Validating an Installation
  • Removing an Installation
  • Summary
  • Chapter Essentials
Lesson 3: Querying SQL Server
  • Understanding the SQL Language
  • SQL Statement Types
  • SQL Syntactical Elements
  • Coding Recommendations
  • Using SELECT Statements
  • Advanced Query Techniques
  • Using DDL Statements
  • Using DCL Statements
  • Modifying Data
  • Create and modify constraints (simple statements).
  • Work with functions
  • SqlGeography Methods
  • Tuning and Optimizing Queries
  • Summary
  • Chapter Essentials
Lesson 4: Database Concepts and Terminology
  • Relational Database Theory
  • Database Design Processes
  • Project Management for the DBA
  • Summary
  • Chapter Essentials
Lesson 5: ERD and Capacity Planning
  • Planning a Database
  • Understanding Entity Relationship Diagramming
  • Building an ERD
  • Capacity Planning
  • Summary
  • Chapter Essentials
Lesson 6: Normalization and Other Design Issues
  • Designing for Normalization
  • Designing for Performance
  • Designing for Availability
  • Designing for Security
  • Summary
  • Chapter Essentials
Lesson 7: Creating SQL Server Databases
  • SQL Server Databases
  • Database Storage
  • Database Options and Properties
  • Creating Databases in the GUI
  • Creating Databases with T-SQL
  • Creating Databases with PowerShell
  • Attaching and Detaching Databases
  • Database Snapshots
  • Summary
  • Chapter Essentials
Lesson 8: Creating Tables
  • Data Types
  • Query and manage XML data.
  • Collations
  • Table Creation Process
  • Data Partitioning
  • Summary
  • Chapter Essentials
Lesson 9: Indexes and Views
  • Understanding Indexes
  • Creating Basic Indexes
  • Creating Advanced Indexes
  • Managing Indexes
  • Understanding Views
  • Creating Views
  • Summary
  • Chapter Essentials
Lesson 10: Triggers and Stored Procedures
  • Triggers Defined
  • Using Triggers
  • Creating Triggers
  • Understanding Stored Procedures
  • Creating Stored Procedures
  • Summary
  • Chapter Essentials
Lesson 11: Appendix A
  • Certification Objectives Map
Microsoft Exam 70-461: Querying Microsoft SQL Server 2012 Practice Lab:
  • Create and alter tables using Transact SQL syntax
  • Create and alter views
  • Create and modify constraints
  • Create and alter DML triggers
  • Query data by using SELECT statements
  • Implementing Sub queries
  • Implementing Data Types
  • Implementing Aggregate Queries
  • Query and Manage XML Data
  • Modifying Data
  • Working with Functions
  • Optimizing Queries
  • Troubleshooting Queries
Microsoft Exam 70-462: Administering Microsoft SQL Server 2012 Databases Curriculum:
Lesson 1: Understanding SQL Server's Role
  • What Is Information Technology?
  • Introduction to Databases
  • Database Servers and Applications
  • SQL Server's Role
  • Summary
  • Chapter Essentials
Lesson 2: Installing SQL Server 2012
  • Installation Planning
  • Installing a Default Instance
  • Installing Named Instances
  • Installing to a Cluster
  • Installing Extra Features
  • Upgrading from Previous Versions
  • Validating an Installation
  • Removing an Installation
  • Summary
  • Chapter Essentials
Lesson 3: Working with the Administration Tools
  • SQL Server Configuration Manager
  • SQL Server Management Studio
  • SQL Server Data Tools
  • SQL Server Profiler
  • Books Online
  • Windows Server Administration for the DBA
  • Summary
  • Chapter Essentials
Lesson 4: SQL Server Command-Line Administration
  • Introducing the Command Prompt
  • General Commands
  • Batch Files
  • Mastering SQLCMD
  • Introducing Windows PowerShell
  • Using SQL Server PowerShell Extensions
  • Summary
  • Chapter Essentials
Lesson 5: Creating SQL Server Databases
  • SQL Server Databases
  • Database Storage
  • Database Options and Properties
  • Creating Databases in the GUI
  • Creating Databases with T-SQL
  • Creating Databases with PowerShell
  • Attaching and Detaching Databases
  • Database Snapshots
  • Summary
  • Chapter Essentials
Lesson 6: Indexes and Views
  • Understanding Indexes
  • Creating Basic Indexes
  • Creating Advanced Indexes
  • Managing Indexes
  • Understanding Views
  • Creating Views
  • Summary
  • Chapter Essentials
Lesson 7: Implementing Advanced Features
  • Understanding and Installing Analysis Services
  • Understanding Integration Services
  • Understanding and Installing Reporting Services
  • Implementing Database Mail
  • Configuring Full-Text Indexing
  • Implementing Transparent Data Encryption
  • Data Compression
  • Summary
  • Chapter Essentials
Lesson 8: Creating Jobs, Operators, and Alerts
  • Standardize, Automate, and Update
  • Understanding SQL Server Jobs
  • Creating T-SQL Jobs
  • Creating SSIS Jobs
  • Creating Windows Command Jobs
  • Creating and Using Operators
  • Creating and Using Alerts
  • Using WSUS for SQL Server 2012
  • Summary
  • Chapter Essentials
Lesson 9: Performance Monitoring and Tuning
  • Performance Tuning Principles
  • Performance and Troubleshooting Tools
  • Audit SQL Server instances
  • Blocks, Locks, and Deadlocks
  • SQL Server Profiler
  • Database Engine Tuning Advisor
  • Performance Monitoring with System Monitor
  • Using the Resource Governor
  • Performance Studio
  • Advanced Monitoring Tools
  • Summary
  • Chapter Essentials
Lesson 10: Policy-Based Management
  • Policy-Based Management
  • Centralized Server Management
  • Standardizing with PBM and CMS
  • Summary
  • Chapter Essentials
Lesson 11: Backup and Restoration
  • Backing Up a Database
  • Backing Up System Databases
  • Restoring a Database
  • Backing Up the Environment
  • Summary
  • Chapter Essentials
Lesson 12: Security Threats and Principles
  • Security Defined
  • Security Threats
  • Security Principles
  • Summary
  • Chapter Essentials
Lesson 13: Authentication and Encryption
  • Understanding Authentication
  • SQL Server Authentication Methods
  • Logins, Users, and Roles
  • Understanding Encryption
  • Summary
  • Chapter Essentials
Lesson 14: Security Best Practices
  • Establishing Baselines
  • Implementing Least Privilege
  • Auditing SQL Server Activity
  • Configuring the Surface Area
  • Understanding Common Criteria and C2
  • Summary
  • Chapter Essentials
Lesson 15: AlwaysOn and High Availability
  • Introducing AlwaysOn Technology
  • Mirroring and AlwaysOn
  • Failover Solutions
  • Selecting Hardware for AlwaysOn
  • Summary
  • Chapter Essentials
Lesson 16: SQL Server Failover Clustering
  • Understanding Windows Failover Clustering Service
  • Implementing a Windows Cluster
  • Installing SQL Server 2012 to a Cluster
  • Monitoring and Managing a SQL Server Cluster
  • Summary
  • Chapter Essentials
Lesson 17: Database Mirroring and Snapshots
  • RAID-based Data Redundancy
  • Using Database Mirroring
  • Understanding Log Shipping
  • Implementing Database Snapshots
  • Summary
  • Chapter Essentials
Lesson 18: Implementing Replication
  • SQL Server Replication
  • Importing and Exporting Data
  • Summary
  • Chapter Essentials
Lesson 19: SQL Server Integration Services
  • Integration Issues
  • Installing SSIS
  • Configuring SSIS Security Settings
  • Deploying Packages
  • SSIS Auditing and Event Handling
  • Extracting, Transforming, and Loading Data
  • Summary
  • Chapter Essentials
Lesson 20: Appendix A
  • Certification Objectives Map
Microsoft Exam 70-462: Administering Microsoft SQL Server 2012 Databases Practice Lab:
  • Install SQL Server and related services
  • Implement a migration strategy
  • Configure additional SQL Server components
  • Manage SQL Server Agent
  • Manage and configure databases
  • Configure SQL Server instances
  • Manage SQL Server Instances
  • Identify and Resolve Concurrency Problems
  • Audit SQL Server Instances
  • Manage Logins and Server Roles
  • Manage Database Permissions
  • Troubleshoot Security
  • Manage Users and Database Roles
Microsoft Exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012 Curriculum:
Lesson 1: Data Warehousing
  • Understanding Data Warehouses
  • Implementing Fact Tables
  • Implementing Dimensions
  • Summary
  • Chapter Essentials
Lesson 2: SQL Server Integration Services
  • Integration Issues
  • Installing SSIS
  • Configuring SSIS Security Settings
  • Deploying Packages
  • SSIS Auditing and Event Handling
  • Extracting, Transforming, and Loading Data
  • Summary
  • Chapter Essentials
Lesson 3: Data Quality Solutions
  • Understanding Data Quality Concerns
  • Installing Data Quality Services
  • Using Master Data Services
  • Cleaning Data
  • Summary
  • Chapter Essentials
Lesson 4: Appendix A
  • Certification Objectives Map
Microsoft Exam 70-462: Administering Microsoft SQL Server 2012 Databases Practice Lab
  • Design and Implement a Data Warehouse
  • Define Connection Managers
  • Implementing Data Flow
  • Managing SSIS Package Execution
  • Implementing Script Tasks
  • Implement Control Flow
  • Implement Data Load Options
  • Troubleshoot Data Integration Issues
  • Install and Maitain SSIS Components
  • Implement Auditing Logging and Event Handling
  • Deploy SSIS Solutions
  • Install and maintain Data Quality Services
  • Implement Master Data Management Solutions
  • Manage Master Data Management Solutions
  • Create a Data Quality Project to Clean Data
  • Manage Data Quality Project to Clean Data

All necessary materials are included.

Certification(s):

This course prepares students to take the Microsoft 70-461: Querying SQL Server 2012, Microsoft 70-462: Administering SQL Server 2012 Databases, and Microsoft 70-463: Implementing a Data Warehouse with SQL Server 2012 Server certification exams.


System Requirements:

Internet Connectivity Requirements:
  • Cable and DSL internet connections are recommended.

Hardware Requirements:
  • Minimum Pentium 400 Mhz CPU or G3 Macintosh. 1 GHz or greater CPU recommended.
  • 256MB RAM minimum. 1 GB RAM recommended.
  • 800x600 video resolution minimum. 1025x768 recommended.
  • Speakers/Headphones to listen to Dialogue steaming audio sessions.
  • A microphone to speak in Dialogue streaming audio sessions.
Operating System Requirements:
  • Windows Vista, 7, 8, 8.1, 9, 10
  • Mac OSX 10 or higher.
  • OpenSUSE Linux 9.2 or higher.
Web Browser Requirements:
  • Google Chrome is recommended.
  • Firefox 13.x or greater.
  • Internet Explorer 6.x or greater.
  • Safari 3.2.2 or greater.
Software Requirements:
  • Adobe Flash Player 6 or greater.
  • Oracle Java 7 or greater.
  • Adobe Reader 7 or greater.
Web Browser Settings:
  • Accept Cookies
  • Disable Pop-up Blocker.


**Outlines are subject to change, as courses and materials are updated.**