SQL DBA Online Training Institute
 
 

SQL DBA Online Training (LIVE, Instructor-Led)

Real-time Practical SQL DBA LIVE Online Training for any Production DBA : Capacity Planning, Query Tunining, Index Management, Database Maintenance, High Availability, Disaster Recovery, Temporal Tables, Repairs, Stretch DBs, Always-On in SQL Cluster. Course includes Study Material, 24x7 Online Lab, Certification, Job Support with One Realtime Project. Versions: SQL Server 2012, SQL Server 2014 & 2016.


Schedules for SQL DBA (with Server, T-SQL) LIVE Online Training

Timings (IST) Free Demo Start Date Register
   
6:30 AM to 8 AM April 6th April 6th Register
11 AM to 12:30 PM April 13th April 17th Register
6:30 PM to 8 PM March 25th March 25th Register

 

None of the above schedules work for you? Register @ 24 x 7 Video Training

If you are aware of SQL Server T-SQL then Register for SQL DBA Training
Trainer: Mr Sai Phanindra T (11+ Yrs Exp)

Dur: 7 Weeks (Mon - Sat). Installment Payment

Total Course Fee: INR 18000 / USD 300


* Daily Tasks * Weekly Mock Interviews

* Mini Project * Real-time Project

* Certification Guidance * Placement Services

 

Module I: SQL Server & T-SQL Queries

Duration: 2 Weeks
Module II: Basic SQL DBA

Duration: 2.5 Weeks
Module III: Advanced SQL DBA

Duration: 2.5 Weeks

DAY 0: SQL Server (2016, 2014) Installation

  • What is Database? Need for DB Admins
  • SQL Server Advantages,Editions,Versions
  • Roles, Responsibilities, Levels - SQL DBA
  • How to install SQL Server - Step by Step
  • SQL Server 2016,2014,2012 Installation
  • Common Installation Errors, Solutions
  • Windows Updates, XML Errors, REGEDIT
  • SQL Server Management Studio (SSMS)
  • DBA Checklist - Routine DBA Activities
  • DBA Checklist - Maintenance Activities
  • DBA Checklist - Emergency Activities
  • DAY to DAY Course Plan - Time Table
  • Online Lab Access (24x7 LIVE Server)
  • Mini Project @ Course: L1 DBA Activities
  • Real-time Project: L2, L3 DBA   Register

DAY 1: SQL SERVER ARCHITECTURE

  • SQL Server Architecture - Client/Server
  • Encrypted Data Communication & TDS
  • Data Provider and SQL Connection Types
  • Architecture: Query Processor, Threads
  • Architecture: Storage Engine, File System
  • Architecture: Parser, Optimizer, Mini LSN
  • Architecture: SQL Engine, MDAC, Buffers
  • Architecture: SQLOS Schedules, CLR
  • Write Ahead Log,Lazy Writer,Checkpoints
  • Memory Manager & SQL Server Manager
  • Query Optimizer (QO) and Execution Plans
  • Catalogs & CLR / PowerShell Integrations
  • SQL Database Architecture - RAID Levels
  • SQL DB Architecture: Files and Filegroups
  • Log File Architecture: Virtual Log Files
  • Log File Architecture: Mini LSN & DOP
  • Windows Components for DBA Register

DAY 2: BASIC DDL, DML, DCL, TCL, STATS

  • DDL, DML, DCL, TCL Queries. Types
  • Basic Databases, Tables, SQL Concepts
  • Creating Databases (Basic Level) & Tables
  • Data Store, Data INSERT Variants, Rules
  • Data Retrieval Query Plans - Table Scan
  • Table UPDATEs, Structure Updates, Plans
  • DELETE Versus TRUNCATE. DROP Options
  • SYSTEM DATABASES & MSDB, TempDB
  • Wait Times, Client Statistics @ Queries
  • Implementing TDS @ SQL Server Queries
  • Query Execution Stats with TDS Packets
  • Client and Server Processing Time - Stats
  • Query Waits and Performance Factors
  • Register

