MCSA: SQL Server 2012/2014 Boot Camp

This training addresses the skills for writing basic Transact-SQL queries with SQL Server 2014 for Database Administrators, Developers, and other business intelligence professionals who want to understand fundamental knowledge, tools and capabilities for querying data. Core topics include:

• 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

This training addresses the skills and knowledge for maintaining SQL Server 2014 databases in the enterprise. Students are immersed in material explaining the different tools and capabilities for database management and maintenance. Core topics include:

• 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

This training benefits data warehouse professionals who want to understand the process for implementing data warehouses for Business Intelligence solutions with Microsoft SQL Server 2014 and additional SQL data services. Core topics include:

• 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

Microsoft SQL Server 2014 Intro

• SQL Server Architecture
• SQL Server Versions
• SQL Server Management Studio

T-SQL Querying Intro

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

SELECT Queries

• Simple SELECT Statements
• DISTINCT for Deleting Duplicates
• Column and Table Aliases
• CASE Expressions

Querying Multiple Tables

• Joins
• Inner Joins
• Outer Joins
• Cross Joins and Self Joins

Filtering and Sorting Data

• Sorting Data
• Filtering Data using Predicates
• TOP and OFFSET-FETCH Filtering Options
• Unknown and Missing Values

SQL Server 2014 Data Types

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

DML for Editing Data

• Adding Data
• Editing and Eliminating Data

Built-In Functions

• Queries with Built-In Functions
• Conversion Functions
• Logical Functions
• Functions for NULL

Aggregating and Grouping Data

• Aggregate Functions
• GROUP BY Clause
• HAVING and Filtering Groups

Subqueries

• Uses of queries nested within other queries 
• Self-Contained Subqueries
• Correlated Subqueries
• EXISTS Predicate and Subqueries

Table Expressions

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

Set Operators

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

Window Ranking, Offset and Aggregate Functions

• Windows with OVER
• Window Functions

Grouping and Pivoting Sets

• Queries with PIVOT and UNPIVOT
• Grouping Sets

Querying Data with Stored Procedures

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

T-SQL Programming

• T-SQL Programming
• Program Flow Controls

Error Handling Implementation

• TRY / CATCH Blocks
• Error Information

Transaction Implementation

• Transactions and Database Engine
• Controlling Transactions 

Optimizing Query Performance

• Query Performance Factors
• Presenting Query Performance Data

SQL Server Metadata Querying

• System Catalog Views and Functions
• Using System Stored Procedures
• Querying Dynamic Management Objects

SQL Server 2014 Database Administration Intro

• Database Administration Intro
• SQL Server Platform
• Tools and Techniques

SQL Server 2014 Installation and Configuration

• Planning SQL Server Installation
• Installation
• Post-Install Configuration

Databases and Storage

• Data Storage with SQL Server
• Storage for System Databases
• Storage for User Databases
• Moving Database Files
• Buffer Pool Extension Configuration

Planning and Implementing Backup Strategies

• SQL Server Recovery Models
• Backup Strategies
• Backing up Databases and Transaction Logs
• Backup Options
• Backup Reliability

SQL Server 2014 Database Restoration

• Restore Process
• Restoring Databases
• Advanced Restore Cases
• Point-in-Time Recovery

Data Importing and Exporting

• Transferring Data
• Importing and Exporting Table Data
• Copying or Moving Databases

Monitoring SQL Server 2014

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

SQL Server Activity Tracing

• Tracing Workload Activity
• Using Traces

SQL Server Security Management

• SQL Server Security
• Server-Level Security
• Database-Level Principals
• Database Permissions

Data Access Auditing and Encryption

• Auditing Data Access
• SQL Server Audit Implementation

Performing Ongoing Database Maintenance

• Database Integrity
• Indexes Maintenance
• Automating Routine Maintenance

Automating Management

• SQL Server Management Automation
• SQL Server Agent Jobs Implementation
• SQL Server Agent Jobs Management
• Job Step Security Contexts
• Jobs on Multiple Servers

Alerts and Notifications for Monitoring

• SQL Server Errors
• Database Mail Configuration
• Operators, Alerts, and Notifications Configuration

Data Warehousing Intro

• Data Warehousing
• Data Warehouse Solutions 

Data Warehouse Hardware Considerations 

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

Data Warehouse Design and Implementation

• Logical Design 
• Physical Design 

ETL Solutions

• ETL with SSIS
• Data Sources
• Data Flow Implementation

Control Flow Implementation with SSIS Package

• Control Flow
• Building Dynamic Packages
• Containers
• Consistency Management

SSIS Packages Debugging and Troubleshooting

• Debugging
• Logging Events
• Error Handling

Data Extraction Solution Implementation

• Creating Plans for Data Extraction
• Extracting Changed Data

Loading Data

• Planning Data Loads
• SSIS for Incremental Loads
• Transact-SQL Loading Methods

Data Quality Enforcement

• Data Quality
• Cleansing Data with Data Quality Services
• Matching Data with Data Quality Services 

Master Data Services

• Master Data Services Intro
• Master Data Services Model Implementation
• Master Data Management
• Building a Master Data Hub

Extending SQL Server Integration Services 

• Scripts in SSIS
• Custom Components in SSIS

SSIS Package Deployment and Configuration

• SSIS Deployment
• SSIS Project Deployment
• SSIS Package Execution

Data Consumption

• Business Intelligence Intro
• Enterprise Business Intelligence
• Big Data and Self-Service BI