Azure SQL Database Training

Complete Real-time and Practical Azure SQL Database Training with Real-time Scenarios. This course includes complete practical approach to every detail of Azure SQL Database Development activities including On-premise to Azure Azure Database Migrations, Schema Migrations, Assessments, In-depth Query Tuning, Stretch Databases, Azure Shell, Power Shell, In - Memory Tables, Azure Search, Sharding, Application Connections and Azure CLI. This Azure SQL Database course also includes One Real-time Project on On-Premise to Azure Migration using DMA Tools.

Azure SQL Dev Training with Azure SQL

	PLAN A	PLAN B	PLAN C
Applicable For	Azure SQL Dev	Query Tuning + Azure SQL	SQL Dev + Query Tuning + Azure SQL
Course Duration	1 Week	2 Weeks	6 Weeks
Course Content	Chapters 26 to 30	Chapters 21 to 30	Chapters 1 to 30
Completely Real-time, Practical	✔	✔	✔
Mock Interviews, Case Studies	✔	✔	✔
Azure SQL Database (Cloud)	✔	✔	✔
Azure SQL Database Migrations	✔	✔	✔
Stretch Databases, MCSA 70-762	✔	✔	✔
In-depth Query Tuning	❌	✔	✔
Performance Tools, Locks	❌	✔	✔
Complex SPs in T-SQL	❌	✔	✔
SQL Basics and Query Writing	❌	❌	✔
SQL DB Design, Table Design	❌	❌	✔
Normal Forms, Joins, Queries	❌	❌	✔
Indexes, Stored Procedures	❌	❌	✔
Excel Integration, Pivot Charts	❌	❌	✔
Advanced Stored Procedures	❌	❌	✔
CTE, PIVOT, XML and BLOB Data	❌	❌	✔
Real-time Project [Banking]	❌	✔	✔

Trainer: Mr. Sai Phanindra T

All Session Are Completely Practical & Real Time

Trainer : Mr. Sai Phanindra T

S No	Time (IST, Mon - Fri)	Start Date
SQL Server & T-SQL Schedules [Ch 1 to 20]
1	6 AM - 7 AM	June 27th	Register
2	8 PM - 9 PM	July 12th	Register
3	9 PM - 10 PM	June 8th	Register
4	11 PM - 12 AM	June 19th	Register

S No	Time (IST, Mon - Fri)	Start Date
Azure SQL Dev Schedules [Ch 25 to 30]
5	6:30 AM - 8 AM (W)	June 3rd	Register
6	9 PM - 10:30 PM (W)	June 3rd	Register

Azure SQL Dev Training Highlights

✔ Queries, Joins	✔ Dynamic SQL
✔ Query Tuning	✔ CTEs, TVPs
✔ Excel Imports	✔ In-Memory Tables
✔ Excel Pivots	✔ Azure SQL Concepts
✔ Complex joins	✔ Azure DB Migrations

If none of the above schedules work for you, please opt for On-demand Video Training

Azure SQL Database (DEV) Training Course Contents:

Brochure:

Register Today

Module 1: SQL Server & T-SQL Training Content [For Plans C]

Ch 1: DATABASE INTRODUCTION

Data, Databases and RDBMS Software
Database Types : OLTP, DWH, OLAP
Microsoft SQL Server Advantages, Use
Versions and Editions of SQL Server
SQL : Purpose, Real-time Usage Options
SQL Versus Microsoft T-SQL [MSSQL]
Microsoft SQL Server - Career Options

SQL Server Components and Usage
Database Engine Component and OLTP
BI Components, Data Science Components
ETL, MSBI and Power BI Components
Course Plan, Concepts, Resume, Project
24 x 7 Online Lab for Remote DB Access
Software Installation Pre-Requisites

Ch 7: JOINS & LINKED SERVERS

JOINS - Table Comparisons Queries
INNER JOINS For Matching Data
OUTER JOINS For (non) Match Data
Left Outer Joins with Example Queries
Right Outer Joins with Example Queries
FULL Outer Joins - Realtime Scenarios
Join Queries with "ON" Conditions

CROSS JOIN and CROSS APPLY
One-way, Two way Data Comparisons
Linked Servers Configurations
Linked Servers: RPC Settings & Tests
Linked Servers, Remote Joins in TSQL
Multi Server Connections, DB Access
2 Part, 3 Part, 4 Part Name Conventions

