MCSE: SQL 2012 Data Platform Boot Camp
Click Here to View Schedules & Pricing and to Enroll Online
Duration: 14 Days
Demonstrate your broad skill sets in building and administrating enterprise-scale data solutions both on-premises and in cloud environments. This 14-day boot camp provides students with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2012. This course is the foundation for all SQL Server-related disciplines; namely, Database Administration, Database Development and Business Intelligence. This course also provides students with the knowledge and skills to maintain a Microsoft SQL Server 2012 database. The course focuses on teaching individuals how to use SQL Server 2012 product features and tools related to maintaining a database. Data warehousing is a solution organizations use to centralize business data for reporting and analysis. This course focuses on teaching individuals how to create a data warehouse with SQL Server 2012, implement ETL with SQL Server Integration Services, and validate and cleanse data with SQL Server Data Quality Services and SQL Server Master Data Services. This course introduces SQL Server 2012 and describes logical table design, indexing and query plans. It also focusses on the creation of database objects including views, stored procedures, along with parameters, and functions. Other common aspects of procedure coding, such as transactions, concurrency, error handling, triggers, and SQL CLR are also covered in this course. This course describes how to design and monitor high performance, highly available data solutions with SQL Server 2012. This course focuses on creating plans and designs for database structure, storage, objects, and servers. Students will have the opportunity to practices hands-on skills and design tasks in a virtual lab environment and will learn about topics such as data compression, high availability, data migration, security, and scalability.
Before attending this course, students must have:
•Working knowledge of relational databases.
•Basic knowledge of the Microsoft Windows operating system and its core functionality.
•Basic understanding of virtualization technology (Classroom labs utilize virtual machines)
•Basic knowledge of the Microsoft Windows operating system and its core functionality.
•Some experience with database design.
•Designing a normalized database.
•Creating tables and relationships.
•Some exposure to basic programming constructs (such as looping and branching).
•An awareness of key business priorities such as revenue, profitability, and financial accounting is desirable.
•Knowledge of writing T-SQL queries.
•Knowledge of basic relational database concepts.
•Experience with Transact-SQL
•Familiarity with SQL Server 2012 components and tools
•Familiarity with objects in a SQL Server database
•Familiarity with enterprise Windows network and security architecture
- Authorized Courseware
- Intensive Hands on Skills Development with an Experienced Subject Matter Expert
- Hands on practice on real Servers and extended lab support 1.800.482.3172
- Examination Vouchers & Onsite Certification Testing
- Academy Code of Honor: Test Pass Guarantee
- Optional: Package for Hotel Accommodations, Lunch and Transportation
The Academy is proud to be the #1 Microsoft Gold Certified Partner for Learning Solutions (CPLS) in Florida.
Introduction to Microsoft SQL Server 2012
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.
Getting Started with SQL Azure
This module introduces you to the concepts of SQL Azure. If the virtual machines in your classroom are able to connect to the internet and you have a Windows Azure account you may be able to connect to your Azure server and database. Many of the labs in the rest of this course are enabled for you to perform the lab while connected to your own Azure database in the cloud.
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.
Writing SELECT Queries
This module introduces the fundamentals of the SELECT statement, focusing on queries against a single table.
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.
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.
Working with SQL Server 2012 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.
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.
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.
Improving Query Performance
This module introduces the concepts of system resource usage and the performance impact of querying SQL Server 2012. It will cover, at a high level, the use of indexes in SQL Server, the use of execution plans in SQL Server Management Studio, and the use of SET options to view system resource usage when executing queries. It will also compare set-based operations with cursor-based operations.
Working with Databases
This module describes how data is stored in databases, how to create databases, and how to move databases either within a server or between servers.
Understanding SQL Server 2012 Recovery Models
This module describes the concept of the transaction log and SQL Server recovery models. It introduces the different backup strategies available with SQL Server.
Backup of SQL Server 2012 Databases
This module describes SQL Server Backup and the backup types.
Authenticating and Authorizing Users
This module covers SQL Server security models, logins and users.
Assigning Server and Database Roles
This module covers fixed server roles, user-defined server roles, fixed database roles and user-defined database roles.
Auditing SQL Server Environments
This module covers SQL Server Audit.
Automating SQL Server 2012 Management
This module covers SQL Server Agent, jobs and job history.
Configuring Security for SQL Server Agent
This module covers SQL Server agent security, proxy accounts and credentials.
Performing Ongoing Database Maintenance
This module covers database maintenance plans.
Monitoring SQL Server 2012
This module introduces DMVs and the configuration of data collection.
Managing Multiple Servers
This module covers Central Management Servers and Multi-Server queries, Virtualization of SQL Server and Data-Tier Applications.
Troubleshooting Common SQL Server 2012 Administrative Issues
This module covers common issues that require troubleshooting and gives guidance on where to start looking for solutions.
Data Warehouse Hardware
This module describes the characteristics of typical data warehouse workloads, and explains how you can use reference architectures and data warehouse appliances to ensure you build the system that is right for your organization.
Designing and Implementing a Data Warehouse
In this module, you will learn how to implement the logical and physical architecture of a data warehouse based on industry-proven design principles.
Creating an ETL Solution with SSIS
This module discusses considerations for implementing an ETL process, and then focuses on SQL Server Integration Services (SSIS) as a platform for building ETL solutions.
Debugging and Troubleshooting SSIS Packages
This module describes how you can debug SQL Server Integration Services (SSIS) packages to find the cause of errors that occur during execution. Then module then covers the logging functionality built into SSIS you can use to log events for troubleshooting purposes. Finally, the module describes common approaches for handling errors in control flow and data flow.
Implementing an Incremental ETL Process
This module describes the techniques you can use to implement an incremental data warehouse refresh process.
Incorporating Data from the Cloud into a Data Warehouse
In this module, you will learn about how you can use cloud computing in your data warehouse infrastructure and learn about the tools and services available from the Microsoft Azure Marketplace.
Extending SQL Server Integration Services
This module describes the techniques you can use to extend SQL Server Integration Services (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.
Ensuring Data Integrity through Constraints
This module explains how to enforce data integrity, and implement domain integrity to maintain high quality data. Also it focusses on implementing Entity and Referential Integrity.
Planning for SQL Server 2012 Indexing
This module explains the core indexing concepts and effectiveness of each data type commonly used in indexes.
Implementing Table Structures in SQL Server 2012
This module explains how tables can be structured in SQL Server databases. Also it focusses on designing and working with clustered indexes.
Reading SQL Server 2012 Execution Plans
This module explains how to design additional indexes. Also it focusses on how to read and interpret execution plans.
Designing and Implementing Views
This module introduces Views, and explains how to create and manage Views. Also it focusses on the performance consideration for Views.
Designing and Implementing Stored Procedures
This module describes the potential advantages of the use of stored procedures along with guidelines on creating them.
Merging Data and Passing Tables
This module reviews the techniques that provide the ability to process sets of data rather than individual rows. Also it focusses on how these techniques can be used in combination with TABLE parameter types to minimize the number of required stored procedure calls in typical applications.
Designing and Implementing User-Defined Functions
This module explains how to design and implement user-defined functions that enforce business rules or data consistency, and modify and maintain existing functions written by other developers.
Creating Highly Concurrent SQL Server 2012 Applications
This module explains how to use transactions and the SQL Server locking mechanisms to meet the performance and data integrity requirements of your applications.
Implementing Managed Code in SQL Server 2012
This module explains how to use CLR integrated code to create user-defined database objects that are managed by the .NET Framework.
Storing XML Data in SQL Server 2012
This module introduces XML and shows how XML data can be stored within SQL Server.
Querying XML Data in SQL Server
This module shows how XML data can be queried, including queries written in a language called XQuery.
Designing a Database Server Infrastructure
This module explains how to design an appropriate database server infrastructure for a given business application scenario - including how to decide between on-premise, cloud-based, and hybrid database servers, hardware capacity planning, considerations for storage hardware, and strategies for consolidating database server hardware.
Designing a Logical Database Schema
This module explains how to design a logical schema for a database based on application requirements. This includes planning the level of normalization, and schema and table design, and the use of views.
Designing a Physical Database Implementation
This modules explains how to design the physical implementation of a database for a given set of requirements. The design will include data files, log files, filegroups, and data partitioning, as well as whether or not to use data compression.
Designing a Database Solution for BLOB Data
This modules discusses how to consider options for including binary large object (BLOB) data in a database design.
Designing Database Security
This module explains the key considerations for designing security for SQL Server instances and databases.
Designing a Database Backup Solution
This module explains how to identify and implement the appropriate backup strategy for a given scenario.
Automating Multi-Server Maintenance
This module explains how to better plan and manage multi-server maintenance and automation.
Managing SQL Server with PowerShell
This module provides an overview of PowerShell and describes the benefits of using PowerShell to manage SQL Server 2012.
At Course Completion:
After completing this course, students will be able to:
•Query: multiple tables, SQL Server metadata
•Use: built-in functions, subqueries, set operators, table expressions, window ranking, offset and aggregate functions
•Execute stored procedures
•Implement error handling and transactions
•Sort and filter data
•Program with T-SQL
•Improve query performance
•Plan and install SQL Server.
•Describes the system databases, the physical structure of databases and the most common configuration options related to them.
•Explain the concept of the transaction log and SQL Server recovery models and implement different backup strategies available with SQL Server.
•Create SQL Server Backups.
•Restore SQL Server databases.
•Use the import/export wizards and explain how they relate to SSIS.
•Work with: SQL Server security models, logins and users; fixed server roles, user-defined server roles, fixed database roles and user-defined database roles; permissions and the assignment of permissions; SQL Server Audit, SQL Server Agent, jobs and job history.
•Implement SQL Server agent security, proxy accounts and credentials.
•Configure database mail, alerts and notifications.
•Create database maintenance plans.
•Work with SQL Profiler and SQL Trace stored procedures.
•Introduce DMVs and the configuration of data collection.
•Work with Central Management Servers and Multi-Server queries, Virtualization of SQL Server and Data-Tier Applications.
•Troubleshoot SQL Server databases.
•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, Control Flow in an SSIS Package, an SSIS solution that supports incremental data warehouse loads and changing data, data cleansing by using Microsoft Data Quality Services, Master Data Services to enforce data integrity
•Debug and Troubleshoot SSIS packages.
•Integrate cloud data into a data warehouse ecosystem infrastructure.
•Extend SSIS with custom scripts and components.
•Deploy and Configure SSIS packages.
•Describe how information workers can consume data from the data warehouse.
•Introduce the entire SQL Server platform and its major tools. It will cover editions, versions, basics of network listeners, and concepts of services and service accounts.
•Determine appropriate data types to be used when designing tables, convert data between data types, and create alias data types.
•Be aware of good design practices regarding SQL Server tables and be able to create tables using T-SQL. (Note: partitioned tables are not covered).
•Implement PRIMARY KEY, FOREIGN KEY, DEFAULT, CHECK and UNIQUE constraints, and investigate cascading FOREIGN KEY constraints.
•Determine appropriate single column and composite indexes strategies.
•Create tables as heaps and tables with clustered indexes. Also consider the design of a table and suggest an appropriate structure.
•Read and interpret details of common elements from execution plans.
•Design effective non-clustered indexes.
•Design and implement views
•Design and implement stored procedures.
•Work with table types, table valued parameters and use the MERGE statement to create stored procedures that update data warehouses.
•Design and implement functions, both scalar and table-valued. (Also describe where they can lead to performance issues).
•Perform basic investigation of a deadlock situation and learn how transaction isolation levels affect application concurrency.
•Use both traditional T-SQL error handling code and structured exception handling.
•Design and implement DML triggers
•Learn appropriate uses for SQL CLR integration and implement an existing .NET assembly within SQL Server.
•Store XML data and schemas in SQL Server.
•Perform basic queries on XML data in SQL Server.
•Work with the GEOGRAPHY and GEOMETRY data types
•Implement and query a full-text index.
•Design an appropriate database server infrastructure for a given business application scenario
•Design a logical schema for a database based on application requirements
•Design the physical implementation of a database for a given set of requirements
•Evaluate options for including binary large object data in a database design
•Plan and manage indexes to optimize performance
•Describe the key considerations for designing security for SQL Server instances and databases
•Plan policy-based management to manage server instances, databases, and other SQL Server 2012 objects more efficiently
•Plan SQL Server health monitoring
•Implement SQL Server health monitoring by using SQL Server Utility
•Identify and implement the appropriate backup strategy for a given scenario
•Plan and manage multi-server maintenance and automation
•Understand the benefits of using PowerShell to manage SQL Server 2012
•Design an optimal replication strategy from a given set of business and technical requirements
•Plan and implement a high availability solution
70-461: Querying Microsoft SQL Server 2012
70-462: Administering Microsoft SQL Server 2012 Databases=
70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012
70-464: Developing Microsoft SQL Server 2012 Databases
70-465: Designing Database Solutions for SQL Server 2012
Academy Code of Honor:
The Academy guarantees that students shall pass all vendor examinations during the training program or may re-attend within one year of the program completion date. Students will only be responsible for accommodations and vendor exam fees.