DAY 3: SQL SERVER DATABASE DESIGN

  • SQL Server Database Architecture
  • SQL Server Database Design Concepts
  • Database Properties, Storage Options
  • Data Files : Purpose, Sizing, Options
  • Filegroups : Placement, Usage & Options
  • Log files : Sizing, Placement, Growth
  • Database Creation - GUI & T-SQL Scripts
  • Filegrowth, Autogrowth and MAXSIZE
  • Planning, Designing Very Large Databases
  • Database Structure Modifications, ALTER
  • Adding New Filegroups, Files to Database
  • Capacity Planning, Dedicated Log Storage
  • Data File Architecture - Pages, Extents
  • Log File Architecture - VLF, Mini LSN

DAY 4: TABLE DESIGN & SIMPLE QUERIES

  • Table Design - Creating Tables, Options
  • Routing Tables to Database FileGroups
  • Schemas - Purpose, Usage in Tables
  • Table Design @ T-SQL Scripts, Variants
  • Table Design @ GUI (SSMS) - Usage
  • Naming Conventions - Table Columns
  • Single Row Inserts - Multi-Row Inserts
  • Table Aliases, Column Aliases, Usage
  • INSERT, UPDATE, DELETE, TRUNCATE
  • SELECT Queries with Schema References
  • T-SQL Data Types, NULL Comparison
  • Database Log Files for DML Operations
  • DML UPDATES and Design Limitations
  • DELETE Versus TRUNCATE Statements

DAY 5: CONSTRAINTS and KEYS

  • Constraints and Keys - Purpose, Usage
  • Normal Forms, Relational DB Design
  • OLTP Database Model & BCNF - Relations
  • NULLABILITY Property and Importance
  • UNIQUE KEY Constraints and Importance
  • PRIMARY KEY Constraints and Limitations
  • FOREIGN KEY References and Relations
  • FOREIGN KEY Constraints - Limitations
  • Schema Level and Table Level Relations
  • CHECK Constraints - Usage, Limitations
  • DEFAULT Column Constraints and Usage
  • Identity Property with PRIMARY KEY
  • Composite Primary Keys Concept, Usage
  • Constraints with Naming Conventions

DAY 6: JOINS & TUNING OPTIONS

  • Need for Joins and Use Case Scenarios
  • JOIN - Types, Queries and Usage Options
  • CROSS JOIN - Examples and Scenarios
  • INNER JOIN - Examples and Scenarios
  • OUTER JOINS - Types and Scenarios
  • SELF JOINS with Self References, Types
  • Query Tuning & Optimization Options
  • HASH JOIN Examples, Scenarios, HEAPS
  • MERGE JOIN Examples, Scenarios, Uses
  • LOOP JOIN Examples, Scenarios, Cautions
  • Sub Queries and Joins. Alternate Syntax
  • End User Access to Queries - Interfaces
  • Use Case - Combining JOIN Types
    - An Online Medical DB System

DAY 7: VIEWS and SUB QUERIES

  • Views on Tables, Security Advantages
  • Views as Stored SELECT Statements
  • SCHEMABINDING & ENCRYPTION Options
  • Issues with Views For Data Validations
  • Cascaded Views, WITH CHECK OPTION
  • Orphan Views - Scenarios in Realworld
  • Common System Views, Metadata Access
  • Views with Multi Level Tables - Joins
  • Queries - GROUP BY, HAVING Conditions
  • Queries with GROUPING() and HAVING
  • ROLLUP Options & Column Aggregations
  • CUBE on Table Data - Purpose & Usage
  • HAVING Vs WHERE - Usage, Performance
  • Query Execution Order @ ROLLUP, CUBE

