Course Outline

MCSA SQL Server 2012/2014 Bootcamp

MS507 | Day | 9 Days

The Microsoft Certified Solutions Associate (MCSA): SQL Server 2012/2014 certification shows that you have the primary set of SQL Server skills that are relevant across multiple solution areas in a business environment. The MCSA: SQL Server 2012/2014 certification is a prerequisite for earning the MCSE: Data Platform or Business Intelligence certification.

The primary goal of this course is to help each student pass the exams required to earn the MCSA SQL Server 2012/2014 certification. To do this, your knowledgeable instructor will blend hands-on labs with lecture and practice exams to prepare you to pass each exam. The practice exams identify knowledge gaps that the instructor will fill with customized, hands-on labs and tailored lectures. 

Who should take this course

The MCSA SQL Server 2012/2014 certification is for IT professionals either looking to get their first job in Microsoft Database technology or to document their existing skill sets. This boot camp is intended for students seeking to earn the MCSA SQL Server 2012/2014 certification and who need an expert instructor to guide them throughout the training and exam preparation process.

Course Objectives

After completing this course, students will have the knowledge to:

Course Outline

Module 1: Introduction to Microsoft SQL Server 2014

This module introduces the SQL Server platform and major tools. It discusses editions, versions, tools used to query, documentation sources, and the logical structure of databases.

 

Lessons

 

Module 2: Introduction to T-SQL Querying

This module introduces Transact SQL as the primary querying language of SQL Server. It discusses the basic structure of T-SQL queries, the logical flow of a SELECT statement, and introduces concepts such as predicates and set-based operations.

 

Lessons

 

Module 3: Writing SELECT Queries

This module introduces the fundamentals of the SELECT statement, focusing on queries against a single table.

 

Lessons

 

Module 4: Querying Multiple Tables

This module explains how to write queries which combine data from multiple sources in SQL Server. The module introduces the use of JOINs in T-SQL queries as a mechanism for retrieving data from multiple tables.

 

Lessons

 

Module 5: Sorting and Filtering Data

This module explains how to enhance queries to limit the rows they return, and to control the order in which the rows are displayed. The module also discusses how to resolve missing and unknown results.

 

Lessons

 

Module 6: Working with SQL Server 2014 Data Types

This module explains the data types SQL Server uses to store data. It introduces the many types of numeric and special-use data types. It also explains conversions between data types, and the importance of type precedence.

 

Lessons

 

Module 7: Using DML to Modify Data

This module describes the use of Transact-SQL Data Manipulation Language to perform inserts, updates, and deletes to your data.

 

Lessons

 

Module 8: Using Built-In Functions

This module introduces the use of functions that are built in to SQL Server Denali, and will discuss some common usages including data type conversion, testing for logical results and nullability.

 

Lessons

 

Module 9: Grouping and Aggregating Data

This module introduces methods for grouping data within a query, aggregating the grouped data and filtering groups with HAVING. The module is designed to help the student grasp why a SELECT clause has restrictions placed upon column naming in the GROUP BY clause as well as which columns may be listed in the SELECT clause.

 

Lessons

 

Module 10: Using Subqueries

This module will introduce the use of subqueries in various parts of a SELECT statement. It will include the use of scalar and multi-result subqueries, and the use of the IN and EXISTS operators.

 

Lessons

 

Module 11: Using Table Expressions

This module introduces T-SQL expressions which return a valid relational table, typically for further use in the query. The module discusses views, derived tables, common table expressions and inline table-valued functions.

 

Lessons

 

Module 12: Using Set Operators

This module introduces the set operators UNION, INTERSECT, and EXCEPT to compare rows between two input sets

 

Lessons

 

Module 13: Using Window Ranking, Offset, and Aggregate Functions

This module introduces window functions including ranking, aggregate and offset functions. Much of this functionality is new to SQL Server 2012. It will cover the use of T-SQL functions such as ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE and LAST_VALUE to perform calculations against a set, or window, of rows.

 

Lessons

 

Module 14: Pivoting and Grouping Sets

This module discusses techniques for pivoting data in T-SQL as well to introduce the fundamentals of the GROUPING SETS clause. It will also cover the use of GROUP BY ROLLUP and GROUP BY CUBE syntax in SQL Server.

 

Lessons

 

Module 15: Querying data with Stored Procedures

This module introduces the use of existing stored procedures in a T-SQL querying environment. It discusses the use of EXECUTE, how to pass input and output parameters to a procedure, and how to invoke system stored procedures.

 

Lessons

 

Module 16: Programming with T-SQL

This module provides a basic introduction to T-SQL programming concepts and objects. It discusses batches, variables, control of flow elements such as loops and conditionals, how to create and execute dynamic SQL statements, and how to use synonyms.

 

Lessons

 

Module 17: Implementing Error Handling

This module introduces the use of error handlers in T-SQL code. It will introduce the difference between compile errors and run-time errors, and will cover how errors affect batches. The module will also cover how to control error handling using TRY/CATCH blocks, the use of the ERROR class of functions, and the use of the new THROW statement.

 

Lessons

 

 

 

 

Module 18: Implementing Transactions

This module introduces the concepts of transaction management in SQL Server. It will provide a high-level overview of transaction properties, cover the basics of marking transactions with BEGIN, COMMIT and ROLLBACK.

 

Lessons

 

Module 19: Appendix 1: Improving Query Performance

