Azure SQL (DEV) 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 Dev Training - Trainer : Mr. Sai Phanindra T

  Timings (IST) Free Demo Start Date  
1 6:30 AM to 7:30 AM Oct 17th Oct 18th Register
2 9 AM - 10 AM Recently Started Register
3 10:15 AM to 11:15 AM Oct 30th Oct 31st Register
4 11:15 AM to 12:15 PM Sept 19th Sept 20th Register
5 5:30 PM to 6:30 PM Oct 4th Oct 5th Register

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

All Training Sessions are Completely Practical & Real-time

Azure SQL Developer Training

Course Fee & Duration

Total Course Fee: INR 12000 (USD 200)

Total Duration: 5.5 Weeks

HIGHLIGHTS

Azure SQL Development Azure SQL Deployments
Performance Insights, Tuning Indepth Stored Procedures
Query Tuning, Insights Dynamic Joins in Cloud

All Training Sessions are Completely Practical & Real-time

 

Azure SQL (DEV) Training Course Contents:

PART 1 : SQL Server Basics, Queries and Development

Module I: SQL Server & Design, Queries, Joins

Module II: T-SQL Queries, Tuning & Programming

Chapter 1: SQL SERVER (2017/2016, 2014) INSTALLATION

  • Introduction to Data, Database, DBMS, RDBMS
  • Why Microsoft SQL Server? Advantages (Technical/Usage)
  • SQL Server - Career Options, Certifications, Projects
  • SQL Server Components : DB Engine. Database Types
  • What is SQL? What is T-SQL? Differences. Why T-SQL?
  • Versions and Editions of SQL Server - Overview
  • Session Wise Plan, Material and Real-time Project Details
  • LAB PLAN - 24x7 LIVE Server (Online Lab) For the Course
  • SQL Server 2016/2017 Server Installation Steps
  • SQL Server 2014 / 2012 Versions and Differences
  • System Databases in SQL Server, Configuration Options
  • Instance Types : Default and Named Instances. Instance IDs
  • Service, Authentication and Instance Collation Properties
  • SQL Server Tools - SQL Server Management Studio (SSMS)
  • SQL Server Tools For Mac, Ubuntu and Linux
  • Connection to Local Servers, Remote Server, Cloud Server

Chapter 9: STORED PROCEDURES - LEVEL 1

  • Stored Procedures - Purpose, Syntax, Properties and Types
  • Compilation, Precompilation and Query Optimization (QO)
  • Variables - Usage and Data Types in Stored Procedures
  • Parameters - Usage and Data Types in Stored Procedures
  • Stored Procedure Executions - Syntax, Alternate Options
  • Stored Procedures for Data Validations & Missing Identity
  • Stored Procedures for Dynamic SQL Queries. Views & SPs
  • Stored Procedures for Data Reporting. Advantanges, Tuning
  • Important System Procedures For Metadata Access. Usage
  • Important Extended Procedures For Application Operations
  • IF.. ELSE, IF .. ELSE IF, IIF Conditions. PRINT statements
  • Error Handling Techniques in T-SQL: TRY, CATCH, THROW
  • Dynamic Parameters and Variables. Examples with Views
  • Default Parameter Values, Data Types and NULL Values
  • Batch Executions with Stored Procedures. Variants
  • Unicode Data and Dynamic SQL Queries. sysname Data

Chapter 2: BASIC SQL QUERIES - LEVEL 1

  • What is SQL? Why T-SQL? Basic SQL Queries in SSMS
  • DDL and DML Statements - Creating & Using Databases
  • Testing Installation to Local and Remote Labs
  • Server Connections, MASTER DB Concept and Session IDs
  • Basic SQL for Starters - Introducing Databases, Tables
  • Table Creation (Basic Level) - Columns and Data Types
  • INSERT / Store Data into SQL Server Tables - Options
  • INSERT versus INSERT INTO Options. Performance Difference
  • Single Row Inserts and Multiple Row Inserts
  • SELECT Queries with Row and Column Filters - Syntax
  • AND, OR, NOT, EXISTS, IN, NOT IN, BETWEEN
  • IS, IS NOT, NOT BETWEEN and Other SQL Operators
  • UPDATE Statements with / without Conditions. SET
  • DELETE Statements with Conditions. Logging Options
  • TRUNCATE Statement - DELETE Comparisons, Logging
  • CLIENT - SERVER Architecture (TDS) & Client Statistics
  • QUERY STATISTICS with SQL Queries: Execution Time