Ch 13: STORED PROCEDURES & TVP

Variables: Declaration, Realtime Usage
Variables: Value Assignment, Reporting
Identity Property - Missing ID Values
Data Validations, DMLs in Stored Procs
Dynamic Data Insertions with SPs
TRY..CATCH and THROW Operations
Error Handling, Rollback in SQL Server

Table Valued Parameters (TVP), Usage
ReadOnly Parameters, Stored Procedures
Output Parameters, Stored Procedures
User Data Types and Real-time Use
Table Cloning, Inserts @ Table Variables
Table Variables Usage in T-SQL
Output Parameters, Stored Procedures

Ch 2: SQL SERVER INSTALLATIONS

System Configuration Checker Tool
Versions and Editions of SQL Server
SQL Server and SSMS Installation Plan
SQL Server Pre-requisites : S/W, H/W
SQL Server 2022 / 2019 Installation
SQL Server 2017 Installation
Instance Name and Server Features

Instances : Types and Properties
Default Instance, Named Instances
Port Numbers, Instance Differences
Service and Service Account Use
Authentication Modes and Logins
Windows Logins and SQL Logins
FileStream and Collation Properties

Ch 8: VIEW, SPs, FUNCTIONS BASICS

Database Objects: Overview & Usage
Views: Types, Usage in Real-time
System Predefined Views and Audits
Listing Databases, Tables, Schemas
Functions: Types, Usage in Real-time
System Predefined Functions, Audits
DBId, DBName, ObjectID, ObjectName

Using Parameters in SQL Server
Dynamic Joins for Database Audits
Procedures: Types, Usage in Real-time
User & System Predefined Procedures
Parameters and Dynamic SQL Queries
Sp_help, Sp_helpdb and sp_helptext
sp_pkeys, sp_rename and sp_help

Ch 14: STORED PROCEDURES & Triggers

DML Triggers and DDL Triggers
FOR and INSTEAD OF Triggers
Magic Tables : Inserted, Deleted
Views on Tables - SCHEMABINDING
ENCRYPTION and CHECK OPTION
Cascaded Views, Encrypted Views
Updatable Views, Joins with Triggers

Stored Procedures @ Triggers, Views
FOR and INSTEAD OF Triggers
Magic Tables : Inserted, Deleted
ENCRYPTION and CHECK OPTION
Cascaded Views, Encrypted Views
Updatable Views, Joins with Triggers
Stored Procedures @ Triggers, Views

Ch 3: SSMS Tool, SQL BASICS - 1

SQL Server Management Studio
Local and Remote Connections
System Databases: Master and Model
MSDB, TempDB, Resource Databases
Creating Databases : Files [MDF, LDF]
Creating Tables in User Interface
Data Insertion & Storage. Limitations

SQL : Purpose and Real-time Usage
SQL Versus T-SQL : Basic Differences
DDL, DML, SELECT, DCL and TCL
Creating Tables using SQL Scripts
Data Storage, Inserts - Basic Level
Table Data Verifications with Select
SELECT Statement for Table Retrieval

Ch 9: Triggers & Transactions

Triggers - Purpose, Real-world Usage
FOR/AFTER Triggers - Real time Use
INSTEAD OF Triggers - Real time Use
INSERTED, DELETED Memory Tables
Using Triggers for Data Replication
Enable Triggers and Disable Triggers
Database Level, Server Level Triggers

Transactions : Types, ACID Properties
Transaction Types and AutoCommit
EXPLICIT & IMPLICIT Transactions
COMMIT and ROLLBACK Statements
Open Transaction Scenarios & Cause
Query Blocking Scenarios @ Real-time
NOLOCK and READPAST Lock Hints

Ch 15: STORED PROCEDURES & Cursors

Cursors - Benefits, Cursors in SProcs
Using Cursors in Real-world Scenarios
Cursors : Declaring Variables, Life Cycle
Declaration, Open / Close Cursors
Cursor Types: Forward Only, Scroll
Cursor Types: Static and Dynamic
Keyset Driven Cursors & Indexes

