SQL DBA Online Training Institute
 
 

SQL DBA Online Training (LIVE, Instructor-Led)

Real-time Practical SQL DBA LIVE Online Training designed to work with all major SQL Database Administration activities including DB Design, Normalized Views, Stored Procedures, Indexes, Joins, DB Maintenance, Query Tuning, HA/DR, AAG and SQL Cluster Issues. Study Material, 24x7 Online Lab, Certification, Job Support with One Realtime Project included in the course. Versions: SQL Server 2012, SQL Server 2014 & 2016.


Schedules for SQL DBA LIVE Online Training (Instructor Led)

Schedule I: 6:30 AM to 8 AM India Time (IST)
  Free Demo: June 30th
Course Starts: July 5th. Register Today
   
Schedule II: 11 AM to 12:30 PM India Time (IST)
  Free Demo: July 14th
Course Starts: July 18th. Register Today
   
Schedule III: 7 PM to 8:30 PM India Time (IST)
  Free Demo: June 19th
Course Starts: June 20th. Register Today

Trainer Profile, Duration, Pricing

Trainer: Mr Sai Phanindra T (9+ Yrs Exp, MS FTE)

Duration: 7 Weeks (Mon - Fri/Sat). Installment Pay.

Total Course Fee: INR 18000/- ~ USD 300

Payments by PayPal
Course includes one Real-time Project, Job Support.

If none of the above LIVE Online Training schedules work for you? then opt for On-demand, Self Paced 24x7 Video Training Course

 

Module I: SQL Server & T-SQL

Duration: 2 Weeks
Module II: Basic SQL DBA

Duration: 2.5 Weeks
Module III: Advanced SQL DBA

Duration: 2.5 Weeks

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

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

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

 

All Classes are Instructor-Led & LIVE. Completely Practical and Real-time with Study Material, Session Notes, Tasks and 24x7 LIVE Server.

 

Trainer : Mr. Sai Phanindra T, 9+ Yrs of Experience exclusively on SQL Server, SQL DBA, more..

Training Feedbacks

Register


Register Today For Free Demo