This module presents several key guidelines for writing well-performing queries, as well as ways to monitor the execution of your queries and their impact on Microsoft SQL Server

 

Lessons

 

Module 20: Querying SQL Server Metadata

SQL Server provides access to structured metadata by using a variety of mechanisms, such as system catalog views, system functions, dynamic management objects, and system stored procedures. In this module, you will learn how to write queries to return system metadata using these mechanisms.

 

Lessons

 

Module 21: Introduction to SQL Server 2014 Database Administration

This module introduces the Microsoft SQL Server 2014 platform. It describes the components, editions, and versions of SQL Server 2014, and the tasks that a database administrator commonly performs for a SQL Server instance.

 

Lessons

 

Module 22: Installing and Configuring SQL Server 2014

This module explains how to assess resource requirements for SQL Server 2014 and how to install it..

 

Lessons

 

Module 23: Working with Databases and Storage

This module describes how data is stored in databases, how to create databases, how to manage database files, and how to move them. Other tasks related to storage, include managing the tempdb database and using fast storage devices to extend the SQL Server buffer pool cache, are also discussed.

 

Lessons

 

Module 24: Planning and Implementing a Backup Strategy

In this module, you will consider how to create a backup strategy that is aligned with organizational needs, and learn how to perform the backup operations required by that strategy.

 

Lessons

 

Module 25: Restoring SQL Server 2014 Databases

In this module, you will see how to restore user and system databases and how to implement point-in-time recovery.

 

Lessons

 

Module 26: Importing and Exporting Data

In this module, you will briefly explore tools and techniques so that you can import and export data to and from SQL Server.

 

Lessons

 

Module 27: Monitoring SQL Server 2014

This module explains how to use three of the most commonly used tools: Activity Monitor, dynamic management views and functions (DMVs and DMFs), and Performance Monitor.

 

Lessons

 

Module 28: Tracing SQL Server Activity

This module describes how to use SQL Server Profiler and SQL Trace stored procedures to capture information about SQL Server, and how to use that information to troubleshoot and optimize SQL Server workloads.

 

Lessons

 

Module 29: Managing SQL Server Security

In this module, you will be learn about the core concepts on which the SQL Server security architecture is based, and how to manage security at the server and database levels.

 

Lessons

 

Module 30: Auditing Data Access and Encrypting Data

This module describes the available options for auditing in SQL Server, how to use and manage the SQL Server audit feature, and how to implement encryption.

 

Lessons

 

Module 31: Performing Ongoing Database Maintenance

This module describes common database maintenance tasks that a DBA must perform, and demonstrates how to automate these tasks using maintenance plans.

 

Lessons

 

Module 32: Automating SQL Server 2014 Management

This module describes how to use SQL Server Agent to automate jobs, how to configure security contexts for jobs, and how to implement multi-server jobs.

 

Lessons

 

Module 33: Monitoring SQL Server 2014 by Using Alerts and Notifications

This module covers the configuration of database mail, alerts, and notifications.

 

Lessons

 

Module 34: Introduction to Data Warehousing

This module provides an introduction to the key components of a data warehousing solution and the high-level considerations you must take into account when you embark on a data warehousing project.

 

Lessons

 

Module 35: Data Warehouse Hardware Considerations

This module discusses considerations for selecting hardware and distributing SQL Server facilities across servers.

 

Lessons

 

Module 36: Designing and Implementing a Data Warehouse

This module describes the key considerations for the logical design of a data warehouse, and then discusses best practices for its physical implementation.

 

Lessons

 

Module 37: Creating an ETL Solution with SSIS

This module discusses considerations for implementing an ETL process, and then focuses on Microsoft SQL Server Integration Services (SSIS) as a platform for building ETL solutions.

 

Lessons

 

Module 38: Implementing Control Flow in an SSIS Package

This module describes how to implement ETL solutions that combine multiple tasks and workflow logic.

 

Lessons

 

Module 39: Debugging and Troubleshooting SSIS Packages

This module describes how you can debug packages to find the cause of errors that occur during execution. It then discusses the logging functionality built into SSIS that you can use to log events for troubleshooting purposes. Finally, the module describes common approaches for handling errors in control flow and data flow.

 

Lessons

 

Module 40: Implementing an Incremental ETL Process

This module describes the techniques you can use to implement an incremental data warehouse refresh process.

 

Lessons

 

Module 41: Enforcing Data Quality

This module introduces Microsoft SQL Server Data Quality Services (DQS), and describes how you can use it to cleanse and deduplicate data.

 

Lessons

 

Module 42: Using Master Data Services

Master Data Services provides a way for organizations to standardize data and improve the quality, consistency, and reliability of the data that guides key business decisions. This module introduces Master Data Services and explains the benefits of using it.

 

Lessons

 

Module 43: Extending SQL Server Integration Services

This module describes the techniques you can use to extend SSIS. The module is not designed to be a comprehensive guide to developing custom SSIS solutions, but to provide an awareness of the fundamental steps required to use custom components and scripts in an ETL process that is based on SSIS.

 

Lessons

 

Module 44: Deploying and Configuring SSIS Packages

In this module, students will learn how to deploy packages and their dependencies to a server, and how to manage and monitor the execution of deployed packages.

 

Lessons

 

Module 45: Consuming Data in a Data Warehouse

This module introduces business intelligence (BI) solutions and describes how you can use a data warehouse as the basis for enterprise and self-service BI.

 

Lessons