20462: Administering Microsoft SQL Server Databases Training in St. Joseph

Enroll in or hire us to teach our 20462: Administering Microsoft SQL Server Databases class in St. Joseph, Missouri by calling us @303.377.6176. Like all HSG classes, 20462: Administering Microsoft SQL Server Databases may be offered either onsite or via instructor led virtual training. Consider looking at our public training schedule to see if it is scheduled: Public Training Classes
Provided there are enough attendees, 20462: Administering Microsoft SQL Server Databases may be taught at one of our local training facilities.
We offer private customized training for groups of 3 or more attendees.

Course Description

 
This five-day instructor-led 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.
Course Length: 5 Days
Course Tuition: $2090 (US)

Prerequisites

This course requires that you meet the following prerequisites: 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.

Course Outline

 

Module 1: Introduction to SQL Server 2014 Database Administration
This module introduces the Microsoft SQL Server 2014 platform. It describes the components, editions, and versions of SQL Server 2014, and the tasks that a database administrator commonly performs for a SQL Server instance.

Lessons

    Database Administration Overview
    Introduction to the SQL Server Platform
    Database Management Tools and Techniques

Lab: Using SQL Server Administrative Tools

    Using SQL Server Management Studio
    Using the sqlcmd Utility
    Using Windows PowerShell with SQL Server

After completing this module, you will be able to:

    Describe the SQL Server platform.
    Describe common database administration tasks.
    Use SQL Server administration tools.

Module 2: Installing and Configuring SQL Server 2014
This module explains how to assess resource requirements for SQL Server 2014 and how to install it.

Lessons

    Planning SQL Server Installation
    Installing SQL Server 2014
    Post-Installation Configuration

Lab: Installing SQL Server 2014

    Preparing to Install SQL Server
    Installing SQL Server
    Performing Post-Installation Configuration

After completing this module, you will be able to:

    Plan a SQL Server 2014 installation.
    Install SQL Server 2014.
    Perform post-installation configuration tasks.

Module 3: Working with Databases and Storage
This module describes how data is stored in databases, how to create databases, how to manage database files, and how to move them. Other tasks related to storage, include managing the tempdb database and using fast storage devices to extend the SQL Server buffer pool cache, are also discussed.

Lessons

    Introduction to Data Storage with SQL Server
    Managing Storage for System Databases
    Managing Storage for User Databases
    Moving Database Files
    Configuring the Buffer Pool Extension

Lab: Managing Database Storage

    Configuring tempdb Storage
    Creating Databases
    Attaching a Database

After completing this module, you will be able to:

    Describe how SQL Server stores data.
    Manage storage for system databases.
    Manage storage for user databases.
    Move database files.
    Configure the buffer pool extension.

Module 4: Planning and Implementing a Backup Strategy
In this module, you will consider how to create a backup strategy that is aligned with organizational needs, and learn how to perform the backup operations required by that strategy.

Lessons

    Understanding SQL Server Recovery Models
    Planning a Backup Strategy
    Backing up Databases and Transaction Logs
    Using Backup Options
    Ensuring Backup Reliability

Lab: Backing Up SQL Server Databases

    Backing Up Database
    Performing Database, Differential, and Transaction Log Backups
    Performing a Partial Backup

After completing this module, you will be able to:

    Describe how database transaction logs function, and how they affect database recovery.
    Plan a backup strategy for a SQL Server database.
    Back up databases and transactions logs.
    Perform copy-only, compressed, and encrypted backups.
    Maximize backup reliability.

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
    Advanced Restore Scenarios
    Working with Point-in-Time Recovery

Lab: Restoring SQL Server Databases

    Restoring a Database Backup
    Restoring Database, Differential, and Transaction Log Backups
    Performing a Piecemeal Restore

After completing this module, you will be able to:

    Explain the restore process.
    Restore databases.
    Perform advanced restore operations.
    Perform a point-in-time recovery.

Module 6: Importing and Exporting Data
In this module, you will briefly explore tools and techniques so that you can import and export data to and from SQL Server.

Lessons

    Introduction to Transferring Data
    Importing and Exporting Table Data
    Copying or Moving a Database

Lab: Importing and Exporting Data

    Using the SQL Server Import and Export Wizard
    Using the bcp Utility
    Using the BULK INSERT Statement
    Using the OPENROWSET Function

