Administering Microsoft SQL Server 2014 Databases (Course Code 20462)   Overview

This 5 day course provides students with the knowledge and skills to maintain a Microsoft SQL Server 2014 database. The course focuses on teaching individuals how to use SQL Server 2014 product features and tools related to maintaining a database.

Note: This course is designed for customers who are interested in learning SQL Server 2012 or SQL Server 2014. It covers the new features in SQL Server 2014, but also the important capabilities across the SQL Server data platform.

Apply Today

Instructor Led Learning

Duration: 5 Days
Registration Open Now!

Video Learning

Duration: 5 Days
Registration Open Now!

What you will learn

  • Authenticate and authorize users
  • Assign server and database roles
  • Authorize users to access resources
  • Protect data with encryption and auditing
  • Describe recovery models and backup strategies
  • Backup SQL Server databases
  • Restore SQL Server databases
  • Automate database management
  • Configure security for the SQL Server agent
  • Manage alerts and notifications
  • Managing SQL Server using PowerShell
  • Trace access to SQL Server
  • Monitor a SQL Server infrastructure
  • Troubleshoot a SQL Server infrastructure
  • Import and export data

Before attending this course, students must have:

  • Basic knowledge of the Microsoft Windows operating system and its core functionality.
  • Working knowledge of Transact-SQL.
  • Working knowledge of relational databases.
  • Some experience with database design.

FULL COURSE OUTLINE

Module 1: Installing SQL Server 2014
This module introduces the SQL Server 2014 platform. It describes the components, editions, and versions of SQL Server 2014, SQL Server services and configuration options, and service accounts. The module also explains how to assess resource requirements for SQL Server 2014 and how to install SQL Server 2014.
Lessons

  • Introduction to the SQL Server Platform
  • SQL Server Services and Configuration Options
  • Considerations for Installing SQL Server
  • Installing SQL Server 2014

Module 2: Working with Databases
This module describes he preinstalled system databases, the physical structure of databases, and the most common configuration options related to them.
Lessons

  • Overview of SQL Server Databases
  • Creating Databases
  • Using Database Files and Filegroups
  • Moving Database Files
  • Buffer Pool Extension

Module 3: SQL Server 2014 Recovery Models
In this module, you will consider how to create a strategy that is aligned with organizational needs and see how the transaction logging capabilities of Microsoft SQL Server can help you to achieve an appropriate outcome.
Lessons

  • Backup Strategies
  • Understanding SQL Server Transaction Logging
  • Planning a SQL Server Backup Strategy

Module 4: Backup of SQL Server 2014 Databases
In this module, you will explore the key backup types in more depth and learn how to implement the backups. You will also see how to apply options that affect the way that the backups work.
Lessons

  • Backing up Databases and Transaction Logs
  • Managing Database Backups
  • Working with Backup Options

Module 5: Restoring SQL Server 2014 Databases
In this module, you will see how to restore user and system databases and how to implement point-in-time recovery.
Lessons

  • Understanding the Restore Process
  • Restoring Databases
  • Working with Point-in-Time Recovery
  • Restoring System Databases and Individual Files

Module 6: Importing and Exporting Data
This module covers the use of the import/export wizards and explains how they relate to SSIS. It also introduces BCP and BULK INSERT.
Lessons

  • Transferring Data To/From SQL Server
  • Importing and Exporting Table Data
  • Inserting Data in Bulk
  • Deploying and Upgrading Data-tier Applications

Module 7: Authenticating and Authorizing Users
This module covers SQL Server security models, logins, users, and contained databases.
Lessons

  • Authenticating Connections to SQL Server
  • Authorizing Logins to Access Databases
  • Authorization Across Servers
  • Partially Contained Databases

Module 8: Assigning Server and Database Roles
This module covers fixed server roles, user-defined server roles, fixed database roles, and user-defined database roles.
Lessons

  • Working with Server Roles
  • Working with Fixed Database Roles
  • Creating User-defined Database Roles

Module 9: Authorizing Users to Access Resources
This module covers permissions and the assignment of permissions.
Lessons

  • Authorizing User Access to Objects
  • Authorizing Users to Execute Code
  • Configuring Permissions at the Schema Level

Module 10: Protecting Data by Using Encryption and Auditing
This module describes the available options for auditing in SQL Server, how to use and manage the SQL Server audit feature, and how to implement encryption.
Lessons

  • Options for Auditing Data Access in SQL Server
  • Implementing SQL Server Audit
  • Managing SQL Server Audit
  • Protecting Data by Using Encryption

Module 11: Automating SQL Server 2014 Management
This module describes how to use SQL Server Agent for automation. It also explains the benefits of using master and target servers to centralize the administration of automation.
Lessons

  • Automating SQL Server Management
  • Working with SQL Server Agent
  • Managing SQL Server Agent Jobs
  • Multi-Server Management

Module 12: Configuring Security for SQL Server Agent
This module describes the considerations for SQL Server Agent security, including proxy accounts and credentials.
Lessons

  • Understanding SQL Server Agent Security
  • Configuring Credentials
  • Configuring Proxy Server Accounts

Module 13: Monitoring SQL Server 2014 by Using Alerts and Notifications
This module covers the configuration of database mail, alerts, and notifications.
Lessons

  • Configuring Database Mail
  • Monitoring SQL Server Errors
  • Configuring Operators, Alerts, and Notifications

Module 14: Performing Ongoing Database Maintenance
This module covers database maintenance plans.
Lessons

  • Ensuring Database Integrity
  • Maintaining Indexes
  • Automating Routine Database Maintenance

Module 15: Tracing Access to SQL Server 2014
This module describes how to use SQL Server Profiler and SQL Trace stored procedures to capture information about SQL Server. It also describes how to use Distributed Replay to capture trace information from multiple servers and how to monitor locking.
Lessons

  • Capturing Activity by Using SQL Server Profiler
  • Improving Performance by Using the Database Engine Tuning Advisor
  • Working with Tracing Options
  • Distributed Replay
  • Monitoring Locks

Module 16: Monitoring SQL Server 2014
This module explains how to use Distributed Management Views to monitor SQL Server. It also describes how to configure data collection and the SQL Server Utility.
Lessons

  • Monitoring Activity
  • Capturing and Managing Performance Data
  • Analyzing Collected Performance Data
  • SQL Server Utility

Join Over 10,000 Students that have studied with MasterGrade IT Now

Become Part of MasterGrade IT to Further Your Career.