Chapter 10: STORED PROCEDURES - LEVEL 2

  • Stored Procedures for Sub Queries, Dynamic Sub Queries
  • Stored Procedures for Recursive and Nested Queries
  • OUTPUT Parameters in Stored Procedures. Usage Options
  • Common Table Expressions (CTE) and In-Memory - Syntax
  • Row Number and Rank Generation, Sub Queries, Self Joins
  • Stored Procedures for Parameterized CTE (Sub) Queries
  • Using CTE for Table Data Operations - DML & Retrieval
  • CTE for DML and DDL Operations in Stored Procedures
  • Using Recursive CTEs and Self Joins with Stored Procedures
  • Precautions for Recursive CTEs - Performance Impact
  • Query Tuning Operations with CTEs. Query Store Options
  • CTE Advantages and Limitations - Precompilations
  • Dynamic SQL Queries with Parameters and Variables
  • Cached Plans and Memory Store for Stored Procedures
  • RECOMPILE Options and ENCRYPTION Options - Scenarios
  • Identity Inserts - Manual Sequence. Dynamic Inserts
  • ANCHOR Members and RECURSIVE Members. Termination

Chapter 3: BASIC SQL QUERIES - LEVEL 2

  • Table Design - Creation. Columns - Data Types, Length
  • Table Design using T-SQL Scripts - Syntax, Examples
  • Table Design using User Interface - Usage Options
  • Data Types, Length, NULLs and Naming Conventions
  • BATCH and TRANSACTION Concepts - Insert Examples
  • CREATE, ALTER, DROP -- INSERT, UPDATE, DELETE
  • SELECT Queries with Schema on Tables, Column Aliases
  • T-SQL Data Types and NULL Values. Computed Columns
  • Database Log Files for DML - Logged, NonLogged Options
  • Comparing DELETE and TRUNCATE Statements - TLog Files
  • T-SQL Operators: IN, BETWEEN, IS, AND, OR, EXISTS
  • Default Schema and Default Filegroup for Table Design
  • Basic Sub Queries - SELECT, MIN/ MAX. Column Aliases
  • SET Operations in T-SQL : UNION, INTERSECT, MINUS
  • UNION, UNION ALL Operators. Differences, Row Order
  • Local Temporary Tables, Global Temporary Tables
  • Batch Versus TDS Packets. Client - Server Architecture

Chapter 11: STORED PROCEDURES - LEVEL 3

  • SQL Injection Attacks & Vulnerables: Parameter Sniffing
  • Stored Procedure for ReadWrite Parameters - Usage
  • READONLY Parameters, Table Data Type (User Defined)
  • Error Handling with Table Valued Parameters in SProcs
  • Startup Stored Procedures: Configuration, Server Property
  • Server Startup, Auto Log Options with Stored Procedures
  • Extended Stored Procedures - Purpose, Options & Usage
  • Using Extended Stored Procedures with User Procedures
  • Stored Procedures for Dynamic Values, Calendar Data
  • Cursors - Benefits, Syntax. Using SProcs with Cursors
  • FORWARD_ONLY and SCROLL Cursors Types. Limitations
  • STATIC and DYNAMIC Cursors Types. ABSOLUTE Fetch
  • LOCAL and GLOBAL Cursor Types & Scope, Reusability
  • KEYSET DRIVEN Cursor Types & Performance Options
  • Embedding Cursors in Procedures and User Functions
  • SPs with Cursors @ Dynamic Data Loads, Data Formatting
  • Memory Limitations with Cursors with SP Recompilations

