Administering Microsoft SQL Server 2014 Databases (NE-20462)

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. 

Type

Certified Training Course

Level

Professional

Cost

R 8 800 p.p. (Excl. VAT)

SATV Redeemable 

Yes

Delivery Method

Classroom-based, Instructor-led with emphasis on hands-on exercises.

Duration

5 days

Start time: 09:00 (Registration will commence at 08:30 on the first day)

Prerequisites

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

Students who attend this training can meet the prerequisites by attending the following courses, or obtaining equivalent knowledge and skills:

  • NE-20461: Querying Microsoft SQL Server 2014

Course Objectives

After completing this course, students will be able to:

  • Describe core database administration tasks and tools
  • Install and configure SQL Server 2014
  • Configure SQL Server databases and storage
  • Plan and implement a backup strategy
  • Restore databases from backups
  • Import and export data
  • Monitor SQL Server
  • Trace SQL Server activity
  • Manage SQL Server security
  • Audit data access and encrypt data
  • Perform ongoing database maintenance
  • Automate SQL Server maintenance with SQL Server Agent Jobs
  • Configure Database Mail, alerts and notifications

Exam & Certifications

This course prepares students to write Exam 70-462: Administering Microsoft SQL Server 2012 Databases and counts as credit towards the Microsoft Certified Solutions Associate (MCSA): SQL Server 2012 certification.

Course Topics

1. Introduction to SQL Server 2014 Database Administration

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

2. Installing and Configuring SQL Server 2014

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

3. Working with Databases and Storage

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

4. Planning and Implementing a Backup 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

5. Restoring SQL Server 2014 Databases

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

6. Importing and Exporting Data

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

7. Monitoring SQL Server 2014

Lessons

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

Lab: Monitoring SQL Server 2014

  • Collecting Baseline Metrics
  • Monitoring a Workload

8. Tracing SQL Server Activity

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

9. Managing SQL Server Security

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

10. Auditing Data Access and Encrypting Data

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

11. Performing Ongoing Database Maintenance

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

12. Automating SQL Server 2014 Management

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

13. Monitoring SQL Server 2014 by Using 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