SQL DBA Online Training (LIVE, Instructor-Led)

SQL DBA Training from Real-time, Certified Trainers. Course includes Capacity Planning, Query Tuning, Index Management, Database Maintenance, High Availability, Disaster Recovery (DR), Temporal Tables, Repairs, Always-On in SQL Cluster, SQL Azure Integration (Cloud), Geo Replication. This SQL DBA Course includes Study Material, 24x7 Online Lab, Certification, Job Support with One Real-time Project. Versions: SQL Server 2012,2014,2016 & 2017.

SQL DBA (with Server, T-SQL, Azure SQL DBA)

  PLAN A PLAN B
Duration 6 Weeks 8.5 Weeks
Completely Practical, Real-time Check-Symbol-for-Yes Check-Symbol-for-Yes
Resume, Mock Interviews, Placements Check-Symbol-for-Yes Check-Symbol-for-Yes
Repairs, Migrations, Security Manage Check-Symbol-for-Yes Check-Symbol-for-Yes
HA - DR, Clustering, Always-On, Tuning Croos-symbol-for-Yes Check-Symbol-for-Yes
MCSA: 70-764 (SQL DBA) Certification Croos-symbol-for-Yes Croos-symbol-for-Yes
MCSA: 70-765 (SQL DBA) Certification Croos-symbol-for-No Croos-symbol-for-Yes
Azure SQL Database Croos-symbol-for-No Croos-symbol-for-Yes
Azure SQL DB Admin Croos-symbol-for-No Croos-symbol-for-Yes
Total Course Fee INR 18000/-
USD 300
INR 24000/-
USD 400

SQL DBA Training Schedules - Trainer: Mr Sai Phanindra T


Timings (IST)
Free Demo
Start Date
 
1
6:30 AM to 7:45 AM
Recently Started
Register
2
10:15 AM to 11:30 AM
Dec 27th Dec 28th
Register
3
6:30 PM to 7:45 PM
Jan 2nd
Jan 3rd
Register

If the above schedules does not work for you, please opt for
SQL DBA Video Training (on demand, 24x7 Access)


For One-One and Custom Trainings, please Contact Us


ALL TRAINING SESSIONS ARE COMPLETELY PRACTICAL, REAL-TIME. WE TRULY VALUE YOUR TIME AND MONEY.

 
 

SQL DBA (with Server, T-SQL, Azure) Course Contents:

Module I: SQL Server & Design, Queries

Duration: 1.5 Weeks

Module II: Basic SQL DBA

Duration: 2 Weeks

Module III: Advanced SQL DBA

Duration: 2.5 Weeks

DAY 1: SQL SERVER INSTALLATION

  • What is Database? Need for DB Admins
  • Editions and Versions Of SQL Server
  • Roles and Responsibilities For SQL DBA
  • How to install SQL Server - Step by Step
  • SQL Server 2016, 2014, 2012 Installation
  • SQL Server Management Studio (SSMS)
  • SSMS and SQLCMD Tool Configurations
  • DBA Checklist - Routine DBA Activities
  • DBA Checklist - Maintenance Activities
  • DBA Checklist - Emergency Activities
  • Course Plan and Free Takeaways
  • MCSA Certification Pattern - Course Plan
  • Online Lab Access (24x7 LIVE Server)

DAY 2: SQL SERVER ARCHITECTURE

  • Client - Server Architecture of SQL Server
  • TCP / IP, Named Pipes, Shared Memory
  • SQL Native Client, OLE DB Drivers
  • Query Processor Architecture, SNAC
  • TDS Packets and Storage Engine
  • Parser, Optimizer and File System
  • SQL Engine, MDAC and SQL Buffers
  • Write Ahead Log,Lazy Writer, Checkpoints
  • Query Optimizer (QO) & Execution Plans
  • Query Processing and Optimizer (QO)
  • Log File Architecture - Virtual Log Files
  • Log File Architecture - Mini LSN, Usage
  • DB Catalogs, CLR Integration, MDAC
  • LSN Timestamps, Background Threads
  • SQL OS Components, Thread Scheduler
  • Buffer Manager, IO Manager and CLR
  • SQL Server with External Protocols