Chapter 4: DATABASE & TABLE ARCHITECTURE

  • SQL Server Databases - Purpose and Design Options
  • SQL Database Architecture - Logical and Physical View
  • Database Properties - Files - Types - Storage Options
  • Data Files : Purpose and Sizing. Detailed Architecture
  • Filegroups : Purpose and Grouping Options. Properties
  • Log files : Sizing, Placement & Detailed Architecture
  • Pages, Extents (Uniform, Mixed). Data Allocation Process
  • Write Ahead Log (WAL) and Log Sequence Number (LSN)
  • Virtual Log File (VLF) and MINI LSN. Operation Audits
  • Database Creation using GUI - Adding Files, Filegroups
  • Database File and Filegroup Options. GUI Limitations
  • mdf, ndf, ldf and Filegrowth, Autogrowth Options
  • Adding Filegroups and Files. Size, Property Modifications
  • Routing Tables to Database File Groups, Advantages
  • Schemas - Purpose, Creation and Usage with Tables
  • Schemas TRANSFER with Tables and Synonyms
  • CHAR versus VARCHAR Differences - Type, Size Allocations
  • Planning and Designing Very Large Databases (VLDB)

Chapter 12: TRIGGERS, LINKED SERVERS, XML

  • Triggers - Purpose and Types. Scope Of Usage
  • DML Triggers - Events, Types and Practical Usage
  • FOR / AFTER Triggers - Syntax, Usage and Importance
  • INSTEAD OF Triggers - Syntax, Usage and Importance
  • INSERTED & DELETED Memory Tables with DML Triggers
  • Memory Usage with INSERTED/DELETED Tables. Usage
  • Triggers for Disabling DML Operations. Trigger Priority
  • Triggers for DML Operation Audits and Data Sampling
  • Triggers for Data Distribution to Multiple Tables / Views
  • Database Level Triggers and DDL Operations - FOR Type
  • Server Level Triggers and DDL Operations - FOR Type
  • Triggers for Bulk Operations, Updatable Views (Indexed)
  • Defining Linked Servers and Remote Join Queries
  • Import / Export Options and Triggers with Linked Servers
  • Recursive Triggers, XML Options, XML Queries
  • Real-time Considerations with Triggers - Precautions
  • Stored Procedures with Triggers and Advantages
  • Limitations with Triggers for DDL & DML Operations

Chapter 5: CONSTRAINTS and KEYS

  • Constraints and Keys - Ensuring Table Data Integrity
  • Normal Forms - Types, Relational Database (RDB) Design
  • OLTP Database Model & BCNF - Relations with PK / UQ
  • NULL, NOT NULL and Default Nullability for Columns
  • UNIQUE KEY Constraints: Importance, Uniqueness, Nulls
  • PRIMARY KEY Constraint: Properties, Priority, Limitations
  • FOREIGN KEY Constraint: References, Relations & Usage
  • FOREIGN KEY Constraints : Relating Two or more tables
  • CASCADED Foreign Keys and Relations - UPDATE, DELETE
  • CHECK Constraints: Properties, Conditions and Usage
  • CHECK Constraints: Multi Column Checks & Operators Use
  • DEFAULT Constraints: Properties, Usage and Limitations
  • Relations with Tables across Multiple Schemas, Usage
  • Identity Property with / without PRIMARY KEY, Usage
  • Composite Primary Keys & Practical Use. Recommendations
  • Self Referencing Keys & Usage. Using Unicode References
  • Adding / Modifying Constraints, Keys and Data Types
  • Naming Conventions For Constraints, Columns and Tables
  • Normal Forms - Types, Purpose and Usage. With Examples
  • BCNF: Boycee-Codd Normal Form and Practical Usage

