SQL Server 2014 and 2012

Administering Microsoft SQL Server Databases
(Microsoft Training Course: 20462) - 5 days - £1850 exc VAT

Save up to 50% of the cost of some courses: check our Certification Packages or buy F1 Training Vouchers
 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 
Aberdeen Centre     29     02   
at Nexus Open Systems (Exeter) 15     13   12    
Birmingham Centre  07      14   07  
Bristol Centre11   18   27   19    
Edinburgh Centre      13       
Extended Classroom      13
Glasgow Centre       24  19    
Leeds (City Exchange) Centre       24  19    
London International House Centre E1W      13
Manchester (Portland St) Centre11 08 14 11 16 29
27 24 14 12 09 07  
Newcastle Centre 22   23    14  23   
Reading Centre   25       02   

> Target Audience
This 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. It is intended for individuals who administer and maintain SQL Server databases.
> Course outline
  1. Installing SQL Server 2014
  2. Working with Databases
  3. SQL Server 2014 Recovery Models
  4. Backup of SQL Server 2014 Databases
  5. Restoring SQL Server 2014 Databases
  6. Importing and Exporting Data
  7. Authenticating and Authorizing Users
  8. Assigning Server and Database Roles
  9. Authorizing Users to Access Resources
  10. Protecting Data by using Encryption and Auditing
  11. Automating SQL Server 2014 Management
  12. Configuring Security for SQL Server Agent
  13. Monitoring SQL Server 2014 by using Alerts and Notifications
  14. Performing Ongoing Database Maintenance
  15. Tracing Access to SQL Server 2014
  16. Monitoring SQL Server 2014

Supplementary InformationSubject to demand, training course MS20462 (SQL Server 2014) may be merged with MS10775 (SQL Server 2012), as recommended by Microsoft. SPECIAL DISCOUNT PRICE IS ONLY AVAILABLE ON TRAINING COURSES BOOKED BEFORE 31/10/14.
Module 1: 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
  • Describe the SQL Server Platform
  • Configure SQL Server services
  • Perform pre-installation checks and assess resource requirements
  • Install SQL Server 2014
Module 2: 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
  • Describe the role and structure of SQL Server databases
  • Create, configure, and alter databases
  • Use files & filegroups, move database files
  • Describe the Buffer Pool Extension feature
Module 3: SQL Server 2014 Recovery Models
  • Backup Strategies
  • Understanding 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
  • Describe the various backup strategies that SQL Server provides
  • Describe how database transaction logs function
  • Plan a SQL Server backup strategy
Module 4: 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
  • Perform backups of SQL Server databases and transaction logs, manage backup sets
  • Describe the advanced backup options
Module 5: 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
  • Explain the database restore process
  • Restore databases
  • Describe the concept of point-in-time recovery
  • Describe the advanced concepts of restoring databases
Module 6: 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
  • Transfer data to and from SQL Server
  • Import and export table data
  • Insert data in bulk and optimize the bulk insert process
  • Use DACs to import and export database applications
Module 7: 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
  • Describe how SQL Server authenticates connections and how logins are authorized to access databases
  • Explain the requirements for authorization across servers
  • Describe the benefits of using contained databases
Module 8: 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
  • Assign fixed server roles
  • Configure fixed database roles
  • Create and assign user-defined database roles
Module 9: 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
  • Grant, revoke, and deny permissions on objects
  • Configure permissions on executable code
  • Configure permissions at schema level
Module 10: 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
  • Describe options for auditing data access
  • Implement SQL Server audit
  • Manage SQL Server audit
  • Implement Transparent Data Encryption
Module 11: 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
  • Describe methods for automating SQL Server management
  • Configure jobs, job step types, and schedules
  • Manage SQL Server Agent jobs
  • Configure master and target servers
Module 12: 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
  • Describe the security context for SQL Server Agent jobs
  • Configure credentials
  • Configure proxy accounts
Module 13: 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
  • Configure Database Mail
  • Monitor SQL Server errors
  • Configure operators, alerts, and notifications
Module 14: 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
  • Maintain database integrity by using DBCC CHECKDB
  • Maintain indexes
  • Configure database maintenance plans
Module 15: 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
  • Configure SQL Server Profiler traces and review captured traces
  • Use the Database Engine Tuning Advisor
  • Understand SQL Server advanced tracing concepts
  • Use Distributed Replay
  • Monitor locking
Module 16: 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
  • Monitor activity in SQL Server
  • Configure data collection
  • View reports from data collection
  • Configure a SQL Server Utility
> Pre-Requisites
Before attending this course, students must have the following pre-requisites:
  • 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:
  • 20461C: Querying Microsoft SQL Server 2014
> Purpose
After completing this course, students will be able 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.
> Supplementary Information
Subject to demand, training course MS20462 (SQL Server 2014) may be merged with MS10775 (SQL Server 2012), as recommended by Microsoft. SPECIAL DISCOUNT PRICE IS ONLY AVAILABLE ON TRAINING COURSES BOOKED BEFORE 31/10/14.