Course Details
This accelerated learning environment helps you acquire hands-on skills for on-premise and cloud environments and prepare you for the MCSE: Data Platform certification exams. This extended-hours boot camp, led by senior HBFITES instructors, includes targeted lectures using Microsoft Learning recommended content and six-month access to over 30 hands-on remote labs.
This boot camp includes all the certification exam vouchers required for certification, along with one free retake voucher for each exam, if needed. It also includes access to our online study program, providing indispensable certification exam prep.
Our exclusive online study program includes:
- 24×7 chat support by certified mentors: Direct, on-demand access to live, certified experts for customized training support.
- Access to the online Microsoft Press e-Reference Library, including all training kits, exam references, and rapid review books.
- Recommended Microsoft reading list designed by our instructors, including topics and chapters within the Microsoft Press e-Reference library.
- Certification practice exams from Transcender: Industry-best practice exams that provide realistic simulations of the Microsoft certification exams in online and downloadable versions.
- Recorded ‘Power Hours’ for each exam: Online review session covering key exam objectives conducted by our expert instructors.
- On-demand live remote labs: 24×7 on-demand access for six months so there’s no scheduling required.
This course incorporates content from Microsoft Official Courses (MOC):
- Developing Microsoft SQL Server 2012 Databases (M10776)
- Designing Database Solutions for Microsoft SQL Server 2012 (M20465)
You Will Cover the Objectives for these Exams:
- 70-464: Developing Microsoft SQL Server 2012 Databases
- 70-465: Designing Database Solutions for Microsoft SQL Server 2012
Course Agenda
Exam 70-464: Developing Microsoft SQL Server 2012 Databases
1. SQL Server 2012 and Toolset
- SQL Server Platform
- Working with SQL Server Tools
- Configuring SQL Server Services
2. Working Data Types
- Using Data Types
- Converting Data Types
- Specialized Data Types
3. Designing and Implementing Tables
- Designing Tables
- Working with Schemas
- Creating and Altering Tables
4. Ensuring Data Integrity through Constraints
- Enforcing Data Integrity
- Implementing Domain Integrity
- Implementing Entity and Referential Integrity
5. Planning for SQL Server 2012 Indexing
- Core Indexing Concepts
- Data Types and Indexes
- Single Column and Composite Indexes
6. Implementing Table Structures in SQL Server 2012
- SQL Server Table Structures
- Working with Clustered Indexes
- Designing Effective Clustered Indexes
7. Reading SQL Server 2012 Execution Plans
- Execution Plan Core Concepts
- Common Execution Plan Elements
- Working with Execution Plans
8. Improving Performance through Non-Clustered Indexes
- Designing Effective Non-Clustered Indexes
- Implementing Non-Clustered Indexes
- Using the Database Engine Tuning Advisor
9. Designing and Implementing Views
- Views
- Creating and Managing Views
- Performance Considerations for Views
10. Designing and Implementing Stored Procedures
- Stored Procedures
- Working with Stored Procedures
- Implementing Parameterized Stored Procedures
- Controlling Execution Context
11. Merging Data and Passing Tables
- Using the MERGE Statement
- Implementing Table Types
- Using TABLE Types as Parameters
12. Designing and Implementing User-Defined Functions
- Functions
- Designing and Implementing Scalar Functions
- Designing and Implementing Table-Valued Functions
- Implementation Considerations for Functions
- Alternatives to Functions
13. Creating Highly Concurrent SQL Server 2012 Applications
- Transactions
- Locks
- Management of Locking
- Transaction Isolation Levels
14. Handling Errors in T-SQL Code
- T-SQL Error Handling
- Implementing T-SQL Error Handling
- Implementing Structured Exception Handling
15. Responding to Data Manipulation via Triggers
- Designing DML Triggers
- Implementing DML Triggers
- Advanced Trigger Concepts
16. Implementing Managed Code in SQL Server 2012
- SQL CLR Integration
- Importing and Configuring Assemblies
- Implementing SQL CLR Integration
17. Storing XML Data in SQL Server 2012
- XML and XML Schemas
- Storing XML Data and Schemasin SQL Server
- Implementing the XML Data Type
18. Querying XML Data in SQL Server
- Using the T-SQL for XML Statement
- Starting with XQuery
- Shredding XML
19. Working with SQL Server 2012 Spatial Data
- Spatial Data
- Working with SQL Server Spatial Data Types
- Using Spatial Data in Applications
20. Working with Full-Text Indexes and Queries
- Implementing a Full-Text Index
- Implementing a Stoplist
- Creating a Stored Procedure to Implement a Full-Text Search
Exam 70-465: Designing Database Solutions for Microsoft SQL Server 2012
1. Designing a Database Server Infrastructure
- Planning a Database Server Infrastructure
- Planning Server Hardware
- Considerations for Database Server Consolidation
- Managing Server Resources in a Consolidated Database Infrastructure
2. Designing a Logical Database Schema
- Relational Database Design Techniques
- Planning Schemas and Tables
3. Designing a Physical Database Implementation
- Planning Files and Filegroups
- Planning Data Partitioning and Compression
4. Incorporating Data Files into Databases
- Considerations for Working with Data Files in SQL Server 2012
- Implementing FileStream and FileTables
- Searching Data Files
5. Tuning Database Performance
- Optimizing Query Performance with Indexes
- Managing Query Plans
- Performance Monitoring
6. Designing Database Security
- Security Planning
- Plan Security
- Contained Databases
- Protecting Data with Encryption
7. Policy-Based Management
- Basics of Policy-Based Management
- Planning and Implementing Policy-Based Management
8. Monitoring Server Health
- Basics of Server Health Monitoring
- SQL Server Utility
9. Designing a Database Backup Solution
- SQL Server Backup and Restore
- Planning a Recovery Strategy
10. Automating Multi-Server Maintenance
- Maintenance Automation
- Managing Multiple Servers
11. Managing SQL Server with PowerShell
- Windows PowerShell
- Scripting with Windows PowerShell
12. Replicating Data
- SQL Server Replication
- Planning Replication
13. Designing for High Availability
- High Availability in SQL Server 2012
- AlwaysOn Availability Groups