Chapter 13: TRANSACTIONS & ISOLATION LEVELS

  • Introduction to Transactions - Types
  • Need for Transactions, Transaction Scenarios
  • ACID Properties and Transaction Types. Atomic Property
  • EXPLICIT, IMPLICIT Transactions - Query Blocking
  • IMPLICIT Transactions - Usage, Database Settings
  • AUTOCOMMIT Transactions - Advantages, Usage Examples
  • OPEN Transactions and Audits. OPENTRAN commands
  • Nested Transactions and COMMIT / ROLLBACK Rules
  • SavePoint Options with Explicit Transactions, Rollbacks
  • LOCK HINTS : READPAST, NOLOCK, HOLDLOCK - Usage
  • Isolation Levels : Types of Isolation Levels
  • ReadCommitted & Read UnCommitted Isolation Levels
  • Snapshot Isolation, Serializable Isolation Levels
  • ReadCommitted Snapshot Isolation with Tempdb Usage
  • Impact of Isolation Levels with Concurrent Database Users
  • Choosing the Best Isolation Level in OLTP Environment
  • TRY..CATCH..THROW & Error Handling with Transactions
  • Stored Procedures with with Triggers and Transactions
  • Choosing Transaction Type and Lock Hints
  • Real-world Considerations For Transactions
Real-time Case Study 1 : Online Medicare Database (DESIGN)
One - One Mock Interview + Project Work Starts

Chapter 6: JOINS, SUB QUERIES & NESTED QUERIES

  • JOINS - Purpose and Types, Use Case Scenarios
  • JOIN - Types, Queries and Importance of Reports
  • CROSS JOIN in detail. Examples and Conditions @ WHERE
  • INNER JOIN in detail. Examples with WHERE and ON
  • Comparing INNER JOIN with CROSS JOIN for Conditions
  • OUTER JOINS in detail. LEFT, RIGHT and FULL Joins
  • SELF JOINS with INNER / OUTER Joins. Usage Scenarios
  • Working with Self Joins on non key columns, advantages
  • JOINS with more than 2 tables. Syntax, Precedence Order
  • Query Optimization Considerations with Schema References
  • Deciding the best Join Type, Order and Query Options
  • JOIN Queries with Options and UNION, UNION ALL Operators
  • Basic Sub Queries and Joins. Alternate Syntax & Queries
  • Using ON and WHERE for Join Conditions. Working with NULLs
  • Using SubQueries for Self Joins and Outer Joins
  • Working with Nested Queries and Nested Sub Queries
  • Using Sub Queries and Nested Sub Queries with Outer Joins
  • End User Access to SQL Databases - Reporting Tools, Options
  • A Real-world Case Study understanding Joins & Queries

Chapter 14: INDEXES and QUERY TUNING OPTIONS

  • Indexes: Architecture (Page Level), Purpose and Types
  • Clustered Indexes - Architecture, Fragmentation Issues
  • Non Clustered Indexes - Architecture, Column References
  • SORT_IN_TEMPDB, FILLFACTOR and PAD_INDEX Options
  • Execution Plans and Query Optimization (QO) Techniques
  • Execution Plan - Table Scan, Index Scan and Index Seek
  • INCLUDED INDEXES - Purpose, Index Seeks, Query Tuning
  • COLUMNSTORE Indexes - Advantages, Usage Examples
  • COLUMNSTORE Indexes - Limitation @ Filtered Index
  • COLUMNSTORE Indexes and Online Indexes - Memory Options
  • FILTERED Indexes - Sizing Advantages and Limitations
  • ONLINE Indexes and OFFLINE Indexes - UNIQUE Indexes
  • Materialized Views / Indexed Views - Tuning Options
  • Working with UNIQUE Indexes on Tables, Views
  • Query Optimizer (QO) Options for Index Pages, Data Pages
  • Limitations of Indexes - Impact on DML and SELECT
  • Primary Key Index, Composite Indexes and Precautions
  • RID and Index Key Concepts. Index Page - Data Page Arch"
  • Real-world Considerations For Indexes (Tables, Views)

