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
  • 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

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

live Chat