DAY 3: DATABASE & TABLE ARCH

  • SQL Server Database Architecture
  • Data Files : Purpose, Sizing, Options
  • Filegroups : Placement, Usage & Options
  • Log files : Sizing, Placement, Growth
  • Database Creation - GUI & SQL Scripts
  • Filegrowth, Autogrowth and MAXSIZE
  • Planning, Designing Large Databases
  • Capacity Advantages - Filegroups, Files
  • Database Structure - Modifications
  • Adding New Filegroups, Files to DB
  • Capacity Planning, Dedicated Log Store
  • Data File Architecture - Pages, Extents
  • Log File Architecture - VLF, Mini LSN
  • Routing Tables to File Groups, Usage
  • Schemas - Purpose, Usage with Tables
  • CHAR versus VARCHAR Differences
  • DB Log Files - Logged and NonLogged
  • Default Schema, Aliases for Table Design

DAY 4: CONSTRAINTS and KEYS

  • Constraints and Keys - Purpose, Usage
  • Normal Forms, Relational DB Design
  • OLTP DB Models, BCNF - Relations
  • NULLABILITY Property and Importance
  • UNIQUE KEY Constraints, Importance
  • PRIMARY KEY Constraints, Limitations
  • FOREIGN KEY References, Relations
  • FOREIGN KEY Constraints - Limitations
  • Schema Level and Table Level Relations
  • CHECK Constraints - Usage, Limitations
  • DEFAULT Column Constraints, Usage
  • Identity Property with PRIMARY KEY
  • Constraint Priority and DB Properties
  • Data Types and Aliases with Schemas

DAY 5: JOINS & SUB QUERIES

  • Need for Joins and Use Case Scenarios
  • JOIN - Types, Queries & Usage Options
  • CROSS JOIN - Examples and Scenarios
  • INNER JOIN - Examples and Scenarios
  • OUTER JOINS - Types and Scenarios
  • SELF JOINS with Self References, Types
  • Joins with / without SCHEMA References
  • Joining Multiple Tables and Join Types
  • Aggregations in Joins. Table Aliasing
  • Comparing Inner Join and Outer Joins
  • Sub Queries and Joins. Alternate Syntax
  • Sub Queries with UNION, UNION ALL
  • Aggregations on Joined Sub Queries

DAY 6: VIEWS

  • Views on Tables, Security Advantages
  • Views as Stored SELECT Statements
  • SCHEMABINDING and ENCRYPTION
  • Cascaded Views, WITH CHECK OPTION
  • Orphan Views - Scenarios in Realworld
  • Common System Views, Metadata Access
  • sys.databases, sys.tables, sys.views
  • INFORMATION SCHEMA, Definitions
  • Views with Multi Level Tables - Joins
  • Views on UNION ALL, Sub Queries
  • Views on Nested Queries, Aliases

DAY 7: FUNCTIONS, QUERIES

  • Functions: Types, Purpose and Usage
  • Scalar Value Returning Functions - Usage
  • Table Value Returning Functions
  • RETURNS and RETURN Values. Options
  • BEGIN..END and IIF Functions, CASE
  • Variables and Function Parameters
  • Static Variables and Dynamic variables
  • System Functions and Metadata Access
  • Queries - GROUP BY, HAVING Conditions
  • Queries with GROUPING() and HAVING
  • HAVING Vs WHERE - Performance
  • Query Execution Order @ ROLLUP, CUBE

DAY 8: STORED PROCEDURES - 1

  • 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

DAY 9: STORED PROCEDURES - 2

  • Stored Procedures for Dynamic Queries
  • Recursive Queries: OUTPUT Parameters
  • Using Stored Procedures with Cursors
  • FORWARD_ONLY and SCROLL Cursors
  • STATIC, DYNAMIC Cursors. ABSOLUTE
  • LOCAL and GLOBAL Cursor. Reusability
  • KEYSET Cursor & Performance Options
  • Embedding Cursors in Procedures
  • Memory Limitations with Cursors, SPs
  • WHILE Loops, System Global Variables
  • Need for Recompilations, Outdated Plans
  • Dynamic SQL Parameters and Variables
  • OUTPUT Parameters, RECOMPILE Option

DAY 10: TRIGGERS

  • Use of Triggers - Use, Performance
  • FOR / AFTER Triggers - Importance
  • INSTEAD OF Triggers - Importance
  • INSERTED, DELETED Memory Tables
  • Triggers for DML Audits, Data Sampling
  • Database Level, Server Level Triggers
  • Bulk Operations & Updatable Views
  • Table Audits and Linked Servers
  • Data Replication using Triggers
  • DDL Triggers : Database Scoped
  • DDL Triggers : Server Scoped
  • Performance Impact of Triggers

