Courses for Microsoft Certification from Activia

20462: Administering Microsoft SQL Server 2014 Databases

A 5-Day Training Course

Course Content

Authorised Microsoft Training Course

This is an authorised Microsoft training course, delivered by certified Microsoft trainers at fully equipped centres using the latest equipment, software and course materials. There is no better way to undertake your training, to maximise your learning and ensure the highest marks possible in your certification exams.

Course Aims

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.

Who Should Attend

The primary audience for this course is individuals who administer and maintain SQL Server 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 databases.
Attendees should also 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.

Course Objectives

Delegates will learn how to:

  • Plan and install SQL Server.
  • Describe system databases, the physical structure of databases, and the most common configuration options related to them.
  • Explain the concept of the transaction log and the SQL Server recovery models and implement different backup strategies that are available with SQL Server.
  • Back up SQL Server databases.
  • Restore SQL Server databases.
  • Import and export data by using wizards, the bulk copy utility, and data-tier applications.
  • Work with SQL Server security models, logins, users, and contained databases.
  • Work with server roles and database roles.
  • Use permissions to secure resources.
  • Use SQL Server audit and Transparent Data Encryption.
  • Work with SQL Server Agent to configure and execute jobs.
  • Implement SQL Server Agent security, proxy accounts, and credentials.
  • Configure database mail, alerts, and notifications.
  • Create database maintenance plans.
  • Use SQL Server Profiler, SQL Trace, and Distributed Relay.
  • Use Distributed Management Views and configure data collection.

Certification

This course helps people to prepare for exam 70-462.

