Microsoft SQL Administration course Beginner level by SQL School

Module 1 - SQL Server Fundamentals & T-SQL

1. SQL Server Introduction & Installation
SQL Server Installation
Services & Authentication
SQL Server Components & Firewall
SSMS, Configuration Tools & SMO
Protocols & Server Aliases
Linked Servers & Types
2. SQL Server 2012 Database Design
SQL Database Creation
Files & File Group Options
Data File Recommendations
Log File Recommendations
DB Offline & Online Options
Database Modifications
3. SQL Server Tables & Data
Table Design and T-SQL Data Types
INSERT & SELECT Options, Aliasing
Schemas, Tables & File Group Usage
DELETE versus TRUNCATE
4. Data Validations and Constraints
Constraints - Properties & Objects
UNIQUE KEY and NOT NULL
PRIMARY KEYS & Usage
Composite Keys, CHECK and DEFAULTS
5. Basic Queries and JOIN Queries
EXISTS, APPLY, WHERE
ORDER BY, GROUPING, HAVING
Sub Queries & Nested Queries
Bulk Operations Local & Remote
6. Views and Row Data Security
Views as Virtual Objects
Views as Physical Objects
SCHEMABINDING, ENCRYPTION
Issues with ALTER TABLE
System Views & Dynamic Management Views
Views with Joins and Views 7. Indexes and Query Tuning
Architecture of Table Indexes
Clustered & Non Clustered Indexes
SORT_IN_TEMPDB, ONLINE
FILLFACTOR and PAD_INDEX
Execution Plans and Performance
Unique Indexes and Uses
Working with Indexed Views
8. Stored Procedures and Benefits
Stored Procedures & Use
Variables and Parameters
SCHEMABINDING, ENCRYPTION
INPUT & OUTPUT Parameters
Dynamic SQL Queries
Procedure Cache & Recompilation
9. User Defined Functions and Usage
Functions Use & Types
Scalar & Table Valued Functions
SCHEMABINDING, ENCRYPTION
System Functions and Events
Date, Time, String and Metadata Functions
10. Triggers and Cursors Memory Limitations
DML Triggers & Performance
INSERTED and DELETED tables
STATIC and DYNAMIC cursors
SCROLL & FORWARD_ONLY
LOCAL and GLOBAL cursors
KEYSET Cursors and Usage
Embedding Cursors in SPs
11. Transaction Concepts in Realtime
Transaction Types & Use
Blocking - DML Operations
Blocking - DDL Operations
COMMIT & ROLLBACK Options
SAVEPOINT & CHECKPOINT
Deciding Transaction Types
Querying SQL_MODULES View

Module 2 Basic SQL DBA

12. Transaction Locks and Deadlocks
Significance of LOCKS and Usage
Types of Locks and Lock Hints?
Isolation Levels - Types & Usage
Choosing best Isolation Level
TempDB Issues with Isolation Levels
Recovery Models and Options
Choosing Best Recovery Model
Real-time Data Deployment Issues
13. Database Backups Strategies
Need for Backups Strategies
Database Backups and Partial Backups
FORMAT, INIT, SKIP, CHECKSUM Options
Backup using T-SQL Scripts & UI
Backup validations and Restore Paths
MSDB History Tables, Backup Audits
Compressed Backups and Expiry
Log backups and Truncation options
Backup Audit Tables in MSDB
Media Sets and Media Truncate Options
14. Database Restores and Recovery Paths
Need for Restores and Types
Backup Verification using T-SQL & UI
Restore Phases and Recovery Path
Database, File Group, File Restores
PARTIAL and Piecemeal Restores
Point-In-Time Restores and Options
Compressed Tail Log Restores, Recovery
MOVE and FILELISTONLY Options
15. SQL Server Jobs & Alerts
Creating Jobs using UI
Creating Jobs using T-SQL Scripts
Understanding Job Steps and Schedules
Backup Schedules & Jobs using Scripts
Job Failures and History View
Enabling and Disabling Jobs
Job Activity Monitor and Agent Status
Alert System and DB Mail Configuration 16. Data Replications and DR Process
Replication Architecture and Configuration
Snapshot and Transactional Replications
Merge Replication, DB Recovery process
Peer-Peer with Backup Initialization
Replication Types - PUSH & PULL
Replication Alerts, Warnings and Emails
Replication Monitors and Event Watch
Replication for Load Balancing & DR
Agent Operators, Notifications / Emails
Data Access Modes and Agent Properties
17. Log Shipping and DB Mirroring
Disaster Recovery in SQL Server
Log Shipping Configuration and Modes
Manual DB Failover using Log Shipping
DB Mirroring Configuration and Modes
Automatic and Manual Failover Options
Mirror Monitors, Network Considerations
Comparing Log Shipping and Mirroring
18. Security and Data Encryption
Levels of Security Implementation
Server level Logins and DB level Users
Server Roles and User Mappings
Schema Level Security and DB Roles
Table and Column permissions, DB roles
Data Encryption Keys and Certificates
Login Failures, Authentication Audits
19. Server Audits and DB Monitors
Types of Audits and SQL Tools
Server level Audits and Error Logs
Activity Monitors and IO Usage Reports
SQL Profiler, Deadlock Graphs and Filters
DMVs for Query Audits, Tempdb Audits
DMFs for Index, Session and Log Audits
DMFs, DMVs for Queries, Tempdb Audits
Important Queries for DBCC
Important DMVs and DMFs
Security Audits and Profiling