DAY 11: TRANSACTIONS

  • ACID Properties and Transaction Types
  • EXPLICIT Transactions - Advantages
  • IMPLICIT Transactions - Advantages
  • Open Transactions - Query Blocking
  • AUTOCOMMIT Transactions - Advantages
  • Nesting Transactions and Save Points
  • @@TRANCOUNT and @@ERROR
  • DBCC OPENTRAN and SP_WHO2
  • LOCK HINTS: READPAST, NOLOCK
  • Transactions with SET Options, Cautions
  • Transactions with Triggers, SPs, Views

DAY 12: QUERY TUNING (INDEXES)

  • 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 and PAD_INDEX Options
  • INCLUDED Indexes, HEAP Performance
  • COLUMN STORE Indexes - Advantages
  • FILTERED Indexes - Query Performance
  • ONLINE Indexes and OFFLINE Indexes
  • Materialized Views (Indexed Views) Usage
  • Unique Clustered, NonClustered Indexes
  • Estimated Execution Plans and Usage
  • Actual Execution Plans - Precautions
  • Client Statistics and Perf. Metrics
  • Query Statistics, Client Statistics
  • Column Statistics and Index Statistics

DAY 13: BACKUPS - OPTIONS

  • 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
  • Mirrored Backups, FORMAT, SKIP, STATS
  • Tape Backups - LOAD, UNLOAD, ERRORS
  • COMPRESSION, CHECKSUM, VERIFY
  • ContinueOnError, Backup Validation
  • Backup History - Media Set, Media Family
  • Compatibility, Recovery Model Options
  • COPY_ONLY Backups and Importance
  • Backup Devices and Verification
  • Backups using GUI and T-SQL Scripts
  • Real-time Errors, Solutions @ Day 35

DAY 14: RESTORES & DB RECOVERY

  • Restore Phases - COPY, REDO, UNDO
  • File Restores and Filegroup Restores
  • FILELISTONLY, VERIFYONLY, REPLACE
  • PARTIAL, PIECEMEAL Restores - Tuning
  • Tail Log Backups & DB Recovery Options
  • REDO Phase in 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
  • Choosing correct DB Recovery Model
  • Point-In-Time Restores and Log Recovery
  • Restores From Backup Devices & Mirrors
  • Restores using GUI & T-SQL Scripts
  • Restore Audits and Standby Databases
  • Checkpoint LSN and Restore Positions
  • Real-time Errors, Solutions @ Day 35

DAY 15: JOBS, SQL SERVER AGENT

  • SQL Server Agent Service, Agent XPs
  • SQL Agent Jobs - GUI Options, MSDB
  • Job Steps: T-SQL, SSIS, Replication
  • Job Schedules and Email Notifications
  • Job Executions, Disable/Enable Options
  • Job History and Job Activity Monitor
  • Import & Export (SSIS) Operations
  • SSIS Packages for Data Import/Export
  • SSIS Package Store - File System, MSDB
  • SSIS Jobs - Scheduling SSIS Packages
  • Backup Strategies For Minimal Data Loss
  • Backup Jobs & Recovery Modes : Usage
  • FULL, SIMPLE, BULKLOGGED Modes
  • Backup Limitations with Recovery Modes
  • Startup Jobs, Onetime, Recurring Jobs
  • Job Activity Monitor and Agent Threads

DAY 16: PERFORMANCE TUNING - 1

  • Identifying Long Running Queries
  • Dynamic Management Objects: DMV, DMF
  • Audit Long Running Queries - DMV/DMF
  • Query Statistics & LIVE Execution Plans
  • CROSSAPPLY with Dynamic Views
  • Avoiding Sub Queries and Conditions
  • Comparing Sub Queries and Query Joins
  • Query Tuning and Resource Optimization
  • Common Table Expressions (CTE) & Uses
  • Using CTE, Data Retrieval in Stored Procs
  • CTEs for Avoiding Self Joins, Scenarios
  • CTE Advantages and Memory Limitations
  • HASH JOIN Examples and Precautions
  • MERGE JOIN Examples and Precautions
  • LOOP JOIN Examples and Precautions
  • OUTER APPLY and Multi - Level Joins

DAY 17: PERFORMANCE TUNING - 2

  • Table Partitions and Query Tuning Options
  • Partition Functions and Partition Schemes
  • Partition Ranges, Values and Sort Orders
  • Partition Un-partitioned Tables: Indexes
  • Aligned / Indexed Partitions - Importance
  • Data Compression - ROW & PAGE Levels
  • Partition Numbers & Filtered Compression
  • Managing Partitions and Tuning Options
  • Partition Management @ SPLIT, MERGE
  • Partition Elements, Concolation Options
  • STATISTICS - Purpose and Tuning Options
  • Column Statistics and Index Statistics
  • Manual Update of Column Statistics
  • Statistics in Query Tuning Process
  • STATISTICS in Indexes, Query Conditions
  • LIVE Query Statistics, Table Statistics

