MicroSoft SQL Server DBA Training course

Module 1 – Basic SQL Server DBA  2014

1. Transaction Locks and Deadlocks

5. Data Replications and DR Process

• Significance of LOCKS and Usage
• Replication Architecture and Configuration
• Types of Locks and Lock Hints?
• Snapshot and Transactional Replications
• Isolation Levels - Types & Usage
• Merge Replication, DB Recovery process
• Choosing best Isolation Level
• Peer-Peer with Backup Initialization
• TempDB Issues with Isolation Levels
• Replication Types - PUSH & PULL
• Recovery Models and Options
• Replication Alerts, Warnings and Emails
• Choosing Best Recovery Model
• Replication Monitors and Event Watch
• Real-time Data Deployment Issues
• Replication for Load Balancing & DR

• Agent Operators, Notifications / Emails

2. Database Backups Strategies

• Data Access Modes and Agent Properties
• Need for Backups Strategies

• Database Backups and Partial Backups

6. Log Shipping and DB Mirroring

• FORMAT, INIT, SKIP, CHECKSUM Options
• Disaster Recovery in SQL Server
• Backup using T-SQL Scripts & UI
• Log Shipping Configuration and Modes
• Backup validations and Restore Paths
• Manual DB Failover using Log Shipping
• MSDB History Tables, Backup Audits
• DB Mirroring Configuration and Modes
• Compressed Backups and Expiry
• Automatic and Manual Failover Options
• Log backups and Truncation options
• Mirror Monitors, Network Considerations
• Backup Audit Tables in MSDB
• Comparing Log Shipping and Mirroring
• Media Sets and Media Truncate Options

7. Security and Data Encryption

3. Database Restores and Recovery Paths

• Levels of Security Implementation
• Need for Restores and Types
• Server level Logins and DB level Users
• Backup Verification using T-SQL & UI
• Server Roles and User Mappings
• Restore Phases and Recovery Path
• Schema Level Security and DB Roles
• Database, File Group, File Restores
• Table and Column permissions, DB roles
• PARTIAL and Piecemeal Restores
• Data Encryption Keys and Certificates
• Point-In-Time Restores and Options
• Login Failures, Authentication Audits
• Compressed Tail Log Restores, Recovery

• MOVE and FILELISTONLY Options

8. Server Audits and DB Monitors

• Types of Audits and SQL Tools

4. SQL Server Jobs & Alerts

• Server level Audits and Error Logs
• Creating Jobs using UI
• Activity Monitors and IO Usage Reports
• Creating Jobs using T-SQL Scripts
• SQL Profiler, Deadlock Graphs and Filters
• Understanding Job Steps and Schedules
• DMVs for Query Audits, Tempdb Audits
• Backup Schedules & Jobs using Scripts
• DMFs for Index, Session and Log Audits
• Job Failures and History View
• DMFs, DMVs for Queries, Tempdb Audits
• Enabling and Disabling Jobs
• Important Queries for DBCC
• Job Activity Monitor and Agent Status
• Important DMVs and DMFs
• Alert System and DB Mail Configuration
• Security Audits and Profiling

9. Database Maintenance Plans (SSIS)

13. DB Engine Configurations and PBM

• Database Maintenance Plan Strategies
• Working with SQL Engine Properties
• Wizard Based DMPs and Schedules
• DTC and Remote Connection Properties
• Control Flow Tasks, Precedence Usage
• Query Governor and Timeout Options
• Index Rebuilds and Reorganization
• Database State Properties and Options
• Deciding Fill Factor and PAD_INDEX Options
• Policy Based Management (PBM) Usage
• Managing MSDB and TEMPDB
• Database Facets and Conditions
• Using NOTIFY OPERATOR task in DMP
• Scheduling and Reporting Policies
• Using UPDATE STATISTICS and Agent
• Resource Governor & Browser Tools
• Maintenance Issues in Real-time
• Server Properties and Configurations

Module 2 – Advanced SQL Server DBA 2014

10. Partitions and Full Text Indexes

14. Server Updates and Upgrades

• Table Partitions and Compressions
• Establish Downtime, Sample Notifications
• Aligned Partitions and Fill Factor
• PRIOR Maintenance activities, Precautions
• Managing Partitions for Query Tuning
• Applying Patch/hot fix and Service Packs
• Statistics with Indexes and Partitions
• Verifying Updates and Smoke Test Options
• Full Text Search Usage and Catalogs
• Understanding Upgrade Advisor Issues
• Full Text Indexes for Query Tuning
• Server Upgrades and Smoke Tests
• CHANGE_TRACKING options and Use
• Rollback Service Packs and Precautions
• Tuning Implementations with Cache
• System Database Rebuilds
• Compressed Indexes with Partitions

15. Windows and SQL Cluster Configurations

11. Database Engine Tuning Advisor (DTA)

• Windows and SQL Licensing Options
• Creating Workload Files using Profiler
• Domain Controller and Active Directory
• Workload Tables with SQL Profiler
• Windows and Network Configuration
• TUNING Templates and Column Filters
• PING tests and DTC Configurations
• Index Selectivity and DTA Usage
• QUORUM settings and SAN Settings
• Filtered Indexes and Index Sizing
• SQL Server Cluster Installation
• Role of Statistics in Performance Tuning
• SQL Group and RAID Configurations
• Index Fragmentation and REBUILDS
• Virtual SAN Setup and Verification

12. Alerts and Troubleshooting

16. SQL Cluster Issues and Troubleshooting

• LOG SPACE Issues - Alerts and Solutions
• SQL Cluster Node Installation
• TEMPDB Issues - Alerts and Solutions
• QUORUM Check and DTC Settings
• MEMORY Issues - Alerts and Solutions
• Connection Issues and Drains
• DB STATE Issues - Alerts and Solutions
• SQL Cluster Configurations and Heartbeat
• Network Issues - Alerts and Optimizations
• Security Issues with Solutions
• Using PERFMON Counters and Filters
• Storage Issues with Solutions
• Replication and Mirroring Thresholds
• Always-On (AAG) Groups and HA
• Activity Monitor and Deadlock Issues
• Practical Considerations For HA

http://futurepointtech.com/sql-server-dba-training.html

http://futurepointtech.com/sql-server-dba-training.html