MCSA: SQL 2012/2014 Boot Camp

Course Outline

1. 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

Lab 13: Using a Data Warehouse