DAY 8: FUNCTIONS & SUB QUERIES

  • Functions: Types, Purpose and Usage
  • Scalar Value Returning Functions - Usage
  • Inline Table Value Returning Functions
  • Multi-line Table Value Returning Functions
  • Table Variables and Usage with Functions
  • Function Arguments - Usage and Options
  • SCHEMABINDING & ENCRYPTION Options
  • System Functions and Metadata Access
  • Date & Time Formats, Styles, DATEDIFF
  • SOUNDEX, DIFFERENCE, CASE, ISNULL
  • CAST, CONVERT, TRY_PARSE, COALESCE
  • String Functions, ROW_NUMBER, RANK
  • CASE, MERGE, PIVOT, DENSE_RANK
  • Views for Sub Queries and Functions
  • Real-time Case Study on Joins, Functions, Sub Queries

DAY 9: STORED PROCEDURES - BASICS

  • Stored Procedures - Purpose and Usage
  • Execution Options and Variants, Batch
  • Variables, Parameters in Stored Procs
  • Stored Procedures For Data Validations
  • Stored Procedures For Dynamic Queries
  • Important System Procedures, Metadata
  • Extended Procedures For App. Access
  • IF.. ELSE, ELSE IF, IIF, IS Conditions
  • Error Handling: TRY, CATCH, THROW
  • Usage of THROW - Performance Issues
  • Precompilation of Stored Procedures
  • Cached Plans, Compiled Plans, PC Cache
  • Need for Recompilations, Outdated Plans
  • Dynamic SQL Parameters and Variables
  • Default Parameter Values, Usage Types
  • OUTPUT Parameters and Query Options
  • RECOMPILE Option & ENCRYPTION Option

DAY 10: STORED PROCs @ CURSORS, CTE

  • Cursor Types - Benefits and Options
  • FORWARD_ONLY & SCROLL Cursors
  • STATIC and DYNAMIC Cursors - Memory
  • LOCAL, GLOBAL Cursors Usage Scope
  • KEYSET DRIVEN Cursors, Performance
  • SPs with Joins, Temp Tables & Cursors
  • Nested Sub Queries with Stored Procs
  • Common Table Expressions (CTE) & Uses
  • Using CTE, Data Retrieval in Stored Procs
  • CTEs and Query Tuning Options with SPs
  • CTEs with Tuning Options inside SPs
  • CTEs for Avoiding Self Joins, Scenarios
  • In-Memory Benefits of CTEs,Query Tuning
  • Compilations and Recompilations - CTE
  • Real-world Memory Issues with CTEs

DAY 11: TRIGGERS & TRANSACTIONS

  • Use of Triggers - Use and Performance
  • FOR / AFTER Triggers - Importance
  • INSTEAD OF Triggers - Importance
  • INSERTED and DELETED Memory Tables
  • Triggers for DML Audits, Data Sampling
  • Database Level & Server Level Triggers
  • Bulk Operations & Updatable Views
  • ACID Properties and Transaction Types
  • EXPLICIT Transaction Types, Advantages
  • IMPLICIT Transactions Types, Advantages
  • Open Transactions - Query Blocking
  • AUTOCOMMIT Transactions - Advantages
  • Nesting Transactions and Save Points
  • LOCK HINTS: READPAST, NOLOCK, etc..

DAY 12: INDEXES & QUERY TUNING

  • Indexes Types - B Tree Architecture
  • Clustered Indexes - Architecture
  • NonClustered Indexes - Architecture
  • Execution Plans & Query Optimization
  • Table Scan, Index Scan, Index Seek
  • SORT_IN_TEMPDB and Index Memory
  • FILLFACTOR, PAD_INDEX, Fragmentation
  • INCLUDED Indexes and Performance
  • COLUMN STORE Indexes - Advantages
  • FILTERED Indexes - Query Performance
  • ONLINE Indexes and OFFLINE Indexes
  • Indexes with Merge and Loop Joins
  • Primary Key Index, Composite Indexes
  • STATISTICS - Types and Tuning Options
  • STATISTICS with Index Query Conditions
  • Materialized Views (Indexed Views) Usage
  • Unique Clustered, NonClustered Indexes
  • Index Fragmentations and Precautions
  • Clustered Versus NonClustered Indexes
Register