Chapter 7: VIEWS & FUNCTIONS - LEVEL 1

  • VIEWS - Benefits For Data Access, Table Operations
  • Defining Views on Tables - Syntax, Options, Uses
  • Views as Stored SELECT Statements, Data Access
  • SCHEMABINDING and ENCRYPTION Options - Advantages
  • Issues with Views For Data Validations - Solutions
  • Cascaded Views and WITH CHECK OPTION, Advantages
  • Orphan Views - Scenarios and Realworld Solutions
  • Common System Views For Metadata Access, Object IDs
  • Views on Multi Level Tables. Joins. Partitioned Views
  • Data Synchronization and Metadata Refresh with Views
  • Functions: Types, Purpose and Usage. Return Values
  • Scalar Value Returning Functions - Examples, Usage
  • Inline Table Value Returning Functions - Dynamic Joins
  • Multi-Line Table Value Returning Functions - Usage
  • Table Variables and Usage with Functions. Table Data Type
  • Variables and Parameters in SQL Server. Usage Differences
  • Dynamic Query Conditions with Functions. Return, Returns
  • SCHEMABINDING and ENCRYPTION Options with Functions

Chapter 15: SQL SERVER ARCHITECTURE

  • Client - Server Architecture of SQL Server
  • SQL Server Tools - Connection Options, TDS Packets
  • Protocols : TCP / IP, Named Pipes, Shared Memory
  • SQL Native Client (SNAC) and OLE DB Drivers / Providers
  • ISO - OSI Model of Data Connections, Encrypted Data
  • Query Processing and Query Optimizer (QO) Components
  • SQL Server Architecture For Database Engine, LCM Options
  • Architecture - Query Processor and Storage Engine
  • Architecture - Query Parser, Optimizer, Mini LSN, MDAC
  • Architecture - SQL Engine, SQL Manager and Query Buffers
  • Architecture - Write Ahead Log (WAL), Lazy Writer Threads
  • Architecture - SQLOS Threads and Task Schedulers, CLR
  • SQL Database Architecture - RAID Levels (S/W, H/W)
  • Log Sequence Numbers (LSN) and Time Mapping. Audits
  • Log File Architecture - Virtual Log Files and Usage
  • Log File Architecture - Mini LSN & Degree Of Parallelism
  • DB Catalogs, CLR Integration and MDAC Components
  • LSN Timestamps and MINILSN. Background Threads @ SQL

Chapter 8: FUNCTIONS, QUERIES & VIEWS - LEVEL 2

  • GROUP BY Queries for Data Reporting - Options
  • GROUP BY with HAVING Conditions and GROUPING()
  • GROUPING SETS Clause and Equivalents. Rollup and Cube
  • ROLLUP and CUBE - Sub Totals, Grand Totals, Aggregates
  • ROLLUP of Table Data. Column Aggregations. ORDER BY
  • CUBE on Table Data - Purpose & Usage. CASE Queries
  • HAVING versus WHERE and ON Conditions - Usage Differences
  • Query Execution Order with Joins, ORDER BY and ROLLUP
  • Important System Functions and Metadata. Object Name, IDs
  • Date and Time Functions, Date Format, Styles and DATEDIFF
  • SOUNDEX, DIFFERENCE, CASE, ISNULL, COALESCE Functions
  • CAST, CONVERT, TRY_PARSE, TRY_CONVERT Functions
  • ROW_NUMBER, RANK, DENSE_RANK Functions. Row Sequences
  • PATINDEX, CHARINDEX, RTRIM / LTRIM, REVERSE Functions
  • CASE Statement (with/without Expressions), PIVOT Usage
  • MERGE Statement - MATCHED and NONMATCHED Operations
  • Miscellaneous System Functions and Dynamic Conditions
  • Using Views for Queries and Sub Queries with Functions

REAL-TIME PROJECT (BANKING)

  • Phase 1: Understanding Project Requirement - Banking
  • Phase 1: Database Design with FileGroups, Schemas
  • Phase 1: Table Design with FileGroups, Schemas
  • Phase 1: Defining Constraints, Relations, Synonyms
  • Phase 2: Views for Data Inserts, Joined Queries, newid
  • Phase 2: Common Reporting Functions, User Access
  • Phase 2: Queries for PIVOT, DENSE_RANK, PARTITION BY
  • Phase 2: INSERTS with PIVOT, Calculations, Sub Queries
  • Phase 3: End-to-End Implementation - Data Validations
  • Phase 3: Stored Procedures for Dynamic Data Inserts
  • Phase 3: Updatable Views and Triggers for DML, Indexes
  • Phase 3: DML Operations with PIVOT and Pagination
  • Phase 3: ADVANCED, COMPLEX Stored Procedures in T-SQL
  • Phase 3: DB Documentation Tools, Deployment Options
  • Testing the Database using Visual Studio Tools
  • GO LIVE : ON-PREMISE Deployment
  • Go LIVE : Deployment to Azure SQL Server (For Plan C)
