Toll Free - 877-442-3915

Account | Self-Paced Login

Account | Self-Paced Login | 877-442-3915

20764-Administering a SQL Database Infrastructure  Course

20764-Administering a SQL Database Infrastructure Training Course


Course Description:  The Administering a SQL Database Infrastructure - 20764 course is a 5-day course that is designed to teach students who administer and maintain SQL Server databases the knowledge and skills to administer a SQL server database infrastructure. The course is also beneficial to students who develop applications that deliver content from SQL Server databases.

Topics covered in the course include:

  • Authenticating and authorizing users
  • Assigning server and database roles
  • Authorizing users to access resources
  • Protecting data with encryption and auditing
  • Describing recovery models and backup strategies
  • Backing up SQL Server databases
  • Restoring SQL Server databases
  • Automating database management
  • Configuring security for the SQL Server agent
  • Managing alerts and notifications
  • Managing SQL Server using PowerShell
  • Tracing access to SQL Server
  • Monitoring a SQL Server infrastructure
  • Troubleshooting a SQL Server infrastructure
  • Importing and exporting data

Target Student:This course is designed for IT Professionals who administer and maintain SQL Server databases.

Students should possess the following experience before attending the course:

  • Working knowledge of relational database
  • Working knowledge of Transact-SQL
  • Basic experience with the Microsoft Windows operating system and its core features
  • Some database design experience

Training Dates
(Click on the course name below to view course details and full list of class dates)

20764-Administering a SQL Database Infrastructure
Locations: Available Nationwide and Online
Class Date(s): 9/23/2019 - 9/27/2019
Course Fee : $2795


20764-Administering a SQL Database Infrastructure
Locations: Available Nationwide and Online
Class Date(s): 10/21/2019 - 10/25/2019
Course Fee : $2795


20764-Administering a SQL Database Infrastructure
Locations: Available Nationwide and Online
Class Date(s): 11/18/2019 - 11/22/2019
Course Fee : $2795


Course Syllabus

Section 1: SQL Server Security
This section discusses SQL Server security models, logins, users, partially contained databases, and cross-server authorization.

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

Lab :Authenticating Users
Create Logins
Create Database Users
Correct Application Login Issues
Configure Security for Restored Databases

Section 2: Assigning Server and Database Roles
Using roles simplifies the management of user permissions. With roles, you can control authenticated users’ access to system resources based on each user’s job function—rather than assigning permissions user-by-user, you can grant permissions to a role, then make users members of roles. Microsoft SQL Server includes support for security roles defined at server level and at database level.

Topics :
Working with server roles
Working with Fixed Database Roles
Assigning User-Defined Database Roles

Lab :Assigning server and database roles
Assigning Server Roles
Assigning Fixed Database Roles
Assigning User-Defined Database Roles
Verifying Security

Section 3: Authorizing Users to Access Resources
In this section, students will see how object permissions are managed. In addition to access permissions on database objects, SQL Server provides the ability to determine which users are allowed to execute code, such as stored procedures and functions. In many cases, these permissions and the permissions on the database objects are best configured at the schema level rather than at the level of the individual object. Schema-based permission grants can simplify your security architecture. You will explore the granting of permissions at the schema level in the final lesson of this module.

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

Lab :Authorizing users to access resources
Granting, Denying, and Revoking Permissions on Objects
Granting EXECUTE Permissions on Code
Granting Permissions at the Schema Level

Section 4: Protecting Data with Encryption and Auditing
This section discusses the available options for auditing in SQL Server, how to use and manage the SQL Server Audit feature, and how to implement encryption.

Topics :
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
Working with SQL Server Audit
Encrypt a Column as Always Encrypted
Encrypt a Database using TDE

Section 5: Recovery Models and Backup Strategies
In this section, students will learn 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.

Topics :
Understanding Backup Strategies
SQL Server Transaction Logs
Planning Backup Strategies

Lab :Understanding SQL Server recovery models
Plan a Backup Strategy
Configure Database Recovery Models

Section 6: Backing Up SQL Server Databases
In this section, students will learn how to perform SQL Server backups, including full and differential database backups, transaction log backups, and partial backups.

Topics :
Backing Up Databases and Transaction Logs
Managing Database Backups
Advanced Database Options

Lab :Backing Up Databases
Backing Up Databases
Performing Database, Differential, and Transaction Log Backups
Performing a Partial Backup

Section 7: Restoring SQL Server 2016 Databases
In this section, students will see how to restore user and system databases and how to implement point-in-time recovery.

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