DAY 13: BACKUPS - DB, FILEGROUP, FILE

  • Backups - Background Threads, MSDB
  • Backups - Types, Importance, Options
  • Data Backups, Checkpoints, Page Level
  • File Backups, Filegroup Backups - Extents
  • Log Backup Mechanism, Data Truncates
  • Partial Backups with ReadOnly Filegroups
  • Tuning Database Backup Operations
  • Mirrored Backups, FORMAT, SKIP, STATS
  • Tape Backups - LOAD, UNLOAD, ERRORS
  • COMPRESSION, CHECKSUM, DB VERIFY
  • CONTINUE_ON_ERROR, Backup Validation
  • Backup History From MSDB Database
  • Backup Audits - Media Set, Media Family
  • Remote Backups and Security Options
  • Compatibility, Recovery Model Options
  • COPY_ONLY Backups and Importance
  • Important Queries for Backup Audits
  • Backup Verification, Security KEYS
  • Backups using GUI and T-SQL Scripts
  • Backup Devices - Purpose, Options

DAY 14: RESTORES & DATABASE RECOVERY

  • Data Recovery using Backups, Log Files
  • Restore Phases - COPY, REDO, UNDO
  • Database Restores & FILELIST Options
  • File Restores and Filegroup Restores
  • FILELISTONLY, VERIFYONLY, REPLACE
  • PARTIAL & PIECEMEAL Restores - Tuning
  • Tail Log Backups & DB Recovery Options
  • REDO Phase with T-Log Backups, Purpose
  • Tail Log Backup Restores, UNDO Phase
  • Partial Backups - STATS, VERIFY Options
  • Strategies: Piecemeal/Piecemeal Restores
  • SQL Server 2012 to 2014,2016 Restores
  • Recovery Models: Choosing Correct Models
  • Point-In-Time Restores and Log Recovery
  • Restores From Backup Devices & Mirrors
  • Restores From existing Databases, Files
  • Restores using SQL Server T-SQL Script
  • Restores using GUI. Data & Log Options
  • Transaction Undo Files (TUF) Purpose
  • Restore Audits and Standby Databases
  • Checkpoint LSN and Restore Positions

DAY 15: JOBS, SQL SERVER AGENT, DB MAIL

  • SQL Server Agent Service, Agent XPs
  • SQL Agent Jobs - GUI Options, MSDB
  • Job Steps: T-SQL, SSIS, Replication
  • Job Schedules and Frequency Options
  • Job Schedules and Email Notifications
  • Job Executions, Disable/Enable Options
  • Job History and Job Activity Monitor
  • Windows Event Notifications, Operators
  • DB Mail Configurations, Database Profiles
  • SMTP Accounts, Email Settings, Ports
  • Default Public Profiles, DB Mail Security
  • Import & Export (SSIS) Operations
  • OLE-DB, SQLNCLI and MS Jet Drivers
  • SSIS Packages for Data Import/Export
  • SSIS Package Store - File System, MSDB
  • SSIS Jobs - Scheduling SSIS Packages
  • Backup Strategies For Minimal Data Loss
  • Startup Jobs, Onetime, Recurring Jobs

DAY 16: REPLICATION For HA & DR -- LEVEL 1

  • Replication Architecture and Entities
  • Replication Topology, Plan, Connections
  • Distributor Server Configurations, Options
  • Distribution DB Components and Options
  • Distribution DB Configuration, Snapshots
  • Publication Types - Purpose, Importance
  • DB Articles, Publications, Subscriptions
  • PULL Subscription Options and Types
  • Snapshot Replication and Repl Agents
  • Configure, Secure, Schedule Snapshots
  • Snapshot Subscriptions and Replica DB
  • Transactional Replication Configuration
  • Log Reader Agent - Configuration, Keys
  • Tracer Tokens - Latency and Ranking
  • Replication Monitor - Usage and Options
  • Read Only Subscribers and Load Balancing
  • Replication Jobs and Verification Options
  • Adding Articles to Existing (LIVE) Replica
  • Adding Subscribers to Existing Replica
  • Schema Level Replication, DDLs in Repl
  • Stopping, Starting Replication Agents
  • Scripting Replication Jobs, Publications
  • Disable/Enable Replication, Agent Stops

