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.
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.
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.
Module 1: Installing SQL Server 2014
This module introduces the SQL Server 2014 platform. It describes the components, editions, and versions of SQL Server 2014, SQL Server services and configuration options, and service accounts. The module also explains how to assess resource requirements for SQL Server 2014 and how to install SQL Server 2014.
Module 2: Working with Databases
This module describes he preinstalled system databases, the physical structure of databases, and the most common configuration options related to them.
Module 3: SQL Server 2014 Recovery Models
In this module, you will consider how to create a strategy that is aligned with organizational needs and see how the transaction logging capabilities of Microsoft SQL Server can help you to achieve an appropriate outcome.
Module 4: Backup of SQL Server 2014 Databases
In this module, you will explore the key backup types in more depth and learn how to implement the backups. You will also see how to apply options that affect the way that the backups work.
Module 5: Restoring SQL Server 2014 Databases
In this module, you will see how to restore user and system databases and how to implement point-in-time recovery.
Module 6: Importing and Exporting Data
This module covers the use of the import/export wizards and explains how they relate to SSIS. It also introduces BCP and BULK INSERT.
Module 7: Authenticating and Authorizing Users
This module covers SQL Server security models, logins, users, and contained databases.
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.
Module 9: Authorizing Users to Access Resources
This module covers permissions and the assignment of permissions.
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.
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.
Module 12: Configuring Security for SQL Server Agent
This module describes the considerations for SQL Server Agent security, including proxy accounts and credentials.
Module 13: Monitoring SQL Server 2014 by Using Alerts and Notifications
This module covers the configuration of database mail, alerts, and notifications.
Module 14: Performing Ongoing Database Maintenance
This module covers 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.
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.
This course requires that you meet the following prerequisites:
Students who attend this training can meet the prerequisites by attending the following courses, or obtaining equivalent knowledge and skills: