Azure SQL DBA (ADMIN) Online Training (LIVE, Instructor-Led)

Azure SQL Database is a cloud-based, elegant technology for creating and using databases in Microsoft Cloud. Easy UI and Faster, reliable deliverables with SQL Server Cloud. We at SQL School offer complete real-time and practical on Azure SQL Database Trainings.

Azure SQL Database Online Training from SQL School. This course includes Basic to Advanced Azure SQL Database Development, Azure SQL Database Administration, Performance Insights, Geo Replication, Database Synchronization, Advanced Security with TDE and RLS (Row Level Security), DDM and Azure Virtual Machines. This Azure SQL Database Training course also include MCSA Certification Guidance. Register Today

AZURE SQL Database Video Training - How it works?


Real-time, Practical Training Videos, accessible 24x7. Study, Practice Material and Lab Work for every video. Trainer available for doubts clarifications and support. Theory Material to be provided in Advance. 

 

Arrange Callback from the Trainer

Trainer : Mr. Sai Phanindra T (12+ Yrs Exp)


Course Fee : INR 24000/- (USD 400). Payable in TWO installments

COURSE HIGHLIGHTS
Azure SQL Development Azure SQL DB Admin
Performance Insights, Tuning DB Migrations, Deployments
Azure SQL DB Security Geo Replication, DB Sync
DR Drills, Threat Detections HD Insight, Azure DataLake
 

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

Module I: SQL Server & T-SQL Queries

Module II: Basic SQL DBA

Module III: Advanced SQL DBA

DEMO VIDEO: SQL Server 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 2017, 2016, 2014, 2012
  • 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 Takeaways from Course
  • Weekly Mock Interviews, Resume
  • Placement Assistance, Course Certificates
  • MCSA Certification Pattern - Course Plan
  • Online Lab Access (24x7 LIVE Server)

VIDEO 1: BASIC SQL: DDL and DML

  • Testing Installation, Server Connections
  • SSMS Tool (Client) and Object Explorer
  • Defining Query Sessions - Session IDs
  • Basic SQL with Databases and Tables
  • DDL, DML, DCL and TCL Statements
  • Basic Table Creation - Data Types
  • INSERT / Store Data into SQL Tables
  • Single, Multiple Row Inserts @ NULL
  • SELECT Operators: IN, AND, OR
  • IS, UNION ALL, NOT and BETWEEN
  • UPDATE and DELETE Statements
  • DELETE Versus TRUNCATE. DROP
  • SYSTEM DATABASES - Importance
  • MSDB, TempDB, Resource and Model
  • Local Temp Tables. Global Temp Tables
  • Wait Times, Client Statistics @ Queries
  • CLIENT - SERVER Architecture (TDS)
  • SQL Native Client (SNAC) Statistics
  • Implementing TDS @ SQL Queries

VIDEO 2,3: DATABASE & TABLE 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
  • Routing Tables to File Groups, Advantages
  • Schemas - Purpose and Usage with Tables
  • CHAR versus VARCHAR Differences
  • DB Log Files - Logged, NonLogged Options
  • Default Schema, Aliases for Table Design
  • Data Types,Aliased Queries with Schemas

VIDEO 4: 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
  • DB Design with Constraints, Schemas

VIDEO 5: JOINS & VIEWS

  • 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
  • Joins with / without SCHEMA References
  • Joining Multiple Tables and Join Types
  • Joining Tables across Schemas, Filegroups
  • Basic Aggregations with Joins. Aliasing
  • Comparing Inner Join and Outer Joins
  • Comparing Inner Join and Cross Joins
  • Deciding the best Join Type, Options
  • Sub Queries and Joins. Alternate Syntax
  • End User Access to Queries - Interfaces
  • Sub Queries and Simple Aggregations
  • Sub Queries with UNION, UNION ALL

VIDEO 6: 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, PAD_INDEX, Fragmentation
  • INCLUDED Indexes, HEAP 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
  • Materialized Views (Indexed Views) Usage
  • Unique Clustered, NonClustered Indexes
  • Common Table Expressions (CTE) & Uses
  • Using CTE, Data Retrieval in Stored Procs
  • CTEs for Avoiding Self Joins, Scenarios

VIDEO 7: FUNCTIONS, SUB QUERIES

  • Views on Tables, Security Advantages
  • Views as Stored SELECT Statements
  • SCHEMABINDING & ENCRYPTION
  • Cascaded Views, WITH CHECK OPTION
  • Orphan Views - Scenarios in Realworld
  • Common System Views, Metadata Access
  • sys.databases, sys.tables, sys.views
  • INFORMATION SCHEMA views, Definitions
  • 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
  • Functions: Types, Purpose and Usage
  • Scalar Value Returning Functions - Usage
  • Inline Table Value Returning Functions
  • Multi-line Table Value Returning Functions
  • RETURNS and RETURN Values. Options
  • BEGIN..END and IIF Functions, CASE
  • Function Arguments - Usage and Options
  • Variables and Function Parameters
  • Static Variables and Dynamic variables
  • Table Variables, WHILE LOOP Concepts
  • System Functions and Metadata Access
  • Date & Time Formats, Styles, DATEDIFF
  • CAST, CONVERT, RANK, ROW_NUMBER
  • Views for Sub Queries and Functions
  • Views for Aggregations and Calculations

VIDEO 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
  • Need for Recompilations, Outdated Plans
  • Dynamic SQL Parameters and Variables
  • Default Parameter Values, Usage Types
  • OUTPUT Parameters and Query Options
  • RECOMPILE & ENCRYPTION Options

VIDEO 9: STORED PROCEDURES - 2

  • Stored Procedures for Dynamic Queries
  • Recursive Queries: OUTPUT Parameters
  • Common Table Expressions, In-Memory
  • Stored Procedures for CTE (Sub) Queries
  • Using CTE for Table Data Operations
  • CTE for DML & DDL in Stored Procedures
  • Using Stored Procedures with Cursors
  • FORWARD_ONLY and SCROLL Cursors
  • STATIC, DYNAMIC and ABSOLUTE
  • LOCAL and GLOBAL Cursor. Reusability
  • KEYSET Cursor & Performance Options
  • Embedding Cursors in Procedures
  • Memory Limitations with Cursors, SPs

VIDEO 10: 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..

VIDEO 11: SQL SERVER ARCHITECTURE

  • 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
  • Query Optimizer (QO) and Execution Plans
  • SQL DB Architecture: Files and Filegroups
  • Virtual Log Files & Mini LSN & DOP
  • Query Processing and Optimizer (QO)
  • Client - Server Architecture of SQL Server
  • TCP / IP, Named Pipes, Shared Memory
  • SQL Native Client (SNAC), OLE DB Drivers
  • SQL Database Architecture - RAID Levels
  • Log Sequence Numbers (LSN), Mapping
  • Log File Architecture - Virtual Log Files
  • Log File Architecture - Mini LSN, Usage
  • DB Catalogs, CLR Integration, MDAC
  • LSN Timestamps, Background Threads

VIDEO 12: BACKUPS - DB RECOVERY

  • 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, VERIFY
  • CONTINUE_ON_ERROR, Backup Validate
  • 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
  • Real-time Errors and Solutions

VIDEO 13: RESTORES, DB 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
  • Choosing correct DB Recovery Model
  • 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
  • Real-time Errors and Solutions

VIDEO 14: JOBS, SQL 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
  • Job Activity Monitor, Agent Threads

VIDEO 15: PERFORMANCE TUNING - 1

  • Identifying Long Running Queries
  • Dynamic Management Objects: DMV, DMF
  • Audit Long Running Queries - DMV/DMF
  • Audit Frequent Running Queries - DMF
  • Query Audits and DMVs / DMFs with Joins
  • Query Statistics & LIVE Execution Plans
  • CROSSAPPLY with Dynamic Views
  • Avoding Sub Queries and Conditions
  • Comparing Sub Queries and Query Joins
  • Query Tuning and Resource Optimization
  • STATISTICS - Purpose and Tuning Options
  • Column Statistics - Usage Advantages
  • Index Statistics - Auto Index Creations
  • Manual Update of Column Statistics
  • Statistics in Query Tuning Process
  • STATISTICS in Indexes, Query Conditions
  • LIVE Query Statistics, Table Statistics
  • HASH JOIN Examples and Precautions
  • MERGE JOIN Examples and Precautions
  • LOOP JOIN Examples and Precautions
  • OUTER APPLY and Multi - Level Joins

VIDEO 16: 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
  • LIKE Operator - Using Wild-cards
  • Full Text Search Service Configuration
  • Full Text Search - Database Settings
  • Database Catalogs (FTC) and Storage
  • Full Text (FT) Indexes for Query Tuning
  • Full Text Columns and Primary Key Index
  • Full Text Index - Search Query Issues
  • Full Population, Incremental Population
  • CONTAINS() and FREETEXT() Functions
  • Token Search and Inflectional Forms
  • Performance Tuning @ Full Text Indexes
  • New Tuning Options @ SQL 2016

VIDEO 17: PERFORMANCE TUNING - 3

  • Index Internals and Execution Plans
  • Execution Plans, Statistics, Query Cost
  • Index Fragmentation Issues, Performance
  • SAMPLED and 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, Metadata Info
  • Filtered Indexes and Size Limitations
  • Table Statistics, Query Tuning Options
  • Heaps, Clustered, Nonclustered Indexes
  • Fill Factor, Pad Index and Query Tuning
  • Memory Pages and IO Performance
  • MEMORY LEAKS and PAGE WAITS
  • LATCH WAITS and Performance Impact

MORE TUNING: VIDEOS 26, 27