DAY 18: PERFORMANCE TUNING - 3

  • LIKE Operator - Using Wild Cards
  • Full Text Search Architecture
  • StopWords, Stemmer and Thesaurus
  • Full Text Search Service Configuration
  • Database Catalogs (FTC) and FDHost.exe
  • Full Text (FT) Indexes for Query Tuning
  • Full Text Columns and Primary Key Index
  • Full Population, Incremental Population
  • CONTAINS() and FREETEXT() Functions
  • Token Search and Inflectional Forms
  • Performance Tuning @ Full Text Indexes
  • CONTAINSTABLE and FREETEXTTABLE
  • Real-world Considerations with FTS
  • FT Index Management Options, Statistics
  • Stop Words Management, Tuning Options
  • New Tuning Options @ SQL 2017

DAY 19: PERFORMANCE TUNING - 4

  • Index Internals and Index Management
  • Execution Plans, Statistics, Query Cost
  • Index Fragmentation Issues, Performance
  • SAMPLED & DETAILED Scans. FillFactor
  • Index Rebuilds (Online/Offline), Tuning
  • Index Reorganization and Advantages
  • Page, Row Compressions with Indexes
  • Filtered & Online Indexes, Indexes Views
  • GAM, SGAM Pages, Table Statistics
  • Heaps, Clustered, Nonclustered Indexes
  • Fill Factor, Pad Index and Query Tuning
  • DTA: Sequential / Parallel Query Tuning
  • DTA Tool with Profiler, Trace Tables, Cache
  • Workload Files & Tables in Profiler
  • SQL Profiler Tuning and Tuning Templates
  • Database Tuning Advisor (DTA) - Usage
  • DTA for Procedure Cache, Recent Queries
  • Understanding PDS Options with Indexes

DAY 20: PERF. TUNING - LEVEL 5

  • Memory Optimized Tables and Filegroups
  • Temporal Tables, SYSTEM_VERSIONING
  • Temporal Tables For Audits, Performance
  • In-Memory Tables and Index Options
  • Extended Events & Performance Impact
  • LIVE Query Statistics - Monitoring, Metrics
  • LIVE Execution Statistics, Hash Plans
  • Use DMVs and DB Performance Metrics
  • PERFMON Counters and MSDTC Service
  • Memory, IO Resources : Performance
  • MEMORY LEAKS and LATCH WAITS
  • CPU, Thread Management, Fibers
  • Resource Governor - Resource Pools
  • Resource Workload Groups, Settings
  • Windows Fibers and Priority Boost
  • Degree Of Parallelism (DOP) Settings
  • Performance Tuning - Checklist Activities

DAY 21: REPLICATION For HA - LEVEL 1

  • Replication Architecture and Topology
  • Distributor Server Configurations, 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
  • Transactional Replication Configuration
  • Log Reader Agent - Configuration, Keys
  • Tracer Tokens - Latency and Ranking
  • Replication Monitor - Usage and Options
  • Replication Jobs and Load Balancing
  • Adding Articles to Existing (LIVE) Replica
  • Stopping, Starting Replication Agents
  • Scripting Replication Jobs, Publications
  • Disable/Enable Replication, Agent Stops

DAY 22: REPLICATION For HA - LEVEL 2

  • Transactional Replication, Tracer Tokens
  • Merge Replication and Merge Agent Job
  • Replication Conflicts and ROWGUIDCOL
  • Peer-Peer Replication Connections, Nodes
  • 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
  • Peer-Peer Vs 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 for HA and DR - Strategies
  • Replication Errors and Job Audits
  • Common Replication Issues, Solutions
  • Replication Conflicts, Solutions
  • SQL DB to Heterogenous DB Replication

DAY 23, 24: HA DR STRATEGIES

  • 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 & Manual Failover
  • Log Shipping Jobs - Errors and Solutions
  • DB Mirroring Architecture For HA,DR
  • TCP Endpoints and TCP Network Security
  • Heartbeat and Polling Concepts in DM
  • Automatic Fail-Over Procedures, Tests
  • PARTNER OFFLINE Conditions & Options
  • DB Mirroring Monitors and Commit Loads
  • SYNCHRONOUS & ASYNCHRONOUS
  • Mirroring Monitor, Stop/Resume Options
  • Need for Always-On & Higher Availability
  • DB Recovery without Witness. Failover

