SQL Server Administration for Developers and DBAs

Learn to manage SQL Server professionally and securely

Course Summary

Are you ready to elevate your skills in working with SQL Server?

While this powerful platform often manages itself, many essential tasks require the expertise of a dedicated Database Administrator (DBA). Our comprehensive course is your gateway to becoming a proficient DBA!

Many professionals find themselves in the role of what we call an “accidental DBA.” You might have been the closest person to the server when an issue arose, or perhaps you’re a developer looking to deepen your understanding of SQL Server management. Whether you’re new to the field or have been self-taught, this course is designed to provide you with the solid foundation that many aspiring DBAs wish they had.

Ask Yourself:

Have you worked with SQL Server?
Do you need to manage one or more servers but feel underprepared?
Are you familiar with SQL Server security?
Do you understand file structures, file groups, and compression?
Can you automate regular tasks securely?
Are you ready to move beyond the basics and learn from an expert?

If any of these relate to you, this course is tailor-made for you! Join us for detailed instruction, engaging practical exercises, and quizzes designed to reinforce your learning.

Don’t miss this opportunity to invest in your career and unlock your full potential in database administration! This is the most comprehensive course on the market.

Enroll now for $295 USD !

Modules and Lessons

Module 0: Getting started

Who is this course for ?
Who is Greg ?
What will I learn in this course ?
Configuring your lab environment
Labs overview and instructions

Module 1: Core concepts

Module introduction
What is SQL Server?
What is T-SQL?
Types of SQL statements
Logins
Users
SSMS
Documentation
Server names and instances
Databases
Schemas
Tables
Columns
Stored procedures
Functions
Module 1 quiz
Module 1 lab

Module 2: Planning for SQL Server installation

Module introduction
Determining required resources
Planning for IO
Planning for CPU
Planning for memory
Planning for networking
Planning for service accounts
Virtualizing SQL Server
Pre-installation testing
Downloading installation media
Downloading latest CU
Downloading Report Server
Module 2 quiz
Module 2 lab

Module 3: Installing SQL Server

Module introduction
Installation resources
Installation window order
Edition
License terms
Global rules and updates
Install rules
Installation type
Azure extension
Feature selection
Instance configuration
Server configuration
Reporting services
Patching
Post-installation checks
Unattended installation
Renaming a server
Changing service accounts
Module 3 quiz
Module 3 lab

Module 4: Managing SQL Server databases

Module introduction
System databases
Database files
Database data
Number of database files
Size of database files
tempdb
Creating a user database
Configuring database options
Filegroups
Moving database files
Moving system databases
Shrinking database files
Module 4 quiz
Module 4 lab
Module 4 lab solution

Module 5: Planning a backup and recovery strategy

Module introduction
Data modification
Transaction log
Full recovery model
Simple recovery model
Bulk logged recovery model
Defining RTO
Defining RPO
Defining 9s of availability
Recommended strategy
Backup testing and retention
Module 5 quiz
Module 5 lab
Module 5 lab solution

Module 6: SQL Server backup and recovery types

Module introduction
Full backup
Differential backup
Log backup
Partial backup
Copy only backup
File or filegroup backup
Tail log backup
Backup media
Restore phases
Recovery types
Strategy example 1
Strategy example 2
Strategy example 3
Strategy example 4
Module 6 quiz

Module 7: Implementing a backup strategy

Module introduction
Perform full backups
Mirrored backups
Striped backups
Verifying backups
Backup compression
Backup encryption
Perform differential backups
Perform transaction log backups
Module 7 quiz
Module 7 lab

Module 8: Implementing a recovery strategy

Module introduction
Viewing backup file contents
Restore a database
Restore transaction logs
With standby
Point in time recovery
Stopping at marked transactions
Perform file or filegroup restore
master database restore
model database restore
Restoring other system databases
Module 8 quiz
Module 8 lab

Module 9: Managing users and roles

Module introduction
Authentication vs Authorization
SQL Server authentication options
Creating Windows logins
Creating SQL Server logins
Database users from logins
Contained database users
Special users
Inpersonation vs Delegation
Mismatched SIDs
Server level permissions
Fixed server roles
public server role
User-defined server roles
Fixed database roles
User-defined database roles
Application roles
Adding role members
Module 9 quiz
Module 9 lab
Module 9 lab solution

