Best Training Institute For SQL DBA

SQL DBA Training

 

SQL Database Administration (SQL DBA) Course. Carefully designed for starters as well as for working professionals. Course includes SQL Server Architecture in detail, Understanding Query Design, Query Performance Tuning, Upgrades, Migrations, Troubleshooting, Disaster Recovery (DR) and High Availability (HA) Solutions including SQL Clustering with Always-On (AAG). This SQL DBA Course also includes One Real-time Project, Resume and Placement Support.

 

Schedules & Timings

 

 Training Modes:   Online / Classroom / Video
   
 Training Silos: Fast-Track / One-One / Weekend

 * Job Support Available

Register Today

 

 

  Trainer Name Exp Details
  1   Mr Sai Phanindra 10+ Years   Profile
       
  2   Mr S Mishra 12+ Years   Profile
 
SQL DBA Training - Highlights
Register Today

 

  Completely Real-time and Practical

  Detailed DB & Table Design (Banking DB)

  Constraints, Views, Joins Upto 24 Tables

  Conditional MERGE, Recursive, PIVOT, RANK

  Dynamic SQL with Stored Procedures

  Triggers, Indexed Views & Transactions

  Save Points, Query Hints, BLOB & XML

 

One Real-Time Project

 



 

 

SQL DBA Training Course Content

 

Course Content

 

SQL Server

SQL Introduction & Installation
  • SQL Server 2012 Installation
  • SQL Server 2014 Installation
  • WMI Checks & Configuration Tool
  • Server Components and Features
  • Firewall Options and Instance Rules
  • Service Accounts, Purpose and Usage
  • Authentication Modes & Logins
  • Instance Configuration Settings
  • Understanding Service Accounts, Usage
  • Management Studio (SSMS) Tool Usage
  • Shared Features, Client Components
SQL Server Database Design
  • SQL Server Database Architecture
  • DB Catalog and Storage Options
  • Data Files : Purpose and Storage
  • Filegroups : Purpose and Usage
  • Log files : Purpose and Audits
  • Transaction Log Files - Considerations
  • Sizing and Placement - Data Files
  • Sizing and Placement - Log Files
  • Locations and Sizing Recommendations
  • Filegrowth and MAXSIZE Options
  • Scripting Database Structures
  • Database ALTERs and Considerations
  • Designing Very Large Databases (VLDB)
  • Placement of Files and Filegroups
SQL Server Tables Design
  • Tables Design Concepts, Normal Forms
  • Table Creation Process and Options
  • Role of Schemas and Table Design
  • DML, DDL and SELECT Operations
  • Single Row and Multi-Row Inserts
  • File Groups and Table Data Mapping
  • Table Aliases, Column Aliases, Usage
  • INSERT & SELECT Variants in T-SQL
  • Data Types and Column Usage
  • Row size Computationsand NULLs
  • Basic INSERT, UPDATE, DELETE
  • Multi-Row Inserts & SELECT
  • SELECT Qeries with Schema References
  • Basic Data Types and NULL Values
  • Design Limitations with UPDATES
  • Role of Log Files for DML Operations
  • DELETE versus TRUNCATE Statements
Normal Forms, Keys & Constraints
  • Constraints and Keys - Purpose, Usage
  • Normal Forms and Relational DB Design
  • OLTP Database and BCNF - Relations
  • NULLABILITY Property and Importance
  • UNIQUE KEY Constraints, Importance
  • PRIMARY KEY Constraints, Limitations
  • Use of FOREIGN KEY References
  • FOREIGN KEY Constraints - Relation
  • Schema Level and Table Level Relations
  • CHECK Constraints - Usage, Limitations
  • DEFAULT Column Constraints, Usage
  • Identity Property with PRIMARY KEY
  • Composite Primary Keys & Usage
  • Constraints with Naming Conventions