DAY 25: DB HEALTH CHECKS, AUDITS

  • Query Resources - CPU, IO and Memory
  • Activity Monitor (AM) Tool - CPU, Memory
  • SQL Profiler Tool Usage, Audit Trace Files
  • SQL Profiler Templates: Standard, Tuning
  • Event Extraction Settings, Filter Columns
  • Profile Trace File Rollover, Size Settings
  • DB Health Check: DBCC Commands
  • Allocation Errors and Consistency Errors
  • ESTIMATEONLY, NOINFOMSGS, Tempdb
  • Log Space Audits and Tempdb Audits
  • Memory Usage & Disk Usage Audit DMVs
  • Storage Allocation Issues (Page, Extent)
  • LOCKS - Types, Impact, Monitoring
  • Shared Locks, Exclusive Locks, WAITS
  • Deadlock Graphs with SQL Profiler, XDL
  • DEADLOCKS - Avoidance, Prevention
  • LIVELOCKS - Avoidance, Prevention
  • Isolation Levels For OLTP - TempDB

DAY 26: SECURITY MANAGEMENT

  • Authentication Types - WINDOWS, SQL
  • Windows Logins and REGEIDT Settings
  • SQL Server Logins, POLICIES, EXPIRY
  • LOGINS: Server Level Security, Options
  • USERS: Database Level Security, Options
  • SCHEMAS: Object Level Security, Options
  • System Server Roles, Database Roles
  • GRANT, WITH GRANT, DENY, REVOKE
  • CONTROL, OWNERSHIP, Authorization
  • DMVs for Server and DB Security Audits
  • KEYS, Passwords - Data Level Encryption
  • CERTIFICATES & Data Encryption
  • SQL Server Credentials, Job Security
  • Proxies - Purpose, Usage and Options
  • SSIS Proxies for Job Level Subsytems
  • Password Resets and Disabling Logins
  • Scripting Logins, Users, Roles, Schemas
  • Security Audits, Role Membership
  • Containment Databases and Purpose
  • Contained Database Users and Roles

DAY 27: 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
  • 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 28: DB 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
  • Log Repairs, FORATTACH, REBUILDS
  • Linked Server Security, Management
  • Server Dashboard, Connection Audits
  • Data Migration Assistant (DMA) Tool
  • Using DMA Tool for DB Analysis, Upgrades
  • DMA Tool: DB Assessment, Migration

DAY 29: PATCHES, 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 & DAC
  • Pre Database Maintenance Activities
  • Post Database Maintenance Activities
  • Real-world Management Considerations

DAY 30: CLUSTERING CONFIGURATION

  • 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 31: SQL SERVER CLUSTERS

  • Need for Centralized Authentication
  • Domain Controller (DC) Configuration
  • Active Directory Settings and Usage
  • DCPROMO Settings for Active Directory
  • Server Configuration Manager, Options
  • Configuration Manager - Users & Groups
  • Working with Active Directory (AD) Edits
  • Network Configurations and Precautions
  • PING Configurations and DTC Options
  • QUORUM settings and SAN Options
  • SQL Server Cluster Installation
  • Cluster Configurations - Active/Active
  • Cluster Configurations - Active/Passive
  • MS DTC Configurations (Local/Remote)

DAY 32-34: SQL CLUSTERS, ALWAYS-ON

  • Install SQL Server Clusters
  • Clustering Nodes : Install, Update
  • Need for Always - On Availability
  • Always-On Prerequisites, Node Config
  • SQL Group & AD Syncup Operations
  • SAN System for Shared Data Storage
  • Smoke Test Procedures in Real-time
  • Fail-Over Disk & RAID Implementation
  • Cluster Connection Issues and Drains
  • 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
  • Port Settings, Backup Strategies in AAG
  • IP Configurations & WSFC Settings
  • Node Majority, Disk & File Share Majority
  • Active-Passive Cluster Configurations
  • Network Security Issues with Solutions
  • RAID, Storage Issues with Solutions
  • Availability Replica - Readable Secondary
  • Active Secondary, SYNC, ASYNC (AAG)
  • Replica Recommendations for Always-On
  • Routine DBA Challenges for AAG - HA/DR
  • SLA/OLA Challenges, Tickets in Always-On

