Best way to contact me *

    About this Course

    This 5-day instructor-led course provides students who administer and maintain SQL Server databases with the knowledge and skills to provision and administer a SQL server database infrastructure. Additionally, it will be of use to individuals who develop applications that deliver content from SQL Server databases. This course combines the material from Microsoft’s 20764 and 20765 courses, but focus the topics around implementing SQL Server in an on-premise configuration. Even though the class uses SQL Server 2016, the concepts and topics apply to SQL Server 2008 through 2014.

    Audience Profile

    The primary audience for this course are individuals who administer and maintain on premise 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 audiences for this course are individuals who develop applications that deliver content from SQL Server databases.

    At Course Completion

    After completing this course, students will be able to:

    • Provision a Database Server
    • Configure SQL Server
    • Create and Manage Databases and Files
    • Perform Ongoing Database Maintenance Activities
    • Describe recovery models and backup strategies
    • Backup SQL Server databases
    • Restore SQL Server databases
    • Authenticate and authorize users
    • Assign server and database roles
    • Authorize users to access resources
    • Automate database management
    • Configure security for the SQL Server agent
    • Manage alerts and notifications
    • Trace access to SQL Server with Extended Event
    • Monitor a SQL Server infrastructure


    Module 1: SQL Server Components

    This module introduces the SQL Server 2016 platform. It describes the components, editions, and versions of SQL Server 2016, and the tasks that a database administration commonly performs to configure and SQL Server instance.


    • Introduction to the SQL Server Platform
    • Overview of SQL Server Architecture
    • SQL Server Services and Configuration Options

    Module 2: Installing SQL Server

    One of the key responsibilities of database administrator is to provision databases and server. This includes planning and performing the installation of SQL Server on a physical servers and virtual machines. This module explains how to assess resource requirements for SQL Server 2016 and how to install it.


    • Considerations for installing SQL Server
    • TempDB Files
    • Installing SQL Server 2016
    • Automating Installation

    Module 3: Working with Databases

    This module describes the preinstalled system databases, the physical structure of databases and the most common configuration options related to them.


    • Overview of SQL Server Databases
    • Creating Databases
    • Database Files and Filegroups
    • Moving Database Files
    • Buffer Pool Extension

    Module 4: Recovery Models and Backup Strategies

    In this module, you will consider how to create a strategy that is aligned with organizational needs, based on the available backup models, and the role of the transaction logs in maintaining database consistency.


    • Understanding Backup Strategies
    • SQL Server Transaction Logs
    • Planning Backup Strategies

    Module 5: Backing Up SQL Server Databases

    In this module, you will learn how to implement various backup strategies using the SQL Server backup commands.


    • Backing Up Databases and Transaction Logs
    • Managing Database Backups
    • Advanced Database Options

    Module 6: Restoring SQL Server Databases

    In this module, you will see how to restore user and system databases and how to implement point-in-time recovery.


    • Understanding the Restore Process
    • Restoring Databases
    • Advanced Restore Scenarios
    • Point-in-Time Recovery

    Module 7: Performing Database Maintenance

    This module describes common database maintenance tasks that a DBA must perform, and demonstrates how to automate these tasks using maintenance plans.


    • Ensuring Database Integrity
    • Maintaining Indexes
    • Automating Routine Database Maintenance

    Module 8: Authenticating and Authorizing Users

    Protection of data within your SQL Server databases is essential and requires a working knowledge of the issues and SQL Server security features. This module describes SQL Server security models, logins, users, partially contained databases, and cross-server authorization.


    • Authenticating Connections to SQL Server
    • Authorizing Logins to Connect to databases
    • Authorization Across Servers
    • Partially Contained Databases

    Module 9: Assigning Server and Database Roles

    Microsoft SQL Server includes support for security roles defined at server level and at database level. This module covers fixed server roles, user-defined server roles, fixed database roles, and user-defined roles.


    • Working with server roles
    • Working with Fixed Database Roles
    • Assigning User-Defined Database Roles

    Module 10: Authorizing Users to Access Resources

    In this module, you will see how these object permissions are managed. In addition to access permissions on database objects, SQL Server provides the ability to determine which users can execute code, such as stored procedures and functions.


    • Authorizing User Access to Objects
    • Authorizing Users to Execute Code
    • Configuring Permissions at the Schema Level

    Module 11: Automating SQL Server Management

    This module describes how to use SQL Server Agent to automate jobs, how to configure security contexts for jobs, and how to implement multi-server jobs.


    • Automating SQL Server management
    • Working with SQL Server Agent
    • Managing SQL Server Agent Jobs
    • Multi-server Management

    Module 12: Configuring Security for SQL Server Agent

    It is important to understand how to create a minimal privilege security environment for jobs that run inside SQL Server Agent. This module describes the considerations for SQL Server Agent security, including proxy accounts and credentials.


    • Understanding SQL Server Agent Security
    • Configuring Credentials
    • Configuring Proxy Accounts

    Module 13: Monitoring SQL Server with Alerts and Notifications

    This module covers the configuration of Database Mail, alerts, and notifications for a SQL Server instance.


    • Monitoring SQL Server Errors
    • Configuring Database Mail
    • Operators, Alerts, and Notifications

    Module 14: Tracing Access to SQL Server with Extended events

    SQL Server Extended Events is a flexible, lightweight event-handling system built into the Microsoft SQL Server Database Engine. This module focuses on the architectural concepts, troubleshooting strategies and usage scenarios of Extended Events.


    • Extended Events Core Concepts
    • Working with Extended Events

    Module 15: Monitoring SQL Server

    This module explains how to use Distributed Management Views to monitor SQL Server. It also describes configuration of data collection and the SQL Server Utility.


    • Monitoring activity
    • Capturing and Managing Performance Data
    • Analyzing Collected Performance Data
    • SQL Server Utility


    In addition to their professional experience, students who attend this training should already have the following technical knowledge:

    • 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


    Online Event


    Live Online Training

    Get the same training you expect in the classroom without leaving your office or home. These are NOT recorded classes. They are LIVE sessions with an expert instructor. We use the latest in video conferencing technologies and audio so you can confidently participate in any class just like being right there in person. We guarantee the effectiveness of our online training delivery approach that we will give you your money back if you are not totally satisfied.  Ask us for a demo.

    Online class requirements:

    • Moderate to fast Internet
    • A phone or computer headset is required in order to hear the instructor/moderator). You can use Computer Audio (VoIP) or you can dial in from a regular phone.  For convenience, we recommend a hands-free headset or phone.
    • Training software must be installed on your computer (trial versions are acceptable)
    • RECOMMENDED: Dual Monitors or computers. For optimal online learning experience, we recommend participants have dual monitors or two computers. Your online classroom credentials allow you to join multiple times from multiple computers. Participants should use one monitor or computer to view the instructor’s shared screen and another monitor or computer to work with the software.

    What happens when you enroll in an online class

    When you register for an online class, you will receive a welcome email followed by login access to the Citrix GoToTraining virtual classroom. A workbook (printed copy or eBook) will be sent to you prior to the start of class.

    Online Training Advantages

    Convenience: You don’t have to travel and can attend from your home, office or anywhere with an internet connection. Our online classes are conducted using GoToTraining, a more robust version of the popular GoToMeeting screen sharing and conferencing platform. To accommodate multiple time zones, courses are typically scheduled from 10am – 5pm Eastern with a one-hour lunch break at 12:30 – 1:30 pm Eastern and a 10-minute break in the morning and afternoon. When conducting custom online course for your group, class times can be modified to accommodate your timezone.

    Interactive Learning: Our online training is fully interactive. You can speak and chat with the instructor and classmates at any time. Various interactive techniques are used in every class. Our small class sizes (typically 4 – 8 students), allow our instructors to focus on individual performance and issues and to work closely with you to meet your unique needs. Classes are designed to be a hands-on learning experience, providing opportunities for you to try your new skills while the instructor is available for review, questions, and feedback. You have the option to give the instructor permission to view your computer to provide one-on-one assistance when needed.

    GSA Schedule: 47QTCA19D008F