Views, JOINS and Sub Queries
  • Benefits of Views in SQL Database
  • Defining Views on Tables and Views
  • Views as Stored SELECT Statements
  • SCHEMABINDING, ENCRYPTION Options
  • Issues with Views For Data Validations
  • Cascaded Views, WITH CHECK OPTION
  • Orphan Views - Realworld Scenarios
  • Common System Views & Metadata
  • Limitations, Real-world Considerations
  • Database Diagrams and Entity Relations
  • INNER JOIN and Table Intersections
  • OUTER JOINS - Types & Comparisons
  • CROSS JOIN, Alternatives and Usage
  • Views on Multi Level Tables - Joins
Views with Joins - Queries
  • MERGE Operations - Benefits, Usage
  • TARGET & NOT MATCH with MERGE
  • Query Joins in Updates and Deletes
  • Working with SET Options and Joins
  • NOCHECK Options and Limitations
  • Table Comparisons and Options
  • Use of GO statement in Batch Scope
  • Nested Queries - Benefits and Usage
  • Sub Queries, Nested Queries, Usage
  • Temporary Tables and Object Types
  • Multi Value Insert Statements in T-SQL
  • Synonyms - Creation, Purpose, Usage
  • Synonyms - Joins, Usage & Limitations
Indexes and Query Tuning
  • Indexes Architecture, Types, Purpose
  • Clustered Indexes - Architecture, Usage
  • Non Clustered Indexes - Usage
  • Indexes on Table Columns With Options
  • Execution Plans - Table & Index Scan
  • Index Seek and Multi Index Options
  • SORT_IN_TEMPDB & FillFactor Options
  • INCLUDED Indexes & Query Optimizer
  • Materialized Views (Indexed Views)
  • Constraints and Keys with Indexes
  • Indexes for Joins & UNIQUE Constraints
  • Using Indexes in Views and Query Plans
Stored Procedures & Benefits
  • Stored Procedures - Purpose, Usage
  • Use of Variables & Parameters(IN / OUT)
  • Stored Procedures for Data Validations
  • Stored Procedures for Dynamic Queries
  • Stored Procedures for Data Reporting
  • System Procedures @ Metadata Access
  • IF.. ELSE and IF .. ELSEIF Conditions
  • RECOMPILE Options and Execution Plans
  • Common Table Expressions (CTE), Uses
  • CTE for Data Retrieval and Updates
  • CTE Usage and Query Performance
  • Dynamic SQL Queries and Parameters
  • OUTPUT Parameters and Options
Functions (UDF) and Queries
  • Functions: Types, Purpose and Usage
  • Scalar Value Returning Functions, Usage
  • Inline Table Value Returning Functions
  • Multi-line Table Value Functions
  • Data Generation with Table Variables
  • Function Arguments - Usage Options
  • SCHEMABINDING, ENCRYPTION Options
  • Using System Functions & Metadata
  • Date Functions, CAST and CONVERT()
  • CROSSAPPLY and GROUP BY Operations
  • HAVING, WHERE Conditions in SELECT
  • ROW_COUNT(), GROUPING(), ROLLUP()
  • ELSE .. IF, WHILE and Iteration Loops
  • Joins - Table, Views & Functions
Triggers and Memory Limitations
  • Use of Triggers - Purpose and Scope
  • DML Triggers and Performance Impact
  • FOR / AFTER DML Triggers - Importance
  • INSTEAD OF DML Triggers - Importance
  • INSERTED and DELETED Magic Tables
  • Triggers for DML Audit Operations
  • Triggers for Data Sampling Operations
  • Database Triggers - Design and Usage
  • Server Triggers - Design and Usage
  • Using Triggers for Bulk Operations(DML)
  • Using Triggers for Updatable Views
  • Using Triggers for Data Distribution
  • Triggers with Join Queries, Cascades
  • Memory Limitations & Performance
