SQL Server Administration

Take your SQL Server skills from querying to full database administration. This hands-on course covers database design, server-side programming, security, backup and recovery, automation, and performance tuning, giving you the practical experience needed to confidently build, manage, and optimize SQL Server environments.

What You'll Learn

By the end of this course, you will be able to:

• Create and manage databases, tables, views, indexes, and schemas in SQL Server
• Write stored procedures, functions, and triggers for server-side logic
• Implement error handling with TRY...CATCH and manage transactions reliably
• Configure SQL Server authentication, users, roles, and permissions
• Perform database backups and restores to protect against data loss
• Create and schedule maintenance plans and SQL Server Agent jobs
• Read execution plans and optimize query performance
• Use the First Responder Kit to diagnose health, performance, and index issues

Requirements

• Basic understanding of relational database concepts
• Familiarity with SQL syntax (SELECT, INSERT, UPDATE, DELETE)

Audience

• Database Developers
• Application Developers
• Data Analysts and Engineers
• System and Database Administrators

Topics

Introduction

• Overview of SQL Server and Relational Databases
• SQL Server Management Studio (SSMS)
• Query Editor and Object Explorer

Data Modification Language (DML)

• Working with Data
• SELECT, INSERT, UPDATE, and DELETE Statements
• Using Joins to Combine Data from Multiple Tables

Data Definition Language (DDL)

• Creating and Designing Tables
• Defining Keys and Constraints
• Creating and Using Views

Server-Side Programming

• Creating and Executing Stored Procedures
• Writing and Using Functions
• Implementing Triggers
• Error Handling with TRY...CATCH
• Managing Transactions

Indexes and Schemas

• Creating and Managing Indexes
• Understanding Clustered and Non-Clustered Indexes
• Working with Database Schemas

System Databases and Configuration

• Exploring System Databases
• Creating and Configuring New Databases

Security and Administration

• SQL Server Authentication
• Managing Users and Roles
• Permissions and Access Control

Backup and Restore

• Creating Database Backups
• Restoring Databases from Backup

Automation and Maintenance

• Creating and Scheduling Maintenance Plans
• Creating and Managing SQL Server Agent Jobs
• Automating Routine Tasks

Performance Monitoring and Tuning

• Reading and Interpreting Execution Plans
• Optimizing Queries for Performance
• Tracing Activity with SQL Server Profiler

Advanced Concepts

• Understanding the Transaction Log
• Recovery Models and Strategies
• Table Partitioning
• Database Snapshots

First Responder Kit

• Health Checks with sp_Blitz
• Performance Analysis with sp_BlitzFirst
• Index Optimization with sp_BlitzIndex
• Query Performance with sp_BlitzCache
• Monitoring Sessions with sp_BlitzWho

SQL Server Integration Services (SSIS)

• Creating an SSIS Package
• Deploying and Scheduling SSIS Packages