Demonstrate your broad skill sets in building and administrating enterprise-scale data solutions both on-premises and in cloud environments.
The Academy’s MCSE SQL Server 2012 Data Platform Boot Camp is a combination of the following official Microsoft courses:
Querying Microsoft SQL Server 2012: 10774 (exam 70-461)
Administering Microsoft SQL Server 2012 Databases: 10775 (exam 70-462)
Implementing a Data Warehouse with Microsoft SQL Server 2012: 10777 (exam 70-463)
Developing Microsoft SQL Server 2012 Databases: 10776 (exam 70-464)
Designing Database Solutions for SQL Server 2012: 20465 (exam 70-465)
This MCSE certification requires you to show continued ability to perform in your chosen solution area by completing a recertification exam every three years.
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.
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