Cursors, Joins and Queries
  • Cursor Variables in T-SQL Queries
  • Cursor Variables in Joins, Data Access
  • Cursor For Dynamic SQL Programming
  • Cursors With Stored Procedures
  • Cursor Types - Benefits and Options
  • FORWARD_ONLY & SCROLL Cursors
  • STATIC and DYNAMIC Cursors - Memory
  • LOCAL and GLOBAL Cursor Types
  • KEYSET DRIVEN Cursors, Performance
  • SPs with Joins, Temp Tables & Cursors
  • Embedding Cursors @ Stored Procedures
  • SPs with Cursors for Dynamic Loads
  • Table Variables Versus Temp Tables
Transaction and Stored Procedures
  • Understanding Transactions Concepts
  • ACID Properties, Transaction and Types
  • EXPLICIT Transaction Types & Usage
  • IMPLICIT Transactions Types, Options
  • AUTOCOMMIT Transactions, Advantages
  • Transactions Nesting - Rules and Usage
  • SAVEPOINT & Query Blocking Scenarios
  • Real-world Blocking Problems, Solutions
  • Lock Hints and CTE Queries - Tuning
  • Using CTE and Temp Tables in SPs

Basic DBA

Locks, Issues and Deadlocks
  • Understanding Locks & Lock Manager
  • SP_WHO2 and SP_LOCK Outputs
  • Lock Types, Lock Hints & Escalations
  • Avoiding WAITS on PAGE and TABLE
  • Isolation Levels - Types and Usage
  • READ COMMITTED Isolation and Blocking
  • READ UNCOMMITED and Dirty Reads
  • SERIALIZABLE and REAPEATABLE
  • SNAPSHOT & READCOMITTED SNAPSHOT
  • Choosing Isolation Levels For OLTP
  • Statement Locks & Transaction Locks
  • TempDB Issues with Isolation Options
  • Locking Hints to Avoid Blockings
  • A DEADLOCK Scenario and DML Audits
  • Deadlock Detections and XDL Graphs
Backups - Architecture and Management
  • Backups Concept and Mechanism
  • Backups Usage and Limitations
  • Data Backups - Syntax and Types
  • Log Backup Mechanism and Use
  • CHECKPOINT and TRUNCATE Options
  • Tuning Database Backup Operations
  • File Backups and Filegroup Backups
  • COMPRESSION, CHECKSUM and STATS
  • Partial Backups with Filegroups
  • Backup Mirrors and Restore Options
  • Backup Splits and Restore Options
  • Media Set, Media Family and Options
  • FORMAT, NOFORMAT, INIT and NOINIT
  • Remote Backups and Security Options
  • CHECKSUM and CONTINUE_ON_ERROR
  • Compatibility, Recovery Model Options
  • Recovery Models and Backups
  • COPY_ONLY Backups and Importance
  • MSDB History Tables and Audits
  • Important Queries for Backup Audits
  • Backup Verification Procedures
Restores - Architecture and Management
  • Recovery Paths and FILELIST Options
  • Database Restores & RECOVERY Options
  • File Restores and Filegroup Restores
  • FILELISTONLY and VERIFYONLY Options
  • MOVE, REPLACE Restore Options
  • PARTIAL and Piecemeal Restores
  • Tail Log Backups & Database States
  • Re-Restoring (REDO) Log Backups
  • Compressed Tail Log Restores
  • Re-Restoring (REPLACE) Backups Sets
  • Restores for Partial Online Databases
  • Choosing Right Backups for Restores
  • SQL 2012 Backup to SQL 2014 Restore
  • Partial Backups and STATS Options
  • Choosing correct Recovery Model
  • Point-In-Time Restores & Recovery
  • Overwrite Options for DB Restores
  • Backup Devices Creation & Usage
Jobs: Architeture and Monitorintg Options
  • Understanding SQL Agent Service
  • Creating Jobs with T-SQL Scripts
  • Job Steps and Parse Check Options
  • Job Schedules and Notifications
  • Job Executions, Disable/Enable Options
  • Job History and Monitoring Tasks
  • Backup Jobs and SQL Agent Service
  • Scheduling Backups & Strategies
  • Backup Devices With Job Steps