DAY 17: REPLICATION For HA & DR -- LEVEL 2

  • Transactional Replication, Tracer Tokens
  • Merge Replication and Merge Agent Job
  • Replication Conflicts and ROWGUIDCOL
  • Peer-Peer Replication - Configurations
  • Peer-Peer Replication Connections, Nodes
  • Adding Peer Nodes, Node ID Conflicts
  • Replication across Lower/Higher Versions
  • Replication Conflicts - Options, sp_MSRepl
  • IDENTITY Property & Schema Replication
  • PUSH and PULL Subscriptions - Options
  • Domain Account Security in Replication
  • Merge, Snapshot Replication Limitations
  • Replica Initialization with Backups, Issues
  • Transactional Versus Snapshot Replication
  • Peer-Peer Versus Merge Replication For HA
  • Load Balancing Options with Replication
  • Replica Server @ Appl Connection Strings
  • Replication Conflicts, Errors and Solutions
  • Replication Warnings and Agent Alerts
  • Replication Errors, Events and Solutions
  • Replication Errors @ DB Engine, Solutions
  • Replication for HA and DR - Strategies
  • Replication Limitations and Precautions

DAY 18: LOG SHIPPING - DISASTER RECOVERY

  • Log Shipping Topology, Operation Modes
  • Primary and Secondary: Recovery Plan
  • Log Shipping Monitor Settings, Alerts
  • NORECOVERY Configuration and Usage
  • STANDBY Mode Configuration, Purpose
  • Log Shipping Jobs & Restore Schedules
  • Copy and Restore Jobs with Secondary
  • Log Shipping Monitor Status Reports
  • Manual Failover Process: Recovery Options
  • Log Shipping Topology - Limitations
  • Versioning Issues, Data Traffic. Data Loss
  • Log Shipping for DB Recovery Procedures
  • Log Shipping Jobs - Errors and Solutions
  • Log Shipping Reports and Data Recovery
  • Log Shipping Standby: Delay Restores
  • Scripting Log Shipping Configurations
  • Standby Disconnections, Latency Options
  • Errors and Solutions @ LS Copy, Restore

DAY 19: DATABASE MIRRORING - HA & DR

  • DB Mirroring Architecture For HA,DR
  • DB Mirroring Configuration Scenarios
  • Backups and Restores for DB Mirroring
  • TCP Endpoints and TCP Network Security
  • Heartbeat and Polling Concepts in DM
  • Service Accounts Configurations, Use
  • Automatic Fail-Over Procedures, Tests
  • Manual Failover Options and Scenarios
  • PARTNER OFFLINE Conditions & Options
  • DB Mirroring Monitors and Commit Loads
  • SYNCHRONOUS & ASYNCHRONOUS Modes
  • Manual Failover and HA Partner Roles
  • Mirroring Monitor, Stop/Resume Options
  • Real-World Considerations & DR Options
  • DR & HA with DB Mirroring Advantages
  • Need for Always-On & Higher Availability
  • DB Mirroring Errors and Solutions

DAY 20: SECURITY MANAGEMENT - LEVEL 1

  • Authentication Types - WINDOWS, SQL
  • Windows Logins and REGEIDT Settings
  • SQL Server Logins, POLICIES, EXPIRY
  • Default Databases, Keys, Languages
  • LOGINS: Server Level Security, Options
  • USERS: Database Level Security, Options
  • SCHEMAS: Object Level Security, Options
  • System Server Roles, Database Roles
  • User Server Level Roles & Permissions
  • User Database Level Roles & Permissions
  • Schemas and DB User Mappings - Usage
  • GRANT, WITH GRANT, DENY, REVOKE
  • CONTROL, OWNERSHIP, AUTHORIZATION
  • Testing Security Operations, Scripting
  • Common Security Functions & Queries
  • DMVs for Server and DB Security Audits
  • Useful Security Audit Queries (DMVs)