DAY 35: ERRORS, SOLUTIONS

  • Routine DBA & Emergency DBA Activities
  • ROOT CAUSE ANALYSIS STRATEGIES
  • Common Backup Errors, Solutions
  • Restore Errors and DB Offline Errors
  • Replication Errors and Solutions
  • Log Shipping Errors and Solutions
  • Trace Flags and Usage. DBCC PAGE
  • DB Suspect Errors and Solutions
  • Dynamic Data Masking (DDM)
  • Security Scenarios with DDM Feature
  • 3rd Party Tools (Litespeed) and KBs
  • Ticketting Tools and SLA / OLA

Module I: AZURE SQL DATABASE

Module II: AZURE SQL DBA

DAY 1: AZURE CLOUD INTRO

  • Introduction to Cloud. Need for Cloud, Advantages
  • Cloud Architecture Basics - Iaas, PasS and SaaS
  • Operational Advantages of Cloud, Cloud Providers
  • Advantages of Microsoft Cloud - Azure Platform
  • Service Models, Private & Public Clouds
  • SQL Databases in Microsoft Azure and Advantages
  • Azure SQL & Databases - Need, Importance
  • Azure Sources - Types, Microsoft Market Place
  • Azure SQL Database, Azure SQL Data Warehouse
  • Azure Analysis Services, BLOB and TABLE Storage
  • Azure Cosmos DB, Data Lake, DH Insight, Spark
  • Virtual Machines and Apps, Programs in Azure
  • Azure SQL Variants and Service Tiers
  • Advantages of Azure SQL Databases & Tools
  • Comparing Azure with AWS and Google Cloud
  • Microsoft Azure Price Tiers & Subscription

DAY 8: AUTOMATED DB RECOVERY, GEO REPLICATION

  • Automated Backups in Azure SQL Database
  • Long Term Backup Retentions. Backup cost, Frequency
  • Recovery Service Vaults, Policies with RA-GRS
  • Automated Backups - Retentions, Audits. LTR Policy
  • Restore Points and Azure SQL Database Recovery
  • Failover Groups and Active Geo-Replication
  • Read-Write Failover Policy, Automated Failover
  • Geo Replication Configurations - Azure Locations
  • Primary-Secondary and Read / Write Modes
  • Upgrading / Downgrading Primary Database
  • Perform Disaster Recovery (DR) Drills
  • Power Shell For Geo Replication and Failback Options
  • Manage SQL database failover using Transact-SQL
  • Manage SQL database failover using Power Shell
  • Manage SQL database failover using the REST API
  • Backup Errors, Geo Replication Errors - Solutions

DAY 2: AZURE CLOUD CONFIGURATIONS

  • Azure Cloud Subscription, Azure Portal Options
  • Azure Resources, Marketplace and Dashboards
  • Azure SQL Database Architecture Components - in detail
  • Price Tiers: Basic, Standard, Premium, PremiumRS
  • Isolated Price Trier - Advantages, Performance
  • Creating SQL Servers in Azure and in Virtual Machines
  • Elastic Pools and Configuration Options - Advantages
  • DTU : Data Transaction Units : Architecture, Pools
  • eDTUs and Elastic Pool, per Database Settings
  • EDTU Cost, eDTU max/min Limits and Performance
  • Resource Groups and Resource Pools in Azure SQL
  • Azure SQL Databases : Technical Features, Benefits
  • Built-In Intelligence and Scalability, Tools For Usage
  • Advanced Security Compliance, ARM and ASM Topologies
  • Need for OSM Workspace - Operations Management Suite

DAY 9: SECURITY MANAGEMENT - AZURE SQL DB

  • Firewall and Virtual Network Configurations
  • Server-level Firewall Rules in the Azure portal
  • Database-level Firewall Rule using SSMS
  • Connection strings and Security Options
  • Logins, Users, Roles and Permissions using T-SQL
  • GRANT, DENY, REVOKE and WITH GRANT using T-SQL
  • SQL Database Auditing and SQL Database Threat Detection
  • Transparent Database Encryptions (TDE) & Security
  • Service Keys and TDE for Security, AD Authentication
  • Row Level Security (RLS), Security Roles
  • Impacted Databases and Security Options
  • Azure IAM Controls, Manage SMB File Shares in Azure
  • Dynamic Data Masking (DDM) and Encryption Functions
  • T-SQL Scripts & Portal to Implement Data Mask Functions
  • Power Shell Scripts to Implement DDM - MASK / UNMASK