Default Cursor Types and Usage
FETCH operation and Data Retreival
Keyset Cursors and @@FetchStatus
Nesting of Stored Procedures - Dynamic
Data Formatting and WHILE Loops
Using Temporary Tables for Formatting
Loading Data from SPs to Temp Tables

Ch 4: SQL BASICS - 2

Creating Databases & Tables in SSMS
Single Row Inserts, Multi Row Inserts
Rules for Data Insertion Statements
SELECT Statement @ Data Retrieval
SELECT with WHERE Conditions
Batch Concept and Go Statement
AND and OR Operators Usage

IN Operator and NOT IN Operator
Between, Not Between Operators
LIKE and NOT LIKE Operators
UPDATE Statement & Conditions
DELETE & TRUNCATE Statements
Logged and Non-Logged Operations
ADD, ALTER and DROP Columns

Ch 10: DB Architecture & Group By

Database Architecture: Data Files
Database Architecture: Log Files
Primary and Secondary Data Files
Log Files Creation and Realtime Use
Creating and Using Filegroups
Linking Tables with Filegroups
Scripting Database and Objects

GROUP BY: Importance, Realtime Use
GROUP BY Queries and Aggregations
Group By Queries with Having Clause
Group By Queries with Where Clause
Using WHERE and HAVING in T-SQL
Using Group By in Data Audits
Using Group By with Joins, Audits

Ch 16: STORED PROCEDURES & CTEs

CTE: Common Table Expressions
Real-time Scenarios with CTEs - Usage
Using CTEs for Data Retreival, SELECT
Using CTEs for Data Manupulations
Sub Queries with Self Joins : Issues
CTEs for Avoiding Self Joins, Tuning
CTEs for Avoiding Sub Queries, Tuning

Window Functions: RowNumber()
Rank() and Dense_Rank() Functions
ROW_NUMBER() with CTE Queries
Identifying Duplicated Rows
Remving Duplicate Rows with CTEs
Recursive CTEs and ANCHOR Element
Termination Checks in Recursive CTEs

Ch 5: SQL Basics - 3, T-SQL INTRO

Database Objects : Tables and Schemas
Schemas : Group Tables in Database
Schemas : Security Management Object
Creating Schemas & Batch Concept
Using Schemas for Table Creation
Data Storage in Tables with Schemas
Data Retreival and Usage with Schemas

Table Migrations across Schemas
Import and Export Wizard in SSMS
Data Imports with Excel File Data
Performing Bulk Operations in SSMS
Temporary Tables : Real-time Use
Local and Global Temporary Tables
# and ## Prefix, Scope of Usage

Ch 11: TSQL Queries: Group By, Joins

Joins with Group By Queries in TSQL
Joining 3 Tables with Group By
Joining 4 Tables with Group By
Multi Table Joins with Table Aliases
Table & Column Aliases with Joins
Joins with HAVING Conditions
Joins with WHERE & Aggregations

Joins with Sub Queries, Formatting
Joins with IIF() Function, Conditions
Joins with CASE Statement Conditions
UNION and UNION ALL Operator
Storing Queries in Database Views
Office Data Connections, Excel Reports
Manual Data Refresh & Reports

Ch 17: FUNCTIONS Level 2, XML & JSON

Functions: Types, Real-world Usage
Scalar Value Returning Functions
Inline Table Value Functions
Multi-Line Table Value Functions
WHILE Loops and Iterations in T-SQL
Table Variables Usage in T-SQL
Data Type Conversions with Functions

Composite Keys , Computed Columns
Self Referencing Keys, Self Joins
Adding Keys to Existing Tables
XML AUTO, XML RAW and XML PATH
BULK INSERT, BULKCOLUMN, JSON
OPENROWSET, PIVOT and UNPIVOT
JSON Files - Data Import into SQL DB

Ch 6 : CONSTRAINTS, INDEXES

Constraints and Keys - Data Integrity
NULL, NOT NULL Property on Tables
UNIQUE KEY Constraints: Importance
PRIMARY KEY Constraint: Importance
FOREIGN KEY Constraint: Importance
REFERENCES, CHECK and DEFAULT
Candidate Keys and Identity Property

Database Diagrams and ER Models
Relationships Verification and Links
Indexes : Basic Types and Creation
Index Sorting and Search Advantages
Clustered and NonClustered Indexes
Primary Key and Unique Key Indexes
Need for Indexes - working with Keys