Real-time Case Study 2 : Online Medicare Database (QUERIES)
One - One Mock Interview + Project Evaluation (BANKING)
PART 2 : Advanced Queries and Performance Tuning

Level 1 - Query Tuning

Level 2 - Query Tuning

Chapter 16: QUERY TUNING - CTE, JOIN OPTIONS, STATS

  • Identifying Long Running Queries & Activity Monitor
  • Using Important Dynamic Management Objects (DMV, DMF)
  • Avoiding Self Joins - Real-world Scenarios
  • Avoding Sub Queries and Conditions - Real-world Scenarios
  • Comparing Sub Queries & Joins - Performance Baselines
  • Using CTEs for Memory Based Query Pre-Fetch
  • Query Tuning and Resource Optimization Options
  • STATISTICS - Purpose and Types. Query Tuning Options
  • Column Statistics - Creation and Usage. Advantages
  • Index Statistics - Auto Creation with Indexes, Usage
  • Manual Update of Column Statistics - GUI & Scripting
  • Role of Statistics in Query Tuning Process - Options
  • STATISTICS with Indexes and Query Conditions. Updates
  • LIVE Query Statistics (SQL Server 2016), Table Statistics
  • HASH JOIN - Examples and Precautions. Usage
  • MERGE JOIN - Examples and Precautions. Usage
  • LOOP JOIN - Examples and Precautions. Usage
  • OUTER APPLY, Hybrid and Multi - Level Joins
  • Indexes on Join Options - MERGE and LOOP Joins. Usage
  • Real-world Scenarios @ ERP (LIVE) Database

Chapter 19: PERF. MONITORING, LIVE EXECUTION PLANS

  • Memory Optimized Tables, Optimized Filegroups
  • Memory Snapshot Settings and Real-world Usage
  • Temporal Tables and SYSTEM_VERSIONING
  • Temporal Tables For DML Audits, Performance Impact
  • In-Memory Tables Creation and Index Options
  • Working with Extended Events & Performance Impact
  • LIVE Query Statistics - Monitoring Options, Metrics
  • LIVE Query Statistics - Tracing, and Baselining
  • Collecting and Analyzing Data Using Extended Events
  • Implementing Performance Baseline Methodologies
  • Optimize the file configuration of your databases
  • Use DMVs and gather DB Performance Metrics
  • Memory Tables Versus Temp Tables/Table Variables
  • LIVE Execution Statistics, Hash Plans, Performance
  • Natively Compiled Stored Procedures, Performance
  • Creating System Versioned Temporal Tables
  • Querying and Modifications to Temporal Tables
  • Bulk Inserts, OPENROWSET with Temporal Tables
  • Tuning Bulk Inserts - Recovery Models & Logging
  • Real-world Scenarios @ ERP (LIVE) Database

Chapter 17: PARTITIONS and FULL TEXT SEARCH

  • Big Data - Performance Considerations
  • Table Partitions and Query Tuning Options
  • Partition Functions and Partition Schemes
  • Partition Ranges, Values and Sort Orders
  • Partitioning Un-partitioned Tables using Indexes
  • Aligned / Indexed Partitioning and Performance
  • Data Compression Types - ROW Level, PAGE Level
  • Partition Numbers and Filtered Compression Concepts
  • Managing Partitions and Query Tuning Options
  • LIKE Operator - Limitations. Using Wild-cards
  • Full Text Search (FTS) Configuration Options
  • Full Text Search Service Activation - DB Level
  • Filter Daemon Launcher Service - Purpose, Settings
  • Database Catalogs (FTC) and Storage Locations
  • Full Text (FT) Indexes for Query Tuning
  • Full Text Columns and Primary Key Index
  • Full Text Index For Searching Queries. Issues
  • Full Population and Incremental Population
  • CONTAINS() and FREETEXT() Functions
  • Token Search, Inflectional Forms, Opertors
  • Data Populations and FILESTREAM with FTS
  • FETCH, OFFSET and OUTPUT Options with FTS