Module 10: Managing server and database permissions

Module introduction
Server principals
Database principals
Securables
Assigning permissions
GRANT option
Table and view permissions
Column permissions
Default schemas
Resolving object names
Schema permissions
Stored procedure permissions
Ownership chaining
Function permissions
Assembly permissions
Testing permissions by impersonation
Module 10 quiz
Module 10 lab
Module 10 solution

Module 11: Auditing SQL Server Databases

Module introduction
SQL Server auditing prior to 2008
Using triggers for auditing
Using SQL Trace for auditing
C2 common criteria
SQL Server auditing from 2008
Enabling SQL Server audit
Audit destinations
Server audit specifications
Database audit specifications
Reading audit output
Audits on failover servers
Module 11 quiz
Module 11 lab
Module 11 lab solution

Module 12: Table and index structures

Module introduction
Files extents and pages
Table structure heap
Table structure clustered index
Non-clustered index over heap
Non-clustered index over clustered index
Index column order
RID and clustered index lookups
Covering indexes
Included columns
Filtered non-clustered indexes
Indexed views
Primary keys and indexes
Unique constraints and indexes
Foreign keys and indexes
Module 12 quiz
Module 12 lab
Module 12 lab solution

Module 13: Table partitioning

Module introduction
Table partitioning overview
Using partitioned views
Using vertical partitioning of data
Using partitioned tables
Create partition function
Create partition scheme
Create partitioned table
Benefits of partitioning
Sliding window designs
SWITCH data in and out
Partition alignment for indexes
Module 13 quiz
Module 13 lab
Module 13 lab solution

Module 14: Table compression and columnstores

Module introduction
Large databases are costly
Compress data files?
Table compression
Deprecated vardecimal data type
Page compression
Create with compression
Rebuild with compression
Index compression
Estimating space savings
Space requirements
xVelocity compression
Columnstore indexes
Restrictions in 2012
Enhancements in 2014
Enhancements in 2016
Module 14 quiz
Module 14 lab
Module 14 lab solution

Module 15: Automating management with SQL Server Agent

Module introduction
Automating administration
SQL Server Agent
SQL Server Agent core objects
Creating jobs
Creating job steps
Scripting jobs
Job schedules
Job security
T-SQL job step permissions
Non-T-SQL job step permissions
Credentials and proxies
Working with Powershell and DBA tools
Module 15 quiz
Module 15 lab

Module 16: Configuring database mail

Module introduction
Database mail
Configuring database mail
Managing mail profiles
Mail system parameters
Sending email
Viewing email logs
Module 16 quiz
Module 16 lab

Module 17: Using Alerts and Notifications for Monitoring

Module introduction
Creating and configuring operators
Enabling agent mail properties
Configuring notifications
Configuring alerts
Job activity monitor
Viewing job history
Configuring job history retention
Module 17 quiz
Module 17 lab

Module 18: Automating routine database maintenance

Module introduction
Database integrity
Database page verification
Logical consistency checks
Useful DBCC options for Consistency
Repairing databases
Indexes and performance
Intentional internal fragmentation
Adjusting fragmentation
Database maintenance plans
Module 18 quiz
Module 18 lab

Module 19: Importing and exporting SQL Server data

Module introduction
bcp
BULK INSERT
Improving insert performance
OPENROWSET
SQL Server import and export data
Integration Services
Azure Data Factory
Module 19 quiz
Module 19 lab

Module 20: Governing resources

Module introduction
Resource governor overview
Alternatives to resource governor
Governed resources
Workload groups
Resource pools
Internal and default groups and pools
Effective max and shared percentages
Classifier functions
Useful DMVs
Dynamic change effects
Detecting classifier failures
Best practices to follow
Module 20 quiz
Module 20 lab

Module introduction
Encryption
Transparent database encryption
In-memory OLTP and natively compiled procedures
Filestream and file tables
Replication
Row level security
Data masking
High availability
Arc enabling servers
Tracing activity using SQL Trace and Extended Events
Linked servers
Full-text indexing
Service Broker

Module 22: Next steps

Clean up resources
Summary and further steps