Module 3 Advanced SQL DBA

20. Database Maintenance Plans (SSIS)
Database Maintenance Plan Strategies
Wizard Based DMPs and Schedules
Control Flow Tasks, Precedence Usage
Index Rebuilds and Reorganization
Deciding Fill Factor and PAD_INDEX Options
Managing MSDB and TEMPDB
Using NOTIFY OPERATOR task in DMP
Using UPDATE STATISTICS and Agent
Maintenance Issues in Real-time
21. Partitions and Full Text Indexes
Table Partitions and Compressions
Aligned Partitions and Fill Factor
Managing Partitions for Query Tuning
Statistics with Indexes and Partitions
Full Text Search Usage and Catalogs
Full Text Indexes for Query Tuning
CHANGE_TRACKING options and Use
Tuning Implementations with Cache
Compressed Indexes with Partitions
22. Database Engine Tuning Advisor (DTA)
Creating Workload Files using Profiler
Workload Tables with SQL Profiler
TUNING Templates and Column Filters
Index Selectivity and DTA Usage
Filtered Indexes and Index Sizing
Role of Statistics in Performance Tuning
Index Fragmentation and REBUILDS
23. Alerts and Troubleshooting
LOG SPACE Issues - Alerts and Solutions
TEMPDB Issues - Alerts and Solutions
MEMORY Issues - Alerts and Solutions
DB STATE Issues - Alerts and Solutions
Network Issues - Alerts and Optimizations
Using PERFMON Counters and Filters
Replication and Mirroring Thresholds
Activity Monitor and Deadlock Issues 25. DB Engine Configurations and PBM
Working with SQL Engine Properties
DTC and Remote Connection Properties
Query Governor and Timeout Options
Database State Properties and Options
Policy Based Management (PBM) Usage
Database Facets and Conditions
Scheduling and Reporting Policies
Resource Governor & Browser Tools
Server Properties and Configurations
26. Server Updates and Upgrades
Establish Downtime, Sample Notifications
PRIOR Maintenance activities, Precautions
Applying Patch/hot fix and Service Packs
Verifying Updates and Smoke Test Options
Understanding Upgrade Advisor Issues
Server Upgrades and Smoke Tests
Rollback Service Packs and Precautions
System Database Rebuilds
27. Windows and SQL Cluster Configurations
Windows and SQL Licensing Options
Domain Controller and Active Directory
Windows and Network Configuration
PING tests and DTC Configurations
QUORUM settings and SAN Settings
SQL Server Cluster Installation
SQL Group and RAID Configurations
Virtual SAN Setup and Verification
28. SQL Cluster Issues and Troubleshooting
SQL Cluster Node Installation
QUORUM Check and DTC Settings
Connection Issues and Drains
SQL Cluster Configurations and Heartbeat
Security Issues with Solutions
Storage Issues with Solutions
Always-On (AAG) Groups and HA
Practical Considerations For HA