Chapter 20: DATABASE TUNING ADVISOR (DTA) TOOL, DOP

  • DTA: Usage, Sequential / Parallel Query Tuning
  • DTA Tool with Profiler, Trace Tables, Cache
  • Understanding Workload Files & Tables in Profiler
  • SQL Profiler Tuning and Tuning Templates
  • Database Tuning Advisor (DTA) - Usage
  • DTA Tool for Procedure Cache, Reent Queries
  • DTA Tool for Multi-Database Connections
  • Understanding PDS Options with Indexes
  • Choosing Correct Option (PDS) for Tuning
  • Resource Governor - Resource Pools - Tuning
  • Resource Workload Groups - Creation, Settings
  • LOW, HIGH, MEDIUM Priority Quries - Resources
  • Classifier Functions, Cost Based Optimization
  • Query Priority, CPU / Memory / IO Limits
  • Windows Fibres, Priority Boost, DOP Options
  • Processor Settings and Counters. Thresholds
  • Recommended Thread Counts and Fibres. Settings
  • CHANGE_TRACKING Options, Limitations
  • BLOB and BULK Operations with DTA Tool
  • DTA Tool - Limitations with Heaps, Transactions
  • OPENROWSET Queries and Correlated Queries
  • Real-world Scenarios @ ERP (LIVE) Database

Chapter 18: INDEXED QUERIES, MISSING INDEXES

  • Index Internals and Execution Plans
  • Understanding Execution Plans, Statistics, Cost
  • Index Fragmentation - Issues, Performance
  • SAMPLED and DETAILED Query Scans. FillFactor
  • Index Rebuilds (Online/Offline), Tuning Options
  • Index Reorganization Process and Advantages
  • Page, Row Compressions with Indexes - Cautions
  • Filtered Indexes, Online Indexes, Indexes Views
  • GAM, SGAM Pages, Metadata Header Info
  • Filtered Indexes and Index Size Limitations
  • Table Statistics & Query Tuning Options
  • Handling Heaps, Clustered, and Nonclustered Indexes
  • Fill Factor, Pad Index and Query Tuning
  • Memory Pages & IO Resources : Query Performance
  • MEMORY LEAKS & PAGE WAITS: Query Performance
  • LATCH WAITS and Query Performance Impact

Chapter 21: PERFMON COUNTERS, DDM

  • PERFMON Counters and PSSDIAG Tools
  • Dynamic Data Masking (DDM) - Performance
  • Secured Column Access - DDM Functions
  • Impersonation Options with Data Masking
  • Index Management Options - SQL 2016, 2017
  • Distributed Replay Controller Tool, SCOM
  • Data Migration Assistant (DMA) Tool
  • PSSDIAG Tool - Performance Monitoring
  • SQL Browser Server - TCP IP, Trace Flags
  • CPU, Thread Management and Windows Fibres
  • Priority Boost Settings and Windows Kernel
  • Working with Machine Code @ SQL Server 2017
  • Non-TSQL queries in SSMS - Performance Benefits
  • Index Management Options - SQL Server 2016
  • New Tuning Options - SQL Server 2017
  • Performance Tuning - Checklist Activities
Real-time Case Study 3 : Implementing Query Tuning Concepts
Real-time Case Study 4 : Tuning an ERP Database (On-Premise)
PART 3 : Azure SQL Database Development & Queries

Azure SQL Database Queries

Azure SQL Database Dev / Programming