DAY 3: AZURE SQL DATABASE CONFIGURATION

  • Creating Azure SQL Server Instances
  • Creating Azure SQL Databases, Price Tiers
  • SQL Database – Cloud Database as a Service
  • Subscription Options and Database Sources
  • Elastic Pools & Tier Selection - Recommendations
  • Database Name Identifiers, Naming rules & restrictions
  • Server Names - Locations, Admin Users, Passwords
  • S1/S2/S3 DTU bands and Performance, Storage
  • Add-On Storage Options. Database Provisioning
  • Firewall Rules, IP Configuration Ranges
  • Azure Dashboard - Metrics, Notification Options
  • Azure SQL Database Collation, Connection Options, Tools
  • SQL Server Management Studio (SSMS) & Visual Studio
  • SQL Server Data Explorer Tool in Azure Cloud
  • .NET, PHP, Node.js, Java, Ruby, Python
  • Creating Azure SQL Databases in SSMS Tool
  • T-SQL Scripts for Azure SQL Database

DAY 10: AUDITS and THREAT DETECTION

  • Activity Logs and OMS Server Configuration
  • OMS Workspace and Log Analytics. Operational Logs
  • Instance Audits and Threat Detection Process
  • Audit Storage Configuration, Service Tiers with Azure
  • LRS, GRS, and "RA - GRS" Storage Modes in Azure
  • Azure Resource Manager (ARM) Scripts with Power Shell
  • Threat Detection Types : SQL Injection & Vulnerability
  • Audit Types: Server Audits and Database Audits
  • Events, Principals, Types and Action Parameters
  • DMVs and DMFs for Audits (T-SQL). Power Shell Scripts
  • Merging and Reading Audit Files. Analyzing Audit Reports
  • Vulnerability Assessment (VA) Reports, Notifications
  • Security Risk Reports and Summaries : HIGH/MEDIUM/LOW
  • Azure SQL Database Recovery from Availability Groups
  • Azure SQL Database Recovery from Deleted Databases
  • Manage SQL database auditing using Azure Power Shell
  • Manage SQL database auditing using REST API

DAY 4: DEVELOP AZURE SQL DATABASE

  • Executing T-SQL Scripts in Azure
  • Creating Tables and Defining Constraints
  • Cascades, Constraint Rules and Index Rules
  • Clustered Indexes in Azure SQL Database Tables
  • Programming Objects: Stored Procedures in Cloud
  • Automated Recompilations, Complex Stored Procedures
  • Triggers and Memory Tables Architecture in Cloud
  • CTE : Common Table Expressions and Performance
  • User Defined Functions and Views for Data Reporting
  • Differences between On-Premise and Cloud SQL Databases
  • Executing T-SQL Scripts in Azure SQL Database
  • Linked Servers with On-Premise and Cloud
  • SSMS "Generate Script" Options, Advanced Options
  • Azure SQL Database JSON Features, Data Imports
  • Azure SQL Database In-Memory Tables - Advantages
  • Temporal Tables, In-Memory OLTP Tables with Azure SQL DB
  • Excel Reporting Options from Azure SQL Database
  • Data Explorer Options with Azure SQL Databases
  • XML Data Storage & Reports. BLOB Data Storage

DAY 11: PERFORMANCE TUNING in AZURE SQL DB

  • Manage database workload in SQL Server
  • Elastic Scale for Azure SQL Database
  • Service Tier and Edition Selection
  • Scaling Options - Horizontal, Vertical
  • Sharding Technique for Performance Tuning
  • SaaS approach with single / multi user tenants
  • Shard Maps & Shardlets: List Mapping, Range Mapping
  • Shard Map Manager and Data Dependent Routing
  • Query Routing and Shard Querying. Elastic Tools
  • Using DMVs and DMFs in Azure Databases
  • Monitor Azure SQL Database Query Plans
  • Moving data between scaled-out cloud databases
  • Split-Merge Tool and Split Merge Service
  • Performance Monitoring Counters For Azure SQL DB
  • CPU, DTU Limits. Data IO and Size Factors
  • In-Memory OLTP Tables and Performance Impact
  • Active Recommendations, Tuning History. Resource Governor
  • FORCE PLAN, CREATE / DROP INDEX. ON / OFF INHERITS
  • Automated Tuning Options in Azure Database Cloud

DAY 5: AZURE SQL DATABASE MIGRATIONS

  • Database Scripting Wizard in SSMS
  • Scripting On-Premise Databases in T-SQL
  • Data Migration Assistant (DMA) Tool
  • Schema Generation and Compatability Issues
  • Generating Data Scripts, Assessment, Schema Options
  • Prepare and Deploy Fixes. Database Snapshots
  • Resolving Database Migration Compatibility Issues
  • Partially Supported and Unsupported Functions
  • non SQL Server Database Migrations : MS Access, Oracle
  • SQL Server Migration Assistant (SSMA) Tool
  • Import from a BACPAC file using Azure portal
  • Import from a BACPAC file using SQLPackage
  • Import from a BACPAC file using PowerShell
  • Migrate Stored Procedures, In-Memory Tables