Course Outline
This course is a 5-day training course structured into 32 modules.
Module 1
Installing SQL Server 2014
Topics covered
  • Introduction to the SQL Server Platform
  • SQL Server Services and Configuration Options
  • Considerations for Installing SQL Server
  • Installing SQL Server 2014
  • Module 2
    Lab : Installing SQL Server 2014
    Topics covered
  • Performing Pre-installation Stress Tests
  • Installing a SQL Server Instance
  • Performing Post-Installation Checks
  • Module 3
    Working with Databases
    Topics covered
  • Overview of SQL Server Databases
  • Creating Databases
  • Using Database Files and Filegroups
  • Moving Database Files
  • Buffer Pool Extension
  • Module 4
    Lab : Creating and Configuring Databases
    Topics covered
  • Creating a Storage Pool
  • Adjusting tempdb Configuration
  • Creating and Configuring a Database
  • Attaching a Database
  • Using Buffer Pool Extension
  • Module 5
    SQL Server 2014 Recovery Models
    Topics covered
  • Backup Strategies
  • Understanding SQL Server Transaction Logging
  • Planning a SQL Server Backup Strategy
  • Module 6
    Lab : Understanding SQL Server Recovery Models
    Topics covered
  • Planning a Backup Strategy
  • Configuring Recovery Models
  • Reviewing Recovery Models and Strategy
  • Module 7
    Backup of SQL Server 2014 Databases
    Topics covered
  • Backing up Databases and Transaction Logs
  • Managing Database Backups
  • Working with Backup Options
  • Module 8
    Lab : Backing Up SQL Server Databases
    Topics covered
  • Investigating Backup Compression
  • Performing Transaction Log Backups
  • Performing Differential Backups
  • Performing Copy-Only Backups
  • Performing Filegroup Backups
  • Module 9
    Restoring SQL Server 2014 Databases
    Topics covered
  • Understanding the Restore Process
  • Restoring Databases
  • Working with Point-in-Time Recovery
  • Restoring System Databases and Individual Files
  • Module 10
    Lab : Restoring SQL Server Databases
    Topics covered
  • Determining a Restore Strategy
  • Restoring a Database
  • Using STANDBY Mode
  • Module 11
    Importing and Exporting Data
    Topics covered
  • Transferring Data To/From SQL Server
  • Importing and Exporting Table Data
  • Inserting Data in Bulk
  • Deploying and Upgrading Data-tier Applications
  • Module 12
    Lab : Importing and Exporting Data
    Topics covered
  • Importing an Excel Spreadsheet
  • Importing a CSV File
  • Creating and Testing an Extraction Package
  • Creating and Deploying a Data-tier Application
  • Comparing Loading Performance
  • Module 13
    Authenticating and Authorizing Users
    Topics covered
  • Authenticating Connections to SQL Server
  • Authorizing Logins to Access Databases
  • Authorization Across Servers
  • Partially Contained Databases
  • Module 14
    Lab : Authenticating Users
    Topics covered
  • Creating Logins
  • Correcting an Application Login Issue
  • Creating Database Users
  • Correcting Access to a Restored Database
  • Module 15
    Assigning Server and Database Roles
    Topics covered
  • Working with Server Roles
  • Working with Fixed Database Roles
  • Creating User-defined Database Roles
  • Module 16
    Lab : Assigning Server and Database Roles
    Topics covered
  • Assigning Server Roles
  • Assigning Fixed Database Roles
  • Creating and Assigning User-defined Database Roles
  • Checking Role Assignments
  • Module 17
    Authorizing Users to Access Resources
    Topics covered
  • Authorizing User Access to Objects
  • Authorizing Users to Execute Code
  • Configuring Permissions at the Schema Level
  • Module 18
    Lab : Authorizing Users to Access Resources
    Topics covered
  • Assigning Schema-level Permissions
  • Assigning Object-level Permissions
  • Testing Permissions
  • Module 19
    Protecting Data by Using Encryption and Auditing
    Topics covered
  • Options for Auditing Data Access in SQL Server
  • Implementing SQL Server Audit
  • Managing SQL Server Audit
  • Protecting Data by Using Encryption
  • Module 20
    Lab : Using Auditing and Encryption
    Topics covered
  • Determining Audit Configuration and Creating an Audit
  • Creating Server Audit Specifications
  • Creating Database Audit Specifications
  • Testing Audit Functionality
  • Using Transparent Data Encryption
  • Module 21
    Automating SQL Server 2014 Management
    Topics covered
  • Automating SQL Server Management
  • Working with SQL Server Agent
  • Managing SQL Server Agent Jobs
  • Multi-Server Management
  • Module 22
    Lab : Automating SQL Server Management
    Topics covered
  • Creating a Job
  • Scheduling a Job
  • Troubleshooting a Failing Job
  • Creating a Multi-Server Job
  • Module 23
    Configuring Security for SQL Server Agent
    Topics covered
  • Understanding SQL Server Agent Security
  • Configuring Credentials
  • Configuring Proxy Server Accounts
  • Module 24
    Lab : Configuring Security for SQL Server Agent
    Topics covered
  • Troubleshooting Job Execution Failure
  • Resolving Security Issues
  • Performing Further Troubleshooting
  • Module 25
    Monitoring SQL Server 2014 by Using Alerts and Notifications
    Topics covered
  • Configuring Database Mail
  • Monitoring SQL Server Errors
  • Configuring Operators, Alerts, and Notifications
  • Module 26
    Lab : Monitoring SQL Server by Using Alerts and Notifications
    Topics covered
  • Configuring Database Mail
  • Implementing Notifications
  • Implementing Alerts
  • Module 27
    Performing Ongoing Database Maintenance
    Topics covered
  • Ensuring Database Integrity
  • Maintaining Indexes
  • Automating Routine Database Maintenance
  • Module 28
    Lab : Performing Ongoing Database Maintenance
    Topics covered
  • Check Database Integrity by Using DBCC CHECKDB
  • Correcting Index Fragmentation
  • Creating a Database Maintenance Plan
  • Investigating Table Lock Performance
  • Module 29
    Tracing Access to SQL Server 2014
    Topics covered
  • 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 30
    Lab : Tracing Access to SQL Server
    Topics covered
  • Capturing a Trace by Using SQL Server Profiler
  • Analyzing a Trace by Using the Database Engine Tuning Advisor
  • Configuring SQL Trace
  • Module 31
    Monitoring SQL Server 2014
    Topics covered
  • Monitoring Activity
  • Capturing and Managing Performance Data
  • Analyzing Collected Performance Data
  • SQL Server Utility
  • Module 32
    Lab : Monitoring SQL Server
    Topics covered
  • Creating a Utility Control Point
  • Configuring a Health Policy
  • Configuring Instances for Data Collection
  • Working with Data Collector Reports



  • Activia Training :   Training courses for business at twelve locations UK wide
    www.activia.co.uk