MCSE: Data Platform Solutions Expert

MCSE: Data Platform Solutions Expert

MCSE- Data Platform Solutions Expert

The globally recognized standard for IT professionals
Note This certification will retire on March 31, 2017. When the certification retires, all individuals whose transcripts list this certification as active on or after September 26, 2016, will retain the active status.
Find out about the replacement certification, MCSE: Data Management and Analytics.
Demonstrate your broad skill sets in SQL Server administration and in building enterprise-scale data solutions both on-premises and in cloud environments.
Earning an MCSE: Data Platform certification will qualify you for such jobs as database analyst and database designer.


Course name


 Developing Microsoft SQL Server 2012 Databases

About this Course

This 5-day instructor-led 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. 

Course Objectives

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
Course Modules
Module 1: Introduction to SQL Server 2012 and its Toolset
Module 2: Working with Data Types
Module 3: Designing and Implementing Tables
Module 4: Ensuring Data Integrity through Constraints
Module 5: Planning for SQL Server 2012 Indexing
Module 6: Implementing Table Structures in SQL Server 2012
Module 7: Reading SQL Server 2012 Execution Plans
Module 8: Improving Performance through Nonclustered Indexes
Module 9: Designing and Implementing Views
Module 10: Designing and Implementing Stored Procedures
Module 11: Merging Data and Passing Tables
Module 12: Designing and Implementing User-Defined Functions
Module 13: Creating Highly Concurrent SQL Server 2012 Applications
Module 14: Handling Errors in T-SQL Code
Module 15: Responding to Data Manipulation via Triggers
Module 16: Implementing Managed Code in SQL Server 2012
Module 17: Storing XML Data in SQL Server 2012
Module 18: Querying XML Data in SQL Server
Module 19: Working with SQL Server 2012 Spatial Data
Module 20: Working with Full-Text Indexes and Queries

Designing Database Solutions for Microsoft SQL Server 2012

About 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

Course objectives
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

Course Modules

Module 1: Designing a Database Server Infrastructure
Module 2: Designing a Logical Database Schema
Module 3: Designing a Physical Database Implementation
Module 4: Designing a Database Solution for BLOB Data
Module 5: Tuning Database Performance
Module 6: Designing Database Security
Module 7: Using Policy-Based Management
Module 8: Monitoring Server Health
Module 9: Designing a Database Backup Solution
Module 10: Automating Multi-Server Maintenance
Module 11: Managing SQL Server with PowerShell
Module 12: Replicating Data
Module 13: Designing for High Availability

 Back to top


All Rights Reseved ©