UNF Professional and Lifelong Learning
UNF Professional and Lifelong Learning · Microsoft

Administering a SQL Database Training

LevelIntermediate
Duration5 Days
Experience1 year: IT business-level professionals
Average Salary$92,298
LabsYes

Offered to the Jacksonville and Northeast Florida community through UNF Professional and Lifelong Learning in partnership with Applied Technology Academy — live online or in person, taught by ATA's practitioner instructors.

Course Overview

Students will learn to:

  • Authenticate and authorize users and assign server and database roles
  • Use encryption and auditing features to protect data
  • Describe recovery models and backup strategies
  • Back up and restore SQL Server databases, including point-in-time recovery
  • Automate database management using SQL Server Agent and PowerShell
  • Trace and monitor a SQL Server infrastructure for performance and troubleshooting
  • Import and export data using native tools
Course Outline
  • Lesson 1: SQL Server Security
    • Authenticating Connections to SQL Server
    • Authorizing Logins to Connect to Databases
    • Authorization Across Servers
    • Partially Contained Databases
    • Lab: SQL Server Security
  • Lesson 2: Assigning Server and Database Roles
    • Working with Server Roles
    • Working with Fixed Database Roles
    • User-Defined Database Roles
    • Lab: Assigning Server and Database Roles
  • Lesson 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
  • Lesson 4: Protecting Data with Encryption and Auditing
    • Options for Auditing Data Access in SQL Server
    • Implementing and Managing SQL Server Audit
    • Protecting Data with Encryption
    • Lab: Using Auditing and Encryption
  • Lesson 5: Recovery Models and Backup Strategies
    • Understanding Backup Strategies
    • SQL Server Transaction Logs
    • Planning Backup Strategies
    • Lab: Understanding SQL Server Recovery Models
  • Lesson 6: Backing Up SQL Server Databases
    • Backing Up Databases and Transaction Logs
    • Managing Database Backups
    • Advanced Database Options
    • Lab: Backing Up Databases
  • Lesson 7: Restoring SQL Server Databases
    • Understanding the Restore Process
    • Restoring Databases and Advanced Restore Scenarios
    • Point-in-Time Recovery
    • Lab: Restoring SQL Server Databases
  • Lesson 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
  • Lesson 9: Configuring Security for SQL Server Agent
    • Understanding SQL Server Agent Security
    • Configuring Credentials
    • Configuring Proxy Accounts
    • Lab: Configuring SQL Server Agent
  • Lesson 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
  • Lesson 11: Introduction to Managing SQL Server by using PowerShell
    • Getting Started with Windows PowerShell
    • Configure, Administer, and Maintain SQL Server using PowerShell
    • Managing Azure SQL Databases using PowerShell
    • Lab: Using PowerShell to Manage SQL Server
  • Lesson 12: Tracing Access to SQL Server with Extended Events
    • Extended Events Core Concepts
    • Working with Extended Events
    • Lab: Using SQL Server Extended Events
  • Lesson 13: Monitoring SQL Server
    • Monitoring Activity
    • Capturing and Managing Performance Data
    • Analyzing Collected Performance Data
    • Lab: Monitoring SQL Server (using Performance Monitor, Data Collection, and Reports)
  • Lesson 14: Monitoring SQL Server (Troubleshooting Focus)
    • Monitor Current Activity
    • Capture and Manage Performance Data
    • Analyze Collected Performance Data
    • Configure SQL Server Utility
    • Lab: Troubleshooting SQL Server (covered in Lesson 15)
  • Lesson 15: Troubleshooting SQL Server
    • Applying a Troubleshooting Methodology
    • Resolving Service-Related Issues
    • Resolving Connectivity and Login Issues
    • Lab: Troubleshooting SQL Server
  • Lesson 16: 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 Data-Tier Applications
    • Lab: Importing and Exporting Data
Intended Audience
  • The material will also be useful to individuals who
  • develop applications that deliver content from SQL Server databases.
Prerequisites
  • Experience using applications on Windows Servers
  • Experience working with SQL Server or another Relational Database Management System (RDBMS)