DAY 12: DATABASE SYNCHRONIZATION

  • SYNCHRONIZATION with Azure SQL Databases
  • SQL Data Sync Service and Sync Groups
  • bi-directional data transfer with Azure SQL DBs
  • Sync Schema, Sync Direction and Sync Interval
  • Conflict Resolution Policy with HUB and MEMBER
  • Metadata Database and Synchronization Topologies
  • Real-time Advantages of Azure DB Synchronization
  • LOAD BALANCING & Automated FAILOVER with DB Sync
  • Cloud to Cloud and Cloud to On-Premise Synchronization
  • HA - DR with Geo Replication & Database Synchronization
  • Update Triggers and Delete Triggers - Azure Instance
  • Azure Database Integration. Elastic Query Plans
  • Requirements and Limitations with Database Synchronization
  • Azure Dashboards and Report Pins. Server Health Reports

DAY 6: INTEGRATING with AZURE SQL DATABASE

  • Azure SQL Database Tables, Views in Excel
  • Excel Pivot Tables and Chart Reports with Azure SQL DB
  • Azure & Excel ODC Connections. Pivot Reports
  • ADO.NET, JDBC and ODBC Connections. Data Mashups
  • Connection Drivers in Azure Cloud - Options
  • Azure Portal Email Configurations, Triggers
  • Azure SQL Database Query Batching - Advantages
  • Azure Cloud Shell - Concepts, Architecture
  • Azure Power Shell - Install and Configure
  • Installing and Scripting with Power Shell
  • PowerShellGet and Version Paths
  • Cloud Shell to run the Azure Power Shell
  • Linux Virtual Machines with Power Shell
  • Windows Virtual Machines with Power Shell

DAY 13: AZURE VIRTUAL MACHINE, OTHER CLOUDS

  • Plan for IaaS, Azure VM. Install Azure Virtual Machines
  • Install/Configure Windows Server, Readhat, Ubuntu
  • SQL Server on Azure VM - Config Settings, Elasitc Pools
  • Resource Groups, Resource Pools and eDTUs
  • Verify SQL Instances in Azure VM. sysadmin Account
  • Azure SQL Database in VM to SQL Cloud
  • Azure SQL Database in VM to on-Prem
  • Deployment Automation Process, Automation Scripts
  • SQL Database in AWS and Google Cloud
  • Real-time Considerations and Bacpac File Security
  • Azure HD Insight Configuration and Power Shell
  • Azure DataLake and Integration with Azure SQL Database
  • Power BI Workspace with Azure SQL Database

DAY 7: MCSA Certifications

Guidance and Mock Certification for 70-762 (SQL Dev)- If Required

 

Guidance and Mock Certification for 70-473 (Azure SQL) - Mandatory

DAY 14: MCSA Certifications

Guidance and Mock Certification for 70-765 (SQL DBA)- If Required

 

Guidance and Mock Certification for 70-473 (Azure SQL) - Mandatory

This AZURE DBA Training Course includes one Real-time Project

Deployments to SQL Azure Azure Database Integration Azure Cloud Versus AWS Cloud Azure Cloud Versus Google Cloud Pricing and Server Tiers Elastic Pools and Config Elastic Query Plans Geo Replication Issues Azure Interview FAQs Resume Preperation Mock Interviews Beneficial For MCSA 70-765 and 70-473 Exams

Above course curriculum applicable for registrations from Dec 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.

 
 

SQL DBA Online Training- Highlights :

  • Completely Practical and Real-time
  • Suitable for Starters + Working Professionals
  • Session wise Handouts and Tasks + Solutions
  • TWO Real-time Case Studies, One Project
  • Weekly Mock Interviews, Certifications
  • Certification & Interview Guidance
 
 
  • Detailed SQL Server Architecture, DB Repairs, Migrations
  • Query Tuning, Stored Procedures, Linked Servers
  • In-Memory, DAC and Contained Databases
  • Routine DBA Activities, Emergency DBA Activities
  • SQL Profiler, SQLDIAG, DTA, DMA, SSMA Tools
  • SQL Clusters, Always-On. SQL DBA in Azure Cloud
Register Today Other Popular Courses: SQL DBA Training, MSBI Training, SSIS Training, SSAS Training, SSRS Training [+] More Courses