MCSA: SQL Server Solutions Associate

Demonstrate your essential skills and breakthrough insights in developing and maintaining the next wave of mission-critical environments.

Earning an MCSA: SQL Server certification will qualify you for a position as a database developer or database analyst.

Required Courses and Exams

  • Querying Microsoft SQL Server 2012 Course 10774 Exam 461
  • Administering Microsoft SQL Server 2012 Databases Course 10775 Exam 462
  • Implementing a Data Warehouse with Microsoft SQL Server 2012 Course 10777 Exam 463

Course 10774A: Querying Microsoft SQL Server 2012

Course Content

Module 1: Introduction to Microsoft SQL Server 2012

Introducing Microsoft SQL Server 2012
Getting Started with SQL Server Management Studio

Module 2: Getting Started with SQL Azure

Overview of SQL Azure
Working with SQL Azure

Module 3: Introduction to T-SQL Querying

Introducing T-SQL
Understanding Sets
Understanding Predicate Logic
Understanding the Logical Order of Operations in SELECT statements

Module 4: Writing SELECT Queries

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

Module 5: Querying Multiple Tables

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

Module 6: 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

Module 7: Working with SQL Server 2012 Data Types

Introducing SQL Server 2012 Data Types
Working with Character Data
Working with Date and Time Data

Module 8: Using Built-In Functions

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

Module 9: Grouping and Aggregating Data

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

Module 10: Using Subqueries

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

Module 11: Using Table Expressions

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

Module 12: Using Set Operators

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

Module 13: Using Window Ranking, Offset and Aggregate Functions

Creating Windows with OVER
Exploring Window Functions

Module 14: Pivoting and Grouping Sets

Writing Queries with PIVOT and UNPIVOT
Working with Grouping Sets

Module 15: Querying SQL Server Metadata

Querying System Catalog Views and Functions
Executing System Stored Procedures
Querying Dynamic Management Objects

Module 16: Executing Stored Procedures

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

Module 17: Programming with T-SQL

T-SQL Programming Elements
Controlling Program Flow

Module 18: Implementing Error Handling

Using TRY / CATCH Blocks
Working with Error Information

Module 19: Implementing Transactions

Transactions and the Database Engine
Controlling Transactions

Module 20: Improving Query Performance

Factors in Query Performance
Displaying Query Performance Data

Course 10775A: Administering Microsoft SQL Server 2012 Databases

Course Outline

Module 1: Introduction to SQL Server 2012 and its Toolset

Introduction to the SQL Server Platform
Working with SQL Server Tools
Configuring SQL Server Services

Module 2: Preparing Systems for SQL Server 2012

Overview of SQL Server Architecture
Planning Server Resource Requirements
Pre-installation Testing for SQL Server

Module 3: Installing and Configuring SQL Server 2012

Preparing to Install SQL Server
Installing SQL Server
Upgrading and Automating Installation

Module 4: Working with Databases

Overview of SQL Server Databases
Working with Files and Filegroups
Moving Database Files

Module 5: Understanding SQL Server 2012 Recovery Models
Backup Strategies
Understanding SQL Server Transaction Logging
Planning a SQL Server Backup Strategy

Module 6: Backup of SQL Server 2012 Databases

Backing up Databases and Transaction Logs
Managing Database Backups
Working with Backup Options

Module 7: Restoring SQL Server 2012 Databases

Understanding the Restore Process
Restoring Databases
Working with Point-in-time recovery
Restoring System Databases and Individual Files

Module 8: Importing and Exporting Data

Transferring Data To/From SQL Server
Importing and Exporting Table Data
Inserting Data in Bulk

Module 9: Authenticating and Authorizing Users

Authenticating Connections to SQL Server
Authorizing Logins to Access Databases
Authorization Across Servers

Module 10: Assigning Server and Database Roles

Working with Server Roles
Working with Fixed Database Roles
Creating User-defined Database Roles

Module 11: Authorizing Users to Access Resources

Authorizing User Access to Objects
Authorizing Users to Execute Code
Configuring Permissions at the Schema Level

Module 12: Auditing SQL Server Environments

Options for Auditing Data Access in SQL
Implementing SQL Server Audit
Managing SQL Server Audit

Module 13: Automating SQL Server 2012 Management

Automating SQL Server Management
Working with SQL Server Agent
Managing SQL Server Agent Jobs

Module 14: Configuring Security for SQL Server Agent

Understanding SQL Server Agent Security
Configuring Credentials
Configuring Proxy Accounts

Module 15: Monitoring SQL Server 2012 with Alerts and Notifications

Configuration of Database Mail
Monitoring SQL Server Errors
Configuring Operators, Alerts and Notifications

Module 16: Performing Ongoing Database Maintenance

Ensuring Database Integrity
Maintaining Indexes
Automating Routine Database Maintenance

Module 17: Tracing Access to SQL Server 2012
Capturing Activity using SQL Server Profiler
Improving Performance with the Database Engine Tuning Advisor
Working with Tracing Options

Module 18: Monitoring SQL Server 2012
Monitoring Activity
Capturing and Managing Performance Data
Analyzing Collected Performance Data

Module 19: Managing Multiple Servers

Working with Multiple Servers
Virtualizing SQL Server
Deploying and Upgrading Data-Tier Applications

Module 20: Troubleshooting Common SQL Server 2012 Administrative Issues
SQL Server Troubleshooting Methodology
Resolving Service-related Issues
Resolving Concurrency Issues
Resolving Login and Connectivity Issues

Course 10777A: Implementing a Data Warehouse with Microsoft SQL Server 2012

Course Outline

Module 1: Introduction to Data Warehousing

Overview of Data Warehousing
Considerations for a Data Warehouse Solution

Module 2: Data Warehouse Hardware

Considerations for Building a Data Warehouse
Data Warehouse Reference Architectures and Appliances

Module 3: Designing and Implementing a Data Warehouse

Logical Design for a Data Warehouse
Physical Design for a Data Warehouse

Module 4: Creating an ETL Solution with SSIS

Introduction to ETL with SSIS
Exploring Source Data
Implementing Data Flow

Module 5: Implementing Control Flow in an SSIS Package

Introduction to Control Flow
Creating Dynamic Packages
Using Containers
Managing Consistency

Module 6: Debugging and Troubleshooting SSIS Packages

Debugging an SSIS Package
Logging SSIS Package Events
Handling Errors in an SSIS Package

Module 7: Implementing an Incremental ETL Process

Introduction to Incremental ETL
Extracting Modified Data
Loading Modified Data

Module 8: Incorporating Data from the Cloud into a Data Warehouse

Overview of Cloud Data Sources
SQL Server Database
The Windows Azure Marketplace

Module 9: Enforcing Data Quality

Introduction to Data Quality
Using Data Quality Services to Cleanse Data
Using Data Quality Services to Match Data

Module 10: Using Master Data Services

Introduction to Master Data Services
Implementing a Master Data Services Model
Using the Master Data Services Add-in for Excel


Module 11: Extending SQL Server Integration Services

Using Custom Components in SSIS
Using Scripts in SSIS

Module 12: Deploying and Configuring SSIS Packages

Overview of SSIS Deployment
Deploying SSIS Projects
Planning SSIS Package Execution


Module 13: Consuming Data in a Data Warehouse

Introduction to Business Intelligence
Introduction to Reporting
Introduction to Data Analysis