SQL DBA Training

1. SQL Server 2012 Installation
2. SQL Server 2014 Installation
3. Server Components and Features
4. Authentication Modes & Logins
5. Instance Configuration Settings
6. Management Studio (SSMS) Tool Usage
7. Type of Databases, System database and User define Database
8. Snapshot Database and sparse files

SQL Server Architecture

1. Page, Extent and Type of Extents
2. File, Type of Files and FileGroup
3. Buffer Pool, Log Pool, SQL Pool
4. Lazy writer, Log writer and checkpoint
5. CHECKPOINT and TRUNCATE Options
6. Type of transaction and Transaction log file

Backup Type, Its Restore and Verification

1. Backups Concept and Mechanism
2. Backups Usage and Limitations
3. Data Backups - Syntax and Types
4. Log Backup Mechanism and Use
5. File Backups and Filegroup Backups
6. COMPRESSION, CHECKSUM and STATS
7. Partial Backups with Filegroups
8. FORMAT, NOFORMAT, INIT and NOINIT
9. CHECKSUM and CONTINUE_ON_ERROR
10. Compatibility, Recovery Model Options
11. Recovery Models and Backups
12. COPY_ONLY Backups and Importance
13. MSDB History Tables and Audits

Locks, Issues and Deadlocks

1. Understanding Lock, Block, Deadlock & Lock Manager
2. SP_WHO and SP_LOCK Outputs
3. Lock Types, Lock Hints & Escalations
4. Avoiding WAITS on PAGE and TABLE
5. Isolation Levels - Types and Usage
6. READ COMMITTED Isolation and Blocking
7. READ UNCOMMITED Isolation Dirty Reads
8. Choosing Isolation Levels For OLTP
9. Statement Locks & Transaction Locks
10. TempDB Issues with Isolation Options
11. Locking Hints to Avoid Blockings
12. Deadlock Detections and XDL Graphs

Performance Tuning

1. Processes, Resource waits
2. Data File I/O
3. Recent Expensive Queries
4. Rebuild indexes, Reorganized Index
5. Checking index fragmentation and maintaining them
6. Database update_statistics
7. Understanding SQL Server Profiler
8. Creating Traces
9. DMVs and DMFs
10. Server Dashboard
11. Performance - Object Execution Statistics

Jobs: Architeture and Monitorintg Options

1. Introduction SQL Agent Service
2. Creating Jobs with T-SQL s
3. Job Steps and Parse Check Options
4. Job Schedules and Notifications
5. Job Executions, Disable/Enable Options
6. Job History and Monitoring Tasks
7. Backup Jobs and SQL Agent Service
8. Scheduling Backups & Strategies

Log Shipping - Architecture, HA and DR

1. Log Shipping Architecture and Jobs
2. Choosing Primary, Standby Servers
3. Log Shipping Monitor Configuration
4. NORECOVERY Configuration and Usage
5. STANDBY Mode Configuration & Usage
6. Log Shipping Jobs and Schedules
7. Backup Jobs and Possible Failures
8. Copy & Restore Jobs with Secondary
9. Log Shipping Monitor Status Reports
10. Manual Failover Process - Options
11. Log Shipping Topology - Limitations
12. Versioning Issues and Data Traffic

DB Mirroring - Architecture, HA and DR

1. DB Mirroring Architecture & Use
2. Mirroring Configuration Scenarios
3. Backups & Restores for Mirrors
4. TCP Endpoints & Network Security
5. High-Safety Automatic failure mode (synchronous mode )
6. High-Safety mode (synchronous mode ) and
7. High-Performance mode (asynchronously)
8. How monitoring of Mirror Server

Replication HA

1. Type of Servers Publisher, Distributor and Subscriber Server
2. Snapshot replications
3. Transactional Replication
4. Transactional With Updatable
5. Merge Replications
6. How monitoring of Replications
7. How Add new Articles in Publisher Server
8. Push Subscriber server and Pull Subscriber server
9. How synchronize data in both side Publish and subscriber ends
10. Types of Replication agents:
11. Snapshot Agent
12. Log Reader Agent
13. Distribution Agent
14. Merge Agent
15. Queue Reader Agent

Clustering Failure HA

1. Connection Setup of Clusters
2. Nodes
3. Quorum
4. Automatic failover
5. Moving to another Node
6. Monitoring Clusters

Security - Logins, Users & Roles

1. SQL Server Security Authentication
2. Server Level Security and Logins
3. Database Level Security Users
4. Schemas and User Mappings - Usage
5. Server Level Roles and Usage
6. Database Level Roles and Usage
7. Testing Security Operations in SQL
8. Login Errors and Password Resets
9. GRANT, DENY & REVOKE Permissions
10. Object Level Security and Levels
11. Database Level Roles and Usage
12. Schema Level Ownership – Benefits
13. How apply security/permission on new user like DB/Server Level

Knowledge of SQL Server Tools

Profiler, DTA, SSMS, SAC, SSCM, PerfMon and Resource monitoring,

Like CPU, Memory, DISK and Network and Overview of all,

DMVs, system sprocs, Performance dashboard,

Spotlight, Data modeling, Apply Patches & Hot fix,

Data modeling etc.

Audits for Database, Server & Queries

1. Query Resources - CPU, IO and Memory
2. Audits - Activity Monitor Usage
3. Audits - SQL Profiler Tool Usage
4. Database Health Check & DBCC
5. Memory Usage and Disk Usage Audits
6. Considerations Using SQL Profiler
7. Deadlock Graphs with SQL Profiler

 SQL Server 2014 New feature (Advance Topics)

1. AlwaysON and Creating AlwaysOn availability groups
2. SnapShot Database
3. In Memory OLTP and OLAP
4. In Memory OLTP scenarios
5. Delayed Durability

Other Benefits Points and High Lights of Training:

1. Every topic will be done by practically
2. Discussions on Real time scenarios
3. Discussions on Frequently asked Interview Questions
4. Resume Preparations
5. Providing All required material
   1. Hands-on practical experience