DAY 21: SECURITY MANAGEMENT - LEVEL 2

  • KEYS, Passwords - Data Level Encryption
  • CERTIFICATES & TRIPLE-DES Algorithm
  • Master Passwords and Data Encryption
  • Testing Database Encryption Levels
  • Master Passwords and Certificate IDs
  • Basic Job Level Security and Options
  • SQL Server Credentials & Scripting
  • Proxies - Purpose, Usage and Options
  • Server Credentials for Agent Proxies
  • SSIS Proxies for Job Level Subsytems
  • Login Errors - Audits, Server Logs
  • Password Resets and Disabling Logins
  • Scripting Logins, Users, Roles, Schemas
  • Scripting Logins - Users Mappings
  • Scripting Users, Roles Permissions
  • Containment Databases and Purpose

DAY 22-23: DB HEALTH CHECK, AUDIT, LOCKS

  • Query Resources - CPU, IO and Memory
  • Activity Monitor (AM) Tool - CPU, Memory
  • Database File IO, Processes, Query Stats
  • SQL Profiler Tool Usage, Audit Trace Files
  • SQL Profiler Templates: Standard, Tuning
  • Event Extraction Settings, Filter Columns
  • DB ID Filters, CPU Filters, SSID Filters
  • Profile Trace File Rollover, Size Settings
  • Database Health Check: DBCC Commands
  • Allocation Errors and Consistency Errors
  • ESTIMATEONLY, NO_INFOMSGS, Tempdb
  • Log Space, Tempdb Audits with DMVs/DMFs
  • Memory Usage & Disk Usage Audit DMVs
  • Audit Long Running Queries - DMV/DMF
  • Audit Frequent Running Queries - DMF
  • Query Audits and DMVs / DMFs with Joins
  • Storage Allocation Issues (Page, Extent)
  • Deadlock Graphs with SQL Profiler Tool
  • LOCKS - Types, Impact, Monitoring
  • Shared Locks, Exclusive Locks, WAITS
  • Query Impact, SEMAPHORE, MD Locks
  • DEADLOCKS - Avoidance, Prevention
  • LIVELOCKS - Avoidance, Prevention
  • Deadlock Graphs @ SQL PROFILER, XDL
  • Isolation Levels For OLTP - TempDB
  • READ COMMITTED, READ UNCOMMITED
  • SERIALIZABLE, REAPEATABLE Levels
  • SNAPSHOT & READCOMITTED SNAPSHOT

Mini Project for SQL DBA (Jnr. DBA)
   Banking Domain: HR,DR,Security,
   Audits, Monitoring, Capacity Planning

DAY 24: PERFORMANCE TUNING - LEVEL 1

  • Big Data - Performance Considerations
  • Table Partitions and Tuning Options
  • Partition Functions - Range Selectivity
  • 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
  • Data Compression with Row Partitions
  • Page Compression & Row Compression
  • Partitions & Compressions, Performance
  • Managing Partitions and Tuning Options
  • LIKE Operator - Limitations & Wild-cards
  • Full Text Search (FTS) Configurations
  • 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
  • CHANGE_TRACKING Options, Limitations
  • New Tuning Options @ SQL 2016
  • Stretch Databases @ SQL 2016
  • Temporal Tables, Tuning: SQL 2016

DAY 25: PERFORMANCE TUNING - LEVEL 2

  • Index Internals and Execution Plans
  • Index Fragmentation - Issues, Solutions
  • SAMPLED & DETAILED Query Scans
  • Filtered Indexes and Sizing Options
  • Analyzing Work Load Tables and Scans
  • Statistics Updates and Query Tuning
  • Index Management Options & Statistics
  • Statistics - AUTO CREATE, AUTO UPDATE
  • Stats Updates (Manual) - Table Issues
  • Index Reorganization Process and Uses
  • Data Pages - Fragmentation Solutions
  • Index Pages - Fragmentation Solutions
  • Index Rebuilds and Table Page Rebuild
  • 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
  • Page Compressions, Resource Optimize
  • Dynamic Data Masking (DDM) - Performance Issues in SQL 2016