Lab :Restoring SQL Server Databases
Restoring a Database Backup
Restring Database, Differential, and Transaction Log Backups
Performing a Piecemeal Restore

Section 8: Automating SQL Server Management
This section discusses how to use SQL Server Agent to automate jobs, how to configure security contexts for jobs, and how to implement multiserver jobs.

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

Lab :Automating SQL Server Management
Create a SQL Server Agent Job
Test a Job
Schedule a Job
Configure Master and Target Servers

Section 9: Configuring Security for SQL Server Agent
This section will discuss how to create a minimal privilege security environment for jobs that run in SQL Server Agent.

Topics :
Understanding SQL Server Agent Security
Configuring Credentials
Configuring Proxy Accounts

Lab :Configuring Security for SQL Server Agent
Analyzing Problems in SQL Server Agent
Configuring a Credential
Configuring a Proxy Account
Configuring and testing the Security Context of a Job

Section 10: Monitoring SQL Server with Alerts and Notifications
This section covers the configuration of Database Mail, alerts, and notifications for a SQL Server instance, and the configuration of alerts for Microsoft Azure SQL Database.

Topics :
Monitoring SQL Server Errors
Configuring Database Mail
Operators, Alerts, and Notifications
Alerts in Azure SQL Database

Lab :Monitoring SQL Server with Alerts and Notifications
Configuring Database Mail
Configuring Operators
Configuring Alerts and Notifications
Testing Alerts and Notifications

Section 11: Introduction to Managing SQL Server by using PowerShell
This section looks at how to use Windows PowerShell with Microsoft SQL Server.

Topics :
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
Getting Started with PowerShell
Using PowerShell to Change SQL Server settings

Section 12: Tracing Access to SQL Server with Extended events
This section focuses on the architectural concepts, troubleshooting strategies and usage scenarios of Extended Events.

Topics :
Extended Events Core Concepts
Working with Extended Events

Lab :Extended Events
Using the System_Health Extended Events Session
Tracking Page Splits Using Extended Events

Section 13: Monitoring SQL Server
SQL Server provides a number of tools that you can use to monitor current activity and record details of previous activity. Students will become familiar with what each of the tools does and how to use them, and how to analyze their output.

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

Lab :Monitoring SQL Server

Section 14: Troubleshooting SQL Server
Students will become familiar with the most common issues that can arise when working with SQL Server systems, and develop a solid methodology for resolving issues.

Topics :
A Trouble Shooting Methodology for SQL Server
Resolving Service Related Issues
Resolving Connectivity and Log-in issues

Lab :Troubleshooting Common Issues
Troubleshooting and Resolving a SQL Login Issue
Troubleshooting and Resolving a Service Issue
Troubleshooting and Resolving a Windows Login Issue
Troubleshooting and Resolving a Job Execution Issue
Troubleshooting and Resolving a Performance Issue

Section 15: Importing and Exporting Data
In this section, students will explore these tools and techniques so that you can import and export data to and from SQL Server.

Topics :
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
Import and Excel Data Using the Import Wizard
Import a Delimited Text File Using bcp
Import a Delimited Text File using BULK INSERT
Create and Test an SSIS Package to Extract Data
Deploy a Data-Tier Application

Available Locations

Alabama
Birmingham
Birmingham South
Huntsville
Mobile
Montgomery

Arizona
Mesa
Phoenix
Scottsdale
Tucson

Arkansas
Bentonville
Fayetteville
Little Rock
Little Rock west

California
Bakersfield
El Segundo
Elk Grove
Fresno
Glendale
Irvine
Los Angeles
Los Angeles West
Mission Valley
Orange
Palo Alto
Petaluma
Pleasanton
Redwood City
Riverside
Sacramento
San Diego
San Diego downtown
San Francisco
San Jose
Walnut Creek
Woodland Hills

Colorado
Boulder
Centennial
Colorado Springs
Denver
Fort Collins
Grand Junction
Lakewood
Loveland

Connecticut
Cheshire
Danbury
East Granby
Hartford
Rocky Hill
Shelton
Stamford

Delaware
Wilmington DE

Florida
Boca Raton
Brandon
Clearwater
Daytona Beach
Fort Myers
Fort Walton Beach
Ft Lauderdale
Jacksonville
Kissimmee
Lake Mary
Lakeland
Land O Lakes
Melbourne
Miami
Miami Doral
Miramar Beach
Naples
Orange Park
Orlando
Orlando Downtown
Orlando Northeast
Palm Beach Gardens
Pensacola
Plantation
Sarasota
St Petersburg
Tallahassee
Tampa
Vero Beach

