MS306: Administering SQL Server 2014 Databases

MS306: Administering Microsoft SQL Server 2014 Databases

 

Class Overview

This Administering Microsoft SQL Server 2014 Databases training class 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.

The primary audience for this course is individuals who administer and maintain SQL Server 2014 databases. These individuals perform database administration and maintenance as their primary area of responsibility, or work in environments where databases play a key role in their primary job.

The secondary audience for this course is individuals who develop applications that deliver content from SQL Server 2014 databases.

Duration: 24 Hours (8* 3 Hours)

Class Goals

Learn to plan and install SQL Server 2014.

Learn to describe system databases, the physical structure of databases, and the most common configuration options related to them.

Learn to explain the concept of the transaction log and the SQL Server recovery models and implement different backup strategies that are available with SQL Server.

Learn to back up SQL Server databases.

Learn to restore SQL Server databases.

Learn to import and export data by using wizards, the bulk copy utility, and data-tier applications.

Learn to work with SQL Server security models, logins, users, and contained databases.

Learn to work with server roles and database roles.

Learn to use permissions to secure resources.

Learn to use SQL Server audit and Transparent Data Encryption.

Learn to work with SQL Server Agent to configure and execute jobs.

Learn to implement SQL Server Agent security, proxy accounts, and credentials.

Learn to configure database mail, alerts, and notifications.

Learn to create database maintenance plans.

Learn to use SQL Server Profiler, SQL Trace, and Distributed Relay.

Learn to use Distributed Management Views and configure data collection.

 

Class Outline

 

Installing SQL Server 2014

Introduction to the SQL Server Platform

SQL Server Services and Configuration Options

Considerations for Installing SQL Server

Installing SQL Server 2014

Lab: Installing SQL Server 2014

Performing Pre-installation Stress Tests

Installing a SQL Server Instance

Performing Post-Installation Checks

Working with Databases

Overview of SQL Server Databases

Creating Databases

Using Database Files and Filegroups

Moving Database Files

Buffer Pool Extension

Lab: Creating and Configuring Databases

Creating a Storage Pool

Adjusting tempdb Configuration

Creating and Configuring a Database

Attaching a Database

Using Buffer Pool Extension

SQL Server 2014 Recovery Models

Backup Strategies

Understanding SQL Server Transaction Logging

Planning a SQL Server Backup Strategy

Lab: Understanding SQL Server Recovery Models

Planning a Backup Strategy

Configuring Recovery Models

Reviewing Recovery Models and Strategy

Backup of SQL Server 2014 Databases

Backing up Databases and Transaction Logs

Managing Database Backups

Working with Backup Options

Lab: Backing Up SQL Server Databases

Investigating Backup Compression

Performing Transaction Log Backups

Performing Differential Backups

Performing Copy-Only Backups

Performing Filegroup Backups

Restoring SQL Server 2014 Databases

Understanding the Restore Process

Restoring Databases

Working with Point-in-Time Recovery

Restoring System Databases and Individual Files

Lab: Restoring SQL Server Databases

Determining a Restore Strategy

Restoring a Database

Using STANDBY Mode

Importing and Exporting Data

Transferring Data To/From SQL Server

Importing and Exporting Table Data

Inserting Data in Bulk

Deploying and Upgrading Data-tier Applications

Lab: Importing and Exporting Data

Importing an Excel Spreadsheet

Importing a CSV File

Creating and Testing an Extraction Package

Creating and Deploying a Data-tier Application

Comparing Loading Performance

Authenticating and Authorizing Users

Authenticating Connections to SQL Server

Authorizing Logins to Access Databases

Authorization Across Servers

Partially Contained Databases

Lab: Authenticating Users

Creating Logins

Correcting an Application Login Issue

Creating Database Users

Correcting Access to a Restored Database

Assigning Server and Database Roles

Working with Server Roles

Working with Fixed Database Roles

Creating User-defined Database Roles

Lab: Assigning Server and Database Roles

Assigning Server Roles

Assigning Fixed Database Roles

Creating and Assigning User-defined Database Roles

Checking Role Assignments

Authorizing Users to Access Resources

Authorizing User Access to Objects

Authorizing Users to Execute Code

Configuring Permissions at the Schema Level

Lab: Authorizing Users to Access Resources

Assigning Schema-level Permissions

Assigning Object-level Permissions

Testing Permissions

Protecting Data by Using Encryption and Auditing

Options for Auditing Data Access in SQL Server

Implementing SQL Server Audit

Managing SQL Server Audit

Protecting Data by Using Encryption

Lab: Using Auditing and Encryption

Determining Audit Configuration and Creating an Audit

Creating Server Audit Specifications

Creating Database Audit Specifications

Testing Audit Functionality

Using Transparent Data Encryption

Automating SQL Server 2014 Management

Automating SQL Server Management

Working with SQL Server Agent

Managing SQL Server Agent Jobs

Multi-Server Management

Lab: Automating SQL Server Management

Creating a Job

Scheduling a Job

Troubleshooting a Failing Job

Creating a Multi-Server Job

Configuring Security for SQL Server Agent

Understanding SQL Server Agent Security

Configuring Credentials

Configuring Proxy Server Accounts

Lab: Configuring Security for SQL Server Agent

Troubleshooting Job Execution Failure

Resolving Security Issues

Performing Further Troubleshooting

Monitoring SQL Server 2014 by Using Alerts and Notifications

Configuring Database Mail

Monitoring SQL Server Errors

Configuring Operators, Alerts, and Notifications

Lab: Monitoring SQL Server by Using Alerts and Notifications

Configuring Database Mail

Implementing Notifications

Implementing Alerts

Performing Ongoing Database Maintenance

Ensuring Database Integrity

Maintaining Indexes

Automating Routine Database Maintenance

Lab: Performing Ongoing Database Maintenance

Check Database Integrity by Using DBCC CHECKDB

Correcting Index Fragmentation

Creating a Database Maintenance Plan

Investigating Table Lock Performance

Tracing Access to SQL Server 2014

Capturing Activity by Using SQL Server Profiler

Improving Performance by Using the Database Engine Tuning Advisor

Working with Tracing Options

Distributed Replay

Monitoring Locks

Lab: Tracing Access to SQL Server

Capturing a Trace by Using SQL Server Profiler

Analyzing a Trace by Using the Database Engine Tuning Advisor

Configuring SQL Trace

Monitoring SQL Server 2014

Monitoring Activity

Capturing and Managing Performance Data

Analyzing Collected Performance Data

SQL Server Utility

Lab: Monitoring SQL Server

Creating a Utility Control Point

Configuring a Health Policy

Configuring Instances for Data Collection

Working with Data Collector Reports