Replication - Architecture, HA and DR
  • Replication Use & Importance
  • Replication Architecture & Entities
  • Articles, Publications, Subscriptions
  • Deciding Distribution Components
  • Distributor and Snapshot Folders
  • Distribution Configurations, Options
  • Articles, Filters & Publication Options
  • Article Dependencies & Encryptions
  • Snapshot Replication and Agents
  • Working of Transactional Replication
  • PUSH and PULL Subscriptions - Options
  • Replication Monitor - Usage & Options
  • Replication Jobs and Verifications
  • Merge Replication and merge Agent Job
  • Replication Conflicts and ROWGUIDCOL
  • Tuning Merge Agent and Warnings
  • Domain Account Security in Replication
  • Limitations with Merge Replication
  • Pull Subscribers and Distributor
  • Working of Peer-Peer Replication
  • Adding Peer Nodes, Node ID Conflits
  • Replica Initialization with Backups
  • Replication Conflicts and Solutions
  • Replication Events and Data Audits
  • Replication for HA and DR Procedures
Log Shipping & DB Mirroring - HA & DR
  • Log Shipping Architecture and Jobs
  • Choosing Primary, Standby Servers
  • Log Shipping Monitor Configuration
  • NORECOVERY Configuration and Usage
  • STANDBY Mode Configuration & Usage
  • Log Shipping Jobs and Schedules
  • Backup Jobs and Possible Failures
  • Copy & Restore Jobs with Secondary
  • Log Shipping Monitor Status Reports
  • Manual Failover Process - Options
  • Log Shipping Topology - Limitations
  • Versioning Issues and Data Traffic
  • Log Shipping for DR Procedures
  • DB Mirroring Architecture & Use
  • Mirroring Configuration Scenarios
  • Backups & Restores for Mirrors
  • TCP Endpoints & Network Security
  • Heartbeat and Polling Concepts in DM
  • Working with Service Accounts & Use
  • Automatic Fail-Over Procedures, Tests
  • Manual Failover Options & Scenarios
  • PARTNER OFFLINE Conditions & Options
  • DB Mirror Monitors and Commit Loads
  • Real-World Considerations & DR Options
  • DR & HA with DB Mirroring Advantages
Security - Logins, Users & Roles
  • SQL Server Security Authentication
  • Server Level Security and Logins
  • Database Level Security Users
  • Schemas and User Mappings - Usage
  • Server Level Roles and Usage
  • Database Level Roles and Usage
  • Testing Security Operations in SQL
  • Login Errors and Password Resets
  • GRANT, DENY & REVOKE Permissions
  • Common Security Functions & Queries
  • Object Level Security and Levels
  • Database Level Roles and Usage
  • Schema Level Ownership - Benefits
  • Database Certificates and Master Keys
  • Data Level Security and Encryptions
  • Testing Database Encryption Levels
  • Basic Job Level Security & Options
  • SQL Server Credentials & Scripting
  • Proxies - Purpose, Usage and Options
  • Using Server Credentials for Proxies
  • Using Proxies for Job Level Subsytems
  • DMVs for Server and DB Security Audits
  • Useful Security Audit Queries (DMVs)
Audits For Database, Server & Queries
  • Query Resources - CPU, IO and Memory
  • Audits - Activity Monitor Usage
  • Audits - SQL Profiler Tool Usage
  • Database Health Check & DBCC
  • Log Space Usage Audits - DMVs/DMFs
  • Tempdb Usage Audits with DMVs/DMFs
  • Memory Usage and Disk Usage Audits
  • Considerations Using SQL Profiler
  • Deadlock Graphs with SQL Profiler
  • Audit Long Running Queries - DMV/DMF
  • Audit Frequent Queries - DMV/DMF
  • Query Audits and DMVs / DMFs, Joins
  • Audits for Storage Allocation Issues