DAY 26: PERFORMANCE TUNING - LEVEL 3

  • Memory Optimized Tables & Usage
  • LIVE QUERY STATS Analysis in SQL 2016
  • Database Tuning Advisor (DTA) - Usage
  • DTA for Long Running Queries, DB Caching
  • DTA for Parallel/Concurrent Queries, Traces
  • DTA Tool with Profiler, Trace Tables, Logs
  • DTA Tool with Procedure (Plan) Cache
  • Real-world Considerations for DTA Tool
  • Resource Governor and Query Tuning
  • Resource Pools Configuration, MODES
  • Workload Groups & Resource Allocation
  • Classifier Functions - Query Load Plans
  • Query Priority, CPU/Memory Limits
  • Query Memory, Index Memory Settings
  • Thread Level Architecture - CPU Nodes
  • Windows Fibres, Priority Boost Options
  • MSDB Space Issues - Agent Performance
  • DOP - Degree Of Parallelism & Options
  • PDS Options with Indexes For Tuning
  • Choosing PDS for Efficient Tuning

DAY 27: PERFORMANCE TUNING - LEVEL 4

  • Distributed Replay Controller, SCOM
  • System Monitor Tool & Activity Monitor
  • PERFMON Counters and PSSDIAG Tools
  • Counters for Query Stats, Agent Stats
  • SQL Browser Service and Dump Files
  • Log Space Issues & Query Performance
  • Tempdb Issues & Query Performance
  • Memory Issues & Query Performance
  • Automated Alerts and Counter Traces
  • Log Space, Tempdb Issues - Solutions
  • MEMORY, PAGE LATCH, WAITS: Solutions
  • Memory Buckets, VM Space Performance
  • Database Consistency Errors, Solutions
  • Procedure Cache Issues and Solutions
  • DB State Events, Issues and Solutions
  • Network Usage Audit and Optimization
  • SQL Buffer, MEMORY TABLES
  • Index Management Options @ SQL 2016

DAY 28: DB MAINTENANCE PLANS, REPAIRS

  • 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
  • Policy Based Management (PBM), Facets
  • Database Properties and Conditions
  • Policy Management, Events, Schedules
  • PAGE REPAIRS - suspect pages @ MSDB
  • FILE REPAIRS, FILEGROUP REPAIRS
  • DATABASE REPAIRS - DB Consistency
  • DATABASE REPAIRS - Data Allocation
  • DB Modes, Emergency, NOWAIT Options
  • Managed DB Backups @ SQL 2016

DAY 29: DATABASE MIGRATIONS, DMA TOOL

  • Database Migration Options with SSIS
  • DB Design Audits @ File System Store
  • Database Detach Options and Downtime
  • Database Attach Options and Scripting
  • Scripting: Schemas, Object, Keys, Data
  • Scripting Security Logins, Permissions
  • Copy Database Wizard (SSIS) Tool Usage
  • File System, SQL Server Package Store
  • Migration Schedules, Jobs, Alerts, Logs
  • Corrupted Logs, FORATTACH, REBUILDS
  • Linked Server Security, Management
  • Memory Dashboard and Buffer Cache
  • Server Dashboard, Connection Audits
  • Policy Management Options and Agent
  • Facets and Conditions : On Demand
  • Facets and Conditions : On Change
  • Policy Based Management (PBM) Reports
  • Data Migration Assistant (DMA) Tool
  • Using DMA Tool for DB Analysis, Upgrades

DAY 30: PATCH UPDATES, SERVER UPGRADES

  • Establishing Downtime For Maintenance
  • Precautions for Maintenance Activities
  • Service Packs and Patch/hotfix Activities
  • Instance Selectivity for Updates, Cautions
  • Pre & Post Patch Operations. Process
  • Verifications, SmokeTest and Rollbacks
  • Upgrade Advisor Tool - Analysis Reports
  • Upgrade Advisor Issues and Warnings
  • Server Upgrades and Precautions
  • Planning for Maintenance Activities
  • Rebuilding System Databases & Objects
  • Pre Database Maintenance Activities
  • Post Database Maintenance Activities
  • Real-world Management Considerations