After completing this module, you will be able to:

    Describe tools and techniques for transferring data.
    Import and export data.
    Copy or move a database.

 
Module 7: Monitoring SQL Server 2014
This module explains how to use three of the most commonly used tools: Activity Monitor, dynamic management views and functions (DMVs and DMFs), and Performance Monitor.

Lessons

    Introduction to Monitoring SQL Server
    Dynamic Management Views and Functions
    Performance Monitor

Lab: Monitoring SQL Server 2014

    Collecting Baseline Metrics
    Monitoring a Workload

After completing this module, you will be able to:

    Describe considerations for monitoring SQL Server and use Activity Monitor.
    Use dynamic management views and functions to monitor SQL Server.
    Use Performance Monitor to monitor SQL Server.

 
Module 8: Tracing SQL Server Activity
This module describes how to use SQL Server Profiler and SQL Trace stored procedures to capture information about SQL Server, and how to use that information to troubleshoot and optimize SQL Server workloads.

Lessons

    Tracing SQL Server Workload Activity
    Using Traces

Lab: Tracing SQL Server Workload Activity

    Capturing a Trace in SQL Server Profiler
    Generating Database Tuning Recommendations
    Using SQL Trace

After completing this module, you will be able to:

    Trace activity in SQL Server.
    Use captured traces to test, troubleshoot, and optimize database performance.

 
Module 9: Managing SQL Server Security
In this module, you will be learn about the core concepts on which the SQL Server security architecture is based, and how to manage security at the server and database levels.

Lessons

    Introduction to SQL Server Security
    Managing Server-Level Security
    Managing Database-Level Principals
    Managing Database Permissions

Lab: Managing SQL Server Security

    Managing Server-Level Security
    Managing Database-Level Security
    Testing Database Access

After completing this module, you will be able to:

    Describe core security concepts in the SQL Server security architecture.
    Manage server-level security.
    Manage database-level security principals.
    Manage database permissions.

 
Module 10: Auditing Data Access and Encrypting Data
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

    Auditing Data Access in SQL Server
    Implementing SQL Server Audit
    Encrypting Databases

Lab: Auditing Data Access and Encrypting Data

    Implementing Auditing
    Implementing Transparent Database Encryption

After completing this module, you will be able to:

    Describe options for auditing data access.
    Implement SQL Server audit.
    Manage SQL Server audit.
    Implement Transparent Data Encryption.

Module 11: Performing Ongoing Database Maintenance
This module describes common database maintenance tasks that a DBA must perform, and demonstrates how to automate these tasks using maintenance plans.

Lessons

    Ensuring Database Integrity
    Maintaining Indexes
    Automating Routine Database Maintenance

Lab: Performing Ongoing Database Maintenance

    Managing Database Integrity
    Managing Index Fragmentation
    Implementing a Maintenance Plan

After completing this module, you will be able to:

    Ensure database integrity by using DBCC CHECKDB.
    Maintain indexes.
    Configure Database Maintenance Plans.

Module 12: Automating SQL Server 2014 Management
This module describes how to use SQL Server Agent to automate jobs, how to configure security contexts for jobs, and how to implement multi-server jobs.

Lessons

    Automating SQL Server Management
    Implementing SQL Server Agent Jobs
    Managing SQL Server Agent Jobs
    Managing Job Step Security Contexts
    Managing Jobs on Multiple Servers

Lab: Automating SQL Server Management

    Creating a Job
    Scheduling a Job
    Configuring Job Step Security Contexts

After completing this module, you will be able to:

    Describe methods for automating SQL Server management.
    Create jobs, job step types, and schedules.
    Manage SQL Server Agent jobs.
    Configure job security contexts.
    Configure master and target servers.

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

Lessons

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

Lab: Monitoring SQL Server by Using Alerts and Notifications

    Configuring Database Mail
    Implementing Operators and Notifications
    Implementing Alerts

After completing this module, you will be able to:

    Configure Database Mail.
    Monitor SQL Server errors.
    Configure operators, alerts, and notifications.
 

Course Directory [training on all levels]

Upcoming Classes
Gain insight and ideas from students with different perspectives and experiences.

Interesting Reads Take a class with us and receive a book of your choosing for 50% off MSRP.