.Transaction Locks & Deadlocks

• Significance of LOCKS
• Types of Locks and Hints?
• Isolation Levels & Usage
• Choosing Isolation Levels
• TempDB Issues & Isolation
• Recovery Models and Options
• Choosing Best Recovery Model
• DB Deployment Issues

2.Database Backups Strategies

• Backups Usage and Format
• Database & Partial Backups
• FORMAT, INIT, CHECKSUM
• Backup with T-SQL & UI
• MSDB History Tables Audits
• Compressed Backups & Expiry

3.Database Restores & Recovery

• Need for Restores and Types
• Backup Verifications
• Restore and Recovery Path
• DB, File Group, File Restores
• PARTIAL & Piecemeal Restores
• Point-In-Time Restores
• Compressed Tail Log Restores

4.SQL Jobs and DR/HA Plans

• Backup Schedules & Jobs
• Job Step Options
• Job Failures and History
• Job Activity Monitor & Status
• Alert System and DB Mail
• Operators and Notifications
• Disaster Recovery Plans

5.Replication Configurations

• Replication for DR Process
• Snapshot and Transactional
• Merge Replication & Recovery
• Peer-Peer Initialization
• Replication - PUSH & PULL
• Replication Monitors & Events
• Remote Distributors for HA

6.Log Shipping & DB Recovery

• Disaster Recovery Solutions
• Log Shipping Modes
• NORECOVERY and STANDBY
• LS Jobs and Schedules
• LS Monitor and Reports
• Manual Failover Procedure
• Log Shipping for DR Process

7.Database Mirroring & DR/HA

• COPY_ONLY & INIT Backups
• DB Mirroring Configuration
• TCP Endpoints & Security
• Automatic Failover Options
• Manual Failover Options
• SET PARTNER Options
• Monitors & Considerations
• DR & HA with DB Mirroring

8.Security and Data Encryption

• Levels of SQL Security
• Server Logins & DB Users
• Server Roles and Mappings
• Schema Level Security
• Database Role and Use
• Permissions, Database Roles
• Encryption Keys & Certificates

9.SQL Server Audits

• SQL Audits and SQL Tools
• Server Audits and Error Logs
• Activity Monitors & IO Reports
• SQL Profiler, Deadlock Graphs
• DMVs for Queries & Tempdb
• DMVs for Sessions & Process
• DMFs for Session and Logs
• System Views for Metadata

10.DB Audits & Queries

• Activity Monitor & Deadlocks
• I/O and CPU Usage
• Backup Audits in MSDB
• System Tables for Restores
• Recovery versus Restores
• DB Backup Strategies
• Linked Server & Security
• Replication & LS Alerts
• DB Mail Configurations
• Warnings, Emails & Operators

11.Database Tasks & Audits

• Import Export Wizard
• OLE-DB Connections
• Limitations of Import/Export
• DB Scripting & Cloning
• Database Properties
• Database Recovery Plans
• Realtime Considerations