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:
- Configure Database Mail
- Monitor SQL Server errors
- Configure operators, alerts, and notifications
Module 14: Performing Ongoing Database Maintenance
This module covers database maintenance plans.
- Lessons
- 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
- 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:
Book This Course Or Obtain Quote – Now
View Calendar for the latest course
Please Phone Us Now To Speak to One of Our Friendly Consultants
OR
Please Fill in the Form – We Will Get Back to You Within 15 minutes
Please Email Us Now – We Will Get Back to You Within 15 minutes
Don’t delay. Skill up at BOTi Today.
BOTi – Get On Course