Data Imports, Exports (SSIS) and DB Mail
  • Import & Export (SSIS) Operations
  • OLE-DB, SQLNCLI and MS Jet Drivers
  • Scheduling SSIS Packages with Jobs
  • SSIS Proxies and Reading Job History
  • Database Scripting & Encryptions
  • Database Cloning Process and Options
  • Configuring Linked Servers - Custom
  • SQL Server Agent - Alert Properties
  • Database (DB) Mail Configurations
  • Securing DB Profiles : SMTP Accounts
  • Creating Operators and SQL Emails
  • Warnings & Emails with Database Mail
  • Testing Job Failures and Notifications
  • Detach - Attach Procedures (Non-SSIS)
  • DB Scripting and Object Encryptions
  • Linked Servers & Security Options

ADV DBA

Realtime Project Scenario
  • Online Book Sales Project - Project Arch
  • SQL Server Architecture - Basic, Detailed
  • SQL Agent Architecture - Thread Level
  • Capacity Planning and Telnet Settings
  • Corpnet and Extranet Connections
  • SLA & OLA Process in Real-time (PROD)
  • SQL DBA Challenges in Real-time
  • Downtime & Maintenance Notifications
  • Using SQL Browser Service for TCP/IP
  • Configuration Manager Tool - Usage
  • SQL Server Log-On Accounts and Usage
  • Resource Governor & Performance
  • Workload Groups and Tuning Policies
  • RECONFIGURE Options & Performance
  • Routine DBA Activities - Checklist
Performance Tuning - Partitions & Indexes
  • Big Data - Performance Considerations
  • Table Partitions and Tuning Options
  • Partition Functions and Usage
  • Partition Schemes and Reusability
  • Deciding Partition Ranges & Functions
  • Partitioned Data For Complex Queries
  • Partitioning Un-partitioned Structures
  • Aligned Partitions and Performance
  • Fill Factor Options and Index Tuning
  • Partitions for Query Tuning Operations
  • Statistics with Table & Index Partitions
  • Data Compression with Partitions
  • Page Compression & Row Compression
  • Partitions & Compressions, Performance
  • Managing Partitions and Tuning Options
Performance Tuning - Full Text Indexes
  • LIKE Operator - Limitations & Wild-cards
  • Full Text Search (FTS) Configurations
  • Full Text Search Service and Options
  • Database Catalogs (FTC) and Storage
  • Full Text Indexes (FTI) for Tuning
  • Full Text Columns and Primary Index
  • Full Text Index and Searching Queries
  • CONTAINS() and FREETEXT() Functions
  • Manual Data Populations, Index Loads
  • CHANGE_TRACKING Options, Limitations
  • Securing FT Catalogs and FT Indexes
  • Performance Advantages @ FT Indexes
  • Implementing FTS @ Partitioned Tables
Performance Tuning - Index Management & DTA
  • Index Internals and Execution Plans
  • Index Fragmentation - Issues, Solutions
  • SAMPLED & DETAILED Query Scans
  • Database Tuning Advisor (DTA) - Usage
  • PDS Options with Indexes For Tuning
  • Choosing Correct Option (PDS) for Tuning
  • Filtered Indexes and Sizing Options
  • Analyzing Work Load Tables and Scans
  • Understanding Statistics in Query Tuning
  • Statistics Role in Query Tuning Process
  • Index Management Options & Statistics
  • Statistics - AUTO CREATE, AUTO UPDATE
  • Stats Updates (Manual) and Issues
  • Index Rebuilds & Tuning Options
  • Table Rebuild Options with Indexes
  • Index Reorganization Process and Uses
  • Page, Row Compressions with Indexes
  • FILLFACTOR, PADINDEX Index Options
  • Filtered Indexes, Online Indexes, Views
  • Understanding Workload Files in Profiler
  • SQL Profiler Workload Tables & Queries
  • SQL Profiler Tuning and Lock Templates
  • Index Selectivity Options and Statistics