Georgia
Alpharetta
Atlanta Buckhead
Augusta
Columbus GA
Duluth
Kennesaw
Macon
Savannah

Idaho
Boise
Meridian

Illinois
Chicago
Gurnee
Joliet
Naperville
Northbrook
Oakbrook Terrace
Peoria
Rockford
Schaumburg

Indiana
Carmel
Evansville
Fort Wayne
Indianapolis
Indianapolis downtown
South Bend

Iowa
Cedar Rapids
Des Moines
Quad Cities

Kansas
Lenexa
Topeka
Wichita

Kentucky
Erlanger
Lexington
Louisville

Louisiana
Baton Rouge
Lafayette
New Orleans

Maine
Portland ME

Maryland
Annapolis
Baltimore
Bethesda
Columbia MD
Ellicott City
Frederick
Germantown
Greenbelt
Owings Mills
Rockville
Towson

Massachusetts
Boston
Braintree
Danvers
Mansfield
Newton
North Andover
Norwell
Springfield MA
Westborough
Woburn
Worcester

Michigan
Ann Arbor
Franklin MI
Grand Rapids
Kalamazoo
Lansing
Livonia
Novi
Traverse City
Troy

Minnesota
Bloomington
Maple Grove
Minneapolis
Minneapolis downtown
Rochester MN
St Cloud
Woodbury

Mississippi
Gulfport
Jackson

Missouri
Columbia MO
Kansas City
Lees Summit
O Fallon
Springfield
St Louis
St Louis downtown

Montana
Great Falls

Nebraska
Lincoln
Omaha

Nevada
Henderson
Las Vegas
Reno

New Hampshire
Bedford
Concord
Londonderry
Nashua
Portsmouth

New Jersey
Bridgewater
East Brunswick
East Rutherford
Hamilton
Jersey City
Mahwah
Morristown
Mt Laurel
Paramus
Princeton
Red Bank

New Mexico
Albuquerque

New York
Albany
Bohemia
Brooklyn
Buffalo
Harrison
Melville
Mt Kisco
New York City - Grand Central Station
New York City - Lower Manhattan
New York City - Penn Station
Poughkeepsie
Queens
Rochester
Syracuse
Uniondale

North Carolina
Asheville
Chapel Hill
Charlotte
Charlotte North
Charlotte Uptown
Fayetteville
Greensboro
Hickory
Morrisville
Raleigh
Wilmington

North Dakota
Fargo
Grand Forks

Ohio
Akron
Beachwood
Blue Ash
Cincinnati
Cleveland
Columbus
Columbus Downtown
Dayton
Dublin
Independence OH
Norwood
Toledo
Westlake
Youngstown

Oklahoma
Edmond
Oklahoma City
Tulsa

Oregon
Beaverton
Eugene
Medford
Portland
Salem
Tualatin

Pennsylvania
Allentown
Conshohocken
Erie
Harrisburg
Huntingdon Valley
Lancaster
Malvern
Philadelphia
Pittsburgh
Pittsburgh downtown
State College
University Center
Wexford
York

Rhode Island
Providence
Warwick

South Carolina
Cayce
Greenville
Mount Pleasant

South Dakota
Sioux Falls

Tennessee
Chattanooga
Franklin
Knoxville
Memphis
Nashville

Texas
Addison
Amarillo
Austin
Austin downtown
Bryan
Corpus Christi
Dallas
El Paso
Fort Worth
Grapevine
Houston
Houston North
Irving
Katy
Keller
League City
McAllen
Midland
Plano
Plano Frisco
San Antonio
San Antonio downtown
Sugar Land
The Woodlands
Waco

Utah
Provo
Salt Lake City

Vermont
Shelburne

Virginia
Alexandria
Arlington VA
Charlottesville
Fairfax
Fredericksburg
Glen Allen
Lynchburg
Manassas
McLean
Newport News
Norfolk
Reston
Richmond
Roanoke

Washington
Bellevue
Bellingham
Bothell
Kennewick
Mukilteo
Olympia
Puyallup
Seattle
Spokane
Tacoma

Washington DC
Washington DC
Washington DC - Union Station

West Virginia
Martinsburg

Wisconsin
Brookfield
Glendale WI
Green Bay
Madison
Madison east
Milwaukee

Wyoming
Casper
Cheyenne


Online Self-Paced Training Value Package Only $149

"The Instructor made class truly interesting and put everything into real world terms."
- Meredith T(Jersey City, NJ)


Business Computer Skills BBB Profile