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
Our distinct services include :
- Material and Documents: We provide real time working documents and materials to our students.
- Free System Access : We have the latest and most up to date platforms deployed on high-end servers that are available remotely and in-class on a 24/7 basis.
- Interview Preparation: We will prepare you for interviews in your specialized area by providing some real time interview questions. We also conduct mock interviews and assist you in preparing a professional resume.
- Placement Assistance: Our placement service division provides job placement support by submitting your resumes to prospective clients and emails you any new job opportunities in the market. However, we do not provide false guarantees on securing any jobs for you. We have a history of several ex-students who have secured excellent jobs based on our WORLD class training approach, implementation of case study projects and post training support.
- Initial Job Stabilization Support: We support our students to get stabilized in their job in the first TWO months. If our students come across any issues, we are there to help them. Students can send an email to us with their issues, our experts will reply back with possible solutions within 24 hours.
- Free Class Room sessions recording: In addition to the e-class training, we will give you authorization to record all of your e-class room training sessions, so that you can go back and review.