20764: Administering a SQL Database Infrastructure

20764: Administering a SQL Database Infrastructure

Audience

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 audiences for this course are individuals who develop applications that deliver content from SQL Server databases.

Prerequisites

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.

Duration

5 days.

Course Objectives

This five-day instructor-led course provides students who administer and maintain SQL Server databases with the knowledge and skills to administer a SQL server database infrastructure. Additionally, it will be of use to individuals who develop applications that deliver content from SQL Server databases.

After completing this course, students will be able to:

  • Authenticate and authorize users
  • Assign server and database roles
  • Authorize users to access resources
  • Protect data with encryption and auditing
  • Describe recovery models and backup strategies
  • Backup SQL Server databases
  • Restore SQL Server databases
  • Automate database management
  • Configure security for the SQL Server agent
  • Manage alerts and notifications
  • Managing SQL Server using PowerShell
  • Trace access to SQL Server
  • Monitor a SQL Server infrastructure
  • Troubleshoot a SQL Server infrastructure
  • Import and export data

Course Content

Module 1: SQL Server Security
Authenticating Connections to SQL Server
Authorizing Logins to Connect to databases
Authorization Across Servers
Partially Contained Databases
Lab : Authenticating Users
After completing this module, you will be able to:
• SQL Server basic concepts.
• SQL Server connection authentication.
• User login authorization to databases.
• Partially contained databases.
• Authorization across servers.

Module 2: Assigning Server and Database Roles
Working with server roles
Working with Fixed Database Roles
Assigning User-Defined Database Roles
Lab : Assigning server and database roles
After completing this module, you will be able to:
• Describe and use server roles to manage server-level security.
• Describe and use fixed database roles.
• Use custom database roles and application roles to manage database-level security.

Module 3: 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
After completing this module, you will be able to:
• Authorize user access to objects.
• Authorize users to execute code.
• Configure permissions at the schema level.

Module 4: Protecting Data with Encryption and Auditing
Options for auditing data access in SQL Server
Implementing SQL Server Audit
Managing SQL Server Audit
Protecting Data with Encryption
Lab : Using Auditing and Encryption
After completing this module, you will be able to:
• Describe the options for auditing data access.
• Implement SQL Server Audit.
• Manage SQL Server Audit.
• Describe and implement methods of encrypting data in SQL Server.
• Implement encryption.

Module 5: Recovery Models and Backup Strategies
Understanding Backup Strategies
SQL Server Transaction Logs
Planning Backup Strategies
Lab : Understanding SQL Server recovery models
After completing this module, you will be able to:
• Describe various backup strategies.
• Describe how database transaction logs function.
• Plan SQL Server backup strategies.

Module 6: Backing Up SQL Server Databases
Backing Up Databases and Transaction Logs
Managing Database Backups
Advanced Database Options
Lab : Backing Up Databases
After completing this module, you will be able to:
• Perform backups of SQL Server databases and transaction logs.
• Manage database backups.
• Describe advanced backup options.

Module 7: Restoring SQL Server 2016 Databases
Understanding the Restore Process
Restoring Databases
Advanced Restore Scenarios
Point-in-Time Recovery
Lab : Restoring SQL Server Databases
After completing this module, you will be able to:
• Explain the restore process.
• Restore databases.
• Perform advanced restore operations.
• Perform a point-in-time recovery.

Module 8: Automating SQL Server Management
Automating SQL Server management
Working with SQL Server Agent
Managing SQL Server Agent Jobs
Multi-server Management
Lab : Automating SQL Server Management
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 9: Configuring Security for SQL Server Agent
Understanding SQL Server Agent Security
Configuring Credentials
Configuring Proxy Accounts
Lab : Configuring Security for SQL Server Agent
After completing this module, you will be able to:
• Explain SQL Server Agent security.
• Configure credentials.
• Configure proxy accounts.

Module 10: Monitoring SQL Server with Alerts and Notifications
Monitoring SQL Server Errors
Configuring Database Mail
Operators, Alerts, and Notifications
Alerts in Azure SQL Database
Lab : Monitoring SQL Server with Alerts and Notifications
After completing this module, you will be able to:
• Monitor SQL Server errors.
• Configure database mail.
• Configure operators, alerts, and notifications.
• Work with alerts in Azure SQL Database.

Module 11: Introduction to Managing SQL Server by using PowerShell
Getting Started with Windows PowerShell
Configure SQL Server using PowerShell
Administer and Maintain SQL Server with PowerShell
Managing Azure SQL Databases using PowerShell
Lab : Using PowerShell to Manage SQL Server
After completing this module, you will be able to:
• Describe the benefits of PowerShell and its fundamental concepts.
• Configure SQL Server by using PowerShell.
• Administer and maintain SQL Server by using PowerShell.
• Manage an Azure SQL Database by using PowerShell.

Module 12: Tracing Access to SQL Server with Extended events
Extended Events Core Concepts
Working with Extended Events
Lab : Extended Events
After completing this module, you will be able to:
• Describe Extended Events core concepts.
• Create and query Extended Events sessions.

Module 13: Monitoring SQL Server
Monitoring activity
Capturing and Managing Performance Data
Analyzing Collected Performance Data
SQL Server Utility
Lab : Monitoring SQL Server
After completing this module, you will be able to:
• Monitor current activity.
• Capture and manage performance data.
• Analyze collected performance data.
• Configure SQL Server Utility.

Module 14: Troubleshooting SQL Server
A Trouble Shooting Methodology for SQL Server
Resolving Service Related Issues
Resolving Connectivity and Log-in issues
Lab : Troubleshooting Common Issues
After completing this module, you will be able to:
• Describe a troubleshooting methodology for SQL Server.
• Resolve service-related issues.
• Resolve login and connectivity issues.

Module 15: Importing and Exporting Data
Transferring Data to and from SQL Server
Importing and Exporting Table Data
Using bcp and BULK INSERT to Import Data
Deploying and Upgrading Data-Tier Application
Lab : Importing and Exporting Data
After completing this module, you will be able to:
• Describe tools and techniques for transferring data.
• Import and export table data.
• Use bcp and BULK INSERT to import data.
• Use data-tier applications to import and export database applications.

Virtual Courses

ALL of our courses can be delivered virtually. And our Bath public schedule of courses are now available as live virtual sessions, using the popular Zoom Virtual Classroom and remote labs. Delegates can test their access at: www.zoom.us/test

On-Site Courses

Can't attend one of our public classes? Booking for multiple people?

All our courses are available on your site! Delivered for your staff, at your premises.

Contact us to find out more...