Courses
Basic SQL Server DBA 2014
Module 1 – Basic SQL Server DBA 2014
1. 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
2. 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
3. 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
4. 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
5. 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
6. 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
7. 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
8. 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 2 – Advanced SQL Server DBA 2014
9. 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
10. 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
11. 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
12. 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
13. 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
14. 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
15. 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
16. 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