Chapter 22: AZURE CLOUD & AZURE SQL DATABASE

  • Introduction to Cloud. Need for Cloud, Advantages
  • Cloud Architecture Basics - Iaas, PasS and SaaS
  • Advantages of Microsoft Cloud - Azure Platform
  • Advantages of Azure SQL Databases & Tools
  • Service Models, Private & Public Clouds
  • Azure SQL & Databases - Need, Importance
  • Azure Sources - Types, Microsoft Market Place
  • Virtual Machines and Azure SQL Database in VMs
  • Comparing Azure with AWS and Google Cloud
  • Azure Cloud Subscription, Azure Portal Options
  • Azure Resources, Marketplace and Dashboards
  • Microsoft Azure Price Tiers & Subscriptions
  • Azure Account Validations and Service Bands
  • Azure SQL Database Architecture Components

Chapter 26: AZURE SQL DATABASE MIGRATIONS

  • Database Scripting Wizard in SSMS
  • Scripting On-Premise Databases in T-SQL
  • Data Migration Assistant (DMA) Tool
  • Schema Generation and Compatibility 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

Chapter 23: AZURE SQL DATABASE DESIGN

  • Azure SQL Database Architecture Components - in detail
  • Resource Pools and Azure Resources for SQL Databases
  • Creating Azure SQL Server Instances
  • Creating Azure SQL Databases, Price Tiers
  • SQL Database – Cloud Database as a Service
  • Subscription Options and Database Sources
  • 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
  • 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

Chapter 27: 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

Chapter 24: AZURE SQL DATABASE CONFIGURATION

  • DTU : Data Transaction Units : Architecture, Pools
  • eDTUs and Elastic Pool, per Database Settings
  • EDTU Cost, eDTU max/min Limits and Performance
  • 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
  • Need for OSM Workspace - Operations Management Suite

Chapter 28 : ADVANCED T-SQL QUERIES IN AZURE

  • Formatting Functions, STRING_SPLIT
  • CONTEXT_INFO and SESSION_CONTEXT
  • Function Determinism - RAND, ABS
  • EXECUTE AS and REVERT Options with SPs
  • Self Contained Sub Queries, ALL, ANY
  • GROUPING SETS with GROUP BY - Usage
  • LEAD and LAG for ROWOFFSET, CHECKSUM
  • Identify Lock Escalation & Deadlocks
  • Identify ways to remediate deadlocks
  • Natively Compiled Stored Procedures
  • Consolidate Overlapping Indexes
  • .NET, PHP, Node.js, Java, Ruby, Python
  • Creating Azure SQL Databases in SSMS Tool
  • T-SQL Scripts for Azure SQL Database

Chapter 25: 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

Chapter 29 : Azure SQL DATABASE - QUERY TUNING

  • Azure SQL Database Configuration Plans
  • Using SQL Data Explorer Tool, Visual Studio
  • Azure Database Programming Concepts
  • Monitor Azure SQL Database performance
  • Monitor Azure SQL Database query plans
  • Determine best practice use cases for extended events
  • Compare the impact of Extended Events and SQLTrace
  • Firewall Settings and Row Level Security
  • Configure Azure SQL Database Performance Insight
  • Design and Implement Elastic Pools for Azure SQL Database
  • Azure SQL Database JSON Features, Data Imports
  • Excel Reporting Options from Azure SQL Database
Real-time Case Study 5 : Scripting & Migrating SQL DB to Cloud
Real-time Case Study 6 : Tuning an ERP Database (in Azure Cloud)
 
MOCK CERTIFICATION - 1 (MCSA : 70-761) + Material + Dump

 

 
MOCK CERTIFICATION - 2 (MCSA : 70-762) + Material + Dump

 

*Above course curriculum applicable for registrations from March 6th, 2018.
 
24x7 LIVE Online Server (Lab) with Real-time Databases. Course includes ONE Real-time Project. Register Today
 
Register Today Other Popular Courses: SQL DBA Training, MSBI Training, SSIS Training, SSAS Training, SSRS Training [+] More Courses

Job-Oriented Real-time Training @ SQL School Training Institute - Trainer: Mr. Sai Phanindra T

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