VIDEO 18: REPLICATION For HA - DR (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 & 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
  • Real-time Errors and Solutions

VIDEO 19: REPLICATION For HA - DR (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 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 Errors, Events and Solutions
  • Replication for HA and DR - Strategies
  • Replication Limitations and Precautions
  • Replication Errors & Solutions

VIDEO 20: LOG SHIPPING - DB 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 and Recovery
  • 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
  • Real-time Errors and Solutions

VIDEO 21: DB 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
  • 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
  • SET PARTNER Options and Db Recovery
  • DB Recovery without Witness. Failover
  • DB Mirroring Errors and Solutions

VIDEO 22, 23: 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
  • User Server Level Roles & Permissions
  • User Database Level Roles & Permissions
  • Schemas and DB User Mappings - Usage
  • GRANT, WITH GRANT, DENY, REVOKE
  • OWNERSHIP and AUTHORIZATION
  • Testing Security Operations, Scripting
  • Common Security Functions & Queries
  • DMVs for Server and DB Security Audits
  • Useful Security Audit Queries (DMVs)
  • KEYS, Passwords - Data Level Encryption
  • CERTIFICATES & TRIPLE-DES Algorithm
  • Master Passwords and Data Encryption
  • SQL Server Credentials, Job Security
  • Proxies - Purpose, Usage and Options
  • Server Credentials for Agent Proxies
  • 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

VIDEO 24: DB HEALTH CHECK, AUDITS

  • 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 Audits and Tempdb Audits
  • Dynamic Management Views (DMVs)
  • Important Dynamic Management Functions
  • 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 Tool
  • DEADLOCKS - Avoidance, Prevention
  • LIVELOCKS - Avoidance, Prevention
  • Deadlock Graphs @ SQL PROFILER, XDL
  • Isolation Levels For OLTP - TempDB

VIDEO 25: DB MAINT" 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

VIDEO 26: PERFORMANCE TUNING - 4

  • Memory Optimized Tables & Usage
  • LIVE QUERY STATS Analysis in SQL 2016
  • Real-world Considerations for DTA Tool
  • Workload Groups & Resource Allocation
  • Query Priority, CPU / Memory / IO Limits
  • Windows Fibres, Priority Boost and DOP
  • Processor Settings, Counters & Thresholds
  • Recommended Thread Counts and Fibres
  • Query Memory, Memory Leaks, Pagination
  • Thread Level Architecture - CPU Nodes
  • Windows Fibres, Priority Boost Options
  • DOP - Degree Of Parallelism & Options
  • Temporal Tables, SYSTEM_VERSIONING
  • Temporal Tables @ DML Audit Performance
  • In-Memory Tables Creation, Index Options
  • Extended Events & Performance Impact
  • LIVE Query Statistics - Monitoring Options
  • LIVE Query Statistics - Baseline Creation
  • LIVE Execution Statistics, Hash Plans
  • Natively Compiled Procedure Performance
  • System Versioned Temporal Tables
  • Querying VERSIONS from Temporal Tables

VIDEO 27: PERFORMANCE TUNING - 5

  • PERFMON Counters and PSSDIAG Tools
  • Dynamic Data Masking DDM Performance
  • Secured Column Access - DDM Functions
  • Impersonation with Data Masking (DDM)
  • Index Management Options - SQL 2016
  • Distributed Replay Controller Tool, SCOM
  • Data Migration Assistant (DMA) Tool
  • PSSDIAG Tool - Performance Monitoring
  • SQL Browser Server - TCP IP, Trace Flags
  • CPU Thread Management,Windows Fibres
  • Priority Boosting and Windows Kernel
  • Machine Code Usage in Queries
  • %%COLNAME%% Query Performance
  • Index Management Options - SQL 2016
  • New Tuning Options - SQL Server 2016
  • Performance Tuning - Checklist Activities

VIDEO 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
  • Corrupted Logs, 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

VIDEO 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 & Objects
  • Pre Database Maintenance Activities
  • Post Database Maintenance Activities
  • Real-world Management Considerations

VIDEO 30: CLUSTERING CONFIG"

  • 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

VIDEO 31: CLUSTERING: AD Config

  • 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)

VIDEO 32: HA DR @ 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

VIDEO 33,34: 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

Video 35, 36: Real-time Project for SQL DBA

ECommerce Domain:
Always-On (AG) Groups, HR & DR Implementation, DB Monitoring PSSDIAG, Tuning, Trace Flags, Policy Based Management (PBM), Facets Server Aliases, Configuration Manager 3rd Party Tools, SLA-OLA, DBA Checklist Activities Database Migration to SQL Azure Routine DBA Activites & Solutions Emergency DBA Activities & Solutions Common Erros & Solutions

Azure SQL Database DEV & DBA (Applicable for SQL DBA Training Course - PLAN B)

Module I: AZURE SQL DATABASE

Module II: AZURE SQL DBA

Video 37: 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

Video 43: 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

Video 38: 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

Video 44: 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

Video 39: 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

Video 45: 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

Video 40: 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

Video 46: 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

Video 41: 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

Video 47: 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

Video 42: 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

Video 48: 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

 

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

 

 

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

 

* Above course curriculum applicable for registrations from Jan 6th, 2018.

Every video is completely practial, real-time. Session wise notes and lab work for each video. Trainers availble for doubts clarifications.

SQL DBA Video 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
  • 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
  • High Availability, Disaster Recovery, Always-On
Register Today Other Popular Courses: SQL DBA Training, MSBI Training, SSIS Training, SSAS Training, SSRS Training [+] More Courses