Ch 12 : ER MODELS, NORMAL FORMS

First Normal Form and Atomicity
Second Normal Form, Candidate Keys
3rd Normal Form Multi Value Dependency
Boycee-Codd Normal Form : BNCF
Fourth Normal Form Realtime Advantages
Self Reference Keys and 4 NF Usage
1:1, 1:M, M:1, M:M Relationship Types

MERGE Statement - Comparing Tables
WHEN MATCHED and NOT MATCHED
Incremental Load with MERGE Statement
UPSERT Operations with MERGE
Stored Procedures for Merge Statement
DML Operations with ON Keyword
Tuning / Optmizing Merge Statement

Ch 18 - 20: REAL-TIME PROJECT (BANKING)
Includes 2500 Lines of Code (SOLVED).
Phase 1: DATABASE DESIGN

Understanding Project Requirements
End to End Project Work Flow
Naming Conventions in Real-time
Primary (mdf) and Secondary (ndf) Files
Implmenting FileGroups For Performance
Table Schemas : Creation and Use
Implementing Normal Forms (OLTP)

Computed Columns and Data Types
SQL_Variant, Bit, sysname Data Types
Test Data Insertions, Options
Email and Phone Number Validations
Data Types Conversions, Validations
Data Validations and Schema Validations

Phase 2: QUERY DESIGN

Join Types and Join Options For Reports
Views with JOIN Options For Query Store
Implementing Functions for Calculations
Using PIVOT Tables in Queries
Dynamic Conditions in Queries
Parameterized Queries in T-SQL
Dynamic Joins and Conditions

Using User Defined Functions (UDF)
Using Stored Procedures for T-SQL
Merge Option for Faster Queries
Using Lock Hints inT-SQL Queries
Creating and Using Office Data Connections
Excel Pivot Tables and Pivot Charts

Phase 3: PROGRAMMING

Event Handling , Error Handling
Stored Procedures with Transactions
Error Handling, Event Handling Options
Transaction Nesting, Save Points
Stored Procedures with Tables
Stored Procedures with Views
Stored Procedures with Functions

Automating DML with Triggers
Project Deployments, Project FAQ
Project Solution Explanation
Resume Points from the Project
Interview FAQs from Project
Interview FAQs For Each Concept

Case Study 1 -
For Database, Table Design (Constraints, Keys)

Case Study 2 -
For Query Writing + Window Functions
(Rank, RowNumber)

Module 2: Performance Tuning & Azure SQL Dev Training Content [For Plans A & B]

