• Online, Self-Paced
Course Description

Microsoft SQL Server 2014 builds from version 2012 to provide optimal performance for applications using In-Memory OLTP, which is a memory-optimized database engine integrated into the SQL Server engine. SQL Server 2014 also provides the ability to create hybrid platform and cloud scenarios by enabling cloud backups and recoveries to decrease on premise costs for storage. This course will discuss and demonstrate the objectives for SQL Server 2014 for the 70-464 Microsoft SQL Server: Developing Databases certification exam, this will include the In-Memory OLTP feature and how to write scripts to automate backups to the cloud. It will also demonstrate other database development tasks, such as designing and creating database objects and programming objects and how to optimize and troubleshoot queries.

Learning Objectives

In-Memory OLTP and Automate Backups

  • start the course
  • create in-memory OLTP tables
  • troubleshoot In-Memory OLTP issues
  • monitor In-Memory OLTP issues
  • create scripts to automate backups

Managing Tables, Indexes, and Constraints

  • create calculated columns in tables
  • implement cascading deletes
  • create indexed views
  • create a aligned index on a partitioned table
  • create XML selective indexes

Developing and Design Database Objects

  • manage transactions in a given scenario
  • implement error handling using TRY, CATCH, and THROW
  • design for maximize concurrency and transaction isolation
  • design index locking properties and transactions to minimize locking
  • design query paging, including OFFSET and FETCH
  • create scripts that use SET based operations

Optimizing Queries and Security

  • create a plan guide
  • optimize queries for better performance
  • manage contention and auto growth in a database
  • implement Resource Governor to manage resources in a database
  • review Extended Event logs and optimize Extended Event session settings
  • use Activity Monitor
  • integrate performance monitor data with SQL Traces

Framework Connections

The materials within this course focus on the Knowledge Skills and Abilities (KSAs) identified within the Specialty Areas listed below. Click to view Specialty Area details within the interactive National Cybersecurity Workforce Framework.