Site Powered by nite.org

Data Platform Administrator
Build skills equivalent to the MCSE: Data Platform
  • Credit Hours: 5 Days

  • Format: Virtual Classroom - Instructor-led, Online

  • Professional Certificate: MCSE: Data Platform

  • Recommended for: IT professionals with at least 3 years of hands-on experience implementing and administering database systems, Database administrators, Systems engineers, Systems analysts, and Technical consultants

Data Platform Administrators course prepares students for employment in the Informational Technology industry. This course is an accelerated learning environment that builds skills and techniques for on-premise and cloud environments. Though theory-based lectures and virtual labs, students will obtain the knowledge of database objectives, logical table design, indexing, query plans, and procedure coding. Upon completion of this program, students will possess the skills and knowledge necessary to obtain employment and will be employable by various companies within the industry, as well as the opportunity for career advancement. 

This program prepares students for employment as a Database Administrator. According to the Bureau of Labor Statistics, employment change between 2010 and 2020 will grow 25% greater than the annual average.

What You'll Learn

  • Database objectives, logical table design, indexing, query plans, procedure coding
  • Plan and design a database structure, storage, objects and servers along with how to design and monitor high performance, highly available data solutions, data migration, security and scalability
  • Key topics and concepts 

Who Needs to Attend

  • IT Professionals with at least three years of hands-on experience implementing and administering database systems
  • Database Administrators, Systems Engineers, Systems Analysts and Technical Consultants who have completed the MCSA: SQL Server 2012 certification or have equivalent knowledge

Prerequisites

  • MCSA: SQL Server 2012 certification or equivalent knowledge
  • Three years of hands-on experience working in a SQL Server 2008 or SQL Server 2012 environment
  • Basic knowledge of the Microsoft Windows operation system and its core functionality

Course Outline

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

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

Labs

Lab 1: SQL Server and its Toolset

Lab 2: Work with Data Types

Lab 3: Design and Implement Tables

Lab 4: Ensure Data Integrity through Constraints

Lab 5: Plan for SQL Server Indexing

Lab 6: Implement Table Structures in SQL Server

Lab 7: Read SQL Server Execution Plans

Lab 8: Improve Performance through Non-Clustered Indexes

Lab 9: Design and Implement Views

Lab 10: Design and Implement Stored Procedures

Lab 11: Pass Tables and Merge Data

Lab 12: Design and Implement User-Defined Functions

Lab 13: Create Highly Concurrent SQL Server Applications

Lab 14: Handle Errors in T-SQL Code

Lab 15: Respond to Data Manipulation via Triggers

Lab 16: Design and Implement Views - CLR Lab 17: Store XML Data in SQL Server

Lab 18: Query XML Data in SQL Server

Lab 19: Work with SQL Server Spatial Data

Lab 20: Work with Full-Text Indexes and Queries

Lab 1: Plan Database Server Consolidation

Lab 2: Design a Logical Database Schema

Lab 3: Design a Physical Database Implementation

Lab 4: Implement a Solution for Storing Data Files

Lab 5: Use Indexes and Plan Guides

Lab 6: Plan and Implement Security

Lab 7: Plan Policy-Based Management

Lab 8: Monitor Server Health

Lab 9: Plan and Implement a Backup Strategy

Lab 10: Automate Multi-Server Maintenance

Lab 11: Manage SQL Server with Windows PowerShell

Lab 12: Plan and Implement SQL Server Replication

Lab 13: Implement High Availability

Purchase this Course

$3695.00

Online