SQL Server DBA Online Training

Hello sir, I want to join SQL DBA Online Training of SQL School and I am here as I want to know about its course content so please tell me about this??

[Shaleen]

SQL School is strongly committed to provide complete real-time and practical training exclusively on Microsoft SQL Server and Datawarehousing technologies.

It was established in February, 2008

SQL DBA LIVE Online Training designed to work with all major SQL Database Administration activities including DB Design, Normalization Rules, Stored Procedures, Indexes, Joins, DB Maintenance, Query Tuning, HA/DR, AAG and SQL Cluster Issues.

SQL DBA Training Course contents:
Module I: SQL Server & T-SQL
Duration: 2 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 and 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 and Importance
PRIMARY KEY Constraints and Limitations
Use of FOREIGN KEY References
FOREIGN KEY Constraints - Relation
Schema Level and Table Level Relations
CHECK Constraints - Usage, Limitations
DEFAULT Column Constraints and Usage
Identity Property with PRIMARY KEY
Composite Primary Keys & Usage
Constraints with Naming Conventions

Views, JOINS and Sub Queries
Benefit of Views in SQL Server 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 and 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 and 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 and Purpose
Clustered Indexes - Architecture, Usage
Non Clustered Indexes - Usage
Indexes on Table Columns With Options
Execution Plans - Table & Index Scan
Index Seek and Multi Level 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 and Usage
Use of Variables & Parameters (IN / OUT)
Stored Procedures for Data Validations
Stored Procedures for Dynamic Queries
Stored Procedures for Data Reporting
System Procedures For Metadata Access
IF.. ELSE and IF .. ELSEIF Conditions
RECOMPILE Options and Execution Plans
Common Table Expressions (CTE) & Uses
Using CTE for Data Retrieval and Updates
CTE Usage and Query Performance
Dynamic SQL Queries and Parameters
OUTPUT Parameters in Stored Procedures

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 and 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 and 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 Params
Cursor Types - Benefits and Options
FORWARD_ONLY & SCROLL Cursors
STATIC and DYNAMIC Cursors - Memory
LOCAL and GLOBAL Cursor Types - Scope
KEYSET DRIVEN Cursors and Performance
SPs with Joins, Temp Tables & Cursors
Embedding Cursors in Stored Procedures
SPs with Cursors for Dynamic Data 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 and Query Blocking Scenarios
Real-world Blocking Problems - Solutions
Lock Hints and CTE Queries - Tuning
Using CTE and Temp Tables in SPs


Module II: Basic SQL DBA
Duration: 2 Weeks
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 Isolation 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 and Management Options

Module III: Advanced SQL DBA
Duration: 2 Weeks
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 and 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 Compressions & Row Compressions
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 with FT Indexes
Implementing FTS on 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
Understanding PDS Options with Indexes
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 and 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 and 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, Monitoring, Optimization
Performance Monitor (PERFMON) Counters
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

DB Migrations, Service Packs & Server Upgrades
Establishing Downtime For Maintenance
Precautions for Maintenance Activites
Detach - Attach Procedures (SSIS)
Copy Database Wizard - DB Copy/Move
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 and SQLCMD

Introduction to SQL Server Clusters
Understanding SQL Clustering Need
SQL Server Clustering Architecture
Understanding Ping Tests and 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

Contact Details:
SQL School
SequelGate (I) Technologies Pvt. Ltd.
#108, Lane No: 2, Road No: 1
Beside Bus Stop, SR Nagar
Hyderabad - 38 (India)