MCSA: SQL Server 2012 Boot Camp
Click Here to View Schedules & Pricing and to Enroll Online
Duration: 5 Days
The Academy of South Florida offers the fastest, most effective method to learn the industry-standard, fundamental features of Microsoft SQL Server that have made Microsoft's premiere database solution a worldwide leader in data-driven business and application infrastructure.
With this SQL Server 2012 course, you will gain valuable insight into the new, powerful capabilities that SQL Server 2012 adds to a database developer and administrator's solutions tool box.
MCSA: Microsoft SQL Server 2012 certification covers everything from designing, installing, configuring, and maintaining a Microsoft Database environment, to implementing and troubleshooting database objects. This program covers all material and exams necessary to achieve an MCSA in Microsoft SQL Server 2012.
This 7-day boot camp is a combination of the following official Microsoft courses:
• 20461: Querying Microsoft SQL Server 2012
• 20462C: Administering Microsoft SQL Server 2012 Databases
• 20463C: Implementing a Data Warehouse with Microsoft SQL Server 2012
This course 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 helps people prepare for exam 70-461. This course 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. Also this course helps you prepare for the Exam 70-462. Data warehousing is a solution organizations use to centralize business data for reporting and analysis. This instructor-led 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 helps people prepare for exam 70-463.
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)
10774: Querying Microsoft SQL Server 2012
Module 1: 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.
Module 2: 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.
Module 3: 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.
Module 4: Writing SELECT Queries
This module introduces the fundamentals of the SELECT statement, focusing on queries against a single table.
Module 5: 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.
Module 6: 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.
Module 7: 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.
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.
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.
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.
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.
Module 12: Using Set Operators
This module introduces operations involving multiple sets of data. It will cover the use of the UNION, UNION ALL, APPLY, CROSS APPLY, OUTER APPLY operators as well as the EXCEPT and INTERSECTS operators.
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.
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 2012.
Module 15: Querying SQL Server Metadata
This module introduces the use of SQL Server system objects in T-SQL queries. It will cover the use of system catalog views, system stored procedures, system functions, and dynamic management objects.
Module 16: Executing 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.
Module 17: 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.
Module 18: 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.
Module 19: 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.
Module 20: 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
10775: Administering Microsoft SQL Server 2012 Databases
Module 1: Introduction to SQL Server 2012 and its Toolset
This module introduces the entire SQL Server platform and its major tools. It covers editions, versions, basics of network listeners, and concepts of services and service accounts.
Module 2: Preparing Systems for SQL Server 2012
This module covers planning for an installation related to SQL Server I/O requirements, 32 bit vs 64 bit, memory configuration options and I/O subsystem pre-installation checks using SQLIOSim and SQLIO.
Module 3: Installing and Configuring SQL Server 2012
This module details installing and configuring SQL Server.
Module 4: 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.
Module 5: 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.
Module 6: Backup of SQL Server 2012 Databases
This module describes SQL Server Backup and the backup types.
Module 7: Restoring SQL Server 2012 Databases
This module describes the restoration of databases.
Module 8: Importing and Exporting Data
This module covers the use of the import/export wizards and explains how they relate to SSIS. Also introduces BCP.
Module 9: Authenticating and Authorizing Users
This module covers SQL Server security models, logins and users.
Module 10: Assigning Server and Database Roles
This module covers fixed server roles, user-defined server roles, fixed database roles and user-defined database roles.
Module 11: Authorizing Users to Access Resources
This module covers permissions and the assignment of permissions.
Module 12: Auditing SQL Server Environments
This module covers SQL Server Audit.
Module 13: Automating SQL Server 2012 Management
This module covers SQL Server Agent, jobs and job history.
Module 14: Configuring Security for SQL Server Agent
This module covers SQL Server agent security, proxy accounts and credentials.
Module 15: Monitoring SQL Server 2012 with Alerts and Notifications
This module covers the configuration of database mail, alerts and notifications.
Module 16: Performing Ongoing Database Maintenance
This module covers database maintenance plans.
Module 17: Tracing Access to SQL Server 2012
This module covers SQL Profiler and SQL Trace stored procedures.
Module 19: Managing Multiple Servers
This module covers Central Management Servers and Multi-Server queries, Virtualization of SQL Server and Data-Tier Applications.
Module 20: 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.
10777: Implementing a Data Warehouse with Microsoft SQL Server 2012
Module 1: 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 starting a data warehousing project.
Module 2: 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.
Module 3: 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.
Module 4: 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.
Module 5: Implementing Control Flow in an SSIS Package
Control flow in SQL Server Integration Services packages enables you to implement complex ETL solutions that combine multiple tasks and workflow logic. This module covers how to implement control flow, and design robust ETL processes for a data warehousing solution that coordinate data flow operations with other automated tasks.
Module 6: 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.
Module 7: Implementing an Incremental ETL Process
This module describes the techniques you can use to implement an incremental data warehouse refresh process.
Module 8: 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.
Module 9: Enforcing Data Quality
Ensuring the high quality of data is essential if the results of data analysis are to be trusted. This module explains how to use the SQL Server 2012 Data Quality Services (DQS) to provide a computer assisted process for cleansing data values and identifying and removing duplicate data entities.
Module 10: Using Master Data Services
This module introduces Master Data Services and explains the benefits of using it in a data warehousing context. The module also describes the key configuration options for Master Data Services, and explains how to import and export data. Finally, the module explains how to apply rules that help to preserve data integrity, and introduces the new Master Data Services Add-in for Excel.
Module 11: 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.
Module 12: Deploying and Configuring SSIS Packages
SQL Server Integration Services provides tools that make it easy to deploy packages to another computer. The deployment tools also manage any dependencies, such as configurations and files that the package needs. In this module, you will learn how to use these tools to install packages and their dependencies on a target computer.
Module 13: Consuming Data in a Data Warehouse
This module introduces Business Intelligence (BI), describes the components of SQL Server that you can use to create a BI solution, and the client tools that users can use to create reports and analyze data.
At Course Completion:
After completing this course, students will be able to:
• Write SELECT queries
• Query multiple tables
• Use built-in functions
• Use subqueries
• Execute stored procedures
• Use set operators
• Implement error handling
• Implement transactions
• Use table expressions
• Sort and filter data
• Use window ranking, offset and aggregate functions
• Query SQL Server metadata
• 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.
• Work with fixed server roles, user-defined server roles, fixed database roles and user-defined database roles.
• Work with permissions and the assignment of permissions.
• Work with SQL Server Audit.
• Work with 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.
• Implement Control Flow in an SSIS Package.
• Debug and Troubleshoot SSIS packages.
• Implement an SSIS solution that supports incremental data warehouse loads and changing data.
• Integrate cloud data into a data warehouse ecosystem infrastructure.
• 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 information workers can consume data from the data warehouse.
- 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.
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.