DAY 31: CLUSTERING CONFIGURATION - HA

  • Understanding SQL Clustering Service
  • SQL Server Clustering Architecture
  • Windows and SQL Server Licensing
  • Windows Server Installation Options
  • Service Pack and Patch Installations
  • QUORUM Options for Windows Clusters
  • SQL Server Cluster Plan - Licensing
  • Installing Windows MSCS Service, Tests
  • Ping Tests and Heart-beat Checks, DC
  • Domain Configurations, Precautions
  • MSCS - Microsoft Cluster Services, Nodes
  • MSCS Service Startup Options, Issues
  • Verifying SQL Server Cluster Installation

DAY 32: CLUSTERING: ACTIVE DIRECTORY, DTC

  • Need for Centralized Authentication
  • Domain Controller (DC) Configuration
  • Active Directory Settings and Usage
  • DCPROMO Settings for Active Directory
  • Server Configuration Manager, Options
  • Configuration Manager Features, Users
  • Configuration Manager, Adding Groups
  • Working with Active Directory (AD) Edits
  • Network Configurations and Precautions
  • PING Configurations and DTC Options
  • QUORUM settings and SAN Options

DAY 33, 34, 35: SQL CLUSTERS, ALWAYS-ON

  • Need for Always - On Availability
  • Always-On Prerequisites, Node Config
  • SQL Server Cluster Installation
  • Cluster Configurations - Active/Active
  • Cluster Configurations - Active/Passive
  • 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 Connection Issues and Drains
  • Network Security Issues with Solutions
  • RAID, Storage Issues with Solutions
  • Installing Updates - SQL Server Clusters
  • Add Nodes to SQL Cluster - Active/Active
  • SQL Server Cluster Utilities & Quorum
  • Cluster Working and Operative Modes
  • Configuration Settings, SQL Monitors
  • Always-On Availability Groups (AAG)
  • Prerequisites for Always-On for HA/DR
  • Primary & Secondary Configurations
  • Synchronization Settings, HA Options
  • Replica Recommendations for Always-On
  • Backup Storage and Types - Strategies
  • Port Settings and Replica Config in AAG
  • Active-Passive Cluster Configurations
  • Flexible Failover Policy, HA/DR Process
  • Availability Replica - Readable Secondary
  • Active Secondary, SYNC, ASYNC (AAG)
  • IP Configurations & WSFC Settings
  • Node Majority, Disk & File Share Majority
  • SQL Server 2016 - Whats new @ AAG
  • Routine DBA Challenges for AAG - HA/DR
  • Temporal Tables - AAG : SQL 2016

 

Real-time Project for SQL DBA
   ECommerce Domain: Always-On,
   HR, DR, PSSDIAG, Tuning, Errors,
   Solutions, 3rd Party Tools, SLA-OLA,
   DBA Checklist Activities (5 Days)
*Above course curriculum applicable for registrations from Feb 6th, 2017.

 

24x7 LIVE Online Server (Lab) with Real-time Databases. Course includes ONE Real-time Project. Register Today

 

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, 11+ Yrs of Experience exclusively on SQL Server, SQL DBA, more..

Training Reviews

Register Now

 

SQL DBA Online Training - Highlights

  • Completely Practical and Real-time
  • Suitable for Starters + Working Professionals
  • Session wise Handouts and Tasks + Solutions
  • Daily Mock Tests and Weekly Revision
  • TWO Real-time Case Studies, One Project
  • Certification & Interview Guidance
  • Detailed SQL Server Architecture, DB Design
  • Query Tuning, Stored Procedures, Linked Servers
  • In-Memory, DAC and Contained Databases
  • Routine DBA Activities, Emergency DBA Activities
  • SQL Profiler, SQLDIAG, DTA and Litespeed Tools
  • High Availability, Disaster Recovery, Always-On

 

Register Today - Other Popular Courses: SQL Server Training, MSBI Training, SSIS / SSAS / SSRS Training [+] More Courses

Register Today For Free Demo