Management - SSIS Database Maintenance Plans
  • Database Maintenance Plans (SSIS)
  • DB Maintenance Strategies & Schedules
  • MSDB History Management Options
  • Backup Files and Space Management
  • Reorganizing Indexes with Maint. Plans
  • Stats Updates (Automatic) and Jobs
  • Modifying SSIS DB Maintenance Plans
  • Scheduling and Maintaining SSIS Plans
  • Log File Issues & Shrinking Operations
  • SQL DB Engine Properties & Guidelines
  • Service Configuration Manager Options
  • Distributed Transactions & Connections
  • Query Governor Usage and DOP Options
  • Policy Based Management (PBM), Facets
  • Database Properties and Conditions
  • Scheduling Policies, Event Management
  • Considerations for Policy Management
Alerts and Troubleshooting Issues
  • LOG File Space Issues and Solutions
  • TEMPDB Space Issues with Solutions
  • MEMORY Management Issues, Solutions
  • OS Level Memory Clerks and Montoring
  • Memory Leaks, Hits and Solutions
  • Logical & Physical Memory Architecture
  • Memory Buckets, IO Entires
  • Procedure Cache Issues and Solutions
  • Procedure Cache Size & SQL Buffer Size
  • DB State Events, Issues and Solutions
  • Network Usage Audit and Optimization
  • Performance Monitor (PERFMON)
  • Replication Threshold Values & Agents
  • Database Options and Downtime
  • Database States Alerts and DB Events
  • DBCC Commands and Database Repairs
  • Rebuild Operations and Considerations
  • Important DMVs and DMFs for Audits
Database Migrations, Service Packs, Upgrades
  • Establishing Downtime For Maintenance
  • Precautions for Maintenance Activites
  • Detach - Attach (SSIS) - DB Migrations
  • Copy Database Wizard - DB Migrations
  • SMO Connections and Offline Options
  • Service Packs and Patch/hotfix Activites
  • Verifications, SmokeTest and Rollbacks
  • Upgrade Advisor Tool - Analysis Reports
  • Upgrade Advisor Issues and Warnings
  • Server Upgrades and Precautions
  • Planning for Maintenance Activites
  • Rebuilding System Databases, Objects
  • Pre Database Maintenance Activities
  • Post Database Maintenance Activities
  • Real-world Management Considerations
  • Update/Upgrade Rollback Procedures
  • System Database Rebuilds & SQLCMD
Introduction to SQL Server Clusters
  • Understanding SQL Clustering Need
  • SQL Server Clustering Architecture
  • Understanding Ping Tests & Heartbeat
  • Windows and SQL Server Licensing
  • Windows Server Installation Options
  • SP Installation and Quorum Options
  • Installing Windows MSCS Service
  • Testing MSCS Services and AD Options
  • Verifying SQL Cluster Installation
Active Directory and MSDTC
  • Need for Centralized Authentication
  • Domain Controller (DC) Configuration
  • Active Directory Settings and Usage
  • DCPROMO Settings for Active Directory
  • Working with Active Directory (AD) Edits
  • Network Configurations and Usage
  • PING Configurations and DTC Options
  • QUORUM settings and SAN Options
SQL Cluster Configuration
  • SQL Server Cluster Installation
  • Verifying Cluster Configurations
  • Add Nodes to SQL Server Cluster
  • SQL Group & AD Syncup Operations
  • SAN System for Shared Data Storage
  • MS DTC Configurations (Local/Remote)
  • Smoke Test Procedures in Real-time
  • Fail-Over Disk & RAID Implementation
Cluster Issues & Management
  • Cluster Connection Issues and Drains
  • Network Security Issues with Solutions
  • RAID, Storage Issues with Solutions
  • Installing Updates in SQL Clusters
  • QUORUM Checks, Storage Issues
  • SQL Server Cluster Utilities & Usage
  • Cluster Working and Operative Modes
  • Configuration Settings - Monitors
Always-On Availability Groups (AAG)
  • Always-On Availability Groups (AAG)
  • Real-World Considerations For AAG
  • Practical Challenges For HA and DR
  • SQL Server Cluster and AAG Limitations
  • High Availaibility and Uptime in AAG
  • SQL Clusters For High Availability
  • Active-Active Cluster Configurations
  • Active-Passive Cluster Configurations
  • SQL Server Cluster Node Updates

Register Today