Module II: Performance Tuning Applicable for Plans B, C	Module III: Azure SQL Database Development Applicable for Plan C
Ch 21: Tuning 1 - Audits, Indexes Audit Long Running Queries : DMV, DMF Activity Monitor Tool, Server Dashboards Logical I/O, Physical I/O, Database I/O Recent Expensive Queries, Wait Time Active Expensive Queries, Statistics Plan Handle, Execution Time - Audits CPU, IO, Memory Consumption Reports Indexes: Architecture and Index Types B Tree Structure, IAM Page [Root] Clustered & NonClustered Indexes Included, Columnstore, Online Filtered, Covering, Indexed Views Fill Factor and Pad Index Options Query Store - Settings and Advantages	Ch 26: CLOUD, AZURE CONFIG Introduction to Cloud & Advantages Cloud Architecture: IaaS, PasS and SaaS Microsoft Cloud Advantages, Azure Azure Products and SQL Services Paas Implementations For SQL Server IaaS Implementations For SQL Server Comparing PaaS, IaaS Implementations Benefits of SQL Server in Azure Cloud Azure Account and Free Subscription Subscription: Need, Subscription Types Resources: Creation and Usage Resource Groups: Creation and Usage Azure SQL: Realtime Implementations Logical Server, Virtual Machine Options
Ch 22: Tuning 2: Index Management PARTITIONS: Performance Tuning Partition Functions & Partition Schemes Partitioning Un-partitioned Tables: GUI Partition Compression : ROW & PAGE Auditing Table Partitioned Structures Statistics : Auto Creation, Updates Internal and External Fragmentation Index Rebuilding Process and Audits Database Maintenance Plans Jobs Last Used, Page Count, Fragmentation Index Page Count and Index Condition Degree Of Parallelism [DOP] Settings Resumable Indexes: ONLINE, RESUME PAUSE & RESUME in Index Rebuilds	Ch 27: AZURE SQL SERVER CONFIG Installing SSMS and Azure Data Studio Azure Account and Free Subscriptions Azure SQL Server (Logical Server) Azure SQL Server Firewall Settings Firewall Rules - IP for Remote Access Server Properties and Status Format Password Resets in Azure SQL Server Azure SQL Databases & Pricing Tiers Azure SQL Database Access from SSMS Elastic Scaling with Azure Databases General Purpose and Business Critical Basic, Standard and Premium Plans vCore Based Purchasing : Gen 4, Gen 5 Resource Groups For Azure SQL Server
Ch 23: Tuning 3 - Tuning Tools Tuning Tools: Workload Files, .trc Files Profiler Tuning Template, SP Events DTA, Profiler Trace : Recommendations PDS: Physical Design Structures Index, Stats, Partition Recommendations DTA with Query Execution Cache Perfmon Tool : Usage, Permon Counters Real-time Tracking: CPU, Memory, IO Execution Plan Analysis and Internals Query Costs: IO Cost and CPU Cost Query Costs: SubTree & Operator Cost NUMA Nodes, Processor, IO Affinity Thread Count, Degree of Parallelism Table Scan, Index Scan, Index Seek	Ch 28: ELASTIC DTUs, DB MIGRATION Azure Storage: Creation & Containers LRS, GRS, RA-GRS Storage Accounts Storage Containers, Explorer Tool Data and Database Migration in Azure Data Migration Assistant (DMA) Tool DB Migrations To Azure SQL Databases Database Exports and Imports in Azure Migration Scopes: Schema and Data Schema Generation, Data Migration Data Migration Verification, Row Count DTU: Data Transaction Unit Bounding Box Model, Elastic Pool eDTUs, Elastic Pool. Per DB Settings Performance Recommendations
Ch 24: Tuning 4 - Lock Management LOCKS : Types and Isolation Levels S, X, IX,U, MD, Sch-M and Sch-S Lock Audits : SP_WHO2 & SP_LOCK sysprocesses and Lock Waits : Audits Open Transactions, Query Blocking Lock Hints and Isolation Levels Read Committed, Read Uncommitted Serializable and Repeatable Read Snapshot Isolation, Page Versioning Read Committed Snapshot Row Version Choosing Correct Isolation Level Profiler Tool and Lock Templates Profiler Filters, Column Selections XDL Files and Deadlocks Prevention	Ch 29: AZURE SQL DATABASE TUNING Azure SQL Server Level Tuning Options Azure SQL Database Tuning Options Automated Tuning and Peak-Loads Force Plan, Create Index and Drop Index Query Insight and Recommendations IO Metrics, CPU & Query Statistics Data File IO, Log File IO, Custom Reports Query Audits with Query IDs, Dashboards DTU Usage Reports and Elastic Queries Query Recommendations, Query Costs Azure Search Service and Pricing Tiers Suggester and Analyzer Modes for Tuning Retrievable, Facetable, Facetable Indexes Change Tracking & Watermark Columns
Ch 25: FULL TEXT SEARCH, MOT Introduction to Full Text Search (FTS) Stop Words, Stemmer and Thesaurus Indexer Program, Query Processor Database Catalogs (FTC), FDHost.exe Creating Full Text Catalogs (FTCs) CONTAINS() & FREETEXT() Queries Query Performance Impact with FTS Resumable Indexes with SQL Server ONLINE, RESUME, PAUSE Options In-Memory Tables: Creation & Usage Memory Snapshots at Database Temporal Tables : for DML Audits Using Temporal Tables for Data Audits System Versioning, MAX_DURATION	Ch 30: Real-time Project - 2 Azure SQL [PaaS] Implementation Azure SQL DB Project Environment Azure SQL Database Design Azure SQL Queries & Reports Azure T-SQL Programming Cursors and CTEs in Azure SQL SQL Server Versus Azure SQL DB Azure SQL Database Deployments Database Architecture Differences T-SQL Data Types Differences Database Objects Differences Partitions and FTS Differences Indexes & Tuning Differences Maintenance Plans, SQL Agent