BOTi Courses Overview – MCSA: SQL Server 2014 Training
Course Introduction
This course teaches the skills and knowledge required to maintain a Microsoft SQL Server 2014 database and how to use SQL Server 2014 product tools and features related to maintaining a database.
Course Outline
Key outcomes of this course include:
Module 1-7
Install and Plan SQL Server
Knowledge of system databases, the physical foundation of databases, popular configuration options
Understand the use of the SQL Server recovery models and the transaction log
Using SQL Server logins, users, security models and contained databases
Strategies for backing up that are available with SQL Server
Back up and restore SQL Server databases
Export and import data by utilizing wizards, data-tier applications and the utility for bulk copy
Configure database alerts, notifications and mail
Produce database plans for maintenance
Utilize SQL Trace, SQL Server Profiler and Distributed Relay
Utilize Distributed Management Views and configure data collection
Understand database and server roles, permissions to secure resources
Utilizing proxy accounts, and credentials, SQL Server Agent security,
SQL Server audit utilization and Data Encryption (transparent)
Using SQL Server Agent for configuration and job execution
Module 8: Assigning Server and Database Roles
This module covers fixed server roles, user-defined server roles, fixed database roles, and user-defined database roles.
Lessons
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
After completing this module, you will be able to:
Assign fixed server roles
Configure fixed database roles
Create and assign user-defined database roles
Module 9: Authorizing Users to Access Resources
This module covers permissions and the assignment of permissions.
Lessons
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
After completing this module, you will be able to:
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
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
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
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: Automating SQL Server 2014 Management
This module describes how to use SQL Server Agent for automation. It also explains the benefits of using master and target servers to centralize the administration of automation.
Lessons
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
After completing this module, you will be able to:
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
This module describes the considerations for SQL Server Agent security, including proxy accounts and credentials.
Lessons
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
After completing this module, you will be able to:
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
This module covers the configuration of database mail, alerts, and notifications.
Lessons
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
After completing this module, you will be able to:
After completing this module, you will be able to:
Maintain database integrity by using DBCC CHECKDB
Maintain indexes
Configure database maintenance plans
Module 15: Tracing Access to SQL Server 2014
This module describes how to use SQL Server Profiler and SQL Trace stored procedures to capture information about SQL Server. It also describes how to use Distributed Replay to capture trace information from multiple servers and how to monitor locking.
Lessons
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
After completing this module, you will be able to:
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
This module explains how to use Distributed Management Views to monitor SQL Server. It also describes how to configure data collection and the SQL Server Utility.
Lessons
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
After completing this module, you will be able to:
Monitor activity in SQL Server
Configure data collection
View reports from data collection
Configure a SQL Server Utility
Course duration
2 days
Who should attend
This course is designed for the following attendees:
Individuals who maintain and administrate SQL Server databases and those
who develop applications that provide content from SQL Server databases.
Prior learning requirements
For the MCSA: SQL Server 2014 Training Course it is advisable that you have the following experience/knowledge:
Basic knowledge of the Microsoft Windows operating system and its core functionality.
Working knowledge of relational databases.
Working knowledge of Transact-SQL.
Database design experience.
Public Course Schedule and Costs
Please click on link below for related public course/s:
There are no upcoming events at this time.
Book Now or Obtain Instant Quote
We also offer customized courses across the country: Anytime, Anywhere. Click on the link to get get instant proposal or book you course:
We are a diverse and dynamic team of passionate training specialists who believe that self-betterment and additive education are transformative elements that can help people realise their full potential. We are in the constant pursuit of excellence and proud of our authentic BBEEE